[Answered] How do you configure replication in PostgreSQL?

Answer

PostgreSQL supports several methods of replication, with the most common being streaming replication. This allows a standby server to stay up-to-date almost in real-time with a master server. Here is a step-by-step guide on how to set up basic streaming replication.

Step 1: Configure the Master Server

Edit the PostgreSQL Configuration File (postgresql.conf):
- Set wal_level to replica. This includes enough information in the WAL files for the standby servers.
- Set max_wal_senders to the number of standby servers you will connect. This determines how many concurrent connections can be made to stream WAL changes.
- Optionally, configure wal_keep_segments to specify the minimum number of past log file segments kept in the pg_xlog directory in case the standby falls behind.
```
wal_level = replica
max_wal_senders = 3
wal_keep_segments = 64
```
Modify Client Authentication File (pg_hba.conf):
- Add a line that allows replication connections from the IP address of each standby server, using an appropriate authentication method.
```
host    replication     all             192.168.1.50/32            md5
```
Restart PostgreSQL to apply these changes.
```
sudo systemctl restart postgresql
```

Step 2: Configure the Standby Server

Create a Base Backup:
- Stop your standby server if it is running.
- Use pg_basebackup to create a base backup of the master server.
```
pg_basebackup -h master_ip -D /var/lib/postgresql/12/main -U replicator -v -P --xlog-method=stream
```
Replace master_ip and other parameters according to your environment.
Setup Recovery Configuration (recovery.conf):
- In the standby server's data directory, create a recovery.conf file. This file tells the server to go into standby mode and follow the master.
```
standby_mode = 'on'
primary_conninfo = 'host=master_ip port=5432 user=replicator password=your_password'
trigger_file = '/tmp/postgresql.trigger.5432'
```
Start the Standby Server:
- Now, start your PostgreSQL service on the standby server.
```
sudo systemctl start postgresql
```

Step 3: Verify Replication

Check the replication status by connecting to the master and querying the pg_stat_replication view:

SELECT * FROM pg_stat_replication;

You should see the standby server listed, which indicates that replication is actively happening.

Conclusion

This setup provides a basic configuration for streaming replication in PostgreSQL. For production environments, consider additional setups like using slots, configuring synchronous replication, or setting up automatic failover mechanisms.