Source:PostgreSQL Restore: Difference between revisions

From SEPsesam
(Marked this version for translation)
mNo edit summary
(3 intermediate revisions by the same user not shown)
Line 1: Line 1:
<translate><!--T:1-->
<translate><!--T:1-->
<div class="noprint"><languages />{{Copyright SEP AG‎|en}}
<div class="noprint"><languages />{{Copyright SEP AG‎|en}}
{{draft}}


<!--T:2-->
<!--T:2-->
Line 46: Line 47:
|}</div>
|}</div>
<translate><!--T:15-->
<translate><!--T:15-->
The [[Special:MyLanguage/SEP_sesam_Glossary#restore|restore]] of PostgreSQL database(s) is quite similar to a regular file system restore. It is possible to restore the PostgreSQL databases to the ''original'' or to a ''new location''. Restore is triggered from the ''Menu bar'' -> '''Activities''' -> '''Restore'''. Restore wizard guides you through the process of restoring your data. For step-by-step procedure, see [[Special:MyLanguage/Standard_Restore_Procedure|Standard Restore Procedure]].
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.  
<br />Before you begin, check that all the [[Special:MyLanguage/4_4_3:PostgreSQL_Restore#prerequisites|prerequisites]] are met.


<!--T:49-->
==={{anchor|features}}Key features=== 
Note that SEP sesam provides the web ''Restore Assistant'' interface which is designed to be more intuitive and offers additional advanced options compared to ''GUI restore wizard'' while, on the other hand, it does not support the restore of special tasks types, such as ''MS SQL'', ''PostgreSQL'', ''Oracle'', etc. For these task types you can only use the GUI restore wizard to restore your data.
 
*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''.
 
*There are two ways to restore PostgreSQL database(s) in SEP sesam: via the [[Special:MyLanguage/4_4_3:PostgreSQL Restore#GUI|''GUI restore wizard'']] or via the web [[Special:MyLanguage/PostgreSQL Restore#RA|''Restore Assistant'']] (as of v. ≥ [[Special:MyLanguage/SEP_sesam_Release_Versions|SEP sesam 5.0.0 Jaglion V2]]). Although most options are the same in both restore interfaces, the web ''Restore Assistant'' interface is more intuitive and offers additional advanced options.
 
*Scheduling restore is only supported in GUI mode. For details, see [[Special:MyLanguage/Scheduling_Restore|Scheduling Restore]].
 
=== {{anchor|prerequisites}}Prerequisites ===
Before performing PostgreSQL restore, check the following prerequisites:</translate>
*<translate>For a successful restore, all preparation steps must be performed properly. For details, see [[Special:MyLanguage/4_4_3:PostgreSQL_Backup|PostgreSQL Backup]].</translate>
* <translate>Depending on what you want to restore, make sure you have the required '''restore permissions''' and '''access rights'''. For details, see [[Special:MyLanguage/5_0_0:User_Roles_and_Permissions|User Roles and Permissions]].</translate>
* <translate>Make sure that there is '''sufficient free space''' on the target system to which you are restoring your data.


== {{anchor|restore}}Restoring PostgreSQL == <!--T:16-->
== {{anchor|restore}}Restoring PostgreSQL == <!--T:16-->
Depending on the interface you are using, follow the appropriate procedure to restore PostgreSQL DB:
*[[Special:MyLanguage/4_4_3:PostgreSQL_Restore#GUI|''Restore via GUI'']]
*[[Special:MyLanguage/4_4_3:PostgreSQL_Restore#RA|Restore via ''Restore Assistant'']] (as of v. ≥ [[Special:MyLanguage/SEP_sesam_Release_Versions|SEP sesam 5.0.0 Jaglion V2]])
==={{anchor|single}}Restoring a single database (DB)=== <!--T:37-->
<!--T:38--> {{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 [[Special:MyLanguage/4_4_3:PostgreSQL_Backup|PostgreSQL Backup]].}}
==={{anchor|GUI}}PostgreSQL restore via the GUI===


<!--T:17-->
<!--T:17-->
Line 100: Line 126:
<li><translate><!--T:46--> In the final step of the restore wizard, you can review all the settings of your restore task. You can also edit the settings by using '''Change Selection''' button. If you want to start your restore immediately, click '''Start'''. If you want to save the restore task, click '''Save'''.</translate></li></ol>
<li><translate><!--T:46--> In the final step of the restore wizard, you can review all the settings of your restore task. You can also edit the settings by using '''Change Selection''' button. If you want to start your restore immediately, click '''Start'''. If you want to save the restore task, click '''Save'''.</translate></li></ol>
<translate><!--T:36-->
<translate><!--T:36-->
A restore task can be scheduled like any other task. If you want to add a restore task to the schedule, see [[Special:MyLanguage/Scheduling_Restore|Scheduling Restore]].
A restore task can be scheduled like any other task. If you want to add a restore task to the schedule, see [[Special:MyLanguage/Scheduling_Restore|Scheduling Restore]].</translate>
 
<translate>==={{anchor|RA}}PostgreSQL restore via the ''Restore Assistant''===


=={{anchor|single}}Restoring a single database (DB)== <!--T:37-->
You can access the ''Restore Assistant'' in one of the following ways:</translate>
*<translate>via the GUI: by clicking the ''Restore Assistant icon'' in the toolbar or from ''Activities'' -> ''Restore Assistant''</translate>
*<translate>from SEP sesam Web UI: left menu -> ''Restore Assistant''</translate>
*<translate>or by entering the following address in the browser bar: ''http://[sesamserver]:11401/sep/ui/restore/''.</translate>  


<!--T:38-->
{{<translate>note</translate>|
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 [[Special:MyLanguage/4_4_3:PostgreSQL_Backup|PostgreSQL Backup]].</translate>  
*<translate>If you cannot access the web ''Restore Assistant'', check if you have received the appropriate permissions for online restore.</translate>
*<translate>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 [[Special:MyLanguage/SEP_sesam_Glossary#UI_mode|''UI mode'']].</translate><br /><translate>For details, see [[Special:MyLanguage/5_0_0:About_Authentication_and_Authorization|About Authentication and Authorization]] and [[Special:MyLanguage/5_0_0:User_Roles_and_Permissions|User Roles and Permissions]].</translate>}}


{{<translate><!--T:39-->
<translate>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 [[Special:MyLanguage/5_0_0:Restore Assistant|Restore Assistant]].</translate>
Note</translate>|<translate><!--T:40-->
 
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 case, check the restore protocol and the data in the database.</translate>}}
{{<translate>note</translate>|<translate>You can only restore one database if it was backed up alone, with one task per database.</translate>}}
 
<!--
<ol><li><translate>Open the ''Restore Assistant'' in the browser.</translate></li>
<li><translate>In the ''Start'' window, select '''PostgreSQL'''. If you want to perform PostgreSQL restore in the ''advanced'' UI mode, you also have to select '''Advanced View''' and then '''Restore of SQL Server databases'''. Click '''Next'''.</translate></li>
<translate>[[image:Restore_assistant_MS_SQL_start_Jaglion.jpg|600px|link=]]</translate>
<br clear=all>
<li><translate>In the ''PostgreSQL Server'' window, select your PostgreSQL.</translate></li>
<translate>[[image:PostgreSQL_RA_select.jpg|600px|link=]]</translate>
<br clear=all>
<li><translate>In the ''Task'' window, under the ''Task selection'', select your backup task. A [[Special:MyLanguage/SEP_sesam_Glossary#backup_task|backup task]] defines the source data that was backed up by the client.</translate></li>
{{<translate>Tip</translate>|<translate>You can search for a file or directory by entering your search term in the ''Search for files or directories in all backups'' field.</translate>}}
<translate>Under the ''Backup selection'', select the exact backup version you want to restore. You can use the calendar function in the upper right corner to set a date range for the backups.</translate><br />
<translate>Then select whether you want to perform a '''selective''' or '''complete''' restore and click '''Next'''. Note that a selective restore requires an additional step. For a complete restore, you are immediately taken to step 5 (''Target'' tab).</translate><br />
<translate>[[image:PostgreSQL_RA_select_task.jpg|600px|link=]]</translate>
<br clear=all>
<li><translate>In the ''Target'' window, first select the target client for the restore. You can filter the clients by name, location, and OS.</translate></li>
<translate>[[image:PostgreSQL_RA_select_target.jpg|600px|link=]]</translate>
<br clear=all>
<li><translate>The ''Restore to the original target path'' option is enabled by default. Skip this option to restore the files to the ''original location''. Uncheck this option if you want to restore your data to a ''new restore target with relocation'', and specify the new target path; you can type or browse the path where you want to restore your data.</translate></li>
<translate>[[image:PostgreSQL_RA_target_path.jpg|600px|link=]]</translate>
<br clear=all>
<li><translate>Under the ''Execution options'', you can set additional restore options:</translate></li>
<ul><li><translate>'''''Do not overwrite existing items''''': The data is restored only if it does not already exist on the target system.</translate></li>
<li><translate>'''''Overwrite existing items''''': If the data exists on the target server, it is replaced with the restored version. This option must be selected if restoring to the ''original location''.</translate></li></ul>
<li><translate>Among the ''Recovery State Options'', you can select one of the following database recovery options:</translate></li>
<ul><li><translate>''Leave the database ready for use by rolling back uncommitted transactions. Additional transaction logs cannot be restored.'' Additionally, you can also ''preserve the replication settings'' and/or ''restrict access to the restored database''.</translate></li>
<li><translate>''Leave the database non-operational, and do not roll back uncommitted transactions. Additional transaction logs can be restored.''</translate></li>
<li><translate>''Leave the database in read-only mode. Undo uncommitted transactions, but save the undo actions in a file so that recovery effects can be reversed.'' In this case, you must also specify the name of the file ''Standby File''.</translate></li>
<translate>[[image:PostgreSQL_RA_recovery options.jpg|600px|link=]]</translate>
<br clear=all></ul>
<translate>Click '''Next'''. Note that if you are restoring in the ''advanced'' mode, you can set additional restore options in the next step (''Options'' tab). In ''basic'' restore mode, you will be taken to the last step immediately.</translate>
{{anchor|options}}<li><translate>The ''Options'' tab (available only in the ''advanced'' mode) allows you to set the following options:</translate></li>
<translate>Under ''Optional data source selection'', you can select your ''preferred media pool'', ''drive'', ''used media|barcode'', and ''interface'' from the drop-down lists.</translate>
 
<translate>[[image:Advanced_options_optional_data_Jaglion.jpg|600px|link=]]</translate>
<br clear=all>
<translate>The ''Include/ Exclude Filter'' tab allows you to specify which files or directories you want to include or exclude from the restore, for example, enter ''*.docx'' in the appropriate filter to include or exclude all MS Word *.docx files from the restore. You can use the include or exclude filter on the client-side or the exclude filter on the server-side. The latter is not available for complete restores.</translate>


[[image:Advanced_options_filter_Jaglion.jpg|600px|link=]]
<br clear=all>
<translate>Under the ''Advanced restore options'', you can further refine your restore:</translate>
<ul><li><translate>Use the ''Log, Special Options'' tab to change the log level for your specific restore, see [[Special:MyLanguage/Setting_Log_Level|Setting Log Level]]. You can specify additional commands that may be useful for specific options of the <tt>sbc</tt> command. For details about the commands, see [[Special:MyLanguage/SBC_CLI|SBC CLI]].</translate></li>
<translate>[[image:Advanced_options_log_Jaglion.jpg|600px|link=]]</translate>
<br clear=all>
<li><translate>Use the ''Retention, Generation, Pre/Post'' tab if you want to specify the retention period parameter for the restore (how long (in days) the restore task is kept), enable/disable a generation restore, and specify whether to apply a pre- or post-script to the restore task, see [[Special:MyLanguage/4_4_3_Beefalo:Activities#pre_post|Pre/Post options]].</translate></li>
<translate>[[image:Advanced_options_retention_Jaglion.jpg|600px|link=]]</translate>
<br clear=all>
</ul>
<translate>When you have set everything, click '''Next'''.</translate>
<li><translate>In the last step, check the summary of your restore task (restore type (based on [[Special:MyLanguage/SEP_sesam_Glossary#task_type|task type]], selected backup, its date and details, restore options, etc.) and click '''Start restore'''.</translate></li>
<translate>[[image:PostgreSQL_RA_finish_Jaglion.jpg|600px|link=]]</translate>
<br clear=all>
</ol>-->
<translate>=={{anchor|monitor}}Monitoring restore== <!--T:51-->
<translate>=={{anchor|monitor}}Monitoring restore== <!--T:51-->


<!--T:52-->
<!--T:52-->
You can monitor the restore progress using the [[Special:MyLanguage/5_0_0:SEP_sesam_Web_UI#restores|''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 [[Special:MyLanguage/5_0_0:SEP_sesam_Web_UI#restores|SEP sesam Web UI]] or [[Special:MyLanguage/4_4_3_Beefalo:Restores_by_State|Restores by State in the GUI]].
You can monitor the restore progress using the [[Special:MyLanguage/5_0_0:SEP_sesam_Web_UI#restores|''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 [[Special:MyLanguage/5_0_0:SEP_sesam_Web_UI#restores|SEP sesam Web UI]] or [[Special:MyLanguage/4_4_3_Beefalo:Restores_by_State|Restores by State in the GUI]].</translate>
 
{{<translate><!--T:39-->
Note</translate>|<translate><!--T:40-->
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.</translate>}}


==Known issues== <!--T:41-->
<translate>==Known issues== <!--T:41-->
If you have problems with PostgreSQL, check the [[Special:MyLanguage/Troubleshooting_Guide|Troubleshooting Guide]].</translate>
If you have problems with PostgreSQL, check the [[Special:MyLanguage/Troubleshooting_Guide|Troubleshooting Guide]].</translate>
<div class="noprint">
<div class="noprint">
<translate>==See also== <!--T:42-->
<translate>==See also== <!--T:42-->
[[Special:MyLanguage/Standard_Restore_Procedure|Standard Restore Procedure]] – [[Special:MyLanguage/4_4_3:PostgreSQL_Backup|PostgreSQL Backup]]</translate></div>
[[Special:MyLanguage/Standard_Restore_Procedure|Standard Restore Procedure]] – [[Special:MyLanguage/4_4_3:PostgreSQL_Backup|PostgreSQL Backup]]</translate></div>

Revision as of 16:49, 6 June 2022

Other languages:
Copyright © SEP AG 1999-2024. All rights reserved.

Any form of reproduction of the contents or parts of this manual is allowed only with the express written permission from SEP AG. When compiling and designing user documentation SEP AG uses great diligence and attempts to deliver accurate and correct information. However, SEP AG cannot issue a guarantee for the contents of this manual.

Draft.png WORK IN PROGRESS
This article is in the initial stage and may be updated, replaced or deleted at any time. It is inappropriate to use this document as reference material as it is a work in progress and should be treated as such.


Docs latest icon.png Welcome to the latest SEP sesam documentation version 4.4.3 Beefalo/5.0.0 Jaglion. For previous documentation version(s), check PostgreSQL archive.


Overview

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.
  • There are two ways to restore PostgreSQL database(s) in SEP sesam: via the GUI restore wizard or via the web Restore Assistant (as of v. ≥ SEP sesam 5.0.0 Jaglion V2). Although most options are the same in both restore interfaces, the web Restore Assistant interface is more intuitive and offers additional advanced options.

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

Depending on the interface you are using, follow the appropriate procedure to restore PostgreSQL DB:

Restoring a single database (DB)

Information sign.png 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.

PostgreSQL restore via the GUI

Create a new restore task for the PostgreSQL saveset you want to restore.

  1. From the SEP sesam GUI menu bar, select Activities -> Restore. The New Restore Task window opens.
  2. Select what you want to restore. You can search savesets by task name or by filename or path.
  3. Under the Saved in period drop-down lists, specify the time frame for which you want to conduct the search. Click Next.
  4. The search results are displayed. From the list of savesets matching your query, select the version you want to restore. Click Next.
  5. PostgreSQL restore select task Beefalo V2.jpg
  6. Under the Target Settings, set additional options.
    • Under the Target path settings, 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.
      • Information sign.png 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
    • Click the Expert Options button if you want to specify additional restore settings, e.g., modify the log level, define the pre/post script for restore, etc. For details, see the Restore wizard.
    • Information sign.png Note
      The Expert Options button for specifying advanced restore options is available only in advanced UI mode (formerly expert GUI mode). To use Expert Options, make sure your UI mode is set to advanced. For details, see Selecting UI mode.

    PostgreSQL restore target Beefalo V2.jpg

  7. In the final step of the restore wizard, you can review all the settings of your restore task. You can also edit the settings by using Change Selection button. If you want to start your restore immediately, click Start. If you want to save the restore task, click Save.

A restore task can be scheduled like any other task. If you want to add a restore task to the schedule, see Scheduling Restore.

PostgreSQL restore via the Restore Assistant

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/.
Information sign.png 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 Restore Assistant.

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

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 or Restores by State in the GUI.

Information sign.png 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.

Known issues

If you have problems with PostgreSQL, check the Troubleshooting Guide.