PostgreSQL

psql and its meta-commands, roles, pg_dump backups and finding slow queries.

Connecting

Connect to a database

psql -h <host> -p <port> -U <user> -d <database>

Connect with a connection string

psql "postgresql://<user>@<host>:<port>/<database>"

Run a query and exit

psql -h <host> -U <user> -d <database> -c "SELECT now();"

Run an SQL file

psql -h <host> -U <user> -d <database> -f script.sql

psql meta-commands

List databases

\l

Switch to another database

\c <database>

List tables

\dt

Table structure

\d+ <table>

List roles

\du

Expanded output (handy for wide rows)

\x auto

Show query execution time

\timing on

Roles and privileges

Create a user

CREATE ROLE <user> WITH LOGIN PASSWORD 'password';

Create a database with an owner

CREATE DATABASE <database> OWNER <user>;

Read access to all tables in a schema

GRANT SELECT ON ALL TABLES IN SCHEMA public TO <user>;

Backup and restore

Dump a database as SQL

pg_dump -h <host> -U <user> -d <database> > <database>.sql

Dump in custom format

pg_dump -h <host> -U <user> -d <database> -Fc -f <database>.dump

Custom format is compressed and allows selective restore with pg_restore.

Restore a custom dump with 4 jobsadvanced

pg_restore -h <host> -U <user> -d <database> -j 4 --no-owner <database>.dump

Troubleshooting

Running queries

SELECT pid, state, now() - query_start AS duration, query FROM pg_stat_activity WHERE state <> 'idle' ORDER BY duration DESC;

Cancel a query

SELECT pg_cancel_backend(<pid>);

Terminate a connection

SELECT pg_terminate_backend(<pid>);

Table sizes including indexesadvanced

SELECT relname, pg_size_pretty(pg_total_relation_size(relid)) AS size
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 20;

Who is blocking whomadvanced

SELECT pid, pg_blocking_pids(pid) AS blocked_by, query FROM pg_stat_activity WHERE cardinality(pg_blocking_pids(pid)) > 0;

Execution plan with actual numbersadvanced

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM <table> WHERE id = 1;