We've contemplated adding support for something like this to pgbackrest,
since all the pieces are there, but there hasn't been a lot of demand
for it and it kind of goes against the idea of having a proper backup
solution, really.. It'd also create quite a bit of load on the primary
to checksum all the files to do the comparison against what's on the
replica that you're trying to update, so not something you'd probably
want to do a lot more than necessary.
Ok, we want to use pgbackrest to rebuild a standby that has fallen behind (where pg_rewind won't work). After reading the docs, we believe we should use this setup:
a) Primary host: primary cluster
b) Repository host: needed for rebuilding the standby (and having PITR as bonus).
c) Standby host: standby cluster
Some questions:
1) The standby will use streaming replication and will be in sync until someday something funny happens and both standby and repository get out of sync with the primary.
Now, to rebuild the standby first we will have to create a new backup transferring the data from primary -> repository, right?
Wouldn't this also have a load impact on the primary cluster?
2) In the user guide section 17.3 is explained how to create a "pg-standby host" to replicate the data from the repository host.
And in section 17.4 is explained how to setup Streaming Replication to replicate the data from the primary host.
Do 17.3 and 17.4 work together so that the data is replicated from the repository and then streamed from the primary?
3) Before being able to rebuild the standby cluster, would we first need to update the backup on the repository (backup from primary -> repository) in order for streaming replication to work (from primary -> standby)?
4) Once the backup on the repository is ready, what are the chances that streaming replication from primary to standby won't work because they got out of sync again?
5) Could we just work with 2 hosts (primary and standby) instead of 3?
FAQ section 8 says the repository shouldn't be on the same host as the standby and having it on the primary doesn't make much sense because if the primary host is down we won't have access to the backup.
It would be ideal to have the repository on the standby host and taking good care of the configurations. What exactly should be cared of for this setup to be safe?
I'm afraid I'm not understanding very well the pgbackrest design or how to use it efficiently to rebuild a standby cluster that got out of sync.
