PostgreSQL 16 : Streaming Replication2023/12/07 |
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 211 : uncomment wal_level = replica # line 216 : uncomment synchronous_commit = on # line 314 : uncomment (max number of concurrent connections from streaming clients) max_wal_senders = 10 # line 328 : 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] # if using SSL connection, specify [hostssl replication ***] #host replication all 127.0.0.1/32 ident #host replication all ::1/128 ident host replication rep_user 10.0.0.30/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 ~]$
[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 30859/30859 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 339 : uncomment hot_standby = on systemctl start postgresql |
[4] | 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 it 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 |
|