5 2 0:PostgreSQL Restore

Welcome to the latest SEP sesam documentation version 5.2.0 Artemis. For previous documentation version(s), check documentation archive.

Overview

Additional resources

Visit SEP sesam YouTube Channel for latest videos and webinars.

Check FAQ to find the answers to most common questions.

Problems? See the Troubleshooting Guide.

If you are using an older SEP sesam version, refer to Documentation archive.

The restore of PostgreSQL database(s) is quite similar to a regular file system restore. SEP sesam enables you to restore all or only one database from a PostgreSQL server, provided that it was backed up with a separate backup task.

Before you begin, check that all the prerequisites are met.

Key features

PostgreSQL restore is simple, guided and similar to a regular file system restore.

You can restore the PostgreSQL database(s) to the original or to a new location.

Scheduling restore is only supported in GUI mode. For details, see Scheduling Restore.

Prerequisites

Before performing PostgreSQL restore, check the following prerequisites:

For a successful restore, all preparation steps must be performed properly. For details, see PostgreSQL Backup.
Depending on what you want to restore, make sure you have the required restore permissions and access rights. For details, see User Roles and Permissions.
Make sure that there is sufficient free space on the target system to which you are restoring your data.

Restoring PostgreSQL

This procedure provides specific instructions for restoring a PostgreSQL database. For general description of the restore procedure and more details on available options, refer to Standard Restore Procedure.

Restoring a single database (DB)

	Note
	To be able to restore a single DB from a PostgreSQL server, you have to create a separate backup task for each PostgreSQL DB. If the PostgreSQL source in the backup task is set as all, you cannot restore an individual database. For details, see PostgreSQL Backup.

You can access the Restore Assistant in one of the following ways:

via the GUI: by clicking the Restore Assistant icon in the toolbar or from Activities -> Restore Assistant
from SEP sesam Web UI: left menu -> Restore Assistant
or by entering the following address in the browser bar: http://[sesamserver]:11401/sep/ui/restore/.

Note

If you cannot access the web Restore Assistant, check if you have received the appropriate permissions for online restore.
The operations and options available after logging in may differ depending on the user type. Other Web UI display restrictions may depend on the custom roles with specific permissions and the UI mode.
For details, see About Authentication and Authorization and User Roles and Permissions.

With the web Restore Assistant, you can restore PostgreSQL DB to the original or another location using simple or advanced UI mode. When using advanced UI mode, an additional Options tab is available for setting advanced restore options. You can also restore backup into dump file and restore the data to a single file rather than to original location. In this case, you must specify a restore destination path in the Target window (by browsing or typing the path). Optionally, you can also change the name of the dump file. If the dump file name is not specified, it is generated automatically. For more details, see Standard Restore Procedure.

	Note
	You can only restore one database if it was backed up alone, with one task per database.

To restore the PostgreSQL database, follow the Restore Assistant to configure the restore task. In the Target window, you can select one of the following options:

Restore to original target path (set by default): Restores the data to the same location from which it was backed up. If it is set together with the option Overwrite existing items the target database will be deleted first and a new one will be created from the backup saveset.
If you select the option New restore target and enter a new database name, the PostgreSQL command createdb creates a database with the specified name. The data is imported into the new database.

	Note
	You have to set up database users and roles before the database is imported to new target.

Under the Execution options drop-down list, select one of the following options:

Do not overwrite existing items (set by default): The database will not be restored if it already exists on the target server.
Overwrite existing items: If the database exists on the target server, it will be replaced by the restored version.
Auto recover (and online) after restore: The data will be stored directly into the database without caching in the file system. Because the data is written directly into the database, it is not possible to modify it.
No recover after restore: Use this option if you want to modify the data before you import it into the database. The restored data will be stored on the file system first. The import of data has to be started on the command line interface of the server's operating system. The data will be written as a dump file onto the file system (usually in the directory <SESAM_VAR>/work). You can use this file as input file for the PostgreSQL program pg_restore as follows:

 su - postgres
 pg_restore template1 -C /var/opt/sesam/var/work/pgsql-SF20081128224529.tmp

Restoring PostgreSQL single database under a new name

If you want to restore your PostgreSQL database under a new name, proceed as follows:

Create a new empty database (e.g., test) on the PostgreSQL server.
Open the Restore Asistant, select a single PostgreSQL database backup and click Next.
Under the Target window, set the following options:

Under the Target path settings, select the New restore target option and enter or browse for your previously created empty database, e.g., test.
Under the Execution options, select Overwrite existing items and Auto recover after restore; see Execution options above.

Follow the restore Assistant to configure your restore task. If you want to start the restore immediately, click Start. If you want to save the restore task, click Save.

Monitoring restore

You can monitor the restore progress using the Web UI (Monitoring -> Restores) or view the status in the GUI (Main Selection -> Job State -> Restores). The restore overview provides detailed information on the last run of restore jobs, including task name, status (successful, error, in queue...), start and stop time of the last backup, data size, throughput, etc. For details, see SEP sesam Web UI.

	Note
	In case of errors during the restore (e.g., primary key error), the restore status in the SEP sesam GUI/Web UI is displayed as erroneous. In such a case, check the restore protocol and the data in the database.