Question: How do you set up PostgreSQL replication with multiple slaves?

Answer

Setting up PostgreSQL replication with multiple slaves involves configuring one or more standby servers to replicate data from a primary server. This setup can enhance read performance and provide high availability. Here's a comprehensive guide:

Prerequisites:

• A running PostgreSQL instance on the primary server.
• Similar PostgreSQL version installations on slave servers.
• Network connectivity between primary and slaves.

Step 1: Configure the Primary Server

Edit the postgresql.conf file on your primary server:

listen_addresses = '*' # Listen on all interfaces
wal_level = replica # Required for replication
max_wal_senders = 3 # Adjust based on the number of slaves
archive_mode = on # Enable archiving
archive_command = 'cp %p /path/to/archive/%f' # Archive command

Enable client authentication by editing pg_hba.conf:

host replication all 192.168.1.0/24 md5 # Allow replication connections

Restart PostgreSQL to apply changes:

sudo systemctl restart postgresql

Step 2: Create a Replication User

On the primary, create a user dedicated to replication:

CREATE ROLE replicator REPLICATION LOGIN PASSWORD 'password';

Step 3: Prepare Slave Servers

On each slave, stop PostgreSQL:

sudo systemctl stop postgresql

Ensure the data directory is empty, then clone the primary's database cluster using pg_basebackup:

pg_basebackup -h primary_host -D /var/lib/postgresql/10/main -U replicator -P -v -X stream

Configure each slave by creating a recovery.conf file in the PostgreSQL data directory:

standby_mode = 'on'
primary_conninfo = 'host=primary_host port=5432 user=replicator password=password'

Start PostgreSQL on each slave:

sudo systemctl start postgresql

Step 4: Verify Replication

Check the replication status on the primary by querying the pg_stat_replication view:

SELECT * FROM pg_stat_replication;

You should see an entry for each slave server, indicating successful replication setup.

Conclusion

This guide provides a basic approach to setting up PostgreSQL replication with multiple slaves. Depending on your environment and requirements, further customization and tuning might be necessary.