Source:PostgreSQL Restore: Difference between revisions

From SEPsesam
(Fixed navigation to Beefalo.)
(Beefalo V2-related update.)
Line 3: Line 3:


<!--T:2-->
<!--T:2-->
{{Navigation_latest|release=[[Special:MyLanguage/SEP_sesam_Release_Versions|4.4.3/4.4.3 ''Beefalo'']]|link=[[Special:MyLanguage/PostgreSQL|PostgreSQL archive]]}}</div></translate><br />
{{Navigation_latest|release=[[Special:MyLanguage/SEP_sesam_Release_Versions|4.4.3/4.4.3 ''Beefalo V2'']]|link=[[Special:MyLanguage/PostgreSQL|PostgreSQL archive]]}}</div></translate><br />


<translate>==Overview== <!--T:3--></translate>
<translate>==Overview== <!--T:3--></translate>
Line 11: Line 11:
{|style="margin: auto; margin-bottom:1em; width:100%; border:0px solid grey;"
{|style="margin: auto; margin-bottom:1em; width:100%; border:0px solid grey;"
| rowspan="2" style="padding:0px 10px 0px;" | <translate><!--T:5-->
| rowspan="2" style="padding:0px 10px 0px;" | <translate><!--T:5-->
[[File:SEP_next.png|45px|link=Special:MyLanguage/4_4_3:PostgreSQL_Backup]]</translate>
[[File:SEP_next.png|45px|link=Special:MyLanguage/Standard_Restore_Procedure|Standard Restore Procedure]]</translate>
| style="padding:0px 40px 0px 10px; color: grey; font-size: 90%; text-align:left;" | <translate><!--T:6-->
| style="padding:0px 40px 0px 10px; color: grey; font-size: 90%; text-align:left;" | <translate><!--T:6-->
See also: [[Special:MyLanguage/4_4_3:PostgreSQL_Backup|PostgreSQL Backup]]</translate>
See also: [[Special:MyLanguage/Standard_Restore_Procedure|Standard Restore Procedure]] – [[Special:MyLanguage/4_4_3:PostgreSQL_Backup|PostgreSQL Backup]]</translate>
|}
|}


Line 46: Line 46:
|}</div>
|}</div>
<translate><!--T:15-->
<translate><!--T:15-->
The [[Special:MyLanguage/SEP_sesam_Glossary#restore|restore]] of virtual machines (VMs) is quite similar to a regular file system restore. A restore operation offers the possibilities to execute '''with''' or '''without''' (no) [[Special:MyLanguage/SEP_sesam_Glossary#recovery|recover]]. 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 [[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]].


== {{anchor|virtual}}Restoring a PostgreSQL virtual machine == <!--T:16-->
== {{anchor|restore}}Restoring PostgreSQL == <!--T:16-->


<!--T:17-->
<!--T:17-->
Create a new restore task for the VM you want to restore.</translate>
Create a new restore task for the PostgreSQL saveset you want to restore.</translate>
<ol><li>
<ol><li>
<translate><!--T:18-->
<translate><!--T:18-->
From the SEP sesam GUI menu bar, select '''Activities -> Restore'''. The ''New restore task'' window opens.</translate></li>
From the SEP sesam GUI menu bar, select '''Activities''' -> '''Restore'''. The ''New Restore Task'' window opens.</translate></li>
<li><translate><!--T:19-->
<li><translate><!--T:19-->
Select what you want to restore. You can search save sets by ''task name'' or by ''filename or path''.</translate></li>
Select what you want to restore. You can search savesets by ''task name'' or by ''filename or path''.</translate></li>
<li><translate><!--T:20-->
<li><translate><!--T:20-->
Under the '''Saved in period''' drop-down lists, specify the time frame for which you want to conduct the search. Click '''Next'''.</translate></li>
Under the '''Saved in period''' drop-down lists, specify the time frame for which you want to conduct the search. Click '''Next'''.</translate></li>
<li><translate><!--T:21-->
<li><translate><!--T:21-->
The search results are displayed. From the list of save sets matching your query, click the version you want to restore. Click '''Next'''.</translate></li>
The search results are displayed. From the list of savesets matching your query, select the version you want to restore. Click '''Next'''.</translate></li>
<translate><!--T:22-->
<translate><!--T:22-->
[[Image:PostgreSQL_restore_select_task.png|link=]]</translate>
[[Image:PostgreSQL_restore_select_task_Beefalo_V2.jpg|800px|link=]]</translate>
<br clear=all>
<br clear=all>
<li><translate><!--T:23-->
<li><translate><!--T:23-->
Review your restore task configuration and set additional restore options in the final '''Save and Start''' step of the wizard.</translate></li>
Under the ''Target Settings'', set additional options.</translate></li>
<ul><li><translate><!--T:24-->
<ul><li><translate><!--T:24-->
Under the '''Target Path Settings''', select one of the following options:</translate></li>
Under the '''Target path settings''', select one of the following options:</translate></li>
<ul><li><translate><!--T:25-->
<ul><li><translate><!--T:25-->
'''Restore to the 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 files'' the target database will be deleted first and a new one will be created from the backup save set.</translate></li>
'''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.</translate></li>
<li><translate><!--T:26-->
<li><translate><!--T:26-->
If you select the option '''New restore target''' and enter a new database name, the PostgreSQL command <tt>createdb</tt> creates a database with the specified name. The data is imported into the new database.</translate></li>
If you select the option '''New restore target''' and enter a new database name, the PostgreSQL command <tt>createdb</tt> creates a database with the specified name. The data is imported into the new database.</translate></li>
Line 78: Line 78:
Under the '''Execution options''' drop-down list, select one of the following options:</translate></li>
Under the '''Execution options''' drop-down list, select one of the following options:</translate></li>
<ul><li><translate><!--T:30-->
<ul><li><translate><!--T:30-->
'''do not overwrite existing files''' (set by default): The database will not be restored if it already exists on the set target.</translate></li>
'''Do not overwrite existing items''' (set by default): The database will not be restored if it already exists on the target server.</translate></li>
<li><translate><!--T:31-->
<li><translate><!--T:31-->
'''overwrite existing files''': If the database exists on the target server, it will be replaced by the restored version.</translate></li>
'''Overwrite existing items''': If the database exists on the target server, it will be replaced by the restored version.</translate></li>
<li><translate><!--T:32-->
<li><translate><!--T:32-->
'''Auto recover 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.</translate></li>
'''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.</translate></li>
<li><translate><!--T:33-->
<li><translate><!--T:33-->
'''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 <tt><SESAM-VAR>/work</tt>). You can use this file as input file for the PostgreSQL program <tt>pg_restore</tt> as follows:</translate></li>
'''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 <tt><SESAM_VAR>/work</tt>). You can use this file as input file for the PostgreSQL program <tt>pg_restore</tt> as follows:</translate></li>


  su - postgres
  su - postgres
  pg_restore template1 -C /var/opt/sesam/var/work/pgsql-SF20081128224529.tmp
  pg_restore template1 -C /var/opt/sesam/var/work/pgsql-SF20081128224529.tmp
</ul></ul>
</ul>
<li><translate>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 [[Special:MyLanguage/4_4_3_Beefalo:Restore#wizard|Restore wizard]].</translate></li>
{{<translate>note</translate>|<translate>As of v. [[Special:MyLanguage/SEP_sesam_Release_Versions|4.4.3. ''Beefalo'']], the ''Expert Options'' button for specifying more advanced restore options is only available in the [[Special:MyLanguage/SEP_sesam_Glossary#UI_mode|''Advanced'' and ''Expert'' UI mode]]. It is not available for users that run the GUI in ''Basic'' mode. In the latter case, if you want to access the the ''Expert Options'' you first have to change the ''Basic'' UI mode to ''Advanced'' or ''Expert'', as described in [[Special:MyLanguage/4_4_3_Beefalo:Configuration#Beefalo|UI Mode]].</translate>}}
</ul>
<translate><!--T:34-->
<translate><!--T:34-->
[[Image:PostgreSQL_restore_save.png|link=]]</translate>
[[Image:PostgreSQL_restore_target_Beefalo_V2.jpg|800px|link=]]</translate>
<br clear=all>
<br clear=all>
<li><translate><!--T:35-->
<li><translate>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>
If you want to start your database 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]].


=={{anchor|single}}Restoring single databases (DBs)== <!--T:37-->
You can view the status of your restore jobs by selecting '''Job state''' -> '''Restores''' from the ''Main Selection''. Restore overview provides detailed information on the last run of restore jobs, including the task name, status (successful, error, in queue ...), start and stop time of the last backup, data size, throughput, client and message.
 
As of [[SEP_sesam_Release_Versions|4.4.3 ''Beefalo V2'']], you can also monitor the restores online by using new Web UI. For details, see [[Special:MyLanguage/4_4_3_Beefalo:SEP_sesam_Web_UI|SEP sesam Web UI]].
 
=={{anchor|single}}Restoring a single database (DB)== <!--T:37-->


<!--T:38-->
<!--T:38-->
Line 104: Line 110:
{{<translate><!--T:39-->
{{<translate><!--T:39-->
Note</translate>|<translate><!--T:40-->
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 is displayed as erroneous. In such case, check the restore protocol and the data in the database.</translate>}}
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>==Known issues== <!--T:41-->
<translate>==Known issues== <!--T:41-->
Line 110: Line 116:
<div class="noprint">
<div class="noprint">
<translate>==See also== <!--T:42-->
<translate>==See also== <!--T:42-->
[[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 14:52, 25 February 2020

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.

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


Overview

The 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 Standard Restore Procedure.

Restoring PostgreSQL

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
      As of v. 4.4.3. Beefalo, the Expert Options button for specifying more advanced restore options is only available in the Advanced and Expert UI mode. It is not available for users that run the GUI in Basic mode. In the latter case, if you want to access the the Expert Options you first have to change the Basic UI mode to Advanced or Expert, as described in 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.

You can view the status of your restore jobs by selecting Job state -> Restores from the Main Selection. Restore overview provides detailed information on the last run of restore jobs, including the task name, status (successful, error, in queue ...), start and stop time of the last backup, data size, throughput, client and message.

As of 4.4.3 Beefalo V2, you can also monitor the restores online by using new Web UI. For details, see SEP sesam Web UI.

Restoring a single database (DB)

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.

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 case, check the restore protocol and the data in the database.

Known issues

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