Easy Steps to Configure Hot Standby with Logical Replication in PostgreSQL 16

Setting up a hot standby in PostgreSQL 16 using logical replication involves several steps, including configuring the primary and standby servers, setting up the replication slots, and ensuring the replication is working correctly. Here is a step-by-step guide to configuring a hot standby using logical replication in PostgreSQL 16.

Step 1: Configure the Primary Server

1. Install PostgreSQL 16: Ensure PostgreSQL 16 is installed on your primary server. You can download and install it from the official PostgreSQL website or your system’s package manager.

2. Modify the Configuration Files: Edit the postgresql.conf file to enable logical replication and set the necessary parameters.

    wal_level = logical
    max_replication_slots = 10
    max_wal_senders = 10
   

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

    host    replication     replicator      <standby_ip_address>/32    md5
   

3. Create a Replication Role: Create a role for replication on the primary server.

    CREATE ROLE replicator WITH REPLICATION PASSWORD 'yourpassword' LOGIN;
   

Step 2: Set Up the Standby Server

1. Install PostgreSQL 16: Ensure PostgreSQL 16 is installed on your standby server.

2. Initial Data Synchronization: Perform an initial data synchronization from the primary server to the standby server. This can be done using pg_basebackup.

    pg_basebackup -h <primary_ip_address> -D /var/lib/postgresql/16/main -U replicator -v -P --wal-method=stream
   

3. Configure the Standby Server: Edit the postgresql.conf file on the standby server.

    hot_standby = on
   

Step 3: Set Up Logical Replication

1. Create a Publication on the Primary Server: Create a publication for the tables you want to replicate.

    CREATE PUBLICATION my_publication FOR ALL TABLES;
   

2. Create a Subscription on the Standby Server: Create a subscription to the publication from the primary server.

    CREATE SUBSCRIPTION my_subscription CONNECTION 'host=<primary_ip_address> dbname=mydb user=replicator password=yourpassword' PUBLICATION my_publication;
   

Step 4: Verify the Configuration

1. Check the Replication Status: On the standby server, you can check the replication status using:

    SELECT * FROM pg_stat_subscription;
   

2. Verify Data Replication: Insert some data into the primary server’s database and verify that it appears on the standby server.

    -- On Primary Server
    INSERT INTO test_table (column1, column2) VALUES ('value1', 'value2');
   
    -- On Standby Server
    SELECT * FROM test_table;
   

Step 5: Monitoring and Maintenance

1. Monitor Replication Lag: Regularly monitor the replication lag to ensure the standby server is in sync with the primary server.

    SELECT * FROM pg_stat_replication;
   

2. Handle Failover: In case of primary server failure, you can promote the standby server to be the new primary.

    pg_ctl promote -D /var/lib/postgresql/16/main
   

References

• PostgreSQL Documentation on Logical Replication

• Setting Up Logical Replication

• PostgreSQL pg_hba.conf File Documentation

• PostgreSQL CREATE SUBSCRIPTION Documentation

By following these steps, you should be able to set up a hot standby using logical replication in PostgreSQL 16, ensuring high availability and disaster recovery capabilities for your PostgreSQL databases.