PostgreSQL | Terminal Command Collection | Backup (pg_dump)

Tags:

• PostgreSQL
• Database

Show Help

$ pg_dump --help
pg_dump dumps a database as a text file or another file format.

Usage:
  pg_dump [OPTION]... [DBNAME]

General options:
  -f, --file=FILENAME         output file name
  -F, --format=c|t|p          output file format: custom, tar, or plain text
  -v, --verbose               verbose mode
  -Z, --compress=0-9          compression level for compressed formats
  --lock-wait-timeout=TIMEOUT fail after waiting TIMEOUT for a table lock

  --help                      show this help and exit
  --version                   output version information and exit

Options controlling the output:
  -a, --data-only             dump only data, not the schema
  -b, --blobs                 include large objects in the dump
  -c, --clean                 clean database objects before recreating them
  -C, --create                include commands to create the database
  -E, --encoding=ENCODING     dump data using the specified encoding
  -n, --schema=SCHEMA         dump only the specified schemas
  -N, --exclude-schema=SCHEMA dump all schemas except the specified schemas
  -o, --oids                  include OIDs in the dump
  -O, --no-owner              skip restoring object ownership in plain-text format
  -s, --schema-only           dump only the schema
  -S, --superuser=NAME        superuser name to use in plain-text format
  -t, --table=TABLE           dump only tables with the specified names
  -T, --exclude-table=TABLE   dump all tables except the specified tables
  -x, --no-privileges         do not dump access privileges (grant/revoke)
  --binary-upgrade            for upgrade utilities only
  --inserts                   dump data as INSERT commands instead of COPY
  --column-inserts            dump data as INSERT commands with column names
  --disable-dollar-quoting    disable dollar quoting and use SQL standard quoting
  --disable-triggers          disable triggers when restoring data only
  --no-tablespaces            do not dump tablespace assignments
  --role=ROLENAME             run SET ROLE before dumping
  --use-set-session-authorization
                              use SET SESSION AUTHORIZATION instead of ALTER OWNER
                              commands to set ownership

Connection options:
  -h, --host=HOSTNAME      database server or socket directory to connect to
  -p, --port=PORT          database server port number
  -U, --username=NAME      database user to connect as
  -w, --no-password        never prompt for a password
  -W, --password           force password prompt

If no database name is specified, the PGDATABASE environment variable is used.

Backup Dump Example

./pg_dump --port=5432 --username=root --schema=public --file=devkuma.dump --no-owner devkuma

Restore Example

./psql --port=5432 --username=admin --dbname=devkuma --no-password -f devkuma.dump

References

https://www.postgresql.org/docs/10/static/app-pgdump.html