SELECT
    pid,
    usename,
    datname,
    state,
    wait_event_type,
    wait_event,
    now() - xact_start        AS xact_duration,
    now() - query_start       AS query_duration,
    left(query, 100)          AS query_snippet,
    client_addr,
    application_name
FROM pg_stat_activity
WHERE pid != pg_backend_pid()
ORDER BY xact_start NULLS LAST;

-- Change '5 minutes' to your threshold
SELECT
    pid,
    usename,
    datname,
    state,
    wait_event_type,
    wait_event,
    now() - query_start   AS query_duration,
    now() - xact_start    AS xact_duration,
    left(query, 200)       AS query
FROM pg_stat_activity
WHERE
    state != 'idle'
    AND (now() - query_start) > interval '5 minutes'
    AND pid != pg_backend_pid()
ORDER BY query_duration DESC;

-- Run this repeatedly every few seconds to see patterns
SELECT
    COALESCE(wait_event_type, 'CPU / Running') AS wait_type,
    COALESCE(wait_event,      '—')           AS wait_event,
    COUNT(*)                                   AS sessions,
    STRING_AGG(pid::text, ', ')              AS pids
FROM pg_stat_activity
WHERE
    state != 'idle'
    AND pid != pg_backend_pid()
GROUP BY wait_event_type, wait_event
ORDER BY sessions DESC;

SELECT
    blocked.pid                             AS blocked_pid,
    blocked.usename                         AS blocked_user,
    blocked.wait_event_type                 AS blocked_wait_type,
    left(blocked.query, 80)                 AS blocked_query,
    blocking.pid                            AS blocking_pid,
    blocking.usename                        AS blocking_user,
    left(blocking.query, 80)                AS blocking_query,
    now() - blocked.query_start            AS blocked_duration
FROM pg_stat_activity AS blocked
JOIN pg_stat_activity AS blocking
    ON blocking.pid = ANY(pg_blocking_pids(blocked.pid))
WHERE cardinality(pg_blocking_pids(blocked.pid)) > 0
ORDER BY blocked_duration DESC;

-- Terminate the blocker (replace PID)
-- SELECT pg_terminate_backend(blocking_pid);

-- All databases with sizes
SELECT
    datname,
    pg_size_pretty(pg_database_size(datname)) AS db_size
FROM pg_database
ORDER BY pg_database_size(datname) DESC;

-- Top 20 tables by total size (table + indexes + TOAST)
SELECT
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS total_size,
    pg_size_pretty(pg_relation_size(schemaname||'.'||tablename))       AS table_size,
    pg_size_pretty(pg_indexes_size(schemaname||'.'||tablename))       AS index_size
FROM pg_tables
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC
LIMIT 20;

-- Connection usage summary
SELECT
    current_setting('max_connections')::int       AS max_conn,
    COUNT(*)                                       AS used_conn,
    current_setting('max_connections')::int -
        COUNT(*)                                   AS free_conn,
    round(COUNT(*) * 100.0 /
        current_setting('max_connections')::int, 1) AS pct_used
FROM pg_stat_activity;

-- Connections per user and database
SELECT
    usename,
    datname,
    state,
    COUNT(*)   AS conn_count
FROM pg_stat_activity
WHERE pid != pg_backend_pid()
GROUP BY usename, datname, state
ORDER BY conn_count DESC;

-- Database-level cache hit ratio
SELECT
    datname,
    blks_hit,
    blks_read,
    round(blks_hit * 100.0 /
        NULLIF(blks_hit + blks_read, 0), 2) AS cache_hit_pct
FROM pg_stat_database
WHERE datname = current_database()
ORDER BY cache_hit_pct;

-- Per-table cache hit (find cold tables)
SELECT
    schemaname,
    relname,
    heap_blks_hit,
    heap_blks_read,
    round(heap_blks_hit * 100.0 /
        NULLIF(heap_blks_hit + heap_blks_read, 0), 1) AS hit_pct
FROM pg_statio_user_tables
WHERE heap_blks_read > 0
ORDER BY heap_blks_read DESC
LIMIT 20;

-- XID age per database (alert if > 1.5 billion)
SELECT
    datname,
    age(datfrozenxid)                                          AS xid_age,
    round(age(datfrozenxid)::numeric / 2100000000 * 100, 1) AS pct_toward_limit,
    current_setting('autovacuum_freeze_max_age')              AS freeze_max_age
FROM pg_database
ORDER BY xid_age DESC;

-- Tables with oldest XID (find the freeze bottleneck)
SELECT
    schemaname,
    relname,
    age(relfrozenxid)  AS table_xid_age,
    pg_size_pretty(pg_relation_size(oid)) AS size
FROM pg_class
WHERE relkind = 'r'
ORDER BY age(relfrozenxid) DESC
LIMIT 15;

-- Force freeze on a specific table
-- VACUUM FREEZE VERBOSE mytable;

-- Requires: shared_preload_libraries = 'pg_stat_statements'
-- CREATE EXTENSION pg_stat_statements;

-- Top 15 by total execution time
SELECT
    round(total_exec_time::numeric, 2)  AS total_ms,
    calls,
    round(mean_exec_time::numeric, 2)   AS avg_ms,
    round(stddev_exec_time::numeric, 2) AS stddev_ms,
    rows,
    round(total_exec_time / sum(total_exec_time) OVER() * 100, 1) AS pct_total,
    left(query, 100)                     AS query_snippet
FROM pg_stat_statements
WHERE calls > 10
ORDER BY total_exec_time DESC
LIMIT 15;

-- Top by average execution time (worst per-call queries)
SELECT
    round(mean_exec_time::numeric, 2) AS avg_ms,
    calls,
    left(query, 120) AS query
FROM pg_stat_statements
WHERE calls > 5
ORDER BY mean_exec_time DESC
LIMIT 15;

SELECT
    schemaname,
    relname                                                          AS table_name,
    n_live_tup,
    n_dead_tup,
    round(n_dead_tup * 100.0 /
        NULLIF(n_live_tup + n_dead_tup, 0), 1)                     AS dead_pct,
    pg_size_pretty(pg_relation_size(schemaname||'.'||relname))     AS table_size,
    last_autovacuum,
    last_vacuum,
    last_autoanalyze
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY dead_pct DESC, n_dead_tup DESC
LIMIT 20;

-- Action: if dead_pct > 10%, run:
-- VACUUM ANALYZE schemaname.table_name;
-- Or for disk reclaim (LOCKS table!): VACUUM FULL table_name;
-- Production safe: pg_repack -d mydb -t table_name

-- Index sizes and usage
SELECT
    schemaname,
    tablename,
    indexname,
    pg_size_pretty(pg_relation_size(indexrelid))   AS index_size,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes
JOIN pg_index USING (indexrelid)
WHERE pg_relation_size(indexrelid) > 10485760  -- > 10MB
ORDER BY pg_relation_size(indexrelid) DESC
LIMIT 20;

-- Rebuild a bloated index online (no lock)
-- REINDEX INDEX CONCURRENTLY idx_name;
-- Or rebuild all indexes on a table:
-- REINDEX TABLE CONCURRENTLY mytable;

SELECT
    schemaname,
    tablename,
    indexname,
    idx_scan                                                   AS times_used,
    pg_size_pretty(pg_relation_size(indexrelid))             AS index_size,
    pg_size_pretty(pg_relation_size(indrelid))               AS table_size,
    CASE WHEN indisunique THEN 'UNIQUE' ELSE '' END           AS unique_flag
FROM pg_stat_user_indexes
JOIN pg_index  ON indexrelid = pg_index.indexrelid
WHERE
    idx_scan = 0
    AND NOT indisprimary
    AND NOT indisunique
    AND pg_relation_size(indexrelid) > 1048576  -- > 1MB
ORDER BY pg_relation_size(indexrelid) DESC;

-- DROP unused index (always test first):
-- DROP INDEX CONCURRENTLY idx_name;

SELECT
    schemaname,
    relname                                                          AS table_name,
    seq_scan,
    idx_scan,
    round(seq_scan * 100.0 /
        NULLIF(seq_scan + idx_scan, 0), 1)                        AS seq_pct,
    seq_tup_read,
    pg_size_pretty(pg_relation_size(schemaname||'.'||relname))     AS table_size,
    n_live_tup
FROM pg_stat_user_tables
WHERE
    seq_scan > 100
    AND pg_relation_size(schemaname||'.'||relname) > 10485760  -- > 10MB
ORDER BY seq_tup_read DESC
LIMIT 20;

SELECT
    SUM(seq_scan)                                        AS total_seq_scans,
    SUM(idx_scan)                                        AS total_idx_scans,
    round(SUM(idx_scan) * 100.0 /
        NULLIF(SUM(seq_scan) + SUM(idx_scan), 0), 1)  AS idx_pct,
    SUM(seq_tup_read)                                    AS rows_from_seq,
    SUM(idx_tup_fetch)                                   AS rows_from_idx
FROM pg_stat_user_tables;

SELECT
    schemaname,
    tablename,
    indexname,
    idx_scan        AS scan_count,
    idx_tup_read    AS rows_read,
    idx_tup_fetch   AS rows_fetched,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC
LIMIT 30;

SELECT
    schemaname,
    relname,
    n_live_tup,
    n_mod_since_analyze                                             AS modified_since_analyze,
    round(n_mod_since_analyze * 100.0 /
        NULLIF(n_live_tup, 0), 1)                                   AS mod_pct,
    last_autoanalyze,
    last_analyze
FROM pg_stat_user_tables
WHERE n_live_tup > 10000
ORDER BY n_mod_since_analyze DESC
LIMIT 20;

-- Force statistics update on a table
-- ANALYZE VERBOSE mytable;

SELECT name, setting, unit, short_desc
FROM pg_settings
WHERE name LIKE 'autovacuum%'
   OR name LIKE 'vacuum%'
ORDER BY name;

-- Check per-table autovacuum overrides
SELECT
    relname,
    reloptions
FROM pg_class
WHERE reloptions IS NOT NULL
  AND relkind = 'r'
ORDER BY relname;

SELECT
    schemaname,
    relname,
    n_live_tup,
    n_dead_tup,
    round(n_dead_tup * 100.0 /
        NULLIF(n_live_tup + n_dead_tup,0), 1)           AS dead_pct,
    -- autovacuum trigger threshold
    50 + 0.2 * n_live_tup                             AS vacuum_threshold,
    n_dead_tup >
        (50 + 0.2 * n_live_tup)                         AS needs_vacuum,
    last_autovacuum,
    last_vacuum
FROM pg_stat_user_tables
WHERE n_live_tup > 100
ORDER BY n_dead_tup DESC
LIMIT 25;

SELECT
    pid,
    now() - query_start   AS duration,
    query,
    state,
    wait_event_type,
    wait_event
FROM pg_stat_activity
WHERE query LIKE 'autovacuum%'
   OR query ILIKE '%vacuum%'
ORDER BY query_start;

-- Check autovacuum worker count vs max
SELECT
    count(*)                                         AS active_workers,
    current_setting('autovacuum_max_workers')::int   AS max_workers
FROM pg_stat_activity
WHERE query LIKE 'autovacuum:%';

SELECT
    schemaname,
    relname,
    last_vacuum,
    last_autovacuum,
    last_analyze,
    last_autoanalyze,
    vacuum_count,
    autovacuum_count,
    n_live_tup,
    n_dead_tup
FROM pg_stat_user_tables
ORDER BY
    GREATEST(last_vacuum, last_autovacuum) NULLS FIRST
LIMIT 20;

-- Sessions blocking autovacuum's oldest xmin
SELECT
    pid,
    usename,
    datname,
    state,
    backend_xmin,
    now() - xact_start   AS open_transaction_age,
    left(query, 100)     AS last_query
FROM pg_stat_activity
WHERE
    state IN (
        'idle in transaction',
        'idle in transaction (aborted)'
    )
ORDER BY xact_start NULLS LAST;

-- Overall oldest xmin in the cluster
SELECT
    min(backend_xmin) AS oldest_xmin,
    now() - min(xact_start) AS oldest_txn_age
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL;

SELECT
    application_name,
    client_addr,
    state,
    sync_state,
    sent_lsn,
    write_lsn,
    flush_lsn,
    replay_lsn,
    write_lag,
    flush_lag,
    replay_lag,
    pg_size_pretty(sent_lsn - replay_lsn) AS lag_bytes
FROM pg_stat_replication
ORDER BY application_name;

-- On PRIMARY: lag per standby
SELECT
    application_name,
    pg_size_pretty(pg_wal_lsn_diff(
        pg_current_wal_lsn(), replay_lsn))   AS replay_lag_bytes,
    replay_lag                                AS replay_lag_time,
    flush_lag,
    write_lag
FROM pg_stat_replication;

-- On STANDBY: lag from this replica's perspective
SELECT
    now() - pg_last_xact_replay_timestamp()  AS replication_delay,
    pg_last_wal_receive_lsn()                 AS received_lsn,
    pg_last_wal_replay_lsn()                  AS replayed_lsn,
    pg_is_in_recovery()                       AS is_standby;

SELECT
    slot_name,
    slot_type,
    database,
    active,
    active_pid,
    pg_size_pretty(pg_wal_lsn_diff(
        pg_current_wal_lsn(),
        COALESCE(confirmed_flush_lsn, restart_lsn))) AS wal_retained,
    restart_lsn,
    confirmed_flush_lsn
FROM pg_replication_slots
ORDER BY pg_wal_lsn_diff(
    pg_current_wal_lsn(),
    COALESCE(confirmed_flush_lsn, restart_lsn)) DESC;

-- Drop a stale/unused slot (DANGEROUS if consumer still needs it)
-- SELECT pg_drop_replication_slot('slot_name');

-- WAL generation stats (PostgreSQL 14+)
SELECT
    wal_records,
    wal_fpi                                          AS full_page_images,
    pg_size_pretty(wal_bytes)                        AS wal_bytes,
    wal_buffers_full,
    wal_write,
    wal_sync,
    round(wal_write_time / 1000, 2)                AS write_time_sec,
    round(wal_sync_time  / 1000, 2)                AS sync_time_sec
FROM pg_stat_wal;

-- Checkpoint stats
SELECT
    checkpoints_timed,
    checkpoints_req,
    buffers_checkpoint,
    buffers_clean,
    buffers_backend,
    round(checkpoint_write_time / 1000, 2)         AS write_sec,
    round(checkpoint_sync_time  / 1000, 2)         AS sync_sec
FROM pg_stat_bgwriter;

SELECT
    archived_count,
    last_archived_wal,
    last_archived_time,
    failed_count,
    last_failed_wal,
    last_failed_time,
    now() - last_archived_time   AS time_since_last_archive
FROM pg_stat_archiver;

-- Verify archive_mode and archive_command
SHOW archive_mode;
SHOW archive_command;

-- Force a WAL switch to test archive_command
-- SELECT pg_switch_wal();

SELECT
    pg_is_in_recovery()                        AS is_standby,
    pg_last_wal_receive_lsn()                  AS received_lsn,
    pg_last_wal_replay_lsn()                   AS replayed_lsn,
    pg_last_xact_replay_timestamp()            AS last_replay_time,
    now() - pg_last_xact_replay_timestamp()    AS replay_lag;

-- Promote standby to primary (PostgreSQL 12+)
-- SELECT pg_promote();
-- Or: pg_ctl promote -D $PGDATA

# Standard backup (tar format, stream WAL, show progress)
pg_basebackup \
    -U postgres \
    -D /pgBackup/pgsql17/backup/base_$(date +%d%b%Y) \
    -Ft -Xs -P -v \
    > /pgBackup/pgsql17/backup/base_$(date +%d%b%Y).log 2>&1

# Run in background with nohup
nohup pg_basebackup \
    -U postgres \
    -D /pgBackup/pgsql17/backup/base_$(date +%d%b%Y) \
    -Ft -Xs -P -v \
    > /pgBackup/pgsql17/backup/base_$(date +%d%b%Y).log 2>&1 &

# Remote backup (from standby or DBA host)
pg_basebackup \
    -h 192.168.1.51 -p 5432 \
    -U repl_user \
    -D /pgBackup/base_$(date +%d%b%Y) \
    -Ft -Xs -P -v -R

# -R flag auto-creates standby.signal + postgresql.auto.conf
# for streaming replication setup

# Dump entire database (custom format — recommended)
pg_dump -U postgres -d mydb -Fc -v \
    -f /backup/mydb_$(date +%d%b%Y).dmp

# Dump schema only (no data)
pg_dump -U postgres -d mydb --schema-only \
    -f /backup/mydb_schema.sql

# Dump data only
pg_dump -U postgres -d mydb --data-only \
    -Fc -f /backup/mydb_data.dmp

# Dump specific table
pg_dump -U postgres -d mydb \
    -t public.orders -Fc -v \
    -f /backup/orders_$(date +%d%b%Y).dmp

# Dump all databases + global objects
pg_dumpall -U postgres -f /backup/all_$(date +%d%b%Y).sql

# Restore full database
pg_restore -U postgres -d mydb -v \
    /backup/mydb_$(date +%d%b%Y).dmp

# Restore single table into existing DB
pg_restore -U postgres -d mydb \
    -t public.orders -v /backup/orders.dmp

# Restore with parallel workers (faster)
pg_restore -U postgres -d mydb -j 4 -v \
    /backup/mydb.dmp

-- Check if paused at target time
SELECT pg_is_in_recovery(), pg_last_xact_replay_timestamp();

-- Validate your data is as expected
SELECT * FROM myschema.mytable LIMIT 10;

-- Promote to full primary
SELECT pg_wal_replay_resume();

-- Verify promotion complete
SELECT pg_is_in_recovery();  -- should return 'f'

-- List all tablespaces with size
SELECT
    spcname                                          AS tablespace_name,
    pg_catalog.pg_tablespace_location(oid)           AS location,
    pg_catalog.pg_get_userbyid(spcowner)             AS owner,
    pg_size_pretty(pg_tablespace_size(spcname))     AS size
FROM pg_tablespace
ORDER BY spcname;

-- Which databases use each tablespace?
SELECT
    t.spcname    AS tablespace,
    d.datname    AS database
FROM pg_database d
JOIN pg_tablespace t ON d.dattablespace = t.oid
ORDER BY t.spcname;

-- Symbolic links in pg_tblspc directory
-- ls -la $PGDATA/pg_tblspc/

SELECT
    pid,
    locktype,
    COALESCE(relation::regclass::text, '—') AS relation,
    mode,
    granted,
    fastpath,
    pg_stat_activity.state,
    pg_stat_activity.wait_event_type,
    pg_stat_activity.wait_event,
    left(pg_stat_activity.query, 60)          AS query
FROM pg_locks
JOIN pg_stat_activity USING (pid)
WHERE
    locktype != 'virtualxid'
    AND NOT fastpath
ORDER BY granted, pid;