PostgreSQL 14 : Streaming Replication2021/11/17 |
Configure PostgreSQL Streaming Replication.
This configuration is common Primary/Replica settings. |
|
[1] | |
[2] | Configure Primary Host. |
[root@www ~]#
vi /var/lib/pgsql/data/postgresql.conf # line 60 : uncomment and change listen_addresses = ' * '
# line 205 : uncomment wal_level = replica # line 210 : uncomment synchronous_commit = on # line 298 : uncomment (max number of concurrent connections from streaming clients) max_wal_senders = 10 # line 312 : uncomment and change synchronous_standby_names = ' * '
[root@www ~]#
vi /var/lib/pgsql/data/pg_hba.conf # last line : comment out existing lines and all new lines
# host replication [replication user] [allowed network] [authentication method]
host replication rep_user 10.0.0.30/32 md5
host replication rep_user 10.0.0.51/32 md5
# 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 ~]$
[root@www ~]# exit
systemctl restart postgresql
|
[3] | Configure Replica Host. |
# stop PostgreSQL and remove existing data [root@node01 ~]# systemctl stop postgresql [root@node01 ~]# rm -rf /var/lib/pgsql/data/*
# get backup from Primary Host [root@node01 ~]# su - postgres [postgres@node01 ~]$ pg_basebackup -R -h www.srv.world -U rep_user -D /var/lib/pgsql/data -P Password: # password of replication user 34971/34971 kB (100%), 1/1 tablespace [postgres@node01 ~]$ exit
[root@node01 ~]#
vi /var/lib/pgsql/data/postgresql.conf # line 60 : uncomment and change listen_addresses = ' * '
# line 325 : uncomment hot_standby = on systemctl start postgresql |
[4] | On all Nodes, If Firewalld is running, allow PostgreSQL service. |
[root@www ~]# firewall-cmd --add-service=postgresql success [root@www ~]# firewall-cmd --runtime-to-permanent success |
[5] | That's OK if result of the command below on Primary Host is like follows. Verify replication works normally to create databases or to insert data on Primary Host. |
[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 | walreceiver | 10.0.0.51 | streaming | 1 | sync (1 row) |
Sponsored Link |