Обсуждение: Issue in streaming replication after increasing swap.
Dear community Team,
I am using postgresql version 11.6 and currently facing an issue in streaming replication after increasing swap memory on the servers from 8GB to 32GB (master and slave). Master is individually working fine but there is a lag occurs in streaming replication which I resolved through restart the standby but as of now it occurs very offently.
The wals are written,flushed on the standby but not replay on standby and found replay_lag in pg_stat_replication.
Kindly suggest your valuable feedbacks to overcome this issue. I will be very thankful to all.
FYI...
-----------------------------------MASTER:
MASTER=# select version();
version
-----------------------------------------------------------------------------
PostgreSQL 11.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 5.5.0, 64-bit
(1 row)
MASTER: select * from pg_stat_replication ;
pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start |
backend_xmin | state | sent_lsn | write_lsn | flush_lsn | replay_lsn | write_lag | flush_lag | replay_lag
| sync_priority | sync_state
-------+----------+-------------+-------------------------+---------------+-----------------+-------------+----------------------------------+
--------------+-----------+---------------+---------------+---------------+---------------+-----------------+-----------------+---------------
--+---------------+------------
6603 | 10 | postgres | walreceiver | 10.XXX.XX.XXX | | 49486 | 2020-09-21 08:46:51.017256+05:30 |
1559510849 | streaming | 3012/82597F10 | 3012/82597F10 | 3012/82597F10 | 3011/AE068660 | 00:00:00.00038 | 00:00:00.000628 | 00:19:14.31315
9 | 0 | async
-----------------------------------STANDBY :
STANDBY=# select version();
version
-----------------------------------------------------------------------------
PostgreSQL 11.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 5.5.0, 64-bit
(1 row)
STANDBY=# select pg_is_in_recovery(),pg_is_wal_replay_paused(),pg_last_wal_receive_lsn(),pg_last_wal_replay_lsn(),pg_last_xact_replay_timestamp();
pg_is_in_recovery | pg_is_wal_replay_paused | pg_last_wal_receive_lsn | pg_last_wal_replay_lsn | pg_last_xact_replay_timestamp
-------------------+-------------------------+-------------------------+------------------------+----------------------------------
t | f | 3012/950574D8 | 3011/AE068660 | 2020-09-21 13:31:21.639922+05:30
(1 row)
STANDBY=# select pg_is_in_recovery(),pg_is_wal_replay_paused(),pg_last_wal_receive_lsn(),pg_last_wal_replay_lsn(),pg_last_xact_replay_timestamp();
pg_is_in_recovery | pg_is_wal_replay_paused | pg_last_wal_receive_lsn | pg_last_wal_replay_lsn | pg_last_xact_replay_timestamp
-------------------+-------------------------+-------------------------+------------------------+----------------------------------
t | f | 3012/9554DDB8 | 3011/AE068660 | 2020-09-21 13:31:21.639922+05:30
(1 row)
STANDBY=# select pg_is_in_recovery(),pg_is_wal_replay_paused(),pg_last_wal_receive_lsn(),pg_last_wal_replay_lsn(),pg_last_xact_replay_timestamp();
pg_is_in_recovery | pg_is_wal_replay_paused | pg_last_wal_receive_lsn | pg_last_wal_replay_lsn | pg_last_xact_replay_timestamp
-------------------+-------------------------+-------------------------+------------------------+----------------------------------
t | f | 3012/958E08F8 | 3011/AE068660 | 2020-09-21 13:31:21.639922+05:30
(1 row)
--
I am using postgresql version 11.6 and currently facing an issue in streaming replication after increasing swap memory on the servers from 8GB to 32GB (master and slave). Master is individually working fine but there is a lag occurs in streaming replication which I resolved through restart the standby but as of now it occurs very offently.
The wals are written,flushed on the standby but not replay on standby and found replay_lag in pg_stat_replication.
Kindly suggest your valuable feedbacks to overcome this issue. I will be very thankful to all.
FYI...
-----------------------------------MASTER:
MASTER=# select version();
version
-----------------------------------------------------------------------------
PostgreSQL 11.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 5.5.0, 64-bit
(1 row)
MASTER: select * from pg_stat_replication ;
pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start |
backend_xmin | state | sent_lsn | write_lsn | flush_lsn | replay_lsn | write_lag | flush_lag | replay_lag
| sync_priority | sync_state
-------+----------+-------------+-------------------------+---------------+-----------------+-------------+----------------------------------+
--------------+-----------+---------------+---------------+---------------+---------------+-----------------+-----------------+---------------
--+---------------+------------
6603 | 10 | postgres | walreceiver | 10.XXX.XX.XXX | | 49486 | 2020-09-21 08:46:51.017256+05:30 |
1559510849 | streaming | 3012/82597F10 | 3012/82597F10 | 3012/82597F10 | 3011/AE068660 | 00:00:00.00038 | 00:00:00.000628 | 00:19:14.31315
9 | 0 | async
STANDBY=# select version();
version
-----------------------------------------------------------------------------
PostgreSQL 11.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 5.5.0, 64-bit
(1 row)
STANDBY=# select pg_is_in_recovery(),pg_is_wal_replay_paused(),pg_last_wal_receive_lsn(),pg_last_wal_replay_lsn(),pg_last_xact_replay_timestamp();
pg_is_in_recovery | pg_is_wal_replay_paused | pg_last_wal_receive_lsn | pg_last_wal_replay_lsn | pg_last_xact_replay_timestamp
-------------------+-------------------------+-------------------------+------------------------+----------------------------------
t | f | 3012/950574D8 | 3011/AE068660 | 2020-09-21 13:31:21.639922+05:30
(1 row)
STANDBY=# select pg_is_in_recovery(),pg_is_wal_replay_paused(),pg_last_wal_receive_lsn(),pg_last_wal_replay_lsn(),pg_last_xact_replay_timestamp();
pg_is_in_recovery | pg_is_wal_replay_paused | pg_last_wal_receive_lsn | pg_last_wal_replay_lsn | pg_last_xact_replay_timestamp
-------------------+-------------------------+-------------------------+------------------------+----------------------------------
t | f | 3012/9554DDB8 | 3011/AE068660 | 2020-09-21 13:31:21.639922+05:30
(1 row)
STANDBY=# select pg_is_in_recovery(),pg_is_wal_replay_paused(),pg_last_wal_receive_lsn(),pg_last_wal_replay_lsn(),pg_last_xact_replay_timestamp();
pg_is_in_recovery | pg_is_wal_replay_paused | pg_last_wal_receive_lsn | pg_last_wal_replay_lsn | pg_last_xact_replay_timestamp
-------------------+-------------------------+-------------------------+------------------------+----------------------------------
t | f | 3012/958E08F8 | 3011/AE068660 | 2020-09-21 13:31:21.639922+05:30
(1 row)
--
Thanks & Regards
Zaid Raza Khan
9718685858