PostgreSQL Troubleshooting
Feb 22, 2023Logical Replication
Logical Replication is how data is primarily replicated.
SELECT name,setting FROM pg_settings WHERE name IN ('wal_level','rds.logical_replication');
Inactive Replication
If logical replication is not running, then it is wasting space, and we need to ensure that it is dropped if it is not in use.
SELECT slot_name, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(),restart_lsn)) AS replicationSlotLag, active FROM pg_replication_slots ;
# SELECT pg_drop_replication_slot('Your_slotname_name');
Disk Usage
Check what tables are taking up storage space.
SELECT datname, temp_files AS "Temporary files",pg_size_pretty(temp_bytes) AS "Size of temporary files" FROM pg_stat_database ;
SELECT
pg_database.datname,
pg_size_pretty(pg_database_size(pg_database.datname)) AS size
FROM pg_database;
Understand the Sizes of Indexes
SELECT datname, temp_files AS "Temporary files",temp_bytes AS "Size of temporary files" FROM pg_stat_database ;