Fringe Division

Appearance

Postgres ​

IAM ​

Create user ​

sql
CREATE DATABASE $DATABASE;
CREATE USER $USER WITH ENCRYPTED PASSWORD '$PASSWORD';
GRANT ALL PRIVILEGES ON DATABASE mydb TO $USER;

Create role ​

sql
CREATE ROLE $ROLE WITH LOGIN PASSWORD '$PASSWORD';

Grant permissions ​

sql
-- admin
GRANT SELECT, INSERT, UPDATE, DELETE ON all tables IN SCHEMA public TO {ROLE};

-- readonly
GRANT SELECT ON all tables IN SCHEMA public TO {ROLE};

-- readonly-specific schema
GRANT SELECT ON {SCHEMA} TO {ROLE};

-- allow execute functions
GRANT EXECUTE ON all functions IN SCHEMA public TO user;

Activity ​

Get activity log ​

sql
SELECT usename,datname,count(*)
FROM pg_stat_activity
GROUP BY usename,datname;

with runtime

sql
SELECT pid, datname, usename, query_start, now() - query_start as runtime, query
FROM pg_stat_activity
ORDER BY runtime;

Get session count ​

sql
SELECT count(*)
FROM pg_stat_activity;

Vacuum ​

sql
-- full stats
SELECT relname, seq_scan, n_live_tup, n_dead_tup, n_tup_del, last_autovacuum, last_autoanalyze, autovacuum_count, autovacuum_count FROM pg_stat_user_tables;

-- see alive/dead row count
SELECT n_live_tup, n_dead_tup, relname FROM pg_stat_all_tables;

-- prune dead rows
VACUUM job_ticks;

See indexing progress ​

sql
SELECT
   p.phase,
   p.blocks_total,
   p.blocks_done,
        p.blocks_total - p.blocks_done as blocks_left,
   p.tuples_total,
   p.tuples_done,
   ai.schemaname,
   ai.relname,
   ai.indexrelname
FROM pg_stat_progress_create_index p
JOIN pg_stat_activity a ON p.pid = a.pid
LEFT JOIN pg_stat_all_indexes ai on ai.relid = p.relid AND ai.indexrelid = p.index_relid;

Terminate process ​

sql
SELECT * FROM pg_stat_activity;
SELECT pg_terminate_backend(${PID});

Statistics ​

Find most used tables ​

sql
SELECT schemaname, relname,
       COALESCE(seq_scan, 0) + COALESCE(idx_scan, 0) nr_accesses
FROM pg_stat_all_tables
WHERE schemaname='public'
ORDER BY 3 DESC NULLS LAST;

Get table size ​

sql
SELECT
    table_name,
    pg_relation_size(quote_ident(table_name)),
    pg_size_pretty(pg_relation_size(quote_ident(table_name)))
FROM
    information_schema.tables
WHERE
    table_schema = 'public'
ORDER BY
    pg_total_relation_size(quote_ident(table_name)) DESC;

Get index size ​

sql
SELECT
   relname  as table_name,
   pg_size_pretty(pg_total_relation_size(relid)) As "Total Size",
   pg_size_pretty(pg_indexes_size(relid)) as "Index Size",
   pg_size_pretty(pg_relation_size(relid)) as "Actual Size"
   FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC;

Collation ​

bash
CREATE DATABASE $your_database_name LC_COLLATE='th_TH.utf8' LC_CTYPE='th_TH.utf8' TEMPLATE=template0;

Backup and restore ​

bash
# backup
$ pg_dump --host HOST --port 5432 --username USERNAME --format plain --verbose --file OUTFILE.sql --table public.TABLE_NAME DB_NAME
$ psql --host HOST --port 5432 --username USERNAME -d DB_NAME < BACKUP.sql

## another variant
# compression rate: 10x
$ pg_dump --host $HOST --port $PORT --username $USERNAME --dbname $DBNAME -t $TABLE_NAME -Fc -f $FILENAME.bin
$ pg_restore --host $HOST --port $PORT --username $USERNAME --dbname $DBNAME --no-owner --no-privileges  $FILENAME.bin

PSQL ​

bash
# list databases
\l

# list tables
\dt

# list column dtypes
\d+ table_name

# list indexes and size
\di+ {INDEX_PREFIX}*

# select database
\c {DB_NAME}

# login
psql -h HOST -d DB_NAME -U USER

# export to csv
db> \copy (SELECT  * FROM district_boundary) TO '~/Downloads/file.tsv' WITH (FORMAT CSV, HEADER, DELIMITER E'\t')

# export stdout to file
db> \o out.txt

Troubleshooting ​

Tools ​

Playground ​

Resources ​