PostgreSQL basics for people who are not DBAs

psql "postgresql://user:password@host:5432/dbname"

\l            list databases
\c dbname     connect to a database
\dt           list tables
\d tablename  describe a table
\du           list roles
\dn           list schemas
\x            toggle expanded output (great for wide rows)
\q            quit

-- create a role and a database it owns
CREATE ROLE app LOGIN PASSWORD 'strong-random-password';
CREATE DATABASE appdb OWNER app;

-- a table
CREATE TABLE users (
  id         bigserial PRIMARY KEY,
  email      text NOT NULL UNIQUE,
  created_at timestamptz NOT NULL DEFAULT now()
);

-- insert, select, update, delete
INSERT INTO users (email) VALUES ('[email protected]');
SELECT * FROM users ORDER BY created_at DESC LIMIT 10;
UPDATE users SET email = '[email protected]' WHERE id = 1;
DELETE FROM users WHERE id = 1;

-- add an index on a column you filter by
CREATE INDEX users_created_at_idx ON users (created_at DESC);

CREATE ROLE reporter LOGIN PASSWORD 'another-strong-password';
GRANT CONNECT ON DATABASE appdb TO reporter;
GRANT USAGE ON SCHEMA public TO reporter;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO reporter;

# custom-format dump (compressed, restorable selectively)
pg_dump -Fc -f appdb-$(date +%F).dump appdb

# restore
pg_restore -d appdb appdb-2026-01-01.dump

# plain SQL dump (human-readable)
pg_dump appdb > appdb.sql

SELECT count(*) FROM pg_stat_activity WHERE state <> 'idle';
SELECT relname, n_live_tup FROM pg_stat_user_tables ORDER BY n_live_tup DESC LIMIT 10;
SELECT pid, state, query FROM pg_stat_activity WHERE state <> 'idle';