To rebuild a PostgreSQL slave from the master, you will need to take the following steps:
SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = 'replication_database';
Dump the data from the master using pg_dump:
pg_dump -h master_hostname_or_IP -U dump_user -d dump_database -f dumpfile.sql
Import the dumped data into the slave:
psql -h slave_hostname_or_IP -U slave_user -d slave_database -f dumpfile.sql
Set the slave’s new master details, including the master hostname or IP address, the replication user and password, and the WAL log position to which the slave should start replicating:
SELECT pg_create_physical_replication_slot('replication_slot_name');
SELECT pg_start_backup('replication_slot_name', true);
SELECT pg_stop_backup();
SELECT pg_basebackup -D /path/to/data/directory -h master_hostname_or_IP -U replication_user -v -P --xlog-method=stream -S replication_slot_name
Start the slave:
SELECT pg_start_physical_replication(recovery_target_name := 'replication_slot_name');
Check the slave’s status to ensure that it is replicating correctly:
SELECT * FROM pg_stat_replication;
This procedure will rebuild the slave from the master, effectively resetting the slave and starting replication from the current position of the master. It is important to note that this will overwrite any existing data on the slave, so it should only be used if the slave needs to be reset and synchronized with the master.
