Restoring PostgreSQL Databases to a Different Client

Prior to restoring the database to a different server on the different client, make sure the operating system on the source and destination clients is the same.

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 dependent 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.

    Note

    For PostgreSQL Versions 8.0 and 8.1, manually drop the database objects from the PgSQL server before performing the table restore.

• 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 you did not have the global objects backed up, to restore the data with the database user provided in the destination instance properties as the owner, create the additional settings bPGRestoreNoACL and bPGRestoreNoOwner before initiating the restore job.

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

• Use this additional setting bRestoreRolesFromGlobalFile to override the restore setting and create roles as specified in the global objects dump file.

• Roles are restored by default if global objects are backed up. Starting from version 11.42.23, global objects are restored by default. If role creation on the destination is not desired, set the additional setting bRestoreRolesFromGlobalFile to False.

• You must only create roles that do not exist.

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. To restore objects to a different client, from the Destination Client list, select the name of the destination client.

   4. To restore objects to a different instance, 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 Table Level Restore Options dialog box appears.

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

7. To restore objects to a different client, from the Destination Client list, select the name of the destination client.

8. To restore objects to a different instance, 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 tables and 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. To stage the dump to the MediaAgent mount folder, in the Username and Password boxes, enter the credentials of the user that can access the MediaAgent location.

       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.

15. For each database, in the Target Database column, enter the auxiliary database name where you want to restore table data.

16. Click OK.

Restore Entire Database to an Auxiliary Database

You can restore an entire database to a different destination database (also known as auxiliary database) in the table level options. The auxiliary database is not dropped if it is already present in 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 databases that you want to restore, and then click Recover All Selected.

   The PostgreSQL Table Level Restore Options dialog box is displayed. It must display the databases are to be restored.

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

7. To restore objects to a different client, from the Destination Client list, select the name of the destination client.

8. To restore objects to a different server, from the Destination Server list, select the name of the destination server.

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

10. 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.

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

    1. Select Stage-free restore.

    2. To stage the dump to the MediaAgent mount folder, in the Username and Password boxes, enter the credentials of the user that can access the MediaAgent location

       Note

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

12. 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.

13. To restore to an auxiliary database, on the Table Restore Options tab, select the Specify Destination Database for Table Import check box.

14. For each database, in the Target Database column, enter the auxiliary database name where you want to restore table data.

15. Click OK.