Table of Contents
Issue : PostgreSQL – database cluster crashed as pg_xlog was 100% full
Environment:
PostgreSQL 9.6 cluster with primary-standby configuration, having 2 replication slots.
Any recent change in environment: Another DBA triggered AWS DMS task to migrate database to Aurora PostgreSQL.
Immediate action taken to start the databases : After moving some old xlog files to another mount point to release some space on pg_xlog, I was able to start database.
Root Cause: AWS DMS task failed because of some error.Consequently, corresponding replication slot in primary database was in inactive state. Because of live/inactive replication slot in primary database, database cluster was not clearing old xlog files.
I deleted the inactive replication slot and it cleared up huge amount of space on pg_xlog mount point on primary database.
Useful command –
To monitor replication slots –
select * from pg_replication_slots;
To delete replication slot –
select pg_drop_replication_slot(‘aws-dms-rep-slot’)
#-# pg_drop_replication_slot will not work if replication slot is not inactive.
Checkout more PostgreSQL posts
