efm 4 - "Prerequisites" page: adding prerequisite settings when enabling update.physical.slots.period setting

[CC-Hsu]

Summary

Hi, team,

Currently the documentation does not mention about the prerequisite setup when setting up physical replication slot for a EFM cluster.

Specifically, when setting update.physical.slots.period to a non-zero value, one should also

1. Setting different primary_slot_name GUC in the primary server and the failover replica server
2. For a non-superuser setup, the following settings are also required.

--  Not mentioned in the doc
-- Allowing EFM user <user_name> to control replication slot LSN
ALTER USER <user_name> REPLICATION;
-- Allowing EFM user <user_name> to perform replication slot LSN advance functionality
GRANT EXECUTE ON FUNCTION pg_replication_slot_advance TO <user_name> ;
GRANT EXECUTE ON FUNCTION pg_create_physical_replication_slot TO <user_name> ;
GRANT EXECUTE ON FUNCTION pg_drop_replication_slot TO <user_name> ;

Where did you see the problem?

https://github.com/EnterpriseDB/docs/blob/main/product_docs/docs/efm/4/installing/prerequisites.mdx#ensure-that-the-database-user-has-sufficient-privileges

Expected behavior

The documentation should additionally mentions about prerequisites when the physical replication slot feature is used together with EFM.

Screenshots

Normally, the efm.log in the standby DB server should report the following messages

[root@edb16standby ~]# grep advancePhysicalReplicationSlots /var/log/efm-4.8/efm.log
2024-04-16 13:49:58 com.enterprisedb.efm.nodes.EfmAgent advancePhysicalReplicationSlots INFO: slot info to advance: {physlot_on_edb16s=E/D0C48C98, physlot_on_edb16p=E/D0C48C98}
2024-04-16 13:50:07 com.enterprisedb.efm.nodes.EfmAgent advancePhysicalReplicationSlots INFO: slot info to advance: {physlot_on_edb16s=E/D0C48CD0, physlot_on_edb16p=E/D0C48CD0}
2024-04-16 13:52:57 com.enterprisedb.efm.nodes.EfmAgent advancePhysicalReplicationSlots INFO: slot info to advance: {physlot_on_edb16s=E/D0C48DB8, physlot_on_edb16p=E/D0C48DB8}
2024-04-16 13:55:44 com.enterprisedb.efm.nodes.EfmAgent advancePhysicalReplicationSlots INFO: slot info to advance: {physlot_on_edb16s=E/D0C48DB8, physlot_on_edb16p=E/D0C48DB8}
2024-04-16 13:56:00 com.enterprisedb.efm.nodes.EfmAgent advancePhysicalReplicationSlots INFO: slot info to advance: {physlot_on_edb16s=E/D0C48DB8, physlot_on_edb16p=E/D0C48DB8}
[root@edb16standby ~]#

If the primary_slot_name is set the same in both primary and standby, the LSN advance functionality does not work properly.

2024-02-23 08:49:11 com.enterprisedb.efm.nodes.EfmAgent advancePhysicalReplicationSlots INFO: slot info to advance: {streaming_replication_slot=3/D8001658}

If replication privilege is lacked or related replication slot management functions are not granted.

[root@edb16standby ~]# grep ERR /var/log/efm-4.8/efm.log
2024-04-11 15:03:53 com.enterprisedb.efm.DBMonitor advancePhysicalReplicationSlots WARN: Could not advance replication slot physlot_on_edb16s. Error: org.postgresql.util.PSQLException: ERROR: permission denied to use replication slots
2024-04-11 15:05:45 com.enterprisedb.efm.DBMonitor advancePhysicalReplicationSlots WARN: Could not advance replication slot physlot_on_edb16s. Error: org.postgresql.util.PSQLException: ERROR: permission denied to use replication slots
2024-04-11 15:14:45 com.enterprisedb.efm.DBMonitor advancePhysicalReplicationSlots WARN: Could not advance replication slot physlot_on_edb16s. Error: org.postgresql.util.PSQLException: ERROR: permission denied to use replication slots
2024-04-11 15:15:45 com.enterprisedb.efm.DBMonitor advancePhysicalReplicationSlots WARN: Could not advance replication slot physlot_on_edb16s. Error: org.postgresql.util.PSQLException: ERROR: permission denied to use replication slots
2024-04-11 15:18:07 com.enterprisedb.efm.DBMonitor advancePhysicalReplicationSlots WARN: Could not advance replication slot physlot_on_edb16s. Error: org.postgresql.util.PSQLException: ERROR: permission denied to use replication slots
[root@edb16standby ~]# 
[root@edb16standby ~]# grep ERR /var/lib/edb/as16/log/enterprisedb-2024-04-11_000000.log 
2024-04-11 23:03:53.206 CST [416718] efm@edb/efm-4.8 ERROR:  permission denied to use replication slots
2024-04-11 23:05:45.111 CST [417053] efm@edb/efm-4.8 ERROR:  permission denied to use replication slots
2024-04-11 23:14:45.120 CST [417447] efm@edb/efm-4.8 ERROR:  permission denied to use replication slots
2024-04-11 23:15:45.105 CST [417466] efm@edb/efm-4.8 ERROR:  permission denied to use replication slots
2024-04-11 23:18:07.131 CST [417881] efm@edb/efm-4.8 ERROR:  permission denied to use replication slots
2024-04-11 23:22:13.978 CST [418251] efm@edb/efm-4.8 ERROR:  permission denied to use replication slots
2024-04-11 23:22:18.977 CST [418254] efm@edb/efm-4.8 ERROR:  permission denied to use replication slots
2024-04-11 23:22:33.977 CST [418255] efm@edb/efm-4.8 ERROR:  permission denied to use replication slots
[root@edb16standby ~]# 

Browser / Platform

• EFM 4.8
• RHEL 8.x

Additional notes

No response