Sample Header Ad - 728x90

Test/Verify Log-Shipping Backup

0 votes
2 answers
204 views
We recently suffered a power/connectivity outage at a data center housing two PostgreSQL 8.4 secondary servers, that remotely back up our two primaries via log-shipping replication. Backup power kept them alive, but they received no WAL files for a little over two days. The backlog on the two primaries approached 3500 before we got connectivity back again. When I look at the logs in ~postgres/data/pg_log on the two secondaries, I can see in the current log that the first WAL segment to be processed when we got back online is the numerically consecutive very next one after the last one that made it into the the log file from the day of the start of the outage. It *looks* like we didn't miss anything, and the secondaries were caught up in less that a day. But I'd like to verify this. What would most effectively assuage my paranoia would be to break connectivity again so the primaries would begin to buffer up WAL files, promote the secondary machines to primary in isolation, run enough strictly read-only queries to satisfy myself of the integrity of our backup data, and then put the secondaries back in continuous recovery mode and finally restore connectivity. Is that doable? Or would the promotion and processing of queries, even read-only ones, alter the secondary database enough that it could not resume continuous recovery afterwards? I realize that this would not be an issue with streaming replication, but 8.4 does not have that capability.
Asked by Clovis_Sangrail (89 rep)
Apr 11, 2018, 03:54 PM
Last activity: Apr 11, 2018, 05:45 PM