3.3. Backup
The BACKUP command is available at the cluster, database, and table object levels. It provides a means of using the utilities pg_dumpall (if backing up a database cluster) and pg_dump (if backing up a database or a table) through Administrator. For details about the options available for these two utilities, refer to the PostgreSQL 7.3 Reference Manual.
 | Warning |
|---|---|
PostgreSQL - Red Hat Edition supports only pg_dump version 7.3. If you use a different version, success cannot be guarenteed. In particular, a newer version of pg_dump being used with an older server may appear to work, but the produced dump file might not work when reloaded into the older server. If you want still to attempt such a backup, you should perform a -s (schema only) dump and see if that reloads successfully. |
3.3.1. Backing up a Database Cluster
From any cluster, right-click to bring up the context menu, then select BACKUP CLUSTER to bring up the BACKUP DATABASE CLUSTER dialog.
When the BACKUP DATABASE CLUSTER dialog is brought up, you are presented with three categories of options (Dump Method, Content Selectivity, and Other Options).
Dump Method contains radio buttons for specifying the method used to dump the object; for systems utilizing a version of pg_dumpall below 7.3, these options are not available and are thus greyed out when backing up a database cluster.
Content Selectivity contains radio buttons for selecting what is to be included in the produced dumpfile (databases and global objects or only global objects). By default, both databases and global objects are included in the dump. However, you can choose to include only global objects in the dump by checking that radio button. This corresponds to the -g option of pg_dumpall.
Other Options contains a check button for enabling/disabling Verbose Mode, as well as button for viewing more advanced options. Verbose Mode corresponds to the -v option of pg_dumpall, and is only available if the version of pg_dumpall being run on the user's machine is 7.3. Otherwise, this option is greyed out. If Verbose Mode is disabled, then any errors that may occur during the dump may not be visible to you. Clicking the Advanced Options button brings up the Advanced Options dialog:
 | Note |
|---|---|
A bug in pg_dumpall version 7.3 causes no verbose output to appear when you specify -v when the -g (globals only) option is also set. |
The advanced options are grouped into two categories: Other Data To Output To Dump File and Compatibility. Other Data To Output To Dump File contains two check buttons, OIDs and Drop Commands, which allow the user to specify additional elements to include in the dump file, and correspond to the -o and -c options to pg_dumpall, respectively. The -o option is not available for pg_dumpall versions below 7.3; for systems running such versions of pg_dumpall, the OIDs check button is greyed out.
Compatibility contains a check button for ignoring version mismatch, which corresponds to the -i option of pg_dumpall. If the version series of pg_dumpall being used differs from the version series of the database server, this option is, by default, checked; otherwise, the backup may not be able to proceed. Once all advanced options have been set, click OK.
At the bottom of the BACKUP DATABASE CLUSTER dialog is the Dump To File field, in which you are required to specify the path of the file to which the pg_dumpall output will be redirected. You can do this either by entering the filename directly into the field, or by clicking on the button beside the field and selecting the desired file.
After selecting the options for the backup, selecting the dump file, and clicking OK, the following dialog, which shows the output of the pg_dumpall command, appears (provided that Verbose Mode is selected).
After the pg_dumpall command has finished, you can save the output to a file by clicking on the Save button. If Verbose Mode is not selected, the following progress bar dialog appears.
As noted earlier, if Verbose Mode is not selected, you may not be notified if errors occurred during the dump.
3.3.2. Backing up a Database
From any database, right-click to bring up the context menu, then select BACKUP DATABASE to bring up the BACKUP DATABASE dialog.
When the BACKUP DATABASE dialog is brought up, you are presented with four categories of options: Output Format, Dump Method, Content Selectivity, and Other Options. At the top of the dialog are two drop-down lists for selecting the tables to be backed up, and the schemas containing the tables (the leftmost drop-down list corresponds to the schema, and the rightmost drop-down list corresponds to the table). By default, the schema is listed as Default Search Path, and the table is specified as All Tables.
To select a particular table or schema, click on the down arrow button beside each drop-down list to select from one of the choices. This is equivalent to bringing up the BACKUP TABLE dialog directly from the context menu of the specified table.
Output Format contains radio buttons that allow the user to specify the format of the dump file produced (plain-text SQL, tar archive, or custom archive). These correspond to the -F p, -F t, and -F c options to pg_dump, respectively.
Dump Method contains radio buttons for specifying the method used to backup the database (Copy, Insert, or Insert With Explicit Column Names). Copy is the default dump method, whereas Insert and Insert With Explicit Column Names correspond to the -d and -D options to pg_dump.
Content Selectivity contains check buttons for specifying what elements to dump (both the data and the schema, only the data, or only the schema). Dump Data And Schema is the default, and Dump Only Data and Dump Only Schema correspond to the -a and -s options to pg_dump, respectively.
Other Options contains a check button for enabling/disabling Verbose Mode, as well as viewing more advanced options. Clicking on the Advanced Options button brings up the Advanced Options dialog.
The advanced options are grouped into four categories: Other Data To Output To Dump File, Identifiers, Restoration, and Compatibility.
Other Data To Output To Dump File contains check buttons for specifying additional elements to be included in the dump (large objects, OIDs, drop commands, a create database command, object ownership commands, a reconnect script, and access privileges). The first four of these options correspond to the -b, -o, -c, and -C options to pg_dump, respectively. The last three of these options are, by default, checked; unchecking them corresponds to setting the -O, -R, and -x options to pg_dump, respectively.
Restoration contains a check button for disabling triggers when restoring. This corresponds to the -X disable-triggers option of pg_dump, which is only available on pg_dump versions 7.3 or above. Thus, if running a pg_dump version below 7.3, this option is greyed out. Compatibility contains a check button for ignoring version mismatch, which corresponds to the -i option of pg_dump. If the series of the pg_dump version differs from the version series of the database server, this option is, by default, checked; otherwise, the backup may not be able to proceed. Once all advanced options have been set, clicking OK closes the dialog and return focus to the BACKUP DATABASE dialog.
| Note |
|---|---|
When raising the advanced options for table backup, note that the Large Objects option (under Other Data To Output To Dump File) is not available as this option is not supported for the backup of tables. |
At the bottom of the BACKUP DATABASE dialog is the Dump To File field, which requires you to specify the path of the file to which the pg_dump output will be redirected. You can do this either by entering the filename directly into the field or by clicking on the button beside the field and selecting the desired file.
After selecting the options for the backup, selecting the dump file, and clicking OK, the following dialog, which shows the output of the pg_dump command, appears (provided that Verbose Mode is selected).
After the pg_dump command has finished, save the output to a file by clicking on the Save button. If Verbose Mode is not selected, the following progress bar dialog appears.
As with the backup of a database cluster, if Verbose Mode is not selected, it is possible that any errors that occur during the dump will not be visible to you.
3.3.3. Backing up a Table
Backing up a table is similar to backing up a database. From any table, right-click to bring up the context menu, then select BACKUP TABLE to bring up the BACKUP TABLE dialog.
The only difference between the BACKUP TABLE dialog and the BACKUP DATABASE dialog is that, by default, the schema and name of the table from which the BACKUP TABLE dialog was raised appear as the default in the table selection drop-down lists at the top of the dialog. Another table may be backed up from this dialog by selecting the table's name and schema from the drop-down lists.
3.3.4. Incompatible Options
When backing up a database or a table, there are several option combinations that are disallowed because the options are incompatible. The pairs of incompatible options are listed below:
Under Other Data To Output To Dump File, checking both Drop Commands and Create Database Command.
Under Other Data To Output To Dump File, checking Create Database Command and not checking Reconnect Script.
Checking both Insert (as the dump method) and OIDs (as additional data to output to the dump file).
Checking both Insert With Explicit Column Names (as the dump method) and OIDs (as additional data to output to the dump file).