psql and Admin Basics

-- psql "postgres://user@localhost:5432/app"
-- psql -h localhost -U user -d app

-- \dt
-- \d users
-- columns, indexes, FKs

GRANT SELECT, INSERT ON ALL TABLES IN SCHEMA app TO app_rw;
REVOKE ALL ON SCHEMA app FROM PUBLIC;

VACUUM (ANALYZE) orders;
-- updates stats for orders

BEGIN;
SET LOCAL statement_timeout = '5s';
SELECT pg_sleep(1);
COMMIT;
-- timeout applies only in that transaction

-- \timing on
-- Time: 12.345 ms

-- \copy users(email) FROM 'users.csv' CSV HEADER

CREATE ROLE app_ro LOGIN PASSWORD '...';
GRANT CONNECT ON DATABASE app TO app_ro;

SELECT current_user, current_database();
-- role | db

SELECT pid, usename, state, query
FROM pg_stat_activity
WHERE datname = current_database();
-- active backends

SELECT pg_cancel_backend(12345);
-- t if signal sent

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
-- tracking extension installed

SELECT pg_size_pretty(pg_total_relation_size('orders'));
-- e.g. 128 MB

SELECT datname, age(datfrozenxid)
FROM pg_database
ORDER BY 2 DESC
LIMIT 5;
-- higher age = older xid horizon

-- \e
-- opens editor with last query