PostgreSQL 10 : Streaming Replication2021/05/27

	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 Host.

[root@www ~]#

vi /var/lib/pgsql/data/postgresql.conf

# line 59 : uncomment and change

listen_addresses = '

# line 180 : uncomment

wal_level = replica

# line 185 : uncomment

synchronous_commit = on

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

max_wal_senders = 10

# line 237 : uncomment and change (minimum number of past log file segments)

wal_keep_segments =

# line 249 : 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     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

[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 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

30396/30396 kB (100%), 1/1 tablespace
[postgres@node01 ~]$

exit

[root@node01 ~]#

vi /var/lib/pgsql/data/postgresql.conf

# line 59 : uncomment and change

listen_addresses = '

# line 259 : uncomment

hot_standby = on

[root@node01 ~]#

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     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/lib/pgsql/data/recovery.conf

# add [application_name] to auto generated auth file (any name you like, like hostname and so on)

standby_mode = 'on'
primary_conninfo = 'user=rep_user password=password host=www.srv.world port=5432 sslmode=prefer sslcompression=1 krbsrvname=postgres target_session_attrs=any application_name=node01'

[root@node01 ~]#

systemctl start postgresql

[4]	If Firewalld is running, allow PostgreSQL service.

[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 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 | node01           | 10.0.0.51   | streaming |             1 | sync
(1 row)

Matched Content