How to find out when data was inserted to Postgres?

Question

I have inherited an existing Postgres database full of data. Most of the data has a 'created_date' column value. Some of the earlier data was inserted before this was being tracked.

Is there a Postgres metadata table hidden away somewhere that tracks when INSERT queries were done?

Answer 1

Postgres 9.5 or later

You can enable track_commit_timestamp in postgresql.conf (and restart) to start tracking commit timestamps. Then you can get a timestamp for your xmin. Related answer:

• Atomically set SERIAL value when committing transaction

Postgres 9.4 or older

There is no such metadata in PostgreSQL unless you record it yourself.

You may be able to deduce some information from the row headers (HeapTupleHeaderData), in particular from the insert transaction id xmin. It holds the ID of the transaction in which the row was inserted (needed to decide visibility in PostgreSQL's MVCC model). Try (for any table):

SELECT xmin, * FROM tbl LIMIT 10;

Some limitations apply:

• If the database was dumped and restored then, obviously, the information is gone - all rows are inserted in the same transaction.
• If the database is huge / very old / very heavily written, then it may have gone through transaction ID wraparound, and the order of numbers in xmin is ambiguous.

But for most databases you should be able to derive:

• the chronological order of INSERTs
• which rows were inserted together
• when there (probably) was a long period of time between inserts

No timestamp, though.

Answer 2

Building on Erwin Brandstetter's answer, if you have PostgreSQL 9.5 or later, the timestamps of commits are being recorded in the write-ahead log all the time, even if track_commit_timestamp is off. They are recorded there to support point-in-time recovery, where you can roll the database to an exact past state that you can specify as a date and time.

What you get by turning track_commit_timestamp on is an easier way to retrieve that information, where you can simply query with

SELECT pg_xact_commit_timestamp(xid);

where xid is the xmin from the row you care about, and it gives you the timestamp.

That's convenient, but it only works if:

• track_commit_timestamp is on
• it was on when the transaction committed
• the transaction ID is not far enough in the past to be 'frozen'.

(PostgreSQL controls the overhead of remembering transaction IDs forever, by eventually 'freezing' old ones. That also controls how far the track_commit_timestamp-dependent functions can look back. There is another setting, vacuum_freeze_max_age, for adjusting that.)

So what do you do if you need the timestamp for a transaction that happened before you turned on track_commit_timestamp?

As long as it happened in PG 9.5 or later, the timestamp is in the write-ahead log. If you have been keeping backups sufficient for point-in-time recovery, that gives you a crude way to find the answer: you can restore a base backup from before you think it happened, set a recovery 'pause' target timestamp near where you guess it happened, connect when it pauses and query to see if it happened yet. If not, set a slightly later target, let the recovery continue, and check again. This can all be done using the backups in another PostgreSQL instance, to avoid interfering with one running production.

That is a clumsy-enough procedure you might wish you could just go back in time and tell your former self to turn track_commit_timestamp on, so it would have been on when the transaction happened that you are interested in. You can turn on track_commit_timestamp before starting the server to recover from a backup, but that doesn't quite do the trick: if it was turned off at the time of the backup, it will only begin saving timestamps for new transactions, after the ones it recovers.

It turns out it is possible to fool PostgreSQL into thinking track_commit_timestamp was on, and then start the server in recovery, and that has much the desired effect: as it replays transactions from the write-ahead log, it does remember their timestamps, and you can then use pg_xact_commit_timestamp() to query them. It will not have timestamps for anything that was in the base backup, but only for the transactions that followed the base backup and were replayed from the WAL. Still, by choosing a base backup known to be earlier than the wanted transaction, this allows the timestamp to be recovered.

There is no official tool/option to 'retroactively' set track_commit_timestamp in this way, but the (fiddly and unsupported) proof-of-concept has been discussed on pgsql-hackers.