Hi folks,
I am having trouble with a PostgreSQL instance which has had it's disk space usage growing for 15 days.
Given that the space is being used by the write ahead log, and the " Oldest transaction by age " is "replication_slot", I suspect there is something borked about replication (although I have not configured any replication for this instance yet).
This page: https://cloud.google.com/sql/docs/postgres/replication/configure-logical-replication also suggests that replication might be the cause:
" slot that's not actively used to track data causes PostgreSQL to hold onto WAL segments indefinitely, causing the disk space to grow indefinitely. Most tools built on logical decoding create and drop replication slots automatically. Unused replication slots can be detected by querying the pg_replication_slots system view and filtering on the active column. Unused slots can be dropped to remove WAL segments using the pg_drop_replication_slot command."
I do indeed have an inactive replication slot:
+------------------------------------------------+------+---------+------+--------+---------+------+----------+--------+------------+-----------+-------------------+----------+-------------+---------+---------------------------------+-----------+-------------------+--------+------+-------+
|slot_name |plugin|slot_type|datoid|database|temporary|active|active_pid|xmin |catalog_xmin|restart_lsn|confirmed_flush_lsn|wal_status|safe_wal_size|two_phase|inactive_since |conflicting|invalidation_reason|failover|synced|age |
+------------------------------------------------+------+---------+------+--------+---------+------+----------+--------+------------+-----------+-------------------+----------+-------------+---------+---------------------------------+-----------+-------------------+--------+------+-------+
|cloudsql_19_ca147fbb_c6d4_4e36_83f6_7f41b81cf657|null |physical |null |null |false |false |null |34943788|null |CC/4C25FFD0|null |extended |null |false |2024-12-17 17:24:43.507569 +00:00|null |null |false |false |2664130|
+------------------------------------------------+------+---------+------+--------+---------+------+----------+--------+------------+-----------+-------------------+----------+-------------+---------+---------------------------------+-----------+-------------------+--------+------+-------+
So I've tried what the documentation recommends, dropping the slot:
select pg_drop_replication_slot('cloudsql_19_ca147fbb_c6d4_4e36_83f6_7f41b81cf657');
However this fails with:
'pq: must be superuser or cloudsqlreplica to modify replication slots starting with "cloudsql"'
even if I am logged in with the `postgres` user and granted myself replication roles with:
ALTER ROLE postgres WITH REPLICATION;
Am I missing something, or is the documentation suggesting something that is not possible?
Any advice would be welcome.
LinkedIn
Twitter
