In this article, we will explain how to configure streaming replication in postgres (11)from existing primary database to a new standby database( slave).
If your postgres version is 12 or higher , then follow below link:
How to configure streaming replication in postgres 14
ENVIRONMENT DETAILS:
PRIMARY SERVER= 10.20.30.40
STANDBY SERVER =10.20.30.41
REPLICATION MODE- ASYNC
POSTGRES VERSION- 11
NOTE – We are using edb postgres advanced server for our environment. You can use the postgres open version also. Steps are same for both database flavours.
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
-----------+----------+----------+---------+-------+-----------------------+---------+------------+--------------------------------------------
edb. | postgres | UTF8 | C | C | | 2268 MB | pg_default |
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)
postgres=# \conninfo
You are connected to database "postgres" as user "enterprisedb" via socket in "/tmp" at port "5444".
3. Update pg_hba.conf file primary server
Add host entries for standby server(10.20.30.41) in primary sever(10.20.30.410) 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
4. 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 /pgdata/data/postgresql.conf
archive_mode = on # enables archiving; off, on, or always
# (change requires restart)
archive_command = 'cp %p /postdata/pg_archive/%f' # command to use to archive a logfile segment
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
5. Restart postgres services:
systemctl stop edb-as-11
systemctl start edb-as-11
CONFIGURE STANDBY SERVER FOR STREAMING REPLICATION:
1. Install postgres binary on standby server.
On the standby server , just install the postgres binary. No need to initialise the postgres cluster.
Install EDB postgres on Linux.
2. 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/edb/as11/bin/pg_basebackup -D /pgdata/edbdata/ -X fetch -p 5444 -U enterprisedb -h 10.20.30.40 -R
Now check whether files are available under /pgdata/edbdata directory in standby server.
4. Check the content of recovery.conf file.
Verify the recovery.conf file and add parameters if anything is missing.
cat /postdata/edbdata/recovery.conf
[enterprisedb@SCPrLBTB77 edbdata]$ cat recovery.conf
standby_mode = 'on'
primary_conninfo = 'user=enterprisedb password=edb#pgadmindb host=10.20.30.40 port=5444 sslmode=prefer sslcompression=0 krbsrvname=postgres target_session_attrs=any'
promote_trigger_file='/postdata/edbdata/trigg.file'
recovery_target_timeline=’latest’
4. 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
5. Start the postgres services in standby server.
systemctl stop edb-as-11
systemctl start edb-as-11
6. Verify the streaming replication:
-- Run on this primary server for outgoing replication details
postgres=# select * from pg_stat_replication;
-[ RECORD 1 ]----+---------------------------------
pid | 18556
usesysid. | 10
usename | enterprisedb
application_name | walreceiver
client_addr | 10.20.76.12
client_hostname |
client_port | 44244
backend_start | 27-MAY-21 13:56:30.131681 +03:00
backend_xmin |
state | streaming
sent_lsn. | 0/401F658
write_lsn | 0/401F658
flush_lsn | 0/401F658
replay_lsn. | 0/401F658
write_lag |
flush_lag |
replay_lag. |
sync_priority. | 0
sync_state | async
-- 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 pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn(), pg_last_xact_replay_timestamp();
-[ RECORD 1 ]-----------------+---------------------------------
pg_last_wal_receive_lsn | 0/401F658
pg_last_wal_replay_lsn | 0/401F658
pg_last_xact_replay_timestamp | 27-MAY-21 16:26:18.704299 +03:00
postgres=# select * from pg_stat_wal_receiver;
-[ RECORD 1 ]---------+------------------------------------------------------------------------
pid | 7933
status | streaming
receive_start_lsn | 0/4000000
receive_start_tli | 1
received_lsn | 0/401F658
received_tli | 1
last_msg_send_time. | 27-MAY-21 20:29:39.599389 +03:00
last_msg_receipt_time | 27-MAY-21 20:29:39.599599 +03:00
latest_end_lsn. | 0/401F658
latest_end_time | 27-MAY-21 16:31:20.815183 +03:00
slot_name |
sender_host | 10.20.30.40
sender_port | 5444
conninfo | user=enterprisedb passfile=/postdata/enterprisedb/.pgpass dbname=replication host=10.20.30.40 port=5444 fallback_application_name=walreceiver sslmode=prefer sslcompression=0 krbsrvname=postgres target_session_attrs=any
Our replication setup has been completed.
I want to setup PostgreSQL 13 opensource streaming replication on Windows Server 2019
Pls share How To do