Advanced Restore - PostgreSQL iDataAgent

Restore Data Browse and Restore
Table of Contents

Restoring the PostgreSQL Server

When the PostgreSQL Server is corrupted or lost, you can restore and recover it from the latest full backup. Both the data and the logs can be restored using the FSBasedBackupSets.

Ensure that the following guidelines are met before you start the restore:

  • You need to stop the PostgreSQL server before performing the File System restore to recover the entire PostgreSQL server.
  • Make sure to delete or rename the wal and data directories.
  • Restore the File System backups of same version of PostgreSQL and not from higher to lower or lower to higher.

Use the following steps to restore the PostgreSQL server:

  1. From the CommCell Browser, navigate to <Client> | PostgreSQL | <Instance>.
  2. Right-click the <FSBasedBackupset>, and then click All Tasks | Browse and Restore.
  3. Click View Content.
  1. Select the data that you want to restore and click Recover All Selected.
  2. Click OK.

Restoring Individual Databases

You can restore the individual databases (dumps) that are specifically created using dump based backup sets.

Before performing the dump based restore operations, verify the following:

  • Disconnect the database(s) prior to restoring the database(s).

  • Restore the same versions or from lower to higher versions of databases.
  • Before restoring the maintenance database, ensure to clean it up by deleting the database objects like tables, views, etc.
  1. From the CommCell Browser, navigate to <Client> | PostgreSQL | <Instance>.
  2. Right-click the <DumpBasedBackupset> that contains the databases you want to restore and then click All Tasks | Browse and Restore.
  3. Click View Content.
  1. Select the databases you want to restore and click Recover All Selected.
  2. Select the Destination Server.
  3. Click OK.

Restoring Databases to a Point-in-Time

If any undesired transaction occurs in the database, you can revert the database to a state just before the transaction. The point in time option enables you to restore the database to a specific point-in-time. Point-in-time restores are supported by FSBasedBackupSets only. This option is useful in the following scenarios:

  • If any undesired transaction occurs in the database, you can revert the database to a state just before the transaction.
  • If a database fails, you can restore to the state just before the point of failure.
  • You can restore multiple databases to a consistent time. This will be useful for the absolute synchronization of the databases.

When you perform a point-in-time restore for a database, the next scheduled backup for that database will automatically convert to a full backup.

  • You need to stop the PostgreSQL server before performing the File System restore to recover the entire PostgreSQL server to a point in time.
  • Make sure to delete or rename the wal and data directories.
  • Restore the File System backups of same version of PostgreSQL and not from higher to lower or lower to higher.
  • Before performing the point-in-time restore of a database, ensure that transaction logs for the database are backed up.

Follow the steps given below to restore a database(s) to a point in time:

  1. From the CommCell Browser, navigate to <Client> | PostgreSQL | <Instance>.
  2. Right-click the <FSBasedBackupSet>, and then click All Tasks | Browse and Restore.
  1. Click View Content.
  2. Select the databases you want to restore and click Recover All Selected.
  3. Select the Point in Time check box.
  4. From the Restore Time list, select the date and time. The restore time selected must be later than the browse time.
  5. Click OK.

Restoring Individual Database Tables

To restore individual PostgreSQL database tables, select the individual tables from the list of database tables in a tree view and restore them to a destination location.

Before performing the table restore operations, verify the following:

  • Select the parent tables along with all the dependant tables for a successful restore operation.

  • If the tables containing the large objects (blobs), use database restores instead of table level restores.

  • Use dump based database restore to restore all the tables in a database. If you still use table level restore, the restore job may not successfully restore all the tables to destination database. In place table restores may fail in certain conditions due to inherent limitations of PostgreSQL database.

  • Ensure that the tables to be restored are not having any user defined data types.

  • The following database objects will be dropped automatically from PgSQL server during the table restore:
    • Table
    • View
    • Domain
    • Sequence

    All the database objects other than the above mentioned objects, are to be dropped manually before performing a restore operation, else the restore for certain objects will fail.

  • For PostgreSQL Versions 8.0 and 8.1, manually drop the database objects from the PgSQL server before performing the table restore.
  • PostgreSQL Table View Browse and Table Restore are supported only for DumpBasedBackupSets.

  1. From the CommCell Browser, navigate to <Client> | PostgreSQL | <Instance>.
  2. Right-click the <DumpBasedBackupset> that contains the tables you want to restore, and then click All Tasks | Browse and Restore.
  3. In the Restore Options dialog box, select the Table View check box.
  4. Click View Content.
  1. Select the tables you want to restore and click Recover All Selected.
  2. In the PostgreSQL Table Level Restore Options Dialog box, select the destination path or provide a staging path to export table data to any external location or import it to the server.

    If you want to restore it to an auxiliary database associated to the table restores, define the auxiliary database name.

  3. Click OK.

To a Staging Location

The selected individual tables can be exported to a temporary staging location from where we can import to the server or leave it there.

Use the following steps to restore table data to a staging location:

  1. From the CommCell Browser, navigate to <Client> | PostgreSQL | <Instance>.
  2. Right-click the <DumpBasedBackupset> that contains the tables you want to restore and then click All Tasks | Browse and Restore.
  3. In the Restore Options dialog box, select the Table View check box.
  4. Click View Content.
  1. Select the tables you want to restore and click Recover All Selected.
  2. In the PostgreSQL Table Level Restore Options Dialog box, select the Staging check box.
  3. Type or click Browse to select the staging path to where the table data is to be exported.
  4. Select the Do not import to the server check box.

    If you do not select this option, the table data will be exported to the staging path and from there it will be restored to the source database.

  5. Click OK.

To an Auxiliary Database

The selected individual tables can be restored to auxiliary databases. These auxiliary databases may or may not exist in the server.

Use the following steps to restore table data to an auxiliary database:

  1. From the CommCell Browser, navigate to <Client> | PostgreSQL | <Instance>.
  2. Right-click the <DumpBasedBackupset> that contains the tables you want to restore and then click All Tasks | Browse and Restore.
  3. In the Restore Options dialog box, select the Table View check box.
  4. Click View Content.
  1. Select the tables you want to restore and click Recover All Selected.
  2. In the PostgreSQL Table Level Restore Options Dialog box, select the Define auxiliary database name for Table restores check box.
  3. Double-click the area below Target Database, and type the auxiliary database name where the table data will be restored.
  4. Click OK.

Performing an Out-of-Place Restore

To a Different PostgreSQL Server Instance on the Same Client

This restore is supported only for Dump based backup set.

You can restore the individual databases or database objects like tables and views from one instance to another instance in the same client using Dump based restore.

To a Different PostgreSQL Server Instance on a Different Client

This restore is supported for both FS based backup set and Dump based backup set. Prior to restoring the database to a different server on the different client, make sure the operating system on the source and destination clients are the same.

Also, ensure that the following prerequisites are met before running an FS based restore to a different client.

  • Ensure that both the source and the destination servers have the same port and the archive directory location.
  • Postgres user configured for the source and the destination server should be the same.
  • Source and destination PostgreSQL server version should be the same.
  • Make sure to rename or remove the data directory and the Archive log directory from the destination server.
  • Make sure to stop the destination server.

Follow the steps below to restore a database to a different destination server on a different client:

  1. From the CommCell Browser, navigate to <Client> | PostgreSQL.
  2. Right-click the <Instance> and then click All Tasks | Restore PostgreSQL Server.
  3. Select the destination server on a different client from the Destination Server list.
  4. Click OK.

Restore by Job

The Restore By Jobs feature provides the facility to select a specific backup job to be restored. This method of restoring data is considerably faster as it reads continuously on the tape and retrieves the data and does not depend on the indexing subsystem to get the seek offsets on the media.

This feature can be used in different scenarios such as the followings:

  • To restore point-in-time data associated with a specific backup job, such as full, incremental, differential, and so on.
  • To restore CommServe DR data for the disaster recovery or for creating a hot-site purposes.
  • To restore multiplexed data from the same client.

Consider the following:

  • Run a restore by jobs whenever you want to restore the entire contents of the job.
  • Avoid running restores by jobs for jobs associated with the default subclient. If you do this, the entire contents of the machine are restored and it ends up with out of memory error or restoring operating system files and other unwanted directories and files. 
  • Avoid modifying the contents of any associated subclients since this results in the retrieval of older data.

Use the following steps to restore data by jobs.

  1. From the CommCell Browser, right-click the <Agent>, <Instance>, <BackupSet>, or <Subclient> whose data you want to browse.
  2. Click View | Backup History.
  3. Click OK.
  4. Right-click the backup job that needs to be restored and then click Browse and Restore.
  1. Click View Content.
  2. Select the data and logs you want to restore, and then click Recover All Selected.
  3. Click OK.

Scheduling a Restore

Restore jobs can be scheduled from the instance level, DumpBasedBackupSet and subclient level, and FSBasedBackupSet level. Follow the steps given below to schedule a restore job from the backupset level:

  1. From the CommCell Console, navigate to Client Computers | <Client> | PostgreSQL | <Instance>.
  2. Right-click the <BackupSet> and click All Tasks | Browse and Restore.
  3. Click View Content.
  4. Select the data you want to restore and click Recover All Selected.
  5. Click the Job Initiation tab and click Schedule.
  6. Click Configure.
  7. Select the appropriate scheduling options.  For example:
    • Click Weekly.
    • Check the days you want the run the restore job.
    • Change the Start Time to 9:00 PM
    • Click OK to close the Schedule Details dialog
    • Click OK to close the Restore Options dialog

    The restore job will execute as per the schedule.

Managing Restore Jobs

Once you initiate the restore operation, a restore job is generated in the Job Controller. Jobs can be managed in a number of ways. The following sections provide information on the different job management options available:

Restarting Jobs

Jobs that fail to complete successfully are automatically restarted based on the job restartability configuration set in the Control Panel. This configuration can be changed at any time; however, changes to this configuration will affect all jobs run in the entire CommCell.

It is also possible to override the default CommServe configuration for individual jobs by configuring retry settings when initiating the job. This configuration, however, will apply only to the specific job.

Restore jobs for this Agent are re-started from the beginning.

Configure Job Restartability at the CommServe Level

  1. In the CommCell Console, click the Control Panel icon, then double-click Job Management.
  2. In the Job Restarts tab of the Job Management dialog box, select a job type from the Job Type list, and then select the Restartable check box.
  3. To change the maximum number of times the Job Manager will try to restart a job, select a number in the Max Restarts box.
  4. To change the time interval between attempts by the Job Manager to restart the job, select a number of minutes in the Restart Interval (Mins) box.
  5. Click OK to save your changes.

Configure Job Restartability for an Individual Job

  1. From the Restore Options dialog box, click Advanced, then select the Job Retry tab and specify the following as desired:
    • Total Running Time - The maximum elapsed time, in hours and minutes, from the time that the job is created.
    • Number of Retries - The number of times that Job Manager will attempt to restart the job.
    • Kill Running Jobs When Total Running Time Expires - Option to kill the job when the specified Total Running Time has elapsed, even if its state is "Running".
  2. Click OK.

Controlling Jobs

The following controls are available for running jobs in the Job Controller window:

Suspend

Temporarily stops a job. A suspended job is not terminated; it can be restarted at a later time.

Resume

Resumes a job and returns the status to Waiting, Pending, Queued, or Running. The status depends on the availability of resources, the state of the Operation Windows, or the Activity Control setting.

Kill

Terminates a job.

Suspending a Job

  1. From the Job Controller of the CommCell Console, right-click the job and select Suspend.
  2. The job status may change to Suspend Pending for a few moments while the operation completes. The job status then changes to Suspended.

Resuming a Job

  1. From the Job Controller of the CommCell Console, right-click the job and select Resume.
  2. As the Job Manager attempts to restart the job, the job status changes to Waiting, Pending, or Running.

Killing a Job

  1. From the Job Controller of the CommCell Console, right-click the job and select Kill.
  2. Click Yes when the confirmation prompt appears if you are sure you want to kill the job. The job status may change to Kill Pending for a few moments while the operation completes. Once completed, the job status will change to Killed and it will be removed from the Job Controller window after five minutes.

Additional Restore Options

Several additional options are available to further refine your restore operations. The following table describes these options, as well as the steps to implement them.

Be sure to read the overview material referenced for each feature prior to using them.

Option Description Related Topic
Startup Options

The Startup Options are used by the Job Manager to set priority for resource allocation. This is useful to give higher priority to certain jobs. You can set the priority as follows:

  1. From the CommCell Browser, navigate to Client Computers | <Client> | PostgreSQL | <Instance>.
  2. Right-click the <BackupSet> and then click All Tasks | Browse and Restore.
  3. In the Restore Options dialog box, click View Content.
  4. In the Client Browse window, select the database you want to restore and click Recover All Selected.
  5. In the PostgreSQL Database Restore Options dialog box, click Advanced.
  1. In the Advanced Restore Options dialog box, click Startup.
  2. On the Startup tab, select Change Priority.
  3. Enter a priority number - 0 is the highest priority and 999 is the lowest priority.
  4. Select the Start up in suspended State check box to start the job in a suspended state.
  5. Click OK.
Refer to Job Priority and Priority Precedence.
Copy Precedence By default, the system retrieves data from the storage policy copy with the lowest copy precedence. If the data was pruned from the primary copy, the system automatically retrieves data from the other copies of the storage policy in the lowest copy precedence to highest copy precedence order. Once the data is found, it is  retrieved, and no further copies are checked.

You can retrieve data from a specific storage policy copy (Synchronous Copy or Selective Copy). If data does not exist in the specified copy, the data retrieve operation fails even if the data exists in another copy of the same storage policy. Follow the steps given below to retrieve the data from a specific storage policy copy:

  1. From the CommCell Browser, navigate to Client Computers | <Client> | PostgreSQL | <Instance>.
  2. Right-click the <BackupSet> and then click All Tasks | Browse and Restore.
  3. In the Restore Options dialog box, click View Content.
  4. In the Client Browse window, select the database you want to restore and click Recover All Selected.
  5. In the PostgreSQL Database Restore Options dialog box, click Advanced.
  1. In the Advanced Restore Options dialog box, click Copy Precedence.
  2. On the Copy Precedence tab, select the Restore from copy precedence check box.
  3. Enter the copy precedence number.
  4. Click OK.
Refer to Recovering Data From Copies.
Data Path Options

The data recovery operations use a default Library, MediaAgent, Drive Pool, and Drive as the Data Path. You can use this option to change the data path if the default data path is not available. Follow the steps given below to change the default data path:

  1. From the CommCell Browser, navigate to Client Computers | <Client> | PostgreSQL | <Instance>.
  2. Right-click the <BackupSet> and then click All Tasks | Browse and Restore.
  3. In the Restore Options dialog box, click View Content.
  4. In the Client Browse window, select the database you want to restore and click Recover All Selected.
  5. In the PostgreSQL Database Restore Options dialog box, click Advanced.
  1. In the Advanced Restore Options dialog box, click Data Path.
  2. On the Data Path tab, select the MediaAgent and Library.
  3. Select the Drive Pool and Drive for tape library.
  4. Select the name of the Proxy server if you wish to restore using a proxy server.

    You can select the Proxy server only when you are restoring from a snapshot. This option is not available for a regular restore.

  5. Click OK.
Refer to Change Data Path.
Encryption

If the client's data is encrypted with a pass phrase, you must enter the pass-phrase to start the data recovery operation. Follow the steps given below to enter the pass-phrase:

  1. From the CommCell Browser, navigate to Client Computers | <Client> | PostgreSQL | <Instance>.
  2. Right-click the <BackupSet> and then click All Tasks | Browse and Restore.
  3. In the Restore Options dialog box, click View Content.
  4. In the Client Browse window, select the database you want to restore and click Recover All Selected.
  5. In the PostgreSQL Database Restore Options dialog box, click Advanced.
  1. In the Advanced Restore Options dialog box, click Encryption.
  2. Enter the Pass Phrase.
  3. Click OK.
Refer to Data Encryption.
Alerts

This option enables users or user groups to get automatic notification on the status of the data recovery job. Follow the steps given below to set up the criteria to raise notifications/alerts:

  1. From the CommCell Browser, navigate to Client Computers | <Client> | PostgreSQL | <Instance>.
  2. Right-click the <BackupSet> and then click All Tasks | Browse and Restore.
  3. In the Restore Options dialog box, click View Content.
  4. In the Client Browse window, select the database you want to restore and click Recover All Selected.
  5. In the PostgreSQL Database Restore Options dialog box, click Advanced.
  1. In the Advanced Restore Options dialog box, click Alerts.
  2. Click Add Alert.
  3. From the Add Alert Wizard dialog box, select the required threshold and notification criteria and click Next.
  4. Select the required notification types and click Next.
  5. Select the recipients and click Next.
  6. Click Finish.
  7. Click OK.
Refer to Alerts and Notifications.
CommCell Readiness Report The CommCell Readiness Report provides you with vital information such as connectivity and readiness of the Client, MediaAgent, and CommServe. It is useful to run this report before performing a backup or a recovery operation. Follow the steps given below to generate the report:
  1. On the CommCell Console menu bar, select the Reports tab.
  2. Click Readiness.

    The Report Selection dialog box appears.

  3. If necessary, select Reports | CommServe | CommCell Readiness in the left pane.
  4. Select the Computers tab, and then click Modify below the Computers list.
  5. In the dialog box that appears, clear Include All Client Computers and All Client Groups.
  6. Select the appropriate client from the Exclude list, and then click Include >.
  7. Click OK.
  8. Select the MediaAgent tab, and then clear Include All MediaAgents.
  9. Select the appropriate MediaAgent from the Exclude list, and then click Include >.
  10. Click Run.

    The report appears in your default Web browser.

Refer to CommCell Readiness Report.
Restore Job Summary Report

The Restore Job Summary Report provides you with information about all the data recovery jobs that are run in last 24 hours for a specific client and agent. You can get information such as failure reason, failed objects, job options, and so on. It is useful to run this report after performing the restore. Follow the steps given below to generate the report:

  1. On the CommCell Console menu bar, select the Reports tab.
  2. Click Summary.

    The Report Selection dialog box appears.

  3. If necessary, select Reports | Jobs | Job Summary in the left pane.
  4. On the General tab, select Data Recovery Jobs.
  5. Select the Computers tab.
  6. Select the appropriate client and the appropriate agent.
  7. Click Run.

    The report appears in your default Web browser.

Refer to Restore Job Summary Report.