|
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=# |
