All we need is an easy explanation of the problem, so here it is.
I have a streaming replication on pg 14.2 that seems to be working, on a slot named
select * from pg_stat_replication_slots has no output. Do I need to enable anything else?
I have those on:
select * from pg_stat_replication;
-[ RECORD 1 ]----+------------------------------ pid | 21483 usesysid | 376382 usename | repl application_name | replication client_addr | 172.28.201.16 client_hostname | client_port | 42822 backend_start | 2022-03-17 11:25:05.599057-03 backend_xmin | state | streaming sent_lsn | 56/4729C750 write_lsn | 56/4729C750 flush_lsn | 56/4729C750 replay_lsn | 56/46007120 write_lag | 00:00:00.005546 flush_lag | 00:00:00.011271 replay_lag | 00:59:34.522039 sync_priority | 0 sync_state | async reply_time | 2022-03-17 12:24:49.02266-03
select * from pg_replication_slots;
-[ RECORD 1 ]-------+------------ slot_name | slot1 plugin | slot_type | physical datoid | database | temporary | f active | t active_pid | 21483 xmin | catalog_xmin | restart_lsn | 56/47313758 confirmed_flush_lsn | wal_status | reserved safe_wal_size | 10750970024 two_phase | f
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
An important word in the documentation:
The pg_stat_replication_slots view will contain one row per logical replication slot, showing statistics about its usage.
As intended, there is nothing for physical replication slots here. Perhaps this view could be called differently, but now it’s too late to rename.
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂