CentOS 7
Sponsored Link

PostgreSQL 12 : Streaming Replication2020/01/31
 
Configure PostgreSQL Streaming Replication.
This configuration is common Master/Slave settings.
[1]
Install and start PostgreSQL Server on all Nodes, refer to here [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$
exit
[root@www ~]#
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)
Matched Content