Debian PostgreSQL Database Replication Technologies

PostgreSQL on Debian supports several replication technologies to achieve high availability, data redundancy, and workload distribution. Below are the primary methods, their configurations, and use cases:

1. Physical Replication (Streaming Replication)

Overview: A block-level, instance-wide replication method that copies WAL (Write-Ahead Logging) files from a primary (master) server to standby (slave) servers. The standby server replays WALs to stay synchronized with the primary. It is ideal for high availability and disaster recovery.

Configuration Steps:

• On the Primary Server:

  • Edit postgresql.conf to enable replication:

    listen_addresses = '*'  # Allow connections from all IPs
    wal_level = replica     # Enable WAL archiving for replication
    max_wal_senders = 10    # Allow up to 10 replication clients
    wal_keep_segments = 64  # Retain 64 WAL segments (adjust as needed)
    hot_standby = on        # Enable read-only queries on standby (optional)
    

  • Edit pg_hba.conf to allow standby connections:

    host    replication     replicator     <standby_ip>/32    md5
    

  • Restart PostgreSQL: sudo systemctl restart postgresql.
  • Create a replication user:

    CREATE USER replicator WITH REPLICATION PASSWORD 'secure_password' LOGIN;
    

• On the Standby Server:

  • Use pg_basebackup to create a base backup from the primary:

    pg_basebackup -h <primary_ip> -D /var/lib/postgresql/<version>/main -U replicator -P -R
    

    The -R flag auto-generates a recovery.conf file (PostgreSQL 12+ uses postgresql.auto.conf).
  • Start the standby server: sudo systemctl start postgresql.

• Verification:
  Check replication status on the primary:

  SELECT * FROM pg_stat_replication;
  

  This shows connected standbys and their replication lag.

2. Logical Replication

Overview: A table-level, selective replication method that decodes WAL logs into logical changes (e.g., INSERTs/UPDATEs/DELETEs) and applies them to subscribers. It supports cross-version sync, partial table replication, and is ideal for reporting databases or migrating data between versions.

Configuration Steps:

• On the Publisher (Primary):

  • Edit postgresql.conf to enable logical replication:

    wal_level = logical       # Required for logical decoding
    max_replication_slots = 10 # Slots for subscribers (>= number of subscriptions)
    max_wal_senders = 10      # Allow WAL senders for subscribers
    

  • Create a publication for target tables:

    CREATE PUBLICATION my_pub FOR TABLE my_table; -- Single table
    -- OR for all tables:
    CREATE PUBLICATION my_pub FOR ALL TABLES;
    

• On the Subscriber (Standby):

  • Ensure postgresql.conf allows replication:

    max_replication_slots = 10 # Match publisher’s max_replication_slots
    max_logical_replication_workers = 10 # Workers for applying changes
    

  • Create a subscription to connect to the publisher:

    CREATE SUBSCRIPTION my_sub 
    CONNECTION 'host=<primary_ip> dbname=my_db user=replicator password=secure_password' 
    PUBLICATION my_pub;
    

• Key Notes:

  • Published tables must have a replica identity (primary key, unique index, or FULL for full-row replication).
  • DDL changes (e.g., table alters) are not replicated; manual intervention is required on subscribers.

3. Third-Party Tools for Enhanced Replication

Overview: Tools that simplify replication management, add high availability, or support complex topologies. Common tools include:

• Patroni: A cluster manager for PostgreSQL that automates failover, leader election, and configuration management. It integrates with etcd or ZooKeeper to ensure high availability.
• Pgpool-II: Provides load balancing, connection pooling, and failover for read-heavy workloads. It can route read queries to standbys and write queries to the primary.
• Barman: A backup and recovery tool that supports WAL archiving, incremental backups, and point-in-time recovery (PITR). It integrates with cloud storage (e.g., S3) for durable backups.
• pg_rewind: A utility to resynchronize a standby server with a primary after a failure (e.g., network partition). It uses WAL logs to identify and apply missing changes, reducing downtime compared to full restores.

Choosing the Right Replication Method

• Physical Replication: Best for high availability (e.g., standby servers) where data consistency and minimal lag are critical.
• Logical Replication: Ideal for partial table sync (e.g., reporting databases), cross-version upgrades, or when fine-grained control over replicated data is needed.
• Third-Party Tools: Enhance physical/logical replication with features like automated failover, load balancing, and simplified management.

Each method has trade-offs in complexity, performance, and flexibility. Select based on your workload (e.g., OLTP vs. OLAP), high availability requirements, and operational expertise.