PostgreSQL Troubleshooting

Logical 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 ;