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.

Was this content helpful?

White Paper

Free System Design on AWS E-Book

Download this early release of O'Reilly's latest cloud infrastructure e-book: System Design on AWS.

Free System Design on AWS E-Book
Start building today

Dragonfly is fully compatible with the Redis ecosystem and requires no code changes to implement.