Re: Stream Replication not working

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 <
CrawfordMA(at)churchofjesuschrist(dot)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=#
>
>
>
>
>