PostgreSQL 15 : Streaming Replication2023/04/28

	Configure PostgreSQL Streaming Replication. This configuration is common Primary/Replica settings.
[1]	Install and start PostgreSQL Server on all Nodes, refer to here [1].
[2]	Configure Primary Node.

root@www:~#

vi /etc/postgresql/15/main/postgresql.conf

# line 60 : uncomment and change

listen_addresses = '

# line 205 : uncomment

wal_level = replica

# line 210 : uncomment

synchronous_commit = on

# line 308 : uncomment (max number of concurrent connections from streaming clients)

max_wal_senders = 10

# line 322 : uncomment and change

synchronous_standby_names = '

root@www:~#

vi /etc/postgresql/15/main/pg_hba.conf

# add to the end
# host replication [replication user] [allowed network] [authentication method]

host    replication     rep_user        10.0.0.31/32            scram-sha-256
host    replication     rep_user        10.0.0.51/32            scram-sha-256

# create a user for replication

root@www:~#

su - postgres

postgres@www:~$

createuser --replication -P rep_user

Enter password for new role:

# set any password

Enter it again:

postgres@www:~$

exit

root@www:~#

systemctl restart postgresql

[3]	Configure Replica Node.

# stop PostgreSQL and remove existing data

root@node01:~#

systemctl stop postgresql

root@node01:~#

rm -rf /var/lib/postgresql/15/main/*

# get backup from Primary Node

root@node01:~#

su - postgres

postgres@node01:~$

pg_basebackup -R -h www.srv.world -U rep_user -D /var/lib/postgresql/15/main -P

Password:

# password of replication user

30547/30547 kB (100%), 1/1 tablespace
postgres@node01:~$

exit

root@node01:~#

vi /etc/postgresql/15/main/postgresql.conf

# line 60 : uncomment and change

listen_addresses = '

# line 335 : uncomment

hot_standby = on

root@node01:~#

systemctl start postgresql

[4]	That's OK if result of the command below on Primary Node is like follows. Verify the setting works normally to create databases or to insert data on Primary Node.

postgres@www:~$

psql -c "select usename, application_name, client_addr, state, sync_priority, sync_state from pg_stat_replication;"

 usename  | application_name | client_addr |   state   | sync_priority | sync_state
----------+------------------+-------------+-----------+---------------+------------
 rep_user | 15/main          | 10.0.0.51   | streaming |             1 | sync
(1 row)

Matched Content