PostgreSQL 9.0 pg_dump pg_dumpall pg

PG_Dump, PG_DumpAll, PG_RESTORE 9.0 Command Line Cheatsheet

PostgreSQL 9.0 pg_dump, pg_dumpall, pg_restore Cheat Sheet

pg_dump, pg_dump_all, pg_restore are located in the bin folder of PostgreSQL and PgAdmin III installs.

pg_dump dumps a database as a text file or to other formats.

Manual: http://www.postgresql.org/docs/9.0/interactive/app-pgdump.html
Usage: pg_dump [OPTION]... [DBNAME]

pg_dumpall extracts a PostgreSQL database cluster into an SQL script file restorable with psql.

Manual: http://www.postgresql.org/docs/9.0/interactive/app-pg-dumpall.html
Usage: pg_dumpall [OPTION]...

pg_restore restores a PostgreSQL database from an archive created by pg_dump.

Manual: http://www.postgresql.org/docs/9.0/interactive/app-pgrestore.html
Usage: pg_restore [OPTION]... [FILE]

General options: (D - pg_dump, R - pg_restore , A - pg_dumpall)

  R
D R A
D R  
D R A
  R
D R
D R A
D R A
D   A

  -d, --dbname=NAME      
  -f, --file=FILENAME
  -F, --format=c|t|p (p only for pg_dump, psql to restore p)   
  -l, --list          
  -v, --verbose       
  --help              
  --version
  -Z, --compress=0-9
  --lock-wait-timeout=TIMEOUT

  connect to database name
  output file name
  specify backup file format (c = compressed, t = tar, p = plain text)
  print summarized TOC of the archive
  verbose mode
  show this help, then exit
  output version information, then exit
  compression level for compressed formats
  fail after waiting TIMEOUT for a table lock. milliseconds assumed if no units specified

Options controlling the dump / restore: (D - pg_dump, R - pg_restore, A - pg_dumpall)

D R A
D   
D R A
D
D   A
D   A
D
    A
  R
  R
  R
D R
D
D   A
  R
  R
    A
D R
D R A
D R
    A
  R
D 
D R A
D   A
D   A
D R A
D R A
D R A
  R
  R

  -a, --data-only
  -b, --blobs
  -c, --clean    
  -C, --create
  --inserts
  --column-inserts
  -E, --encoding=ENCODING
  -g, --globals-only
  -I, --index=NAME  
  -j, --jobs=NUM           
  -L, --use-list=FILENAME 
  -n, --schema=NAME 
  -N, --exclude-schema=SCHEMA
  -o, --oids                  
  -O, --no-owner          
  -P, --function=NAME(args) 
  -r, --roles-only
  -s, --schema-only       
  -S, --superuser=NAME                          
  -t, --table=NAME
  -t, --tablespaces-only
  -T, --trigger=NAME
  -T, --exclude-table=TABLE
  -x, --no-privileges   
  --binary-upgrade                
  --disable-dollar-quoting    
  --disable-triggers 
  --no-tablespaces            
  --use-set-session-authorization                      
  --no-data-for-failed-tables  
  -1, --single-transaction

  restore only the data, no schema
  include large objects in dump
  clean (drop) schema prior to create (for pg_dumpall drop databases prior to create)
  (D) include commands to create database, (R) create the target database
  dump data as INSERT commands, rather than COPY
  dump data as INSERT commands with column names
  dump the data in encoding ENCODING
  dump only global objects, no databases
  restore named index
  use this many parallel jobs to restore
  use specified table of contents for ordering output from this file
  dump/restore only objects in this schema
  do NOT dump the named schema(s)
  include OIDs in dump
  skip restoration of object ownership
  restore named function
  dump only roles, no databases or tablespaces
  dump/restore only the schema, no data
  specify the superuser user name to use for disabling triggers/and dumping in plain text
  (D) dump the named table(s), (R) restore named table 
  dump only tablespaces, no databases or roles
  (R) restore named trigger
  (D) do NOT dump the named table(s)
  (D) do not dump privileges (R) skip restoration of access privileges (grant/revoke)
  for use by upgrade utilities only
  disable dollar quoting, use SQL standard quoting
  disable triggers during data-only restore
  do not dump/restore tablespace assignments
  use SESSION AUTHORIZATION commands instead of OWNER TO commands
  do not restore data of tables that could not be created
  restore as a single transaction

Connection options:
  -h, --host=HOSTNAME      database server host or socket directory
  -p, --port=PORT          database server port number
  -U, --username=NAME      connect as specified database user
  -w, --no-password        never prompt for password
  -W, --password           force password prompt (should happen automatically)
  -e, --exit-on-error      exit on error, default is to continue

If no input file name is supplied, then standard input is used.

pg_restore Example Use

restore whole database
pg_restore --host=localhost --dbname=db_to_restore_to --username=someuser /path/to/somedb.backup
restore only the schema (no objects)
pg_restore --schema-only=someschema --dbname=db_to_restore_to --username=someuser /path/to/somedb.backup
restore only a specifically named schema's data: note the schema has to exist before hand
pg_restore --schema=someschema --dbname=db_to_restore_to --username=someuser /path/to/somedb.backup

Get a listing of items in backup file and pipe to text file (only works for tar and compressed formats)
pg_restore --list --file=C:/somedb_list.txt backupfilepath

pg_dump, pg_dumpall Example Use

dump database in compressed include blobs show progress
pg_dump -h someserver -p 5432 -U someuser -F c -b -v -f "/somepath/somedb.backup" somedb
dump database in utf8 encoding and wait a maximum of 1 minute for a lock
pg_dump -h someserver -p 5432 -U someuser -E UTF8 --lock-wait-timeout=6000 -F c -b -v -f "/somepath/somedb.backup" somedb
dump all tables named roads in all schemas in compressed binary format
pg_dump -h someserver -p 5432 -U someuser -E UTF8 -t "*.roads" -F c -b -v -f "/somepath/somedb.backup" somedb
backup pgagent schema of postgres db in plain text copy format, maintain oids
pg_dump -h someserver -p 5432 -U postgres -F p -o -v -n pgagent -f "C:/pgagent.sql" postgres
backup table roads in schema ma use column inserts rather than copy
pg_dump -h someserver -p 5432 -U postgres -F p -t "ma.roads" --column-inserts -f "C:/ma.roads.sql" somedb
dump all databases - note pg_dumpall can only output to plain text
pg_dumpall -h someserver -p 5432 -U someuser -c -o -f "/somepath/alldbs.sql"
Restore a full database cluster backup generated wtih pg_dumpall
psql -h someserver -p 5432 -U postgres -f /somepath/alldbs.sql postgres

http://www.postgresonline.com
This work is licensed under a Creative Commons Attribution-Share Alike 3.0 United States License .
Feel free to use this material, but we ask that you please retain the Postgres OnLine website link.