psql [options] [dbname [username]]
sudo -u postgres psql
PostgreSQL ("Postgres") is a powerful open-source relational database. This sheet covers the
psql command-line client, everyday SQL, roles and permissions, backups, replication basics,
performance checks and routine admin tasks. It targets PostgreSQL 14 to 17.
In psql, SQL statements end with a semicolon. Backslash commands (\dt, \l) do not.
Be careful on production servers: DROP, DELETE and UPDATE without WHERE cannot be undone.
Install and service
sudo apt install postgresql postgresql-contrib # Debian/Ubuntu (starts automatically)
sudo dnf install postgresql-server postgresql-contrib # Fedora/RHEL family
sudo postgresql-setup --initdb # RHEL family: create the cluster first
brew install postgresql@16 # macOS
sudo systemctl enable --now postgresql # start now and at boot (RHEL: postgresql-16)
systemctl status postgresql
psql --version
sudo -u postgres psql -c "SELECT version();"
On Debian/Ubuntu you can run several clusters: pg_lsclusters, pg_createcluster, pg_ctlcluster 16 main restart
Connect
sudo -u postgres psql # as the postgres superuser via the local socket
psql -U alice -d mydb # user alice, database mydb (local socket)
psql -h 127.0.0.1 -p 5432 -U alice -d mydb # over TCP (asks for a password)
psql "postgresql://alice@db.example.com:5432/mydb?sslmode=require" # connection URI
psql -U alice -d mydb -c "SELECT now();" # run one statement and exit
psql -U alice -d mydb -f script.sql # run a file of SQL
psql -U alice -d mydb -At -c "SELECT id,name FROM users" > out.txt # unaligned, tuples only
psql -U alice -d mydb -X -q -v ON_ERROR_STOP=1 -f migrate.sql # stop on first error, ignore ~/.psqlrc
export PGHOST=db.example.com PGUSER=alice PGDATABASE=mydb PGSSLMODE=require # environment defaults
~/.pgpass # store passwords: host:port:db:user:password (chmod 600)
Do not put a password on the command line. It shows up in ps and shell history. Use ~/.pgpass or PGPASSWORD in a script.
psql meta-commands (type these at the prompt)
\? Help for backslash commands \h CREATE TABLE Help for SQL syntax
\q Quit \conninfo Current connection
\l \l+ List databases (with sizes) \c dbname Connect to another database
\dt \dt+ List tables \dt schema.* Tables in a schema
\d table Describe a table (columns, indexes) \d+ table With more detail
\di \dv \ds List indexes, views, sequences \df List functions
\dn List schemas \du \du+ List roles
\dp table Show privileges (grants) \dx List extensions
\dT List data types \db List tablespaces
\x Toggle expanded (vertical) output \x auto Vertical only when wide
\timing Show how long each query takes \a Toggle aligned output
\pset pager off Turn off the pager \H HTML output
\i file.sql Run a script \o out.txt Send results to a file (\o to stop)
\copy ... Client-side COPY (see import/export) \e Edit the query in $EDITOR
\g \gx Run the last query (\gx = expanded) \watch 2 Re-run the last query every 2 seconds
\set name value Set a variable, use as :name \! ls Run a shell command
\password alice Change a password safely (hashes it before sending)
\echo text Print text \r Clear the query buffer
Add a tab after "SELECT * FROM us" to autocomplete table names. Up arrow recalls history.
Handy ~/.psqlrc lines: \set QUIET 1 \timing on \x auto \pset null '(null)' \set HISTSIZE 5000 \set QUIET 0
Look around (SQL)
SELECT version();
SELECT current_database(), current_user, inet_server_addr(), inet_server_port();
SELECT datname FROM pg_database;
SELECT * FROM pg_stat_activity; -- who is connected and what they run
SHOW data_directory; SHOW config_file; SHOW hba_file; SHOW port;
SHOW ALL; -- every setting
SELECT name, setting, unit, source FROM pg_settings WHERE name LIKE '%mem%';
SELECT pg_size_pretty(pg_database_size('mydb'));
SELECT pg_size_pretty(pg_total_relation_size('users')); -- table plus indexes and TOAST
SELECT now(), pg_postmaster_start_time(), now() - pg_postmaster_start_time() AS uptime;
Databases, schemas and tables
CREATE DATABASE shop OWNER alice ENCODING 'UTF8' TEMPLATE template0;
DROP DATABASE shop; -- deletes everything in it (no active connections allowed)
CREATE SCHEMA app AUTHORIZATION alice;
SET search_path TO app, public; -- which schemas are searched, in order
CREATE TABLE users (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
email TEXT NOT NULL UNIQUE,
name TEXT,
active BOOLEAN NOT NULL DEFAULT true,
meta JSONB DEFAULT '{}',
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
ALTER TABLE users ADD COLUMN phone TEXT;
ALTER TABLE users ALTER COLUMN name SET NOT NULL;
ALTER TABLE users ALTER COLUMN name TYPE VARCHAR(200);
ALTER TABLE users RENAME COLUMN name TO full_name;
ALTER TABLE users DROP COLUMN phone;
ALTER TABLE orders ADD CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE;
ALTER TABLE users RENAME TO customers;
TRUNCATE TABLE logs RESTART IDENTITY; -- remove all rows fast and reset the counter
DROP TABLE IF EXISTS temp_data CASCADE; -- CASCADE also drops things that depend on it
CREATE TABLE users_copy (LIKE users INCLUDING ALL);
CREATE TABLE users_backup AS SELECT * FROM users;
Common types: integer, bigint, numeric(10,2), text, varchar(n), boolean, date, timestamp, timestamptz, uuid, jsonb, inet, bytea, arrays (text[])
Use timestamptz (with time zone) for times, and text or varchar freely: there is no speed difference between them.
Insert, update, delete
INSERT INTO users (email, name) VALUES ('a@example.com', 'Alice');
INSERT INTO users (email, name) VALUES ('b@example.com','Bob'), ('c@example.com','Cy') RETURNING id;
INSERT INTO users (email, name) VALUES ('a@example.com','Alice') ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name;
INSERT INTO users (email) VALUES ('a@example.com') ON CONFLICT DO NOTHING;
UPDATE users SET name = 'Alicia' WHERE id = 1 RETURNING *; -- always include WHERE
DELETE FROM users WHERE id = 3 RETURNING id; -- always include WHERE
WITH moved AS (DELETE FROM queue WHERE done RETURNING *) INSERT INTO archive SELECT * FROM moved;
Wrap risky changes in a transaction: BEGIN; ... ROLLBACK; (or COMMIT;)
In psql, \set AUTOCOMMIT off makes every session a transaction until you COMMIT.
Query data
SELECT * FROM users LIMIT 10;
SELECT id, email FROM users WHERE name ILIKE 'a%' ORDER BY created_at DESC LIMIT 20 OFFSET 40; -- ILIKE = case-insensitive
SELECT DISTINCT country FROM customers;
SELECT DISTINCT ON (user_id) * FROM orders ORDER BY user_id, created_at DESC; -- latest row per user
SELECT country, count(*) AS n FROM customers GROUP BY country HAVING count(*) > 10 ORDER BY n DESC;
SELECT min(price), max(price), avg(price), sum(price) FROM products;
SELECT * FROM users WHERE id = ANY (ARRAY[1,2,3]) AND created_at >= now() - interval '7 days';
SELECT * FROM users WHERE email ~ '^a.*@example\.com$'; -- regular expression (~* = case-insensitive)
SELECT u.name, o.total FROM users u JOIN orders o ON o.user_id = u.id WHERE o.total > 100;
SELECT u.name, o.id FROM users u LEFT JOIN orders o ON o.user_id = u.id;
SELECT name FROM users WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = users.id);
WITH big AS (SELECT user_id, sum(total) AS t FROM orders GROUP BY user_id) SELECT * FROM big WHERE t > 1000; -- CTE
SELECT name, salary, rank() OVER (PARTITION BY dept ORDER BY salary DESC) FROM staff; -- window function
SELECT coalesce(phone, 'n/a'), nullif(name, ''), greatest(a,b), least(a,b) FROM users;
SELECT CASE WHEN total > 100 THEN 'big' ELSE 'small' END FROM orders;
SELECT date_trunc('month', created_at) AS month, count(*) FROM orders GROUP BY 1 ORDER BY 1;
SELECT now(), current_date, now() - interval '1 hour', age(now(), created_at), to_char(now(), 'YYYY-MM-DD HH24:MI');
SELECT meta->>'plan', meta->'address'->>'city' FROM users WHERE meta @> '{"plan":"pro"}'; -- JSONB operators
SELECT string_agg(name, ', ' ORDER BY name), array_agg(id) FROM users;
SELECT generate_series(1, 10);
SELECT * FROM users TABLESAMPLE SYSTEM (1); -- about a 1% sample
Roles and privileges (users are roles)
CREATE ROLE alice LOGIN PASSWORD 'a-strong-password';
CREATE ROLE app LOGIN PASSWORD '...' NOSUPERUSER NOCREATEDB NOCREATEROLE CONNECTION LIMIT 20;
CREATE ROLE readonly NOLOGIN; -- a group role
GRANT CONNECT ON DATABASE shop TO app;
GRANT USAGE ON SCHEMA public TO app;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app; -- for tables created later
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
GRANT readonly TO alice; -- alice inherits readonly
REVOKE DELETE ON users FROM app;
REVOKE ALL ON DATABASE shop FROM PUBLIC; -- remove the default open access
ALTER ROLE alice WITH PASSWORD 'new-password';
ALTER ROLE alice SET statement_timeout = '30s'; -- per-role setting
ALTER DATABASE shop OWNER TO alice;
ALTER TABLE users OWNER TO alice;
DROP ROLE alice; -- first: REASSIGN OWNED BY alice TO postgres; DROP OWNED BY alice;
\du \dp users SELECT rolname, rolsuper, rolcanlogin FROM pg_roles;
PostgreSQL 15 and newer no longer let ordinary users create objects in the public schema by default.
Give applications their own user with only the privileges they need, and never use the postgres superuser in an app.
Client authentication (pg_hba.conf)
Find it: SHOW hba_file; (Debian: /etc/postgresql/16/main/pg_hba.conf, RHEL: /var/lib/pgsql/16/data/pg_hba.conf)
# TYPE DATABASE USER ADDRESS METHOD
local all postgres peer # local socket: OS user must match
local all all scram-sha-256
host all all 127.0.0.1/32 scram-sha-256
host shop app 10.0.0.0/24 scram-sha-256 # a private subnet
hostssl all all 0.0.0.0/0 scram-sha-256 # remote, TLS only
host replication repl 10.0.0.12/32 scram-sha-256 # for replication
Rules are read top to bottom and the first match wins. After editing: sudo systemctl reload postgresql
or SELECT pg_reload_conf(); Methods: peer, scram-sha-256 (use this), md5 (older), trust (never on a network), reject, cert.
Also set listen_addresses in postgresql.conf (default is localhost only), then restart.
SELECT * FROM pg_hba_file_rules; -- check what was parsed, and any errors
Backup with pg_dump
pg_dump -U alice mydb > mydb.sql # plain SQL, one database
pg_dump -U alice -Fc mydb > mydb.dump # custom format: compressed, flexible restore
pg_dump -U alice -Fd -j 4 mydb -f mydb_dir # directory format, 4 parallel jobs
pg_dump -U alice -t users -t orders mydb > two-tables.sql # specific tables
pg_dump -U alice --schema-only mydb > schema.sql # structure only
pg_dump -U alice --data-only mydb > data.sql # data only
pg_dump -U alice mydb | gzip > mydb-$(date +%F).sql.gz # compressed and dated
pg_dumpall -U postgres > cluster.sql # every database plus roles and tablespaces
pg_dumpall -U postgres --globals-only > globals.sql # just roles and tablespaces
pg_dump -h db.example.com -U alice mydb | ssh backup@host "cat > /backups/mydb.sql"
pg_dump takes a consistent snapshot without blocking readers or writers.
Restore
psql -U alice -d mydb -f mydb.sql # plain SQL
createdb -U postgres -O alice mydb && psql -U alice -d mydb -f mydb.sql
pg_restore -U alice -d mydb mydb.dump # custom or directory format
pg_restore -U alice -d mydb -j 4 --clean --if-exists mydb.dump # parallel, drop objects first
pg_restore -l mydb.dump # list what is inside
pg_restore -U alice -d mydb -t users mydb.dump # one table
gunzip -c mydb.sql.gz | psql -U alice -d mydb
psql -U postgres -f cluster.sql postgres # restore a pg_dumpall file
Test your restores regularly. A backup you have never restored is only a hope.
Physical backups and point-in-time recovery: pg_basebackup, plus WAL archiving (see below). Tools: pgBackRest, Barman, WAL-G.
Import and export data
\copy users TO 'users.csv' WITH (FORMAT csv, HEADER) -- psql client-side: file lives on your machine
\copy users FROM 'users.csv' WITH (FORMAT csv, HEADER)
\copy (SELECT id, email FROM users WHERE active) TO 'active.csv' CSV HEADER
COPY users TO '/tmp/users.csv' WITH (FORMAT csv, HEADER); -- server-side: file lives on the DB server, needs privileges
psql -U alice -d mydb -c "\copy users FROM 'users.csv' CSV HEADER"
Indexes and performance
EXPLAIN SELECT * FROM users WHERE email = 'a@example.com';
EXPLAIN (ANALYZE, BUFFERS) SELECT ...; -- runs it and shows real timings and I/O (wrap DML in BEGIN; ... ROLLBACK;)
Look at: Seq Scan (full table scan) vs Index Scan, actual time, rows (estimate vs actual), and Buffers.
CREATE INDEX idx_users_email ON users (email);
CREATE INDEX CONCURRENTLY idx_orders_user ON orders (user_id, created_at); -- builds without blocking writes
CREATE UNIQUE INDEX uq_users_email ON users (lower(email)); -- expression index
CREATE INDEX idx_active ON users (email) WHERE active; -- partial index
CREATE INDEX idx_meta ON users USING gin (meta); -- GIN for jsonb, arrays, full-text
DROP INDEX CONCURRENTLY idx_users_email;
REINDEX INDEX CONCURRENTLY idx_users_email;
ANALYZE users; -- refresh planner statistics
VACUUM (VERBOSE, ANALYZE) users; -- reclaim dead rows and update statistics
VACUUM FULL users; -- rewrites the table and LOCKS it: avoid on busy systems
SELECT relname, n_live_tup, n_dead_tup, last_autovacuum FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 10;
SELECT relname, seq_scan, idx_scan FROM pg_stat_user_tables ORDER BY seq_scan DESC LIMIT 10;
SELECT indexrelname, idx_scan FROM pg_stat_user_indexes WHERE idx_scan = 0; -- unused indexes
SELECT relname, pg_size_pretty(pg_total_relation_size(relid)) FROM pg_catalog.pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC LIMIT 10;
SELECT round(100.0 * sum(blks_hit) / nullif(sum(blks_hit) + sum(blks_read), 0), 2) AS cache_hit_pct FROM pg_stat_database;
Autovacuum runs by itself. Leave it on. Bloat and long-running transactions are common causes of slowness.
Slow queries: pg_stat_statements
Add to postgresql.conf: shared_preload_libraries = 'pg_stat_statements' then restart, then:
CREATE EXTENSION pg_stat_statements;
SELECT calls, round(total_exec_time::numeric,1) AS total_ms, round(mean_exec_time::numeric,2) AS mean_ms, left(query,80) FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;
SELECT pg_stat_statements_reset();
Log slow statements: log_min_duration_statement = 500 (milliseconds) in postgresql.conf, then reload.
For log analysis use pgBadger: pgbadger /var/log/postgresql/postgresql-16-main.log -o report.html
Activity, locks and connections
SELECT pid, usename, datname, state, now() - query_start AS running, left(query,60) FROM pg_stat_activity WHERE state <> 'idle' ORDER BY running DESC;
SELECT pid, now() - xact_start AS xact_age, state, query FROM pg_stat_activity WHERE xact_start IS NOT NULL ORDER BY xact_age DESC; -- long transactions
SELECT pg_cancel_backend(12345); -- stop the query, keep the connection
SELECT pg_terminate_backend(12345); -- disconnect that session
SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = 'mydb' AND pid <> pg_backend_pid(); -- kick everyone off
SELECT * FROM pg_locks WHERE NOT granted;
SELECT pid, pg_blocking_pids(pid) AS blocked_by, query FROM pg_stat_activity WHERE cardinality(pg_blocking_pids(pid)) > 0;
SELECT count(*) FROM pg_stat_activity; SHOW max_connections;
Too many connections? Use a pooler: PgBouncer (or pgpool-II) in front of Postgres, instead of raising max_connections a lot.
Idle-in-transaction sessions hold locks and stop vacuum. Set idle_in_transaction_session_timeout = '5min'.
Server config (postgresql.conf)
Find it: SHOW config_file; Debian: /etc/postgresql/16/main/postgresql.conf RHEL: /var/lib/pgsql/16/data/postgresql.conf
listen_addresses = 'localhost' # '*' or a private IP to allow remote connections (and firewall port 5432)
port = 5432
max_connections = 100
shared_buffers = 4GB # start at about 25% of RAM on a dedicated server
effective_cache_size = 12GB # about 50-75% of RAM (a planner hint, not an allocation)
work_mem = 32MB # per sort or hash, per query node: raise carefully
maintenance_work_mem = 512MB # for VACUUM and CREATE INDEX
wal_level = replica # 'logical' for logical replication
max_wal_size = 4GB
checkpoint_completion_target = 0.9
random_page_cost = 1.1 # on SSD storage
log_min_duration_statement = 500
log_line_prefix = '%m [%p] %q%u@%d '
Change settings live where possible:
ALTER SYSTEM SET work_mem = '64MB'; -- writes postgresql.auto.conf
SELECT pg_reload_conf(); -- apply settings that need only a reload
SELECT name, setting, pending_restart FROM pg_settings WHERE pending_restart; -- these need a full restart
sudo systemctl restart postgresql
Extensions
\dx -- installed extensions
SELECT * FROM pg_available_extensions ORDER BY name;
CREATE EXTENSION IF NOT EXISTS pgcrypto; -- also: pg_stat_statements, uuid-ossp, pg_trgm, hstore, citext, postgis, pgvector
ALTER EXTENSION pg_trgm UPDATE;
DROP EXTENSION pg_trgm;
Files and logs
/var/lib/postgresql/16/main/ Data directory on Debian/Ubuntu (RHEL: /var/lib/pgsql/16/data/)
/var/log/postgresql/postgresql-16-main.log Log (Debian). RHEL: the log/ folder in the data directory
sudo tail -f /var/log/postgresql/postgresql-16-main.log
journalctl -u postgresql -f
SHOW data_directory; SHOW log_directory;
Never copy or edit files in the data directory while the server runs (except through pg_basebackup).
Streaming replication (primary and standby, basics)
On the primary, postgresql.conf: wal_level = replica max_wal_senders = 10 listen_addresses = '*' wal_keep_size = 1GB
(and hot_standby = on, the default)
CREATE ROLE repl WITH REPLICATION LOGIN PASSWORD '...';
pg_hba.conf: host replication repl 10.0.0.12/32 scram-sha-256 then reload
On the standby (empty data directory), clone the primary:
sudo systemctl stop postgresql
sudo -u postgres rm -rf /var/lib/postgresql/16/main/*
sudo -u postgres pg_basebackup -h 10.0.0.11 -U repl -D /var/lib/postgresql/16/main -R -P -X stream
sudo systemctl start postgresql
-R writes standby.signal and the connection settings, so the standby starts replicating automatically.
Check:
-- on the primary
SELECT client_addr, state, sync_state, replay_lsn FROM pg_stat_replication;
-- on the standby
SELECT pg_is_in_recovery(); -- true on a standby
SELECT now() - pg_last_xact_replay_timestamp() AS lag;
Promote a standby to primary: sudo -u postgres pg_ctlcluster 16 main promote or SELECT pg_promote();
Replication slots keep WAL for a standby: SELECT * FROM pg_replication_slots; A dead slot fills the disk, so drop unused ones.
Tools for automatic failover: Patroni, repmgr, pg_auto_failover. Logical replication (per table) uses CREATE PUBLICATION / CREATE SUBSCRIPTION.
Point-in-time recovery (PITR) idea
archive_mode = on archive_command = 'cp %p /wal_archive/%f' (or pgBackRest / WAL-G)
Take base backups with pg_basebackup, keep the WAL archive, and recover to a moment with recovery_target_time in postgresql.conf plus a recovery.signal file.
Upgrades and maintenance
pg_upgrade --check ... # dry-run check of a major version upgrade
sudo pg_upgradecluster 15 main # Debian helper: upgrades a cluster to the newest installed version
Minor updates (16.3 to 16.4) are just package updates and a restart. Major versions need pg_upgrade or a dump and restore.
vacuumdb --all --analyze-in-stages # rebuild statistics after an upgrade
reindexdb --all
Command-line tools
createdb -U postgres -O alice mydb dropdb mydb
createuser -U postgres --interactive dropuser alice
pg_isready -h 127.0.0.1 Is the server accepting connections? (exit code 0 = yes)
pg_lsclusters (Debian) list clusters and their status
pg_ctl status -D /var/lib/postgresql/16/main pg_controldata /var/lib/postgresql/16/main
vacuumdb -U postgres --all --analyze reindexdb clusterdb
pg_dump / pg_restore / pg_dumpall / pg_basebackup Backup and restore
pgbench -i -s 50 mydb && pgbench -c 10 -T 60 mydb Built-in benchmark
Practical one-liners
# Size of every database
psql -U postgres -c "\l+"
# The 10 largest tables
psql -U postgres -d mydb -c "SELECT relname, pg_size_pretty(pg_total_relation_size(relid)) FROM pg_stat_user_tables ORDER BY pg_total_relation_size(relid) DESC LIMIT 10;"
# Nightly compressed backup of one database (cron)
pg_dump -U backup -Fc mydb -f /backups/mydb-$(date +\%F).dump
# Nightly backup of everything including roles
pg_dumpall -U postgres | gzip > /backups/all-$(date +\%F).sql.gz
# Keep the last 7 days of backups
find /backups -name 'mydb-*.dump' -mtime +7 -delete
# Copy a database to another server
pg_dump -U alice -Fc mydb | pg_restore -h other-host -U alice -d mydb --create --no-owner
# Who is connected, and from where?
psql -U postgres -c "SELECT usename, client_addr, datname, state, count(*) FROM pg_stat_activity GROUP BY 1,2,3,4 ORDER BY 5 DESC;"
# Cancel every query that has run longer than 5 minutes (review first!)
psql -U postgres -c "SELECT pg_cancel_backend(pid) FROM pg_stat_activity WHERE state = 'active' AND now() - query_start > interval '5 minutes' AND pid <> pg_backend_pid();"
# Reset a forgotten postgres password (on the server, as the OS postgres user)
sudo -u postgres psql -c "\password postgres"
# Row counts of every table (estimates are fast; count(*) is exact but slow)
psql -U postgres -d mydb -c "SELECT relname, n_live_tup FROM pg_stat_user_tables ORDER BY n_live_tup DESC;"
# Is the database up? (for health checks)
pg_isready -h 127.0.0.1 -p 5432 && echo up
# Run a query every 2 seconds (inside psql)
SELECT count(*) FROM pg_stat_activity; \watch 2
# Find tables without a primary key
psql -U postgres -d mydb -c "SELECT c.relname FROM pg_class c JOIN pg_namespace n ON n.oid=c.relnamespace WHERE c.relkind='r' AND n.nspname NOT IN ('pg_catalog','information_schema') AND NOT EXISTS (SELECT 1 FROM pg_index i WHERE i.indrelid=c.oid AND i.indisprimary);"
Common errors
FATAL: password authentication failed for user Wrong password, or the user has none. Reset with \password
FATAL: Peer authentication failed for user Local socket uses peer auth: run as that OS user (sudo -u postgres psql), use -h 127.0.0.1, or change pg_hba.conf
FATAL: no pg_hba.conf entry for host ... The client address is not allowed: add a host line, then reload
FATAL: database "x" does not exist Wrong name: \l to list. psql defaults to a database named after the user
FATAL: role "x" does not exist Create it: CREATE ROLE x LOGIN;
psql: could not connect to server: Connection refused The server is down, on another port, or listen_addresses excludes that address. Check systemctl status and pg_lsclusters
FATAL: sorry, too many clients already Raise max_connections a little, and use PgBouncer
FATAL: remaining connection slots are reserved Same problem: only superuser slots are left
ERROR: permission denied for table / schema Missing GRANT. Check \dp and \dn+
ERROR: relation "x" does not exist Wrong database or schema (search_path), or the name needs quotes because of capitals
ERROR: duplicate key value violates unique constraint A UNIQUE or PRIMARY KEY conflict: use ON CONFLICT or fix the data
ERROR: deadlock detected Retry the transaction, and lock rows in a consistent order
ERROR: canceling statement due to statement timeout Query exceeded statement_timeout: optimize it or raise the limit
ERROR: could not extend file / No space left on device Disk full: check df -h, WAL and log directories, and unused replication slots
ERROR: database is being accessed by other users DROP DATABASE needs all sessions closed: terminate them first
Server will not start Read the log file: bad postgresql.conf value, a stale postmaster.pid, wrong permissions, or a full disk
Transaction ID wraparound warnings Vacuum is not keeping up: run VACUUM on the affected database and find long-running transactions
Tips
Use pg_dump -Fc for backups. Custom format is compressed, can restore in parallel, and lets you restore single tables.
Keep autovacuum on. If tables bloat, tune autovacuum for them instead of turning it off.
Use EXPLAIN (ANALYZE, BUFFERS) before adding indexes, and build them with CREATE INDEX CONCURRENTLY on live systems.
Wrap UPDATE and DELETE in a transaction, run a SELECT with the same WHERE first, and use RETURNING to see what changed.
Use timestamptz, not timestamp, and use identity columns or uuid for keys.
Keep the server off the public internet: keep listen_addresses narrow, restrict pg_hba.conf, require TLS with hostssl, and firewall port 5432.
Give each application its own role with minimum privileges, never the postgres superuser.
Use pg_stat_statements and log_min_duration_statement to find the few queries that cause most of the load.
Put PgBouncer in front of busy applications instead of setting max_connections very high.
Watch for long-running and idle-in-transaction sessions. They block vacuum and hold locks.
Test restores and failovers on a schedule, and keep WAL archiving on if you need point-in-time recovery.
Check unused replication slots. A forgotten slot makes WAL pile up until the disk is full.
