3.19. Restore
Once an object has been backed up (dumped) using BACKUP CLUSTER, BACKUP DATABASE, or BACKUP TABLE, the object can be restored with the Restore command.
The Restore command is available at the Database, Table, and Function header levels, and also at the database object level. It provides a means of using the psql (if restoring from a plain-text SQL dump file) and pg_restore (if restoring from a tar or custom archive) utilities through Administrator. For details about the options available for these two utilities, refer to the PostgreSQL 7.3 Administrator's Guide.
3.19.1. Restoring a Database
There are two ways to restore a database: from any Databases header, or from any database object.
From any Databases header, right-click to bring up the context menu, then select RESTORE DATABASE to bring up the general RESTORE DATABASE dialog. Alternatively, from any database object, right-click to bring up the context menu, then select RESTORE DATABASE to bring up the RESTORE DATABASE dialog for that database.
 | Note |
|---|---|
The appearance of the dialog differs slightly depending on the level from which the RESTORE DATABASE dialog is raised. |
When the general RESTORE DATABASE dialog is raised, you are presented with four categories of options: Database Options, Input Format, Content Selectivity, and Other Options. At the top of the dialog is a field for entering the name of the database object to be restored. Generally this must not be the name of a database already under that cluster as this would cause the restoration to fail. However, if the database restoration consists only of restoring elements that do not currently exist under that database (such as restoring a table that does not currently exist under that database), then specifying a pre-existing database is acceptable.
Database Options contains radio buttons for specifying whether you want to restore the objects into an existing database or into a new database. By default, Restore Into New Database is checked. This option creates a new database with the specified name before performing the restoration. The user that holds the current connection for that cluster will be the owner of this database. In order to restore into a new database, you must specify a name that is not already in use by an existing database. If you want to restore the objects into an existing database, choose the Restore Into Existing Database option and specify the database into which you want to restore.
Input Format contains radio buttons for specifying the format of the dump file being used for the restoration (Plain-Text SQL, Tar Archive, and Custom Archive). If Plain-Text SQL is selected, then psql is used for the restoration and all other options are greyed out. If Tar Archive or Custom Archive is selected (corresponding to the -F t and -F c options to pg_restore, respectively), then the other options that are associated with pg_restore become available.
Content Selectivity contains radio buttons for specifying what is restored (Data And Schema, Data Only, or Schema Only). The restoration of both data and schema is the default for pg_restore, and the Restore Only Data and Restore Only Schema options correspond to the -a and -s options to pg_restore, respectively.
Other Options contains a check button for enabling/disabling Verbose Mode and a button for viewing more advanced options.
If Verbose Mode is enabled, you will see a detailed output of the restoration process, which enables you to monitor its progress. If this option is disabled, then any errors that occur during the restoration may not be visible to you.
Clicking the Advanced Options button displays the Advanced Options dialog:
Advanced options are available only when restoring from an archive file (that is, a tar archive or custom archive). The advanced options are grouped into five categories (Selective Restoration, Other Data To Restore, Restoration Protocol, Restoration Order, and Compatibility).
Selective Restoration provides check buttons and entry boxes to restore only particular objects within the database. To restore a particular instance of an object type within the database, check the check button corresponding to that object's type and enter the object name in the corresponding entry box. Only those items that are checked are included in the selective restoration. Choosing to selectively restore a particular table, trigger, index, or function corresponds to setting the -t, -T, -I, and -P options to pg_restore, respectively. Note that, when selecting a particular function to restore, the function name must be specified in the Function Name field and the arguments in the Argument List field. For functions with no arguments, leave the Argument List entry box empty; for functions with one or more arguments, enter in the Argument List entry box a comma-separated list of the argument types (without any schema qualification and without any spaces).
Other Data To Restore contains check buttons for additional elements to include in the restoration (original object ownerships and access privileges). These are, by default, checked; unchecking Restore Original Object Ownership or Restore Access Privileges corresponds to setting the -O and -x options to pg_restore, respectively.
Restoration Protocol contains check buttons for specifying what actions to perform before/during the restoration (dropping database objects before recreating them, creating the database before restoring it, disabling triggers during the restoration, and connecting/reconnecting to the database as needed). The first three of these options correspond to the -c, -C, and -X disable-triggers options to pg_restore, respectively. The fourth of these options is checked by default; unchecking it corresponds to setting the -R option of pg_restore.
Restoration Order contains radio buttons for selecting the order in which elements in the database are restored (in original dump order, OID order, or modified OID order, which correspond to the -N, -o, and -r options to pg_restore, respectively).
Compatibility contains a check button for ignoring version mismatch, which corresponds to the -i option of pg_restore, and is only available on pg_restore versions 7.3 and above; thus, for systems running a pg_restore version below 7.3, this option is greyed out. If it is not greyed out, and if the pg_restore version series differs from the version series of the database server, then this option is, by default, checked; otherwise, the restoration might not be able to proceed. Once all advanced options have been set, click OK.
At the bottom of the RESTORE DATABASE dialog is a field labeled Dumpfile, which requires you to specify the path of the file that psql or pg_restore (depending on the format of the input file) will use to restore the database. You can do this either by directly entering the filename into the field or by clicking on the button beside the entry box and selecting the desired file.
After selecting the options for the restoration, selecting the dump/archive file used for the restoration, and clicking OK, the following dialog, which shows the output of the psql/pg_restore command, appears. (When using the pg_restore command, Verbose Mode must be selected.)
After the psql/pg_restore command has finished, you can:
Save the output to a file by clicking the Save button
Clear the output by clicking on Clear button
Close the output dialog by clicking the Close button.
Otherwise, if Verbose Mode is not selected, the following progress bar dialog appears:
As noted earlier, if Verbose Mode is not selected, it is possible that you may not be made aware of any errors that occurred during the restoration.
Following the restoration, the tree in the Tree View pane temporarily collapses. If the restore operation was successful, the tree expands again, and all restored elements should be present under the expanded Databases header node.
If you have the Ask For Confirmations option set to True, then before every restore operation you are notified of the imminent tree collapse associated with the restore, and given the option to abort the operation.
If the RESTORE DATABASE dialog is brought up from the database object level, then the entry box for entering the name of the object being restored is omitted, as is the Database Options radio buttons. This is because when the dialog is raised from the database object level, it is assumed that the database object being restored into is the same database object that the dialog was raised from.
3.19.2. Restoring a Table
From any Tables header, right-click to bring up the context menu and select RESTORE TABLE to bring up the RESTORE TABLE dialog.
When the RESTORE TABLE dialog appears, you have three options: Input Format, Content Selectivity, and Other Options. At the top of the dialog is a field for entering the name of the table object to be restored. To use a name of a table that already exists under that database, check the Drop Database Object Before Recreating checkbox in the Advanced Options dialog. In this case the object will be dropped, then it will be restored.
Since it is not possible to restore a particular table from a plain-text SQL input file, the only input format options are Tar-Archive and Custom Archive, which correspond to the -F t and -F c options to pg_restore, respectively.
Content Selectivity contains radio buttons for specifying what to restore (Data And Schema, Data Only, or Schema Only). Restoring both the data and the schema is the default for pg_restore, and the restoration of data only and the restoration of schema only correspond to the -a and -s options to pg_restore, respectively.
Other Options contains a check button for enabling/disabling Verbose Mode and a button for viewing more advanced options. Click the Advanced Options button to bring up the Advanced Options dialog:
The advanced options are grouped into four categories (Other Data To Restore, Restoration Protocol, Restoration Order, and Compatibility).
Other Data To Restore contains check buttons for specifying additional elements to be included in the restoration of the table, namely original object ownerships and access privileges. These are, by default, included in the restoration, and unchecking Restore Original Object Ownerships or Restore Access Privileges corresponds to setting the -O or -x options to pg_restore, respectively.
Restoration Protocol contains check buttons for specifying what actions are to be taken before/during the restoration (dropping database objects before recreating them, creating the database before restoring it, disabling triggers during the restoration, and connecting/reconnecting to the database as needed). The first three of these options correspond to the -c, -C, and -X disable-triggers options to pg_restore, respectively. By default, Connect/Reconnect To Database As Needed is checked; unchecking it corresponds to setting the -R option of pg_restore. The option to disable triggers during restoration is available only on pg_restore versions 7.3 and above, so for pg_restore versions below 7.3 this option is greyed out.
Restoration Order contains radio buttons for specifying the order in which elements in the database are restored (in original dump order, OID order, or modified OID order, which correspond to the -N, -o, and -r options to pg_restore, respectively).
Compatibility contains a check button for ignoring version mismatch, which corresponds to the -i option of pg_restore. This option is available only under pg_restore versions 7.3 or above, and thus, for pg_restore versions below 7.3, this option is greyed out. If not greyed out, and if the pg_restore version series differs from the version series of the database server, then this option is, by default, checked; otherwise, the restoration might not be able to proceed. Once all advanced options have been set, click OK.
At the bottom of the RESTORE TABLE dialog is the Archive file field, which allows you to specify the path of the file that pg_restore uses to restore the table.
After selecting the options for the restoration, selecting the dumpfile used for the restoration, and clicking OK, the following dialog, which shows the output of the pg_restore command, appears (provided that Verbose Mode is selected):
After the pg_restore command has finished, the output can be saved to a file by clicking on the Save button; the output may also be cleared by clicking on the Clear button; to close the output dialog, click the Close button. Otherwise, if Verbose Mode is not selected, the following progress bar dialog appears:
As with the restoration of databases, if Verbose Mode is not selected, it is possible that you may not be made aware of any errors that occurred during the restoration.
3.19.3. Restoring a Function
Restoring a function is similar to restoring a table. From any Functions header, right-click to bring up the context menu, then select RESTORE FUNCTION to bring up the RESTORE FUNCTION dialog.
| Note |
|---|---|
The RESTORE FUNCTION dialog is nearly identical to the RESTORE TABLE dialog, except that at the top of the dialog there are two fields, one for entering the function name and one for entering the function arguments. For a function with no arguments, simply leave the Argument List field empty. For a function with one or more arguments, enter the argument types (without any schema qualification) as a comma-separated list, without any spaces. |
3.19.4. Incompatible Options
When restoring a database, table, or function, there are option combinations that are disallowed because the options are incompatible. The pairs of incompatible options are listed below:
Under Restoration Protocol, checking both Drop Database Objects Before Recreating Them and Create Database Before Restoring.
Under Restoration Protocol, checking Create Database Before Restoring and not checking Connect/Reconnect To Database As Needed.