hswaw

implr: did you figure out when TBD is? I'd like to start merging some stuff.

The last mtime in 12's pg_wal directory is Jan 27 21:16, so probably this. We could check logs for when the reboot exactly happened, but those have been rotated out already, so would have to check backups and meeeeh doesn't seem worth the effort

postgres 12 configured to listen on port +1 (5433), you can connect via unix socket with -U postgres -p 5433

For purposes of finding any data that is present in PG12 but missing in PG9 due to rollback we need to also have some well-defined timestamps. These timestamps can be used to determine whether a dated object within PG12 but missing from PG9 is due to a creation-and-less and not having been since removed from PG9.

Ie., if an object is found in PG12 it is assumed to have been lost due to rollback if it's missing from PG9 and its timestamp lies between the following two values:

>>> datetime(year=2021, month=1, day=25).timestamp()
1611529200.0
>>> datetime(year=2021, month=1, day=28).timestamp()
1611788400.0

Or, for your scripts:

incident_start = 1611529200 # when pg12 started to run
incident_end = 1611788400 # when we rolled back to pg9

I'm assuming these more conservatively than the above estimate. All cases of data lost due to the rollback will be manually verified before any action is taken, so it's better to have false positives than false negatives.

9.6 was ancient, out of support, and thus removed. I did a 9.6 -> 14 pg_upgrade (this time taking care to kill 9.6 for good).
The lost data in 12 is still there - just think "14" every time you see "9.6" in this issue.