Debian 10 Buster
Sponsored Link

PostgreSQL : Streaming Replication
2019/08/07
 
Configure PostgreSQL Streaming Replication.
This configuration is Master/Slave settings.
[1]
[2] Configure Master Host.
root@dlp:~#
vi /etc/postgresql/11/main/postgresql.conf
# line 59: uncomment and change

listen_addresses = '
*
'
# line 184: uncomment

wal_level = replica
# line 189: uncomment

synchronous_commit = on
# line 239: uncomment (max number of concurrent connections from streaming clients)

max_wal_senders = 10
# line 241: uncomment and change (minimum number of past log file segments)

wal_keep_segments =
10
# line 253: uncomment and change

synchronous_standby_names = '
*
'
root@dlp:~#
vi /etc/postgresql/11/main/pg_hba.conf
# end line : change like follows

# host replication [replication user] [allowed IP addresses] trust

#host    replication     all             127.0.0.1/32            md5
#host    replication     all             ::1/128                 md5
host    replication     rep_user         127.0.0.1/32            trust
host    replication     rep_user         10.0.0.30/32            trust
host    replication     rep_user         10.0.0.51/32            trust

# create a user for replication

root@dlp:~#
su - postgres

postgres@dlp:~$
createuser --replication -P rep_user

Enter password for new role:
Enter it again:
postgres@dlp:~$
exit
root@dlp:~#
mkdir /var/lib/postgresql/11/archive

root@dlp:~#
chown postgres. /var/lib/postgresql/11/archive

root@dlp:~#
systemctl restart postgresql
[3] Configure Slave Host.
root@node01:~#
systemctl stop postgresql

root@node01:~#
rm -rf /var/lib/postgresql/11/main/*

root@node01:~#
su - postgres
# get backup from Master Server

postgres@node01:~$
pg_basebackup -R -h dlp.srv.world -U rep_user -D /var/lib/postgresql/11/main -P

23771/23771 kB (100%), 1/1 tablespace
postgres@node01:~$
exit
root@node01:~#
vi /etc/postgresql/11/main/postgresql.conf
# line 59: uncomment and change

listen_addresses = '
*
'
# line 263: uncomment

hot_standby = on
root@node01:~#
vi /var/lib/postgresql/11/main/recovery.conf
# add application_name (any name : specify hostname and so on)

standby_mode = 'on'
primary_conninfo = 'user=rep_user passfile=''/var/lib/postgresql/.pgpass'' host=dlp.srv.world port=5432 sslmode=prefer sslcompression=0 krbsrvname=postgres target_session_attrs=any
application_name=node01
'
root@node01:~#
systemctl start postgresql

[4] That's OK if result of the command below is like follows. Make sure the setting works normally to create databases on Master Server.
postgres@dlp:~$
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