Restoring from Backups

1. Restoring Plain SQL Dump

psql -d shop -f shop.sql
psql -d shop -v ON_ERROR_STOP=1 -f shop.sql

pg_restore -d shop shop.dump

pg_restore -d shop /backups/shop.d

pg_restore -d shop -j 8 shop.dump

pg_restore -d shop -t orders -t customers shop.dump

pg_restore -d shop -n app shop.dump

pg_restore -l shop.dump > toc.list

vi toc.list           # delete or reorder lines (prefix with ;)
pg_restore -L toc.list -d shop shop.dump

pg_restore --schema-only -d shop shop.dump

pg_restore --data-only --disable-triggers -d shop shop.dump

pg_restore -c --if-exists -d shop shop.dump   # DROP objects then restore

pg_restore -C -d postgres shop.dump            # CREATE DATABASE then restore

Flag	Effect
--exit-on-error	Stop on first error
--single-transaction	All-or-nothing
--no-owner / --no-privileges	Skip ALTER OWNER / GRANT
--disable-triggers	Skip triggers during data load