PostgreSQL Backup & Recovery

Databases

PostgreSQL

pgadmin4

Backup

pgadmin4

Backup

Restore

If the target database already exists, consider dropping it and recreating it before starting the restore.

pg_dump/pg_restore

Backup

pg_dump

To export...

pg_dump -U username dbname > outputfile.sql

To export schema only (no data)...

pg_dump -s myschema > myschema.sql

To do this from a remote machine...

pg_dump -s -h myhost -U myuser -C myschema > myschema.sql

To backup all databases...

pg_dumpall -h myhost -U postgres -W > outputfile.sql

To backup all tables that start with "AO_8542F1"

pg_dump -h myhost -U atljira -W -t "\"AO_8542F1\"*" -f AO_8542F1.sql jira

In Jira, tables starting with AO_8542F1 relate to the Assets (previosuly Mindville Insight) functionality.Note that the escaped quotes \" ensure that the text is not "folded" to lower case. The * is a wildcard.See: https://www.postgresql.org/docs/current/app-psql.html#APP-PSQL-PATTERNS for more information.

Environment Variables
PGDATABASEPGHOSTPGOPTIONSPGPORTPGUSERPG_COLOR

-a-b-B-c-C-d xxx-e xxx-E xxx-f xxx-F xxx-h-j xxx-n xxx-N xxx-O-p xxx-R-s-S xxx-t xxx-T xxx-U xxx-v-V-w-W-x-Z 0-9

-?

--data-only--blobs--no-blobs--clean--create--dbname=dbname--extension=pattern--encoding=encoding--file=file--format=format--host=host--jobs=njobs--schema=pattern--exclude-schema=pattern--no-owner--port=port--no-reconnect--schema-only--superuser=username--table=pattern--exclude-table=pattern--username=username--verbose--version--no-password--password--no-privileges / --no-acl--compress=0..9--binary-upgrade--column-inserts/--attribute-inserts--disable-dollar-quoting--disable-triggers--enable-row-security--exclude-table-data=pattern--extra-float-digits=ndigits--if-exists--include-foreign-data=foreignserver--inserts--load-via-partition-root--lock-wait-timeout=timeout--no-comments--no-publications--no-security-labels--no-subscriptions--no-sync--no-synchronized-snapshots--no-tablespaces--no-toast-compression--no-unlogged-table-data--on-conflict-do-nothing--quote-all-identifiers--role=rolename--rows-per-insert=nrows--section=sectionname--serializable-deferrable--snapshot=snapshotname--strict-names--use-set-session-authorization--help

no schema, only datainclude large objectsexclude large objectsdrop before createcreate databasedatabase name
character setoutput to fileplain,custom,directory,tarhostnamerun in parallelonly matching schemasexclude matchingdon't set ownershipportobsoleteno datause when disable triggersonly matching tablesexclude matchingusernameverbose modeshow versionnever prompt passwrdforce password promptno grant/revokecompression leveldo not useuse INSERT commands

exclude data keep def
use with --clean
use INSERT commands

role name used for dump

help

Restore

pg_restore

To restore from a 'plain' text file...

psql -U username dbname -f outputfile.sql

To restore from any file type, other than plain text (see above), created by pg_dump...

pg_restore -h myhost -U username -W -d mydatabase -v outputfile.sql

To restore our backup of all tables that start with "AO_8542F1"

pg_restore -h myhost -U atljira -W -d jira -v -c AO_8542F1.sql

In Jira, tables starting with AO_8542F1 relate to the Assets (previosuly Mindville Insight) functionality.

-a-c-C-d xxx-e-f xxx-F xxx-h-I xxx-j xxx-l-L xxx-n xxx-N xxx-O-p xxx-P xxx-R-s-S xxx-t xxx-T xxx-U xxx-v-V-w-W-x-1

-?

--data-only--clean--create--dbname=dbname--exit-on-error--file=file--format=format--host=host--index=index--jobs=njobs--list--use-list=listfile--schema=pattern--exclude-schema=pattern--no-owner--port=port--function=function-name(argtype)--no-reconnect--schema-only--superuser=username--table=pattern--trigger=trigger--username=username--verbose--version--no-password--password--no-privileges / --no-acl--single-transaction--disable-triggers--enable-row-security--if-exists--no-comments--no-data-for-failed-tables--no-publications--no-security-labels--no-subscriptions--no-table-access-method--no-tablespaces--section=sectionname--strict-names--use-set-session-authorization--help

no schema, only datadrop before createcreate databasedatabase nameexit on erroroutput to filec/custom,d/directory,t/tarhostnamerestore definition of named index onlyrun in parallellist TOC of archiverestore only elements listed in listfileonly matching schemasexclude matchingdon't set ownershipportrestore the named function onlyobsoleteno datause when disable triggersonly matching tablesrestore named trigger onlyusernameverbose modeshow versionnever prompt passwrdforce password promptno grant/revokerestore as single transaction

help

Bibliography