Question: How do you configure hot standby replication in PostgreSQL?

Answer

Hot standby replication in PostgreSQL allows a server to act as a read-only replica of the primary database. This setup enhances both high availability and load balancing by allowing the standby server to handle read queries. Here's a guide on how to configure it:

Prerequisites

• Two PostgreSQL servers (primary and standby).
• The same version of PostgreSQL installed on both servers.
• Network connectivity between both servers.

Step 1: Configure the Primary Server

1. Edit postgresql.conf: Locate and edit the postgresql.conf file, usually found in /etc/postgresql/<version>/main/ or /var/lib/pgsql/<version>/data/.

   wal_level = replica
   archive_mode = on
   max_wal_senders = 3
   wal_keep_segments = 64

2. Edit pg_hba.conf: Edit the pg_hba.conf file to allow connections from the standby server.

   host    replication     all             <standby_ip>/32          trust

3. Create a Replication Role: Connect to the primary database and create a role for replication.

   CREATE ROLE replicator REPLICATION LOGIN PASSWORD 'yourpassword';

4. Restart PostgreSQL: Restart the PostgreSQL service to apply the changes.

Step 2: Prepare the Standby Server

1. Stop PostgreSQL: Ensure that the PostgreSQL service is stopped on the standby server.

2. Copy Data from Primary: Use pg_basebackup to copy the data directory from the primary to the standby server.

   pg_basebackup -h <primary_ip> -D /var/lib/pgsql/<version>/data/ -U replicator -vP -W

3. Create recovery.conf: In the data directory on the standby server, create a recovery.conf file with the following content:

   standby_mode = 'on'
   primary_conninfo = 'host=<primary_ip> port=5432 user=replicator password=yourpassword'
   trigger_file = '/tmp/postgresql.trigger.5432'

   Note: For PostgreSQL versions 12 and above, instead of creating a recovery.conf, these settings should be added to the postgresql.conf file, and a standby.signal file should be created to signal that this server is a standby.

4. Start PostgreSQL: Start the PostgreSQL service on the standby server. It will begin replicating from the primary server.

Step 3: Verify Replication

Check the replication status by querying the pg_stat_replication view on the primary server.

SELECT * FROM pg_stat_replication;

You should see the standby server listed, indicating that replication is working successfully.

Conclusion

You now have a basic hot standby replication setup for PostgreSQL. Depending on your environment, further tuning and setup, such as configuring automatic failover with tools like Patroni, may be necessary for full production readiness.