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:

```bash
sudo vim /var/lib/pgsql/data/postgresql.conf
```

2. Ensure that the settings allow connections and enable WAL archiving:

```conf
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:

```bash
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:

```bash
sudo systemctl restart postgresql
```

5. Create a replication user:

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

Step 2: Configure the Standby Server

1. Stop PostgreSQL:

```bash
sudo systemctl stop postgresql
```

2. Remove the existing data directory:

```bash
sudo rm -rf /var/lib/pgsql/data/*
```

3. Use pg_basebackup to clone the primary server:

```bash
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:

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

Insert the following lines, adjusting parameters as necessary:

```conf
standby_mode = 'on'
primary_conninfo = 'host=primary_ip port=5432 user=replicator password=yourpassword'
trigger_file = '/tmp/postgresql.trigger'
```

5. Start PostgreSQL:

```bash
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.