0

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.

1

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()
  • Thanks for your reply! From what you describe, it seems like I'd be better off taking a new base backup and starting replication anew if I have worries about the secondary as it stands now. – Clovis_Sangrail Apr 11 '18 at 18:01
  • @Clovis_Sangrail depends on database size and network speed, if it's small enough and close enough (same datacenter) - you can reinitialize slaves as frequent as you like :-) – filiprem Apr 11 '18 at 18:04
1

The replay process is not going to just skip log files. If it can't find what it needs, it will not proceed, unless someone did something drastic like running pg_resetxlog or manually editing binary files.

Once you promote a standby, you can not demote it again. In newer versions, there is pg_rewind, but I wouldn't use even that without extensive first-hand testing, and you seem to be even more risk-averse than I am.

What you can do pretty easily is just clone the standby, and promote the clone. Then discard it when done testing. All you need is to temporarily allocate a machine and storage. But really, I don't think you need to do anything. There is always the possibility of corruption, but corruption due to this particular spell of connectivity interruption would be way down my list of worries. Much lower than the fact you are running a version which is 4 years after EOL.

  • We are running a pair of 9.6.6 servers as a development environment with streaming replication, and it is much easier to satisfy oneself that the backup in fact has your data. It's hard to keep up with those PostgreSQL folks, they keep bringing out new versions! :) I appreciate that v8.4 needs to go, But I'm thinking I should first bring the development environment up to 10.3 just to familiarize myself, and then migrate to 10.3 in production. – Clovis_Sangrail Apr 11 '18 at 18:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.