PostgreSQL Cheat Sheet

psql -U postgres

psql -h host -U user -d dbname

\l

\c dbname

\dt

\d tablename

\du

\q

\timing on

CREATE DATABASE mydb;

DROP DATABASE mydb;

ALTER DATABASE mydb RENAME TO newdb;

CREATE SCHEMA api;

SET search_path TO api, public;

CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL);

ALTER TABLE users ADD COLUMN email TEXT UNIQUE;

ALTER TABLE users DROP COLUMN email;

CREATE INDEX idx_users_email ON users(email);

CREATE INDEX CONCURRENTLY idx_name ON tbl(col);

DROP INDEX idx_users_email;

TRUNCATE TABLE users RESTART IDENTITY CASCADE;

CREATE USER appuser WITH PASSWORD 'secret';

ALTER USER appuser WITH SUPERUSER;

GRANT ALL ON DATABASE mydb TO appuser;

GRANT SELECT, INSERT ON ALL TABLES IN SCHEMA public TO appuser;

REVOKE ALL ON DATABASE mydb FROM appuser;

pg_dump mydb > backup.sql

pg_dump -Fc mydb > backup.dump

pg_restore -d mydb backup.dump

psql mydb < backup.sql

pg_dumpall > all_databases.sql

EXPLAIN ANALYZE SELECT ...;

SELECT pg_size_pretty(pg_database_size('mydb'));

SELECT * FROM pg_stat_activity;

SELECT pg_cancel_backend(pid);

SELECT pg_terminate_backend(pid);

VACUUM ANALYZE tablename;