In any database setup, ensuring high availability and disaster recovery is crucial for maintaining the integrity and accessibility of data. One of the common practices to achieve this in PostgreSQL is using a master-slave replication architecture. This setup helps in disaster scenarios, where a master database fails, allowing a slave database to be promoted as a master. Here's a detailed process to recover a failed master from a slave in PostgreSQL.

Understanding Master-Slave Replication

Before diving into the recovery process, it's crucial to understand how master-slave replication works in PostgreSQL. In this setup:

• Master Database: The primary server that handles data modifications.
• Slave Database: Receives data updates from the master through a continuous replication process, often using the built-in streaming replication feature.

Process for Promoting a Slave to Master

Step 1: Verify the Slave Status

Ensure that the slave database is fully synchronized with the master before promotion. Check the replication lag, which indicates the current state of synchronization.

1SELECT
2  client_addr,
3  state,
4  sent_lsn AS master_lsn,
5  replay_lsn AS slave_lsn,
6  (pg_catalog.pg_size_pretty(pg_catalog.pg_xlog_location_diff(sent_lsn, replay_lsn))) AS replication_lag
7FROM
8  pg_stat_replication;

Step 2: Perform a Failover

Once you are confident about the slave's readiness:

1. Promote the Slave: Execute the pg_ctl command to promote the slave to a master.

   pg_ctl promote -D /var/lib/pgsql/data

Alternatively, you can use the following SQL command if you have configured trigger_file:

   SELECT pg_wal_replay_pause();

1. Verify the Promotion: Make sure the database is now the master by checking the server's role.

SELECT pg_is_in_recovery();

A result of false implies that the server is now the master.

Step 3: Reconfigure the Failed Master as a Slave

1. Shutdown the Old Master: Ensure the old master is shut down to prevent any data inconsistencies:

   pg_ctl stop -D /var/lib/pgsql/data

1. Reinitialize the Old Master Server Data: Assuming data synchronization, reinitialize the database with the base backup from the new master.

   pg_basebackup -h new_master_host -D /var/lib/pgsql/data -P -U replica_user --write-recovery-conf

1. Configure the Recovery Settings: Review and modify the recovery.conf file as needed to perform to act as a slave.

Step 4: Restart Database Services

After configuring, restart the services:

pg_ctl start -D /var/lib/pgsql/data

Step 5: Verify the Setup

Ensure the old master is now functioning as a slave. Use replication monitoring queries:

SELECT * FROM pg_stat_replication;

Check for the presence of the old master as a replication client.

Key Points Summary

Final Thoughts

Database failover and recovery processes are crucial in high-availability setups. While the procedure outlined targets PostgreSQL versions generally supporting streaming replication, some refinements may vary based on the configuration. Always ensure a robust monitoring system is in place to detect the state and health of the database cluster in real-time.