Question: How do you configure replication in PostgreSQL?

Answer

PostgreSQL supports several methods of replication, with the most common being streaming replication. This allows a standby server to stay up-to-date almost in real-time with a master server. Here is a step-by-step guide on how to set up basic streaming replication.

Step 1: Configure the Master Server

  1. Edit the PostgreSQL Configuration File (postgresql.conf):

    • Set wal_level to replica. This includes enough information in the WAL files for the standby servers.
    • Set max_wal_senders to the number of standby servers you will connect. This determines how many concurrent connections can be made to stream WAL changes.
    • Optionally, configure wal_keep_segments to specify the minimum number of past log file segments kept in the pg_xlog directory in case the standby falls behind.
    wal_level = replica max_wal_senders = 3 wal_keep_segments = 64
  2. Modify Client Authentication File (pg_hba.conf):

    • Add a line that allows replication connections from the IP address of each standby server, using an appropriate authentication method.
    host    replication     all             192.168.1.50/32            md5
    
  3. Restart PostgreSQL to apply these changes.

    sudo systemctl restart postgresql

Step 2: Configure the Standby Server

  1. Create a Base Backup:

    • Stop your standby server if it is running.
    • Use pg_basebackup to create a base backup of the master server.
    pg_basebackup -h master_ip -D /var/lib/postgresql/12/main -U replicator -v -P --xlog-method=stream

    Replace master_ip and other parameters according to your environment.

  2. Setup Recovery Configuration (recovery.conf):

    • In the standby server's data directory, create a recovery.conf file. This file tells the server to go into standby mode and follow the master.
    standby_mode = 'on' primary_conninfo = 'host=master_ip port=5432 user=replicator password=your_password' trigger_file = '/tmp/postgresql.trigger.5432'
  3. Start the Standby Server:

    • Now, start your PostgreSQL service on the standby server.
    sudo systemctl start postgresql

Step 3: Verify Replication

Check the replication status by connecting to the master and querying the pg_stat_replication view:

SELECT * FROM pg_stat_replication;

You should see the standby server listed, which indicates that replication is actively happening.

Conclusion

This setup provides a basic configuration for streaming replication in PostgreSQL. For production environments, consider additional setups like using slots, configuring synchronous replication, or setting up automatic failover mechanisms.

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.