0

Im writing a script to restore a postgres standby server that was out of sync with the master node for a while. I would like the following behavior

  1. Check if i can go with pg_rewind
  2. If pg_rewind fails, then use pg_basebackup

The big deal for me is about the pg_rewind because even if pg_rewind succeeds, it does not guarantee that the standby server will start successfully. In some cases the standby server needs some old wal file of the master (which can be provided using restore_command) and if it is not found, you get this : ERROR: requested WAL segment 00000002000000050000007C has already been removed

So i would like, in addition to pg_rewind successful response, to find a way to ensure that every wal file that will be required on server startup is already available before starting the server. And if it is not available, I continue directly with the second option (perform a full backup with pg_basebackup)

Any help ...

1 Answer 1

0

restore_command should read files from the archive, and it is your responsibility not to delete archived WAL segments you still need for catching up or pg_rewind. So with a WAL archive and a correctly configured restore_command, this will not happen.

The alternative is to use replication slots with replication. You have to set primary_slot_name on the standby to the name of the replication slot you created. When pg_rewind fails, take the backup with

pg_basebackup --slot=slotname ...

Then you can be certain that the primary will retain all the WAL the standby needs. The risk is that the primary can run out of disk space if the standby stays down for too long, so you had better set max_slot_wal_keep_size on the primary to prevent that.

2
  • I understand your recommendations. but the problem is that we can't keep the wal files indefinitely since it takes up space. in my case the repository of archived wal files is configured as a FIFO Cache not exceeding 1Gb. therefore I cannot guarantee the availability of wal files at all times Nov 9, 2023 at 0:04
  • 1GB is ridiculously small. Nov 9, 2023 at 6:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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