PostgreSQL 8.3 pg_dump, pg_dumpall, pg_restore Cheat Sheet

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

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

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

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

Usage: pg_dumpall [OPTION]...

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

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
  -d, --dbname=NAME      
  -f, --file=FILENAME
  -F, --format=c|t|p (p only for pg_dump, psql to restore p)   
  -i, --ignore-version
  -l, --list          
  -v, --verbose       
  --help              
  --version
  -Z, --compress=0-9
 
  connect to database name (pg_dump uses this to mean inserts)
  output file name
  specify backup file format (c = compressed, t = tar, p = plain text)
  proceed even when server version mismatches
  print summarized TOC of the archive
  verbose mode
  show this help, then exit
  output version information, then exit
  compression level for compressed formats

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
D R
D
D   A
  R
  R
    A
D R
D R A
D R
    A
  R
D 
D R A
D R A
D R A
  R
  R
D   A
 
  -a, --data-only
  -b, --blobs
  -c, --clean    
  -C, --create
  -d, --inserts
  -D, --column-inserts
  -E, --encoding=ENCODING
  -g, --globals-only
  -I, --index=NAME  
  -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     
  --disable-triggers      
  --use-set-session-authorization                      
  --no-data-for-failed-tables  
  -1, --single-transaction
  --disable-dollar-quoting
 
  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 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)
  disable triggers during data-only restore
  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
  disable dollar quoting, use SQL standard quoting
 
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, --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 backupfilepath --file=C:/somedb_list.txt

pg_dump, pg_dumpall Example Use

dump database in compressed include blobs show progress
pg_dump -i -h someserver -p 5432 -U someuser -F c -b -v -f "/somepath/somedb.backup" somedb
dump database in sql_ascii encoding
pg_dump -i -h someserver -p 5432 -U someuser -E sql_ascii -F c -b -v -f "/somepath/somedb.backup" somedb
backup pgagent schema of postgres db in plain text copy format, maintain oids
pg_dump -i -h someserver -p 5432 -U postgres -F p -o -v -n pgagent -f "C:/pgagent.sql" postgres
dump all databases - note pg_dumpall can only output to plain text
pg_dumpall -i -h someserver -p 5432 -U someuser -c -o -f "/somepath/alldbs.sql"
http://www.postgresonline.com