PostgreSQL Backup and Restore Cheat Sheet

pg_dump -h localhost -U postgres -d appdb -F c -f appdb.dump

pg_dump -h localhost -U postgres -d appdb -F d -j 4 -f /backups/appdb_dir

pg_dump -h localhost -U postgres -d appdb -F p -f appdb.sql

pg_dump -h localhost -U postgres -d appdb --schema-only -f schema.sql

pg_dump -h localhost -U postgres -d appdb --data-only -f data.sql

pg_restore -h localhost -U postgres -d appdb appdb.dump

pg_restore -h localhost -U postgres -d appdb --clean --if-exists appdb.dump

psql -h localhost -U postgres -d appdb -f appdb.sql

pg_dumpall -h localhost -U postgres --globals-only > globals.sql

pg_dumpall -h localhost -U postgres > cluster.sql

pg_basebackup -h primary-db -U replicator -D /backups/base -Fp -Xs -P

pg_verifybackup /backups/base

pg_upgrade --check -b /usr/lib/postgresql/17/bin -B /usr/lib/postgresql/18/bin -d /var/lib/postgresql/17/data -D /var/lib/postgresql/18/data

pg_upgrade -b /usr/lib/postgresql/17/bin -B /usr/lib/postgresql/18/bin -d /var/lib/postgresql/17/data -D /var/lib/postgresql/18/data

vacuumdb --all --analyze-in-stages

pg_dump -h localhost -U postgres -d appdb -t public.users -f users.sql

pg_dump -h localhost -U postgres -d appdb -n accounting -f accounting.sql

pg_restore -l appdb.dump

pg_restore -h localhost -U postgres -d appdb -t public.users appdb.dump

pg_restore -h localhost -U postgres -d appdb --schema-only appdb.dump

pg_restore -h localhost -U postgres -d appdb --data-only appdb.dump

pg_restore -h localhost -U postgres -d appdb -j 4 appdb.dump

COPY public.users TO '/var/lib/postgresql/users.csv' CSV HEADER;

COPY public.users (user_id, email, created_at)
FROM '/var/lib/postgresql/users.csv' CSV HEADER;

\copy public.users TO './users.csv' CSV HEADER

\copy public.users (user_id, email, created_at) FROM './users.csv' CSV HEADER

COPY (SELECT * FROM public.users) TO PROGRAM 'gzip > /tmp/users.csv.gz' CSV HEADER;

COPY staging_import FROM STDIN WITH (FORMAT csv, HEADER true, FREEZE true);