In this article, we will explain how to configure streaming replication in postgres from primary database to a new standby ,check pg_stat_wal_receiver;
1234567891011121314
postgres=#<span style="color: #ff0000;"> <strong>\list+</strong></span>
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=# <span style="color: #ff0000;"><strong>\conninfo</strong></span>
You are connected to database "postgres" as user "enterprisedb" via socket in "/tmp" at port "5444".123456789101112131415161718192021
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
<span style="color: #ff6600;"><strong>host all all 10.20.30.41/32 md5
</strong></span># 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
<span style="color: #ff6600;"><strong>host replication all 10.20.30.41/32 md5
</strong></span>123456789101112131415161718
postgres=# <span style="color: #ff0000;"><strong>show config_file;</strong></span>
config_file
---------------------------------
/postdata/data/postgresql.conf
(1 row)
<span style="color: #ff0000;"><strong>
vi /pgdata/data/postgresql.conf
</strong></span>
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 logical12
systemctl stop edb-as-11
systemctl start edb-as-11123456
- Run below command on standby server
-- here 10.20.30.40 is primary server ip
standby$<span style="color: #ff0000;"><strong> /usr/edb/as11/bin/pg_basebackup -D /pgdata/edbdata/ -X fetch -p 5444 -U enterprisedb -h 10.20.30.40 -R
</strong></span>1234567
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’1234567891011121314151617
# 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
<span style="color: #ff6600;"><strong>host all all 10.20.30.40/32 md5
</strong></span># 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
<span style="color: #ff6600;"><strong>host replication all 10.20.30.40/32 md5
</strong></span>12
systemctl stop edb-as-11
systemctl start edb-as-11123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960
<strong>
-- Run on this primary server for outgoing replication details
</strong>
postgres=# <span style="color: #ff0000;"><strong>select * from pg_stat_replication;</strong></span>
-[ 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 | <span style="color: #ff0000;"><strong>async</strong></span>
<strong>-- Run below queries on standby db server:
</strong>
postgres=# <span style="color: #ff0000;"><strong>select pg_is_wal_replay_paused();</strong></span>
pg_is_wal_replay_paused
-------------------------
f
<strong>Note - f means , recovery is running fine. t means it is stopped.
</strong>
postgres=# <span style="color: #ff0000;"><strong>select pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn(), pg_last_xact_replay_timestamp();</strong></span>
-[ 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=# <span style="color: #ff0000;"><strong>select * from pg_stat_wal_receiver;</strong></span>
-[ 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=anyPlease to add comments
No comments yet. Be the first to comment!