In this article, we will explain how to configure streaming replication in postgres(14) from existing primary database to a new standby database( slave).
The below steps will be same for POSTGRES 12/13/14.
If you are looking for streaming replication older than postgres 12, then below link:
How to setup streaming replication in postgres 11
ENVIRONMENT DETAILS:
PRIMARY SERVER= 10.20.30.40
STANDBY SERVER =10.20.30.41
REPLICATION MODE- ASYNC
POSTGRES VERSION- 14
Configure master server for streaming replication:
1. Validate the primary database server:
postgres=# \list+
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description
-----------+----------+----------+---------+-------+-----------------------+---------+------------+--------------------------------------------
postgres | postgres | UTF8 | C | C | | 4132 MB | pg_default | default administrative connection database
template0 | postgres | UTF8 | C | C | =c/postgres +| 7601 kB | pg_default | unmodifiable empty database
| | | | | postgres=CTc/postgres | | |
template1 | postgres | UTF8 | C | C | =c/postgres +| 7601 kB | pg_default | default template for new databases
| | | | | postgres=CTc/postgres | | |
(4 rows)
2. Update pg_hba.conf file primary server
Add host entries for standby server(10.20.30.41) in primary sever(10.20.30.40) pg_hba.conf file:
updated pg_hba.conf file:
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all peer
# IPv4 local connections:
host all all 127.0.0.1/32 ident
host all all 10.20.30.41/32 md5
# IPv6 local connections:
host all all ::1/128 ident
# Allow replication connections from localhost, by a user with the
# replication privilege.
local replication all peer
host replication all 127.0.0.1/32 ident
host replication all ::1/128 ident
host replication all 10.20.30.41/32 md5
3. Update postgres.conf file in primary server.
Update the postgrs.conf file with below values.
postgres=# show config_file;
config_file
---------------------------------
/postdata/data/postgresql.conf
(1 row)
vi /oracle/pg_data/postgresql.conf
archive_mode = on # enables archiving; off, on, or always
# (change requires restart)
archive_command = 'cp %p /oracle/pg_data/archive/%f'
max_wal_senders = 10 # max number of walsender processes
# (change requires restart)
wal_keep_segments = 50 # in logfile segments; 0 disables
wal_level = replica # minimal, replica, or logical
wal_log_hints = on # also do full page writes of non-critical updates
4. Restart postgres services:
postgres$/usr/pgsql-14/bin/pg_ctl stop -D /oracle/pg_datapostgres$/usr/pgsql-14/bin/pg_ctl start -D /oracle/pg_data
CONFIGURE STANDBY SERVER FOR STREAMING REPLICATION:
5. Install postgres binary on standby server.
On the standby server , just install the postgres binary. No need to initialise the postgres cluster.
total 16128
-rwxr-xr-x 1 root root 698212 Jul 10 12:17 postgresql14-contrib-14.2-1PGDG.rhel7.x86_64.rpm
-rwxr-xr-x 1 root root 1556440 Jul 10 12:17 postgresql14-14.2-1PGDG.rhel7.x86_64.rpm
-rwxr-xr-x 1 root root 273192 Jul 10 12:17 postgresql14-libs-14.2-1PGDG.rhel7.x86_64.rpm
-rwxr-xr-x 1 root root 5783224 Jul 10 12:17 postgresql14-server-14.2-1PGDG.rhel7.x86_64.rpm
[root@]# rpm -ivh postgresql14-libs-14.2-1PGDG.rhel7.x86_64.rpm
warning: postgresql14-libs-14.2-1PGDG.rhel7.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID 442df0f8: NOKEY
Preparing... ################################# [100%]
Updating / installing...
1:postgresql14-libs-14.2-1PGDG.rhel################################# [100%]
[root@ ]# rpm -ivh postgresql14-14.2-1PGDG.rhel7.x86_64.rpm
warning: postgresql14-14.2-1PGDG.rhel7.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID 442df0f8: NOKEY
Preparing... ################################# [100%]
Updating / installing...
1:postgresql14-14.2-1PGDG.rhel7 ################################# [100%]
[root@ ]# rpm -ivh postgresql14-server-14.2-1PGDG.rhel7.x86_64.rpm
warning: postgresql14-server-14.2-1PGDG.rhel7.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID 442df0f8: NOKEY
Preparing... ################################# [100%]
Updating / installing...
1:postgresql14-server-14.2-1PGDG.rh################################# [100%]
[root@]# rpm -ivh postgresql14-contrib-14.2-1PGDG.rhel7.x86_64.rpm
warning: postgresql14-contrib-14.2-1PGDG.rhel7.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID 442df0f8: NOKEY
Preparing... ################################# [100%]
Updating / installing...
1:postgresql14-contrib-14.2-1PGDG.r################################# [100%]
6. Restore full backup of master on standby server.
- Run below command on standby server
-- here 10.20.30.40 is primary server ip
standby$/usr/pgsql-14/bin/pg_basebackup -D /oracle/pg_data -X fetch -p 5444 -U postgres -h 172.20.30.40 -R
Password:
Now check whether files are available under /oracle/pg_data directory in standby server. Apart from restoring the full backup, It will create standby.signal file and update the postgres.auto.conf file
7. Check the presence of standby.signal file and postgres.auto.conf file content
From postgres 12 onwards, use of recovery.conf file has been deprecated, instead there will be standby.signal file( empty file).
and primary_conninfo details will be inside postgres.auto.conf
-rw——- 1 postgres postgres 0 Jul 10 15:03 standby.signal
-bash-4.2$ cat postgresql.auto.conf
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
primary_conninfo = ‘user=postgres password=postgres channel_binding=prefer host=10.20.30.40 port=5444 sslmode=prefer sslcompression=0 sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=prefer krbsrvname=postgres target_session_attrs=any’
8. Update the pg_hba.conf file.
Add host entries for primary server(10.20.30.40) in standby sever(10.20.30.41) pg_hba.conf file:
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all peer
# IPv4 local connections:
host all all 127.0.0.1/32 ident
host all all 10.20.30.40/32 md5
# IPv6 local connections:
host all all ::1/128 ident
# Allow replication connections from localhost, by a user with the
# replication privilege.
local replication all peer
host replication all 127.0.0.1/32 ident
host replication all ::1/128 ident
host replication all 10.20.30.40/32 md5
9. Start the postgres services in standby server.
postgres$ /usr/pgsql-14/bin/pg_ctl start -D /oracle/pg_data
10. Verify the streaming replication:
-- Run on this primary server for outgoing replication details
postgres=# select * from pg_stat_replication;
-[ RECORD 1 ]----+------------------------------
pid | 10799
usesysid | 10
usename | postgres
application_name | walreceiver
client_addr | 10.20.30.41
client_hostname |
client_port | 51694
backend_start | 2022-07-10 15:07:38.993741+03
backend_xmin |
state | streaming
sent_lsn | 0/3000A00
write_lsn | 0/3000A00
flush_lsn | 0/3000A00
replay_lsn | 0/3000A00
write_lag |
flush_lag |
replay_lag |
sync_priority | 0
sync_state | async
reply_time | 2022-07-10 15:56:51.426376+03
-- Run below queries on standby db server:
postgres=# select pg_is_wal_replay_paused();
pg_is_wal_replay_paused
-------------------------
f
Note - f means , recovery is running fine. t means it is stopped.
postgres=# select * from pg_stat_wal_receiver;
-[ RECORD 1 ]---------+------------------------------------------------------------------------
pid | 7792
status | streaming
receive_start_lsn | 0/3000000
receive_start_tli | 1
written_lsn | 0/3000A00
flushed_lsn | 0/3000A00
received_tli | 1
last_msg_send_time | 2022-07-10 15:58:16.457471+03
last_msg_receipt_time | 2022-07-10 15:57:21.503977+03
latest_end_lsn | 0/3000A00
latest_end_time | 2022-07-10 15:55:16.010796+03
slot_name |
sender_host | 10.20.30.40
sender_port | 5444
conninfo | user=postgres password=******** channel_binding=prefer dbname=replication host=10.20.30.40 port=5444 fallback_application_name=walreceiver sslmode=prefer sslcompression=0 sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=prefer krbsrvname=postgres target_session_attrs=any
