Postgres CDC.sql

/* Provides a listing of all replication slots with WAL size in megabytes for each slot. */

SELECT
  slot_name,
  pg_size_pretty(
    pg_wal_lsn_diff(
      pg_current_wal_lsn(), restart_lsn)) AS retained_wal,
  active,
  restart_lsn FROM pg_replication_slots;

/* Provides a listing of all replication slots that currently exist on the database cluster, along with their current state. */

SELECT * FROM pg_replication_slots;

/* Provides information about the status of clients attached to the walsender. */

SELECT * FROM pg_stat_replication;

/* Provides information about the status of WAL files archived via the archive_command parameter, including times of the last successful and failed archive attempts. */

SELECT * FROM pg_stat_archiver;

/* This command starts the procedure of creating a recovery point and during it Postgres removes unneeded segments from pg_wal. But note, this procedure when running, might cause extra IO and drop queries’ performance, especially in case of large shared buffers and high write traffic. */

CHECKPOINT;

/* Allows a role to use replication functionaluty. */

ALTER ROLE role_name WITH REPLICATION;

/*  Drops existing connections except for yours. */

SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE pg_stat_activity.datname = 'DB name'
  AND pid <> pg_backend_pid();

/*  Shows current WAL level (replica, logical). */

SHOW wal_level;

/*  Drops DB when there are active connections. */

GRANT pg_signal_backend TO your_user;

SELECT pg_terminate_backend (pg_stat_activity.pid)
FROM pg_stat_activity
WHERE pg_stat_activity.datname = 'your_database_name';

DROP DATABASE your_database_name;

REVOKE pg_signal_backend FROM your_user;

/* Provides a listing of all DBs with size in megabytes for Postgres server. */
SELECT 
    datname AS database_name, 
    pg_size_pretty(pg_database_size(datname)) AS size_in_mb
FROM 
    pg_database
ORDER BY pg_database_size(datname) DESC;
    
/* Provides a listing of all tables with size in megabytes for DB. */
SELECT 
    table_schema || '.' || table_name AS table_full_name,
    pg_size_pretty(pg_total_relation_size('"' || table_schema || '"."' || table_name || '"')) AS size_in_mb
FROM 
    information_schema.tables
WHERE 
    table_schema NOT LIKE 'pg_%' 
    AND table_schema != 'information_schema';