Seems there is a problem with the replay on your standby. Either it is too slow or stuck behind some locks ( replay_lag of 20:38:47.00904 indicates this and the flush_lsn is the same as lsn on primary ) . Run pg_locks to see if the replay is stuck behind a lock.
On Mon, Jan 10, 2022 at 11:53 AM Allie Crawford < crawfor...@churchofjesuschrist.org> wrote: > Hi All, > > I have implemented Stream replication in one of my environments, and for > some reason even though all the health checks are showing that the > replication is working, when I run manual tests to see if changes are being > replicated, the changes are not replicated to the standby postgresql > environment. I have been researching for two day and I cannot find any > documentation that talks about the case I am running into. I will > appreciate if anybody could take a look at the details I have detailed > below and give me some guidance on where the problem might be that is > preventing my changes for being replicated. Even though I was able to > instantiate the standby while firewalld was enabled, I decided to disable > it just in case that it was causing any issue to the manual changes, but > disabling firewalld has not had any effect, I am still not able to get the > manual changes test to be replicated to the standby site. As you will see > in the details below, the streaming is working, both sites are in sync to > the latest WAL but for some reasons the latest changes are not on the > standby site. How is it possible that the standby site is completely in > sync but yet does not contain the latest changes? > > > > Thanks in advance for any help you can give me with this problem. > > > > Regards, > > Allie > > > > *Details:* > > > > *Master **postgresql Environment* > > postgresql=# select * from pg_stat_replication; > > -[ RECORD 1 ]----+------------------------------ > > pid | 1979089 > > usesysid | 16404 > > usename | replacct > > application_name | walreceiver > > client_addr | <standby server IP> > > client_hostname | <standby server name> > > client_port | 55096 > > backend_start | 2022-01-06 17:29:51.542784-07 > > backend_xmin | > > state | streaming > > sent_lsn | 0/35000788 > > write_lsn | 0/35000788 > > flush_lsn | 0/35000788 > > replay_lsn | 0/31000500 > > write_lag | 00:00:00.001611 > > flush_lag | 00:00:00.001693 > > replay_lag | 20:38:47.00904 > > sync_priority | 1 > > sync_state | sync > > reply_time | 2022-01-07 14:11:58.996277-07 > > > > postgresql=# > > > > postgresql=# select * from pg_roles; > > rolname | rolsuper | rolinherit | rolcreaterole | > rolcreatedb | rolcanlogin | rolreplication | rolconnlimit | rolpassword | > rolvaliduntil | rolbypassrls | rolconfig | oid > > > ---------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+-------------+---------------+--------------+-----------+------- > > postgresql | t | t | t | t > | t | t | -1 | ******** | > | t | | 10 > > pg_monitor | f | t | f | f > | f | f | -1 | ******** | > | f | | 3373 > > pg_read_all_settings | f | t | f | f > | f | f | -1 | ******** | > | f | | 3374 > > pg_read_all_stats | f | t | f | f > | f | f | -1 | ******** | > | f | | 3375 > > pg_stat_scan_tables | f | t | f | f > | f | f | -1 | ******** | > | f | | 3377 > > pg_read_server_files | f | t | f | f > | f | f | -1 | ******** | > | f | | 4569 > > pg_write_server_files | f | t | f | f > | f | f | -1 | ******** | > | f | | 4570 > > pg_execute_server_program | f | t | f | f > | f | f | -1 | ******** | > | f | | 4571 > > pg_signal_backend | f | t | f | f > | f | f | -1 | ******** | > | f | | 4200 > > replacct | t | t | t | t > | t | t | -1 | ******** | > | t | | 16404 > > (10 rows) > > > > postgresql=# > > > > postgresql=# create database test_replication_3; > > CREATE DATABASE > > postgresql=# > > > > postgresql=# select datname from pg_database; > > datname > > -------------------- > > postgres > > postgresql > > template1 > > template0 > > stream > > test_replication > > test_replication_2 > > test_replication_3 > > (8 rows) > > > > postgresql=# > > > > postgresql=# SELECT pg_current_wal_lsn(); > > pg_current_wal_lsn > > -------------------- > > 0/35000788 > > (1 row) > > > > postgresql=# > > > > > > *Standby **postgresql Environment* > > postgresql=# select * from pg_stat_wal_receiver; > > -[ RECORD 1 > ]---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- > > pid | 17340 > > status | streaming > > receive_start_lsn | 0/30000000 > > receive_start_tli | 1 > > written_lsn | 0/35000788 > > flushed_lsn | 0/35000788 > > received_tli | 1 > > last_msg_send_time | 2022-01-07 14:09:48.766823-07 > > last_msg_receipt_time | 2022-01-07 14:09:48.767581-07 > > latest_end_lsn | 0/35000788 > > latest_end_time | 2022-01-07 14:08:48.663693-07 > > slot_name | wal_req_x_replica > > sender_host | <Master Server IP> > > sender_port | <Master server postgresql port#> > > conninfo | user=replacct password=******** > channel_binding=prefer dbname=replication host=<Master server IP> > port=<postgresql port#> fallback_application_name=walreceiver > sslmode=prefer sslcompression=0 ssl_min_protocol_version=TLSv1.2 > gssencmode=prefer krbsrvname=postgres target_session_attrs=any > > > > postgresql=# > > > > postgresql=# select datname from pg_database; > > datname > > ------------ > > postgres > > postgresql > > template1 > > template0 > > stream > > (5 rows) > > > > postgresql=# select pg_last_wal_receive_lsn(); > > pg_last_wal_receive_lsn > > ------------------------- > > 0/35000788 > > (1 row) > > > > postgresql=# > > > > >
