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.