efm 4 - "Prerequisites" page: adding prerequisite settings when enabling `update.physical.slots.period` setting
Opened this issue · 0 comments
CC-Hsu commented
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
- Setting different
primary_slot_name
GUC in the primary server and the failover replica server - 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?
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