PostgreSQL Bible

---

Queries

View all databases

\l

View all users

\du

View all tables

\dt

Drop database and connections

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

DROP DATABASE mydb;

Permissions

RDS

 rds_iam         | Cannot login                                               | {}
 rds_password    | Cannot login                                               | {}
 rds_replication | Cannot login                                               | {}
 rds_superuser   | Cannot login                                               | {pg_monitor,pg_signal_backend,rds_replication,rds_password}
 rdsadmin        | Superuser, Create role, Create DB, Replication, Bypass RLS+| {}
 rdsrepladmin    | No inheritance, Cannot login, Replication                  | {}

Configuration

reload conf

SELECT pg_reload_conf();

if WAL log fills HDD

find latest log file <PG_DIR>/bin/pg_controldata -D <PG_DATA_DIR>

/usr/lib/postgresql/10/bin/pg_controldata -D /var/lib/postgresql/10/main

delete old files

<PG_DIR>/bin/pg_archivecleanup -n <PG_DIR> 000000010000000F00000026 <PG_DIR>/bin/pg_archivecleanup -d <PG_DIR> 000000010000000F00000026

• dont forget to set number of wal files

creating database

createdb [new database name]

backup & restore

pg_dump -Fc -v -h [endpoint of instance] -U [master username] [database] > [database].dump pg_dump -Fc -t [table_1] -t [table_2] -t [table_3] ... -h [endpoint of instance] -U [master username] [database] > [database].dump pg_restore -v -h [endpoint of instance] -U [master username] -d [new database name] [database].dump

check table sizes

SELECT
   relname as "Table",
   pg_size_pretty(pg_total_relation_size(relid)) As "Size",
   pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) as "External Size"
   FROM pg_catalog.pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC;

check db sizes

SELECT d.datname AS Name,  pg_catalog.pg_get_userbyid(d.datdba) AS Owner,
    CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
        THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname))
        ELSE 'No Access'
    END AS SIZE
FROM pg_catalog.pg_database d
    ORDER BY
    CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
        THEN pg_catalog.pg_database_size(d.datname)
        ELSE NULL
    END DESC -- nulls first
    LIMIT 20

view all material views

SELECT oid::regclass::text
FROM   pg_class
WHERE  relkind = 'm';

force close all connections to db

SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE pg_stat_activity.datname = 'TARGET_DB' -- ← change this to your DB
  AND pid <> pg_backend_pid();

use full row as identity

ALTER TABLE name REPLICA IDENTITY FULL;

fix sequence

-- check max id
SELECT MAX(id) FROM table_name;

-- check next val
SELECT nextval('table_name_id_seq');

-- fix sequence
BEGIN;
    LOCK TABLE table_name IN EXCLUSIVE MODE;
    SELECT setval('table_name_id_seq', COALESCE((SELECT MAX(id)+1 FROM table_name), 1), false);
COMMIT;