Question: How can you set up PostgreSQL replication on CentOS 7?

Answer

Setting up PostgreSQL replication on CentOS 7 involves configuring a primary server (master) and one or more standby servers (slaves). This setup ensures data redundancy and increases the availability of your database system. The following steps outline how to configure streaming replication.

Prerequisites

  • Two CentOS 7 servers with PostgreSQL installed.
  • Network connectivity between both servers.
  • Sudo privileges on both servers.

Step 1: Configure the Primary Server

  1. Edit the postgresql.conf file:

    sudo vim /var/lib/pgsql/data/postgresql.conf
  2. Ensure that the settings allow connections and enable WAL archiving:

    listen_addresses = '*' wal_level = replica max_wal_senders = 3 wal_keep_segments = 64 archive_mode = on archive_command = 'cp %p /path/to/archive/%f'
  3. Edit pg_hba.conf to allow the standby server to connect:

    sudo vim /var/lib/pgsql/data/pg_hba.conf

    Add the following line, replacing standby_ip with the IP address of your standby server:

    host replication all standby_ip/32 trust
    
  4. Restart PostgreSQL:

    sudo systemctl restart postgresql
  5. Create a replication user:

    sudo -u postgres psql -c "CREATE USER replicator REPLICATION LOGIN ENCRYPTED PASSWORD 'yourpassword';"

Step 2: Configure the Standby Server

  1. Stop PostgreSQL:

    sudo systemctl stop postgresql
  2. Remove the existing data directory:

    sudo rm -rf /var/lib/pgsql/data/*
  3. Use pg_basebackup to clone the primary server:

    sudo -u postgres pg_basebackup -h primary_ip -D /var/lib/pgsql/data -U replicator -vP -W

    Replace primary_ip with the IP address of your primary server.

  4. Create a recovery.conf file in the data directory:

    sudo vim /var/lib/pgsql/data/recovery.conf

    Insert the following lines, adjusting parameters as necessary:

    standby_mode = 'on' primary_conninfo = 'host=primary_ip port=5432 user=replicator password=yourpassword' trigger_file = '/tmp/postgresql.trigger'
  5. Start PostgreSQL:

    sudo systemctl start postgresql

Step 3: Verify Replication

Check the replication status by running the following on the primary server:

sudo -u postgres psql -c "select * from pg_stat_replication;"

If configured correctly, you'll see information about the standby server(s) connected to your primary server.

Conclusion

You've now set up basic streaming replication for PostgreSQL on CentOS 7. This setup provides a robust foundation for ensuring data availability and redundancy. For high availability setups, consider exploring additional tools like repmgr or Patroni for automatic failover and management.

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.