Restoring a PostgreSQL Backup to a Different PostgreSQL Server Instance on the Same Client

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.

Before You Begin

• Create a PostgreSQL instance on the destination client.

  For steps to create a PostgreSQL instance, see Creating an Instance.

• For a table-level restore, do the following:

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

    If the objects that you want to restore are available on the destination server and have dependent objects, before you perform restore, drop the objects using DROP TABLE with CASCADE.

  • 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 the PostgreSQL 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.

• Verify that the PostgreSQL software version on the destination is the same version or higher than the version in source.

• To restore database using parallel jobs, do the following:

  • Use parallel jobs to dump the PostgreSQL database.

  • If you want to perform stage free restores for Linux clients, verify that the nfs-utils package is installed on the client and configure the 3DFS cache path on the MediaAgent that hosts the 3DFS server.

  • Enter an optimal value for the number of parallel jobs based on the hardware setup of the server, client, and network. For more information, go to the -j command line argument in the PostgreSQL documentation website.

• Create the necessary roles and tablespaces in the destination. The pg_dump utility that is used to perform DumpBased backup operation does not back up roles and tablespaces by default. To back up this information using the pg_dumpall utility, keep this option enabled during backup - enable backups for global objects in the database.

• If the object selected to restore depends on an extension in the source, ensure that the extension exists in the destination database.

Restore Databases and Tables

1. From the CommCell Browser, navigate to Client > PostgreSQL > Instance.

2. To perform restore at database level, do the following:

   1. Right-click the DumpBasedBackupSet that contains the tables you want to restore and then click All Tasks > Browse and Restore.

      The Browse and Restore Options dialog box appears.

   2. Identify the data that you want to restore, and then click View Content.

3. To perform restore at table level, do the following:

   1. From the Browse window, select databases to restore, and then click Recover All Selected.

      The PostgreSQL Database Restore Options dialog box appears.

   2. Select Restore out of place, and then click Next.

   3. From the Destination Client list, select the name of the destination client.

   4. To restore this PostgreSQL instance to a different instance on the same client, from the Destination Server list, select the name of the destination instance.

4. In the Number of streams box, enter the number of data streams to use in the restore operation.

5. In the Number of parallel jobs box, enter the number of parallel jobs to restore the tables and database on Linux and Windows servers.

6. To perform staged restore of database, do the following:

   1. Select Staged restore.

   2. In the Staging path box, enter the location to set the staging path to a local directory.

7. To perform stage-free restore of database, do the following:

   1. Select Stage-free restore.

   2. For Windows servers, in the Username and Password boxes, enter the credentials of the user that can access the MA mount folder.

      Note

      If the MediaAgent is a Linux server, verify the requirements for 3DFS.

8. To stage the global objects file, do the following:

   1. Select the Stage global objects file check box.

   2. To set the staging path for global objects file, in the Staging Path box, enter the location.

9. Click OK.

Restore Tables to an Auxiliary Database

You can restore tables to a database with the same name or to an auxiliary database. An auxiliary database may or may not exist in the server.

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.

   The Browse and Restore Options dialog box appears.

3. Select the Table Level Browse check box.

4. Click View Content.

5. Select the tables that you want to restore, and then click Recover All Selected.

   The PostgreSQL Database Restore Options dialog box is displayed.

6. Select Restore out of place. Click Next.

7. From the Destination Client list, select the name of the destination client.

8. To restore objects to a different instance on the same client, from the Destination Server list, select the name of the destination instance.

9. In the Number of streams box, enter the number of data streams to use in the restore operation.

10. In the Number of parallel jobs box, enter the number of parallel jobs to restore the database on Linux and Windows servers.

11. To perform staged restore of database, do the following:

    1. Select Staged restore.

    2. In the Staging path box, enter the location to set the staging path to a local directory.

12. To perform stage-free restore of database, do the following:

    1. Select Stage-free restore.

    2. For Windows servers, in the Username and Password boxes, enter the credentials of the user that can access the MA mount folder.

       Note

       If the MediaAgent is a Linux server, verify the requirements for 3DFS.

13. To stage the global objects file, do the following:

    1. Select the Stage global objects file check box.

    2. To set the staging path for global objects file, in the Staging Path box, enter the location.

14. To restore to an auxiliary database, on the Table Restore Options tab, select the Specify Destination Database for Table Import check box. For each database, in the Target Database column, enter the auxiliary database name where you want to restore table data.

15. Click OK.