postgresqldatabase-replication

does pg_restore + wal_type=replica create a consistent replica?


I have a PostgreSQL server that has a pg_dump scheduled every day at 3 AM (low traffic time window) for backup purposes. These dump files are gzipped and stored at a convenient location. This server also has wal_type=replica and proper user with replication roles already set up, and already a hot stand by replica being synchronized.

I need to create a second replica from this server, and I'd like to know if I can leverage my 3 AM pg_dump backups to avoid executing another pg_dump on the primary. Let's say I do the following:

  1. at 10 AM I grab the 3 AM dump file from the primary server
  2. pg_restore it into a new server (replica #2)
  3. set recovery.conf -> primary_conninfo in this new server pointing to the primary
  4. replica #2 start

My question is, will the replication process pick up at 3 AM (date of the dump file) and replicate all the way to the present time? Or do I have to take any additional precautions?

As a related question: If the above works, does this mean I could potentially grab any dump file from any date, and the wal replication will take care of the elapsed time and bring it up to date?


Solution

  • For streaming replication, the standby server needs to be initialized with a physical copy of the actual database files, as created for example by pg_basebackup. You cannot use a logical backup created by pg_dump to initialize a streaming replication standby server.