Database Backup (PostgreSQL)

From DBArtisan
Jump to: navigation, search

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

  1. 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.
  2. Select Utilities > Database Backup to open the Backup Wizard.
  3. 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).