Test/Verify Log-Shipping Backup 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.
After bringing the slave database cluster into standalone read-write mode (either by internal logic inside recovery_command or by restarting it without recovery.conf) it cannot be easily reverted to be a slave. 

If you can, use file system level snapshots. This would be the easiest approach as you can revert the cluster to a known state without copying whole data directory.

More or less that would be the logic for testing slave:

stop_postgres()
make_pgdata_filesystem_snapshot()
remove_recovery_conf()
start_postgres()
run_testing()
stop_postgres()
revert_pgdata_fs_snapshot()
start_postgres()


If you don't have filesystem snapshots you will need a local copy of the whole cluster (rsync will be good to maintain this). Only disadvantage is disk space used, and more time needed.

rsync_pgdata_to_local_copy()
stop_postgres()
rsync_pgdata_to_local_copy()
remove_recovery_conf()
start_postgres()
run_testing()
stop_postgres()
rsync_pgdata_from_local_copy()
start_postgres()