Data Out of Sync with Physical Streaming Replication

Hi All,

We have run into an issue where the data for a primary and standby Postgres
instances are out of sync. We are using streaming physical replication. We
have encountered this issue several times over the last year on different
Postgres Clusters. Postgres indicates everything is in sync but we can
clearly see that is not the case.

The essentials:

PG Verion: 14.7

OS: Rocky Linux 8.10 -> Linux 4.18.0-553.30.1.el8_10.x86_64

Server: VMWare VM

Storage: Enterprise level SSD

We are starting to push Postgres 17 but it will be a while before we
upgrade our existing PG 14 DB’s, therefore, I need to figure out why this
occurs in PG 14. I plan on at a minimum, upgrading to PG 14.15.

I have a cluster setup to show this. We are using the default asynchronous
replication. The primary and standby are in the same data center. I
haven’t been able to reproduce this on demand. Initially, I thought it
might be a storage issue, but we are not seeing any errors in dmesg, kernel
log, journalctl, or on the storage controller.

I’m hoping someone has seen something similar and has some guidance on
further troubleshooting this.

On the PRIMARY, we can see the replication slot is running:

-- DB is in read-write mode:

postgres=# select pg_is_in_recovery();

pg_is_in_recovery

-------------------

f

-- We have single replication slot that shows the lsn written, sent,
flushed, and replayed is all the same:

postgres=# select * from pg_stat_replication;

-[ RECORD 1 ]----+------------------------------

pid | 2457681

usesysid | 16384

usename | xxxxxxxxxx

application_name | xxxxxxxxxxxxxxxx

client_addr | yyy.yyy.yyy.yyy

client_hostname |

client_port | 58522

backend_start | 2025-01-15 11:03:26.99902-06

backend_xmin |

state | streaming

sent_lsn | 0/1E0001B8

write_lsn | 0/1E0001B8

flush_lsn | 0/1E0001B8

replay_lsn | 0/1E0001B8

write_lag |

flush_lag |

replay_lag |

sync_priority | 0

sync_state | async

reply_time | 2025-01-15 11:04:27.149521-06

-- Primary shows no database conflicts:

postgres=# select * from pg_stat_database_conflicts;

-[ RECORD 1 ]----+----------

datid | 1

datname | template1

confl_tablespace | 0

confl_lock | 0

confl_snapshot | 0

confl_bufferpin | 0

confl_deadlock | 0

-[ RECORD 2 ]----+----------

datid | 13747

datname | template0

confl_tablespace | 0

confl_lock | 0

confl_snapshot | 0

confl_bufferpin | 0

confl_deadlock | 0

-[ RECORD 3 ]----+----------

datid | 16390

datname | issuedb

confl_tablespace | 0

confl_lock | 0

confl_snapshot | 0

confl_bufferpin | 0

confl_deadlock | 0

-[ RECORD 4 ]----+----------

datid | 13748

datname | postgres

confl_tablespace | 0

confl_lock | 0

confl_snapshot | 0

confl_bufferpin | 0

confl_deadlock | 0

On the STANDBY server, we can see that there is no lag:

-- Instance is in recovery mode:

postgres=# select pg_is_in_recovery();

pg_is_in_recovery

-------------------

t

-- Received and replayed lsn are the same. Note, these also match the lsn
from the primary.

postgres=# select pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn();

-[ RECORD 1 ]-----------+-----------

pg_last_wal_receive_lsn | 0/1E0001B8

pg_last_wal_replay_lsn | 0/1E0001B8

-- no conflicts:

postgres=# select * from pg_stat_database_conflicts;

-[ RECORD 1 ]----+----------

datid | 1

datname | template1

confl_tablespace | 0

confl_lock | 0

confl_snapshot | 0

confl_bufferpin | 0

confl_deadlock | 0

-[ RECORD 2 ]----+----------

datid | 13747

datname | template0

confl_tablespace | 0

confl_lock | 0

confl_snapshot | 0

confl_bufferpin | 0

confl_deadlock | 0

-[ RECORD 3 ]----+----------

datid | 16390

datname | issuedb

confl_tablespace | 0

confl_lock | 0

confl_snapshot | 0

confl_bufferpin | 0

confl_deadlock | 0

-[ RECORD 4 ]----+----------

datid | 13748

datname | postgres

confl_tablespace | 0

confl_lock | 0

confl_snapshot | 0

confl_bufferpin | 0

confl_deadlock | 0

I've performed a switchover multiple times meaning the standby has become
the primary and the primary has become the standby.

Not all tables are out of sync. In this case, it is three of them:

PRIMARY:

issuedb=# select count(1) from issue;

count

-------

2

(1 row)

issuedb=# select count(1) from comment;

count

-------

1

(1 row)

issuedb=# select count(1) from troubleshoot_issue;

count

-------

2

(1 row)

STANDBY:

issuedb=# select count(1) from issue;

count

-------

3

(1 row)

issuedb=# select count(1) from comment;

count

-------

4

(1 row)

issuedb=# select count(1) from troubleshoot_issue;

count

-------

3

(1 row)

Both are on timeline 21:

PRIMARY:

postgres=# SELECT timeline_id FROM pg_control_checkpoint();

timeline_id

-------------

21

STANDBY:

postgres=# SELECT timeline_id FROM pg_control_checkpoint();

timeline_id

-------------

21

Archive mode is on. I ran pg_amcheck and everything came back clean. I
know synchronous replication is an option, but it feels like something else
is going on and would like to get to the bottom of it.

Any ideas on what could be causing this? I’m planning on turning on
data_checksums and running pg_checksums to see if any corruption is found.

Thanks for the time.

Best Regards,

Tim