0

There is one primary and two replicas in postgresql 16. one replica is completely sync however, the other one keeps on lagging and after some hours its falls way behind and breaks replication.

Primary COnfigurations

maintenance_work_mem    4GB
max_connections 300
max_parallel_workers    8
max_parallel_workers_per_gather 4
max_standby_archive_delay   25min
max_standby_streaming_delay 25min
max_wal_senders 50
max_wal_size    1GB
min_wal_size    80MB
shared_buffers  15GB
wal_init_zero   on
wal_keep_size   15GB
wal_level   replica

Moreover on Replicas below are the parameters:

maintenance_work_mem    4GB
max_connections 300
max_parallel_workers    8
max_parallel_workers_per_gather 4
max_standby_archive_delay   25min
max_standby_streaming_delay 25min
max_wal_senders 50
max_wal_size    1GB
min_wal_size    80MB
shared_buffers  15GB
wal_init_zero   on
wal_keep_size   15GB
wal_level   replica
hot_standby     on

I have been checking the replication lag by below query:

SELECT CASE WHEN pg_last_wal_receive_lsn() = pg_last_wal_replay_lsn() THEN 0 ELSE EXTRACT(EPOCH FROM now() - pg_last_xact_replay_timestamp()) END AS log_delay;

The replica keeps on lagging until the replication goes out.

The error in logs is below:

LOG: invalid magic number 0000 in WAL segment 0000000100000126000000BC, LSN 126/BC480000, offset 4718592

Improve this question
2
  • The issue has been sorted by adding the high value to effective_io_concurrency
    – Wajahat Munir
    Commented Oct 17 at 9:25
  • It is hard to see what good increasing effective_io_concurrency would do in physical replication replay, so I think you are just looking at a coincidence.
    – jjanes
    Commented Oct 21 at 2:08

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.