PostgreSQL Replication and High Availability

PostgreSQL replication allows you to maintain copies of your database on multiple servers. It is crucial for high availability, disaster recovery, load balancing, and minimizing downtime.

1. Types of Replication

• Streaming Replication: Continuous real-time replication from a primary to one or more standby servers.
• Logical Replication: Replicates data at the table level, allowing selective replication and data transformation.
• Synchronous Replication: Primary waits for standby to confirm writes, ensuring zero data loss.
• Asynchronous Replication: Primary does not wait for standby confirmation, slightly faster but may lose recent transactions if primary fails.

2. Setting Up Streaming Replication

Steps to configure streaming replication:

-- On primary server (postgresql.conf)
            wal_level = replica
            max_wal_senders = 3
            wal_keep_segments = 64
            hot_standby = on

            -- On primary pg_hba.conf (allow replication user)
            host replication replicator 192.168.1.10/32 md5

            -- Create replication user
            CREATE ROLE replicator REPLICATION LOGIN PASSWORD 'StrongPass123';

On standby server:

-- Stop PostgreSQL and take base backup
            pg_basebackup -h primary_host -D /var/lib/postgresql/data -U replicator -P --wal-method=stream

            -- Create recovery.conf (PostgreSQL <= 12) or standby.signal (PostgreSQL 13+)
            standby_mode = 'on'
            primary_conninfo = 'host=primary_host port=5432 user=replicator password=StrongPass123'
            trigger_file = '/tmp/failover.trigger'

3. Logical Replication

Logical replication allows selective replication of tables:

-- On primary: create publication
            CREATE PUBLICATION my_publication FOR TABLE employees, departments;

            -- On standby: create subscription
            CREATE SUBSCRIPTION my_subscription
            CONNECTION 'host=primary_host dbname=mydb user=replicator password=StrongPass123'
            PUBLICATION my_publication;

4. Monitoring Replication

• Check replication status on primary:

  d replication

• Check lag on standby:

  SELECT now() - pg_last_xact_replay_timestamp() AS replication_lag;

• Use pg_stat_replication view for detailed status.

5. High Availability Setup

High Availability (HA) ensures your PostgreSQL cluster remains online in case of failure. Common HA setups include:

• Synchronous streaming replication with automatic failover using tools like Patroni or repmgr.
• Load balancers to distribute read queries to standby servers.
• Regular backups combined with replication for disaster recovery.

6. Failover and Switchover

• Failover: Automatic or manual promotion of standby to primary when primary fails.
• Switchover: Planned promotion of standby to primary with minimal downtime.

7. Best Practices

• Use replication for both disaster recovery and load distribution.
• Monitor replication lag regularly.
• Secure replication connections using SSL.
• Test failover and recovery procedures periodically.
• Combine replication with regular backups to ensure data safety.

Conclusion

PostgreSQL replication and high availability are essential for production-grade systems. Streaming and logical replication, combined with proper monitoring and failover strategies, ensure minimal downtime and data safety. In the next tutorial, we will explore PostgreSQL Extensions and Advanced Features to further enhance database capabilities.