Luego de investigar con especialistas en esta materia, programadores de varias ramas y profesores hemos dado con la solución al dilema y la plasmamos en esta publicación.
Solución:
Postgres 9.5 o posterior
Puedes habilitar track_commit_timestamp en postgresql.conf (y reinicie) para comenzar a rastrear las marcas de tiempo de confirmación. Luego puede obtener una marca de tiempo para su xmin. Respuesta relacionada:
- Establecer atómicamente el valor SERIAL al confirmar la transacción
Postgres 9.4 o anterior
No existen tales metadatos en PostgreSQL a menos que los registre usted mismo.
Usted puede ser capaz de deducir algunos información de los encabezados de fila (HeapTupleHeaderData), en particular de la ID de transacción de inserción xmin. Contiene la ID de la transacción en la que se insertó la fila (necesaria para decidir la visibilidad en el modelo MVCC de PostgreSQL). Prueba (para cualquier tabla):
SELECT xmin, * FROM tbl LIMIT 10;
Se aplican algunas limitaciones:
- Si la base de datos se volcó y restauró, obviamente, la información se ha ido: todas las filas se insertan en la misma transacción.
- Si la base de datos es enorme/muy antigua/muy escrita, es posible que haya pasado por el ajuste de ID de transacción y el orden de los números en
xmines ambiguo.
Pero para la mayoría de las bases de datos, debería poder derivar:
- el orden cronológico de los INSERTOS
- qué filas se insertaron juntas
- cuando hubo (probablemente) un largo período de tiempo entre inserciones
Sin embargo, no hay marca de tiempo.
track_commit_timestamp (booleano)
Se utiliza principalmente en el momento de la configuración del servidor de replicación. Registrar el tiempo de compromiso de las transacciones. Este parámetro sólo se puede configurar en
postgresql.confarchivo o en la línea de comandos del servidor. El valor predeterminado es apagado.
Sobre la base de la respuesta de Erwin Brandstetter, si tiene PostgreSQL 9.5 o posterior, las marcas de tiempo de las confirmaciones se registran en el registro de escritura anticipada todo el tiempo, incluso si track_commit_timestamp esta apagado. Se registran allí para respaldar la recuperación de un punto en el tiempo, donde puede llevar la base de datos a un estado pasado exacto que puede especificar como fecha y hora.
Lo que obtienes al girar track_commit_timestamp on es una forma más fácil de recuperar esa información, donde simplemente puede consultar con
SELECT pg_xact_commit_timestamp(xid);
donde xido es el xmin de la fila que le interesa y le proporciona la marca de tiempo.
Eso es conveniente, pero solo funciona si:
track_commit_timestampEstá encendido- fue cuando se comprometió la transacción
- la identificación de la transacción no está lo suficientemente lejos en el pasado para ser ‘congelada’.
(PostgreSQL controla la sobrecarga de recordar los ID de transacciones para siempre, eventualmente “congelando” los antiguos. Eso también controla hasta qué punto el track_commit_timestamp-Las funciones dependientes pueden mirar hacia atrás. Hay otro escenario, vacuum_freeze_max_agepara ajustar eso.)
Entonces, ¿qué hace si necesita la marca de tiempo para una transacción que ocurrió antes de que encendiera track_commit_timestamp?
Siempre que sucediera en PG 9.5 o posterior, la marca de tiempo se encuentra en el registro de escritura anticipada. Si ha estado manteniendo suficientes copias de seguridad para la recuperación en un momento dado, eso le brinda una forma aproximada de encontrar la respuesta: puede restaurar una copia de seguridad base antes de que crea que sucedió, establecer una marca de tiempo objetivo de “pausa” de recuperación cerca de donde supongo que sucedió, conéctese cuando se detenga y consulte para ver si sucedió todavía. De lo contrario, establezca un objetivo un poco más tarde, deje que la recuperación continúe y verifique nuevamente. Todo esto se puede hacer usando las copias de seguridad en otra instancia de PostgreSQL, para evitar interferir con una producción en ejecución.
Ese es un procedimiento lo suficientemente torpe como para desear poder retroceder en el tiempo y decirle a su antiguo yo que se vuelva. track_commit_timestamp encendido, por lo que habría estado encendido cuando ocurrió la transacción que le interesa. Usted puede encender track_commit_timestamp antes de iniciar el servidor para recuperarse de una copia de seguridad, pero eso no funciona del todo: si se apagó en el momento de la copia de seguridad, solo comenzará a guardar marcas de tiempo para nuevas transacciones, después de las que recupera.
Resulta que es posible engañar a PostgreSQL para que piense track_commit_timestampera encendido, y luego inicie el servidor en recuperación, y eso tiene el efecto deseado: a medida que reproduce transacciones del registro de escritura anticipada, recuerda sus marcas de tiempo, y luego puede usar pg_xact_commit_timestamp() para consultarlos. No tendrá marcas de tiempo para nada que estuviera en la copia de seguridad base, sino solo para las transacciones que siguieron a la copia de seguridad base y se reprodujeron desde el WAL. Aún así, al elegir una copia de seguridad base que se sabe que es anterior a la transacción deseada, esto permite recuperar la marca de tiempo.
No hay una opción/herramienta oficial para configurar ‘retroactivamente’ track_commit_timestamp de esta manera, pero la prueba de concepto (difícil y sin respaldo) se ha discutido en pgsql-hackers.