Appendix B. PostgreSQL Packaged Command-Line Tools

This appendix summarizes indispensable command-line tools packaged with PostgreSQL server. We discussed them at length in the book. Here we list their help messages. We hope to save you a bit of time with their inclusion and perhaps make this book a not-so-strange bedfellow.

Database Backup Using pg_dump

Use pg_dump to back up all or part of a database. Backup file formats available are TAR, compressed (PostgreSQL custom format), plain text, and plain-text SQL. Plain-text backup can copy psql-specific commands; therefore, restore by running the file within psql. A plain-text SQL backup is merely a file with standard SQL CREATE and INSERT commands. To restore, you can run the file using psql or pgAdmin. Example B-1 shows the pg_dump help output. For full coverage of pg_dump usage, see “Selective Backup Using pg_dump”.

Example B-1. pg_dump help
pg_dump --help
pg_dump dumps a database as a text file or to other formats.
Usage:
pg_dump [OPTION]... [DBNAME]

General options:
-f, --file=FILENAME         output file or directory name
-F, --format=c|d|t|p        output file format (custom, directory, tar, plain
text)
-j, --jobs=NUM              use this many parallel jobs to dump
-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
--no-sync                    do not wait for changes to be written safely to disk 1
--help                      show this help, then exit
--version                   output version information, then exit
Options controlling the output content:
-a, --data-only             dump only the data, not the schema
-b, --blobs                 include large objects in dump
-B, --no-blobs              exclude large objects in dump  2
-c, --clean                 clean (drop) database objects before recreating
-C, --create                include commands to create database in dump
-E, --encoding=ENCODING     dump the data in encoding ENCODING
-n, --schema=SCHEMA         dump the named schema(s) only
-N, --exclude-schema=SCHEMA do NOT dump the named schema(s)
-o, --oids                  include OIDs in dump
-O, --no-owner              skip restoration of object ownership in
plain-text format
-s, --schema-only           dump only the schema, no data
-S, --superuser=NAME        superuser user name to use in plain-text format
-t, --table=TABLE           dump the named table(s) only
-T, --exclude-table=TABLE   do NOT dump the named table(s)
-x, --no-privileges         do not dump privileges (grant/revoke)
--binary-upgrade            for use by upgrade utilities only
--column-inserts            dump data as INSERT commands with column names
--disable-dollar-quoting    disable dollar quoting, use SQL standard quoting
--disable-triggers          disable triggers during data-only restore
--enable-row-security        enable row security (dump only content user has
                               access to) 3
--exclude-table-data=TABLE  do NOT dump data for the named table(s)
--if-exists                  use IF EXISTS when dropping objects
--inserts                   dump data as INSERT commands, rather than COPY
--no-publications            do not dump publications 4
--no-security-labels        do not dump security label assignments
--no-subscriptions           do not dump subscriptions 5
--no-synchronized-snapshots do not use synchronized snapshots in parallel jobs
--no-tablespaces            do not dump tablespace assignments
--no-unlogged-table-data    do not dump unlogged table data
--quote-all-identifiers     quote all identifiers, even if not key words
--section=SECTION           dump named section (pre-data, data, or post-data)
--serializable-deferrable   wait until the dump can run without anomalies
--snapshot=SNAPSHOT          use given snapshot for the dump  6
--strict-names               require table and/or schema include patterns to
                               match at least one entity each 7
--use-set-session-authorization
use SET SESSION AUTHORIZATION commands instead of
ALTER OWNER commands to set ownership
Connection options:
-d, --dbname=DBNAME      database to dump
-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)
--role=ROLENAME          do SET ROLE before dump
123

New features introduced in PostgreSQL 10.

4

New features introduced in PostgreSQL 9.6.

56

New features introduced in PostgreSQL 9.5.

7

New features introduced in PostgreSQL 9.4.

Server Backup: pg_dumpall

Use pg_dump_all to back up all databases on your server onto a single plain-text or plain-text SQL file. The backup routine will automatically include server-level objects such as roles and tablespaces. Example B-2 shows the pg_dumpall help output. See “Systemwide Backup Using pg_dumpall” for the full discussion.

Example B-2. pg_dumpall help
pg_dumpall --help
pg_dumpall extracts a PostgreSQL database cluster into an SQL script file.
Usage:
pg_dumpall [OPTION]...

General options:
-f, --file=FILENAME          output file name
-v, --verbose                verbose mode
-V, --version                output version information, then exit
--lock-wait-timeout=TIMEOUT  fail after waiting TIMEOUT for a table lock
-?, --help                   show this help, then exit

Options controlling the output content:
  -a, --data-only            dump only the data, not the schema
  -c, --clean                clean (drop) databases before recreating
  -g, --globals-only         dump only global objects, no databases
  -o, --oids                 include OIDs in dump
  -O, --no-owner             skip restoration of object ownership
  -r, --roles-only           dump only roles, no databases or tablespaces
  -s, --schema-only          dump only the schema, no data
  -S, --superuser=NAME       superuser user name to use in the dump
  -t, --tablespaces-only     dump only tablespaces, no databases or roles
  -x, --no-privileges        do not dump privileges (grant/revoke)
  --binary-upgrade           for use by upgrade utilities only
  --column-inserts           dump data as INSERT commands with column names
  --disable-dollar-quoting   disable dollar quoting, use SQL standard quoting
  --disable-triggers         disable triggers during data-only restore
  --inserts                  dump data as INSERT commands, rather than COPY
  --no-publications          do not dump publications 1
  --no-security-labels       do not dump security label assignments
  --no-subscriptions         do not dump subscriptions 2
  --no-sync                  do not wait for changes to be written safely to disk 3
  --no-tablespaces           do not dump tablespace assignments
  --no-unlogged-table-data   do not dump unlogged table data
  --no-role-passwords        do not dump passwords for roles  4
  --quote-all-identifiers    quote all identifiers, even if not keywords
  --use-set-session-authorization
use SET SESSION AUTHORIZATION commands instead o
ALTER OWNER commands to set ownership

Connection options:
  -d, --dbname=CONNSTR     connect using connection string
  -h, --host=HOSTNAME      database server host or socket directory
  -l, --database=DBNAME    alternative default database
  -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)
  --role=ROLENAME          do SET ROLE before dump

If -f/--file is not used, then the SQL script will be written to the standard
output.
1234

New in PostgreSQL 10.

Database Restore: pg_restore

Use pg_restore to restore backup files in tar, custom, or directory formats created using pg_dump. Example B-3 shows the pg_restore help output. See “Restoring Data” for more examples.

Example B-3. pg_restore help
pg_restore --help
pg_restore restores a PostgreSQL database from an archive created by pg_dump.
Usage:
  pg_restore [OPTION]... [FILE]

General options:
  -d, --dbname=NAME        connect to database name
  -f, --file=FILENAME      output file name
  -F, --format=c|d|t       backup file format (should be automatic)
  -l, --list               print summarized TOC of the archive
  -v, --verbose            verbose mode
  -V, --version            output version information, then exit
  -?, --help               show this help, then exit

Options controlling the restore:
  -a, --data-only              restore only the data, no schema
  -c, --clean                  clean (drop) database objects before recreating
  -C, --create                 create the target database
  -e, --exit-on-error          exit on error, default is to continue
  -I, --index=NAME             restore named index
  -j, --jobs=NUM               use this many parallel jobs to restore
  -L, --use-list=FILENAME      use table of contents from this file for
                               selecting/ordering output
  -n, --schema=NAME            restore only objects in this schema
  -N, --exclude-schema=NAME    do not restore objects in this schema 1
  -O, --no-owner               skip restoration of object ownership
  -P, --function=NAME(args)    restore named function
  -s, --schema-only            restore only the schema, no data
  -S, --superuser=NAME         superuser user name to use for disabling triggers
  -t, --table=NAME             restore named relation (table, view, etc.) 2
  -T, --trigger=NAME           restore named trigger
  -x, --no-privileges          skip restoration of access privileges (grant/revoke)
  -1, --single-transaction     restore as a single transaction
  --enable-row-security        enable row security 3
  --disable-triggers           disable triggers during data-only restore
  --no-data-for-failed-tables  do not restore data of tables that could not be
                               created
  --no-publications            do not restore publications 4
  --no-security-labels         do not restore security labels
  --no-subscriptions           do not restore subscriptions 5
  --no-tablespaces             do not restore tablespace assignments
  --section=SECTION            restore named section (pre-data, data, or post-data)
  --strict-names               require table and/or schema include patterns to
                               match at least one entity each 6
  --use-set-session-authorization
                               use SET SESSION AUTHORIZATION commands instead of
                               ALTER OWNER commands to set ownership

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)
  --role=ROLENAME          do SET ROLE before restore
123

New features introduced in PostgreSQL 10.

45

New features introduced in PostgreSQL 9.6. Prior to 9.6, the -t option matched only tables. In 9.6 it was changed to also match foreign tables, views, materialized views, and sequences.

6

New features introduced in PostgreSQL 9.5.

psql Interactive Commands

Example B-4 lists commands available in psql when you launch an interactive session. For examples of usage, see “Environment Variables” and “Interactive versus Noninteractive psql”.

Example B-4. Getting a list of interactive psql commands
?
General
  copyright             show PostgreSQL usage and distribution terms
  errverbose            show most recent error message at maximum verbosity 1
  g [FILE] or ;         execute query (and send results to file or |pipe)
  gexec                 execute query, then execute each value in its result 2
  gset [PREFIX]         execute query and store results in psql variables
  h [NAME]              help on syntax of SQL commands, * for all commands
  gx [FILE]             as g, but forces expanded output mode 3
  q                     quit psql
  crosstabview [COLUMNS] execute query and display results in crosstab 4
  watch [SEC]           execute query every SEC seconds
Help
  ? [commands]          show help on backslash commands
  ? options             show help on psql command-line options
  ? variables           show help on special variables
  h [NAME]              help on syntax of SQL commands, * for all commands
Query Buffer
  e [FILE] [LINE]       edit the query buffer (or file) with external editor
  ef [FUNCNAME [LINE]]  edit function definition with external editor
  ev [VIEWNAME [LINE]]  edit view definition with external editor 5
  p                     show the contents of the query buffer
  
                     reset (clear) the query buffer
  w FILE                write query buffer to file
Input/Output
  copy ...              perform SQL COPY with data stream to the client host
  echo [STRING]         write string to standard output
  i FILE                execute commands from file
 ir FILE               as i, but relative to location of current script
  o [FILE]              send all query results to file or |pipe
  qecho [STRING]        write string to query output stream (see o)
Conditional  6
  if EXPR               begin conditional block
  elif EXPR             alternative within current conditional block
  else                  final alternative within current conditional block
  endif                 end conditional block
Informational
  (options: S = show system objects, + = additional detail)
  d[S+]                 list tables, views, and sequences
  d[S+]  NAME           describe table, view, sequence, or index
  da[S]  [PATTERN]      list aggregates
  dA[+]  [PATTERN]      list access methods 7
  db[+]  [PATTERN]      list tablespaces
  dc[S]  [PATTERN]      list conversions
  dC     [PATTERN]      list casts
  dd[S]  [PATTERN]      show comments on objects
  ddp    [PATTERN]      list default privileges
  dD[S]  [PATTERN]      list domains
  det[+] [PATTERN]      list foreign tables
  des[+] [PATTERN]      list foreign servers
  deu[+] [PATTERN]      list user mappings
  dew[+] [PATTERN]      list foreign-data wrappers
  df[antw][S+] [PATRN]  list [only agg/normal/trigger/window] functions
  dF[+]  [PATTERN]      list text search configurations
  dFd[+] [PATTERN]      list text search dictionaries
  dFp[+] [PATTERN]      list text search parsers
  dFt[+] [PATTERN]      list text search templates
  dg[S+]  [PATTERN]      list roles
  di[S+] [PATTERN]      list indexes
  dl                    list large objects, same as lo_list
  dL[S+] [PATTERN]      list procedural languages
  dm[S+] [PATTERN]      list materialized views
  dn[S+] [PATTERN]      list schemas
  do[S]  [PATTERN]      list operators
  dO[S+] [PATTERN]      list collations
  dp     [PATTERN]      list table, view, and sequence access privileges
  drds [PATRN1 [PATRN2]] list per-database role settings
  dRp[+] [PATTERN]      list replication publications  8
  dRs[+] [PATTERN]      list replication subscriptions  9
  ds[S+] [PATTERN]      list sequences
  dt[S+] [PATTERN]      list tables
  dT[S+] [PATTERN]      list data types
  du[S+]  [PATTERN]      list roles
  dv[S+] [PATTERN]      list views
  dE[S+] [PATTERN]      list foreign tables
  dx[+]  [PATTERN]      list extensions
  dy     [PATTERN]      list event triggers
  l[+]                  list databases
  sf[+] FUNCNAME        show a function's definition
  sv[+]  VIEWNAME       show a view's definition 10
  z      [PATTERN]      same as dp
Formatting
  a                     toggle between unaligned and aligned output mode
  C [STRING]            set table title, or unset if none
  f [STRING]            show or set field separator for unaligned query output
  H                     toggle HTML output mode (currently off)
  pset NAME [VALUE]     set table output option
                         (NAME := {format|border|expanded|fieldsep|fieldsep_zero 
                 | footer|null|
                 numericlocale|recordsep|tuples_only|title|tableattr|pager
                 |unicode_border_linestyle|unicode_column_linestyle
                 |unicode_header_linestyle 11
                 })
  	 [on|off]            show only rows (currently off)
  T [STRING]            set HTML <table> tag attributes, or unset if none
  x [on|off]            toggle expanded output (currently off)
Connection
  c[onnect] {[DBNAME|- USER|- HOST|- PORT|-] | conninfo}
                         connect to new database (currently "postgres")
  encoding [ENCODING]   show or set client encoding
  password [USERNAME]   securely change the password for a user
  conninfo              display information about current connection
Operating System
  cd [DIR]              change the current working directory
setenv NAME [VALUE]   set or unset environment variable
  	iming [on|off]       toggle timing of commands (currently off)
  ! [COMMAND]           execute command in shell or start interactive shell
123

New features introduced in PostgreSQL 10. All conditional options are new.

45678910

New features introduced in PostgreSQL 9.6.

11

New feature introduced in PostgreSQL 9.5.

psql Noninteractive Commands

Example B-5 shows the noninteractive commands help screen. Examples of their usage are covered in “Interactive versus Noninteractive psql”.

Example B-5. psql basic help screen
psql --help
psql is the PostgreSQL interactive terminal.
Usage:
psql [OPTION]... [DBNAME [USERNAME]]

General options:
-c, --command=COMMAND    run only single command (SQL or internal) and exit
-d, --dbname=DBNAME      database name to connect to
-f, --file=FILENAME      execute commands from file, then exit
-l, --list               list available databases, then exit
-v, --set=, --variable=NAME=VALUE
                           set psql variable NAME to VALUE
                           (e.g., -v ON_ERROR_STOP=1)
-X, --no-psqlrc          do not read startup file (~/.psqlrc)
-1 ("one"), --single-transaction
execute command file as a single transaction
  -?, --help[=options]     show this help, then exit
      --help=commands      list backslash commands, then exit 1
      --help=variables     list special variables, then exit 2
--version                output version information, then exit

Input and output options:
-a, --echo-all           echo all input from script
-b, --echo-errors        echo failed commands  3
-e, --echo-queries       echo commands sent to server
-E, --echo-hidden        display queries that internal commands generate
-L, --log-file=FILENAME  send session log to file
-n, --no-readline        disable enhanced command-line editing (readline)
-o, --output=FILENAME    send query results to file (or |pipe)
-q, --quiet              run quietly (no messages, only query output)
-s, --single-step        single-step mode (confirm each query)
-S, --single-line        single-line mode (end of line terminates SQL command)


Output format options:
-A, --no-align           unaligned table output mode
-F, --field-separator=STRING
set field separator (default: "|")
-H, --html               HTML table output mode
-P, --pset=VAR[=ARG]     set printing option VAR to ARG (see pset command)
-R, --record-separator=STRING
set record separator (default: newline)
-t, --tuples-only        print rows only
-T, --table-attr=TEXT    set HTML table tag attributes (e.g., width, border)
-x, --expanded           turn on expanded table output
-z, --field-separator-zero
                           set field separator to zero byte
-0, --record-separator-zero
                           set record separator to zero byte

Connection options:
-h, --host=HOSTNAME      database server host or socket directory
-p, --port=PORT          database server port (default: "5432")
-U, --username=USERNAME  database user name
-w, --no-password        never prompt for password
-W, --password           force password prompt (should happen automatically)

For more information, type "?" (for internal commands) or "help" (for SQL
commands) from within psql, or consult the psql section in the PostgreSQL
documentation.
123

These items are new features introduced in PostgreSQL 9.5.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset