FATAL,55000,”logical replication slot “”pgl_userdatabase_provider1_subscription1"” exists, but wal_level SELECT slot_name,plugin FROM pg_replication_slots slot_name | plugin -+- pgl_userdatabase_provider1_subscription1 | pglogical_output This was what helped us to find the root cause of the issue. However, it failed at restart unexpectedly. Then I thought I can downgrade wal_level on the replicas from logical to hot_standby. This was guessed because streaming replicas cannot be modified.
psql> drop extension pglogical ERROR: cannot execute DROP EXTENSION in a read-only transaction Therefore, I started to suspect that the issue was somehow related to pglogical extension.įirst I have tried to remove the pglogical extension on the read replicas, but it didn’t allow it. Before the renewal of replicas, we had installed pglogical on the database master only and pglogical had not been enabled on streaming replicas. But something was wrong with those read replicas’ configuration.įurther investigating, we have noticed those read replicas inherited the pglogical extension from the database master. Actually, creating a read replica is the responsibility of our PaaS platform and that process does not include DBA operation at all. We’d renewed all 5 read replicas a week ago as a result of a replication failure.
However, at the time of the issue, the pg_xlog was consuming over 1TB of disk space. Each WAL file was 16MB thus 128GB of disk space usage by WAL files was expected. To recycle WAL files we use wal_keep_segments which was adjusted to 8192 on that cluster. After an initial look, it was obvious the replicas were not able to purge the WAL files. This was a database cluster with 5 read replicas that were using WAL streaming. Last week our monitoring tool alerted that the disks of read replicas were filling up in a critical very large database cluster. Why our PostgreSQL replicas couldn’t remove WAL files?
0 kommentar(er)
