Re: Pulling initial physical replication pg_basebackup from a downstream server

On Wed, Apr 25, 2018 at 09:52:47AM -0500, Scott Frazer wrote:
> I'm changing out a data center and I need to setup a new replicated server.
> The bandwidth speeds between the new data center and the master are slower
> than the speeds between the new data center and the current replica.
>
> Can I get the pg_base_backup from the current replica and then tell the new
> server to connect to the master?

Taking a base backup from a replica is a supported operation. The base
backup taken can then be used to connect to the root primary, even if
that's not the server you took the base backup from. Being part of the
same cluster is enough (same system ID generated at initialization
time).

> Would I need to do anything special to
> keep the master from discarding the transaction logs needed to come up to
> speed?

You could create a physical replication slot on the primary, and then
take a backup after being sure that the standby has fetched enough WAL
to be ahead of the LSN position where the replication slot has been
taken. And finally create a recovery.conf in the new replica so as it
uses the slot created on the master. You will finish by retaining a bit
more WAL than necessary, but a client can request WAL data using a
replication slot which is at least the oldest position. Asking for a
newer position would cause the replication slot to just not retain
anymore the data past the point requested.
--
Michael