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:
pg_restore
it into a new server (replica #2)recovery.conf -> primary_conninfo
in this new server pointing to the primaryMy 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?
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.