I gotta believe there's a way to do this, but not clear how. Don't know PostgreSQL internals well enough, however.
Let's say I start a dump of a database at 11:00, and I know said dump takes 5 minutes to complete.
- I have an open transaction (T1) that was created at 10:59 and because [some reason] it doesn't COMMIT till 11:03
- A new transaction (T2) is opened and committed at 11:01
- Another transaction (T3) is created at 11:03 and COMMITs at 11:04
Is there a relatively straight-forward way to capture T1-T3 and include them in the same archive as all of the other committed changes that occurred between the time of my last backup and 11:00? In essence, I want to "loop back" to capture all the activity that occurred between 11-11:05 and save that, too.
Best Answer
No, a
pg_dump
captures the point in time when it started.You seem to want point-in-time-recovery, for which you need
pg_basebackup
and WAL archives.