pgbackrest "Cold-Standby" configuration

[blowoffsteam81]

Hello,

our main psql server is running regular full/differential backups with pgbackrest to an NFS share, located on a NAS.
I have a "cold-standby" server ready to boot, in case the primary server has a hardware failure.

The NFS share, where the pgbackrest backups are located is mounted on both machines so i can easily switch over from the primary to the secondary server, by just utilizing pgbackrest restore. This works fine so far and seems to be the better approach for us instead of running a "hot-standy" all the time. (having a little bit of downtime, once the main server fails but saving some energy so that another server doesn`t need to run 24/7 seems a fair trade-off for us)

The only thing that is missing is a proper backup / wal archiving workflow, once the backup server needs to take over and i was thinking about the best approach for this. Right now we have two repositories with a single stanza defined in pgbackrest.conf.
One is the already mentioned (local) NFS share, the other one is a S3 repository for offsite backups.

Lets say i have to switch over to the backup psql server and my colleagues are working a few days over that machine, then i would obviously need wal archiving/pgbackrest backups from that machine instead of the main one.
I was thinking about creating a separate stanza, just for the backup machine (lets call it secondary). That way i could pull backups
from the main stanza to the backup server, to get the latest state of postgresql quickly up and running on the cold-standby, while at the same time writing backups to the secondary stanza by switching the db-path from main to secondary in pgbackrest.conf.

I could also create a 3rd repository that is only used from the backup psql server. Of course, i would like to avoid that the wal files from server1 are mixed with the wal files from server2 (i would assume that invites problems, right) and as far as i know, pgbackrest tries to push the wal logfiles to every repository by default.

So which approach is better in your opinion or is there an even better way entirely?

Thank you. :)

[pgstef]

Hi,

You won't need a separated stanza nor another repo imo. Pushing wal files/ backups from server2 into the main repository would be an issue if that was a "test" server, not if those are real production data.

You mention that you want to switch over to use server2 as your new primary. The first step in every DR scenario should be to avoid split-brain (writing data in 2 places), so that your "old" primary is effectively broken/disconnected.

I don't really see why using a cold standby would be very much different than using a hot standby (except you won't start it nor try to take a backup from the hot standby server) 🤔

[blowoffsteam81]

You won't need a separated stanza nor another repo imo. Pushing wal files/ backups from server2 into the main repository would be an issue if that was a "test" server, not if those are real production data.

By that you probably mean that in a test server scenario, its is more likely that both (the main and backup server) are writing wal files to the same repository at the same time, which is something i absolutely need to avoid if i understand it correctly, right?

You mention that you want to switch over to use server2 as your new primary. The first step in every DR scenario should be to avoid split-brain (writing data in 2 places), so that your "old" primary is effectively broken/disconnected.

Maybe i am overthinking this but i thought it could cause potential problems if due to a hardware failure, the main server goes offline, i need to boot up the backup server, restore my database(s) via pgbackrest to the latest wal file and from there continue to write backups and wal files to the same repo as the main server did. So basically, thats is not a problem as long as i avoid, that both servers are doint that at the same time, is that correct?

I don't really see why using a cold standby would be very much different than using a hot standby (except you won't start it nor try to take a backup from the hot standby server) 🤔

Well, the hot standby server would need to be up and running 24/7, so that it can be kept up2date with the primary serer as soon as possible, right? With a cold-standby (i know its not an official psql term) i would only boot it up, if there is an actual problem with primary. So in the meantime, it would save some energy costs, cause as long as primary is working properly, the cold-standby can be offline. Hope that makes sense.

[pgstef]

By that you probably mean that in a test server scenario, its is more likely that both (the main and backup server) are writing wal files to the same repository at the same time, which is something i absolutely need to avoid if i understand it correctly, right?

Indeed. You don't want to push archives/backups from a "test" server into your production repo. Actually, you could test a recovery by adding --archive-mode=off to the restore command to disable archiving on the test server.

Maybe i am overthinking this but i thought it could cause potential problems if due to a hardware failure, the main server goes offline, i need to boot up the backup server, restore my database(s) via pgbackrest to the latest wal file and from there continue to write backups and wal files to the same repo as the main server did. So basically, thats is not a problem as long as i avoid, that both servers are doint that at the same time, is that correct?

As long as you only have 1 source of truth (1 active RW server), you'd be fine to use the same repo imo.

Well, the hot standby server would need to be up and running 24/7, so that it can be kept up2date with the primary serer as soon as possible, right? With a cold-standby (i know its not an official psql term) i would only boot it up, if there is an actual problem with primary. So in the meantime, it would save some energy costs, cause as long as primary is working properly, the cold-standby can be offline. Hope that makes sense.

Yes, it makes sense to save up some energy if you can afford the downtime. My thinking was more about the pgBackRest config. Imo, using a cold-standby would probably be the same as using a hot-standby as long as you don't need to take a "backup from standby".

So, using the same stanza/repositories for both your current primary and cold-standby should be fine as long as you have a way to prevent slit-brain ;-)

[blowoffsteam81]

Thank you for giving me reassurance. I appreciate the time. :)