Complete Guide to Streaming Replapation PostgreSQL Configuration Setup 16

PostgreSQL 16 offers strong replication capabilities to ensure data remains available and distributed in real-time between the main server (primary) and a backup server (standby). The following are the steps that need to be followed to configure streaming replication in postgreSQL 16.

Initial preparation

PostgreSQL installation

Make sure PostgreSQL 16 has been installed on both servers (Primary and Standby). If not, you can install it with the following command:

sudo apt-get update
sudo apt-get install postgresql-16

Make user replication

Create a special user for replication on the primary server:

CREATE ROLE rep_user WITH REPLICATION LOGIN PASSWORD 'password';

Configuration on Primary Server

1. Edit postgresql.conf

The location of this file is usually on /etc/postgresql/16/main/postgresql.conf. Add or edit the following lines:

wal_level="replica"
max_wal_senders = 10
max_replication_slots = 10
wal_log_hints="on"
synchronous_standby_names="FIRST 1 (standby1, standby2)"

2. Edit pg_hba.conf

The location of this file is usually on /etc/postgresql/16/main/pg_hba.conf. Add the following lines to allow access from the standby server:

host replication rep_user 10.0.0.2/32 scram-sha-256

3. Restart PostgreSQL

After editing the configuration, restart postgreSQL:

sudo systemctl restart postgresql

4. Create a replication slot (optional)

To make a replication slot, run the following command in PostgreSQL:

SELECT * FROM pg_create_physical_replication_slot('rep_slot');

Thank you for the correction. Start postgreSQL 16, File recovery.conf no longer used. Instead, replication settings are carried out in the file postgresql.conf and through the signal file. The following are the steps that have been updated for PostgreSQL 16:

Configuration on the standby server

1. Stop PostgreSQL:

sudo systemctl stop postgresql

2. Copy data from primary to standby:

   pg_basebackup -h primary_server_ip -D /var/lib/postgresql/16/main -U rep_user -W -P --write-recovery-conf

Option --write-recovery-conf will create a file standby.signal and enter the required replication settings into postgresql.conf.

3. Edit postgresql.conf:

Make sure the postgreSQL.conf file has the following settings:

   primary_conninfo = 'host=primary_server_ip port=5432 user=rep_user password=password'
   primary_slot_name="rep_slot"  # Jika menggunakan slot replikasi
   hot_standby = 'on'

4. Edit pg_hba.conf:

Add an entry to allow connections to the primary server:

   host replication rep_user primary_server_ip/32 scram-sha-256

5. Start postgreSQL:

   sudo systemctl start postgresql

Verification

Check the replication status in Primary:

SELECT * FROM pg_stat_replication;

Sample results

  pid   | usesysid | usename  | application_name | client_addr  | client_hostname | client_port |         backend_start         | backend_xmin |   state   |  sent_lsn   |  write_lsn  |  flush_lsn  | replay_lsn  | write_lag | flush_lag | replay_lag | sync_priority | sync_state |          reply_time           
--------+----------+----------+------------------+--------------+-----------------+-------------+-------------------------------+--------------+-----------+-------------+-------------+-------------+-------------+-----------+-----------+------------+---------------+------------+-------------------------------
 638369 |   340262 | rep_user | 16/main          | 34.50.82.145 |                 |       45992 | 2024-07-10 17:04:53.349471+00 |              | streaming | 30/7C014690 | 30/7C014690 | 30/7C014690 | 30/7C014690 |           |           |            |             0 | async      | 2024-07-10 17:05:23.409507+00
(1 row)

Check the status on standby:

SELECT * FROM pg_is_in_recovery();

Sample results

postgres@hostname:~/16$ psql
psql (16.3 (Ubuntu 16.3-1.pgdg22.04+1))
Type "help" for help.

postgres=# SELECT * FROM pg_is_in_recovery();
 pg_is_in_recovery 
-------------------
 t
(1 row)

postgres=# 

If everything goes well, you now have an active postgresql streaming replication setup. This replication ensures that the data on the standby server is always updated in real-time from the primary server, thereby increasing the availability and reliability of your data.

Source:

By following this guide, you can set streaming replication on PostgreSQL 16 easily and make sure your data is always synchronous between Primary and Standby servers.