PostgreSQL Backup & Recovery

pgadmin4

Backup

Restore

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

pg_dump/pg_restore

Backup

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

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


https://www.postgresql.org/docs/8.1/backup-online.html
https://www.postgresql.org/docs/9.2/backup.htmlhttps://www.postgresql.org/docs/11/backup.html
https://docs.vmware.com/en/Site-Recovery-Manager/8.1/com.vmware.srm.install_config.doc/GUID-E1FC67CD-48E3-4B25-AA1D-8A2408F5B517.html
SQL Dumphttps://www.postgresql.org/docs/11/backup-dump.html
pg_dump/pg_restorehttps://www.a2hosting.com/kb/developer-corner/postgresql/import-and-export-a-postgresql-databasehttps://www.enterprisedb.com/download-postgresql-binarieshttps://tecadmin.net/backup-and-restore-database-in-postgresql/https://www.postgresqltutorial.com/postgresql-administration/postgresql-backup-databasehttps://askubuntu.com/questions/501091/command-pg-dump-not-foundhttps://www.postgresql.org/docs/current/app-psql.html#APP-PSQL-PATTERNShttps://dba.stackexchange.com/questions/242992/how-to-restore-a-postgres-database-created-via-an-older-version-of-postgreshttps://stackoverflow.com/questions/2732474/restore-a-postgres-backup-file-using-the-command-linehttps://stackoverflow.com/questions/33594039/pg-dump-and-pg-restore-across-different-major-versions-of-postgresqlhttps://stackoverflow.com/questions/7359827/creating-a-database-dump-for-specific-tables-and-entries-postgres
https://www.postgresql.org/docs/current/app-pgrestore.htmlhttps://www.postgresql.org/docs/9.3/app-pgdump.htmlhttps://www.postgresql.org/docs/current/app-pgdump.htmlhttps://www.postgresql.org/docs/9.3/app-pgrestore.htmlhttps://www.postgresql.org/docs/current/app-pgrestore.html
File System Level Backupshttps://www.postgresql.org/docs/11/backup-file.html
Continuous Archiving and Point In Time Recovery (PITR)https://www.postgresql.org/docs/11/continuous-archiving.html
Otherhttps://stackoverflow.com/questions/33594039/pg-dump-and-pg-restore-across-different-major-versions-of-postgresqlhttps://stackoverflow.com/questions/12836312/postgresql-9-2-pg-dump-version-mismatchhttps://askubuntu.com/questions/646603/how-do-i-solve-a-server-version-mismatch-with-pg-dump-when-i-need-both-postgrehttps://www.eversql.com/exporting-mysql-schema-structure-to-xml-using-mysql-clients/