PostgreSQL 12 : Streaming Replication2020/01/31 |
Configure PostgreSQL Streaming Replication.
This configuration is common Master/Slave settings. |
|
[1] | |
[2] | Configure Master Host. |
[root@www ~]#
vi /var/opt/rh/rh-postgresql12/lib/pgsql/data/postgresql.conf # line 59: uncomment and change listen_addresses = ' * '
# line 193: uncomment wal_level = replica # line 198: uncomment synchronous_commit = on # line 286: uncomment (max number of concurrent connections from streaming clients) max_wal_senders = 10 # line 288: uncomment and change (minimum number of past log file segments) wal_keep_segments = 10
# line 300: uncomment and change synchronous_standby_names = ' * '
[root@www ~]#
vi /var/opt/rh/rh-postgresql12/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 all 127.0.0.1/32 ident #host replication all ::1/128 ident 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 -bash-4.2$ createuser --replication -P rep_user Enter password for new role: # set any password Enter it again:
-bash-4.2$
[root@www ~]# exit
systemctl restart rh-postgresql12-postgresql
|
[3] | Configure Slave Host. |
# stop PostgreSQL and remove existing data [root@node01 ~]# systemctl stop rh-postgresql12-postgresql [root@node01 ~]# rm -rf /var/opt/rh/rh-postgresql12/lib/pgsql/data/*
# get backup from Master Host [root@node01 ~]# su - postgres -bash-4.2$ pg_basebackup -R -h www.srv.world -U rep_user -D /var/opt/rh/rh-postgresql12/lib/pgsql/data -P Password: # password of replication user 33551/33551 kB (100%), 1/1 tablespace -bash-4.2$ exit
[root@node01 ~]#
vi /var/opt/rh/rh-postgresql12/lib/pgsql/data/postgresql.conf # line 59: uncomment and change listen_addresses = ' * '
# line 315: uncomment hot_standby = on
[root@node01 ~]#
vi /var/opt/rh/rh-postgresql12/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 all 127.0.0.1/32 ident #host replication all ::1/128 ident host replication rep_user 10.0.0.30/32 md5 host replication rep_user 10.0.0.51/32 md5
[root@node01 ~]#
vi /var/opt/rh/rh-postgresql12/lib/pgsql/data/postgresql.auto.conf # add [application_name] to auto generated auth file (any name you like, like hostname and so on)
primary_conninfo = 'user=rep_user password=password host=www.srv.world port=5432 sslmode=prefer sslcompression=0 gssencmode=prefer krbsrvname=postgres target_session_attrs=any application_name=node01'
[root@node01 ~]# systemctl start rh-postgresql12-postgresql |
[4] | If Firewalld is running, allow PostgreSQL service on all Nodes. |
[root@www ~]# firewall-cmd --add-service=postgresql --permanent success [root@www ~]# firewall-cmd --reload success |
[5] | That's OK if result of the command below on Master Host is like follows. Make sure the setting works normally to create databases or to insert data on Master Host. |
-bash-4.2$ 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 | node01 | 10.0.0.51 | streaming | 1 | sync (1 row) |
Sponsored Link |
|