PostgreSQL 13 : Streaming Replication2021/05/18 |
Configure PostgreSQL Streaming Replication.
This configuration is common Primary/Replica settings. |
|
[1] | |
[2] | Configure Primary Node. |
root@www:~#
vi /etc/postgresql/13/main/postgresql.conf # line 59 : uncomment and change listen_addresses = ' * '
# line 196 : uncomment wal_level = replica # line 201 : uncomment synchronous_commit = on # line 290 : uncomment (max number of concurrent connections from streaming clients) max_wal_senders = 10 # line 305 : uncomment and change synchronous_standby_names = ' * '
root@www:~#
vi /etc/postgresql/13/main/pg_hba.conf # add to the end # host replication [replication user] [allowed network] [authentication method]
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:~$
root@www:~# exit
systemctl restart postgresql
|
[3] | Configure Replica Node. |
# stop PostgreSQL and remove existing data root@node01:~# systemctl stop postgresql root@node01:~# rm -rf /var/lib/postgresql/13/main/*
# get backup from Primary Node root@node01:~# su - postgres postgres@node01:~$ pg_basebackup -R -h www.srv.world -U rep_user -D /var/lib/postgresql/13/main -P Password: # password of replication user 32215/32215 kB (100%), 1/1 tablespace postgres@node01:~$ exit
root@node01:~#
vi /etc/postgresql/13/main/postgresql.conf # line 59 : uncomment and change listen_addresses = ' * '
# line 318 : uncomment hot_standby = on
root@node01:~#
vi /var/lib/postgresql/13/main/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 postgresql |
[4] | That's OK if result of the command below on Primary Node is like follows. Verify the setting works normally to create databases or to insert data on Primary Node. |
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) |
Sponsored Link |
|