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.
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”.
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 --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 -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) --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 --no-security-labels do not dump security label assignments --no-subscriptions do not dump subscriptions --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 --strict-names require table and/or schema include patterns to match at least one entity each --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
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.
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 --no-security-labels do not dump security label assignments --no-subscriptions do not dump subscriptions --no-sync do not wait for changes to be written safely to disk --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 --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.
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.
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 -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.) -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 --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 --no-security-labels do not restore security labels --no-subscriptions do not restore subscriptions --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 --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
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”.
?
General copyright show PostgreSQL usage and distribution terms errverbose show most recent error message at maximum verbosity g [FILE] or ; execute query (and send results to file or |pipe) gexec execute query, then execute each value in its result 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 q quit psql crosstabview [COLUMNS] execute query and display results in crosstab 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 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 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 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 dRs[+] [PATTERN] list replication subscriptions 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 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 }) [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
Example B-5 shows the noninteractive commands help screen. Examples of their usage are covered in “Interactive versus Noninteractive psql”.
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 --help=variables list special variables, then exit --version output version information, then exit Input and output options: -a, --echo-all echo all input from script -b, --echo-errors echo failed commands -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.