Database Backup (PostgreSQL)
Go Up to Database Backup
This wizard lets you generate a pg_dump script, backing up one or more tables or views of a database.
To create a database backup
- Connect to the datasource where the database to be backed up is located and ensure that it is the selected datasource. For details, see Connected/Selected Datasource options.
- Select Utilities > Database Backup to open the Backup Wizard.
- Use the following table as a guide to performing tasks as you pass through the wizard panels:
Panel | Option | Description |
---|---|---|
Playback |
New Backup Operation |
Select to define a new backup operation. |
Previous Backup Operation |
Lets you specify the specific datasource and database where the tables and views to be backed up are located.
| |
Datasource and Output Options |
What are the datasource and database |
Lets you specify the specific datasource and database where the tables and views to be backed up are located. |
Specify the format for the data file(s) to be generated |
Lets you specify a -F (--format) output format option of Custom (custom-format archive suitable for input into Database Restore utility), Plain text (plain-text SQL script file), Directory (directory-format archive suitable for input into Database Restore utility), or Tar (TAR-format archive suitable for input into Database Restore utility). | |
Compress ratio (not available if the Tar output format is selected) |
Lets you provide a -Z option value of 0-9, specifying a pg_dump compression level. | |
Encoding |
Lets you provide a -E option value, specifying the character set encoding of the dump file. | |
Rolename |
Lets you provide a --role option value, instrucing pg_dump to issue a SET ROLE command on connection to the database. | |
Files |
Specify a path for the output file to be generated and Specify a path for the message file to be generated |
These settings lets you specify the paths for the output and message files as well as the optional file that lets you reuse the current backup operation. |
Options |
Sections |
Lets you provide one or more --section option values ( Pre-data. Data, Post-data) specifying that the selected sections are dumped. |
Type of Objects |
Only data - corresponds to a -a (--data-only) option, specifying that only data be dumped. Only schema - corresponds to a -s (--schema-only) option, specifying that only object definitions be dumped. Blobs - corresponds to a -b (--sblobs) option, specifying that large objected be included in the dump. | |
Don’t save |
Owner- corresponds to a -O (--no-owner) option, specifying no output commands to set ownership of objects to match the original database. Privilege - corresponds to a -X (--no-privileges) option, preventing dumping of access privileges. Tablespace - corresponds to a --no-tablespaces) option, specifying no output commands to select tablespaces. Unlogged table data - corresponds to a --no-unlogged-table-data option, specifying that the contents of unlogged tables are not dumped. | |
Queries |
Include CREATE DATABASE statement - corresponds to a -C (--create) option, specifying that the output begin with a command to create the database. Include DROP DATABASSE statement - corresponds to a -C (--clean) option, specifying that the commands to drop objects are specified before commands to create them.. Use Column Inserts - corresponds to a --column-inserts option, specifying that data is dumped as INSERT commands with explicit column names Use Insert commands - corresponds to a --inserts option, specifying that data is dumped as INSERT commands rather than COPY. | |
Disable |
Trigger- corresponds to a --disable-triggers option, specifying that the dump include commands to temporarily disable triggers on the target tables while data is reloaded. $ quoting- corresponds to a --disable-dollar-quoting option, specifying that dollar quoting is disabled for function bodies. | |
Miscellaneous |
Use SET SESSION AUTHORIZATION - corresponds to a --use-set-session-authorization option, specifying that object ownership is determined using SET SESSION AUTHORIZATION commands instead of ALTER OWNER commands. With OIDs - corresponds to a --oids option, specifying that object identifiers are dumped with every table. Verbose messages - corresponds to a --verbose option, specifying that verbose mode be used. Force double quotes on identifiers - corresponds to a --quote-all-identifiers option, forcing quoting of all identifiers. | |
Tables |
Select the tables and/or views to be exported |
Lets you select the tables that are to be dumped. |
Summary |
Provides a short summary of the dump operation options. |
- 4. Finally, use the Finish button to perform the backup.
The generated script can be used in a subsequent restore operation. For details, see Database Restore (PostgreSQL).