Restoring PostgreSQL Tables and Other Objects

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 You Begin

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

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

• During a restore operation, job results directory is used as the location to create a FIFO file (for Linux) and pipe (for Windows) to read data and send to pg_restore. If job results directory does not have sufficient space, change the job results directory path. For more information, see Changing the Path of the Job Results Directory.

• Restores create the auxiliary database if it is not present in the server. However, if you are restoring Enterprise database to PostgreSQL Community edition, create the destination database manually before starting the restore operation.

• 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 PostgreSQL server before performing the table restore.

• 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 pg_restore page 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 Tables

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 and click View Content.

4. Select the tables you want to restore and click Recover All Selected.

   The PostgreSQL Table Level Restore Options dialog box appears.

5. Select Restore in place, and then click Next.

6. Click OK.

Restore Table Data 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.

   The Browse and Restore Options dialog box appears.

3. Select the Table Level Browse check box and click View Content.

4. Select the tables you want to restore and click Recover All Selected.

   The PostgreSQL Table Level Restore Options dialog box appears.

5. Select Restore in place. Click Next.

   The PostgreSQL Table Level Restore Options dialog box appears.

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

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

8. On Linux and Windows servers, to perform staged restore of table data, 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.

9. To perform stage-free restore of table data, 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.

10. In the Table Import Options section, do the following:

    1. select the Skip Import check box.

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

11. 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 and click View Content.

4. Select the tables you want to restore and click Recover All Selected.

   The PostgreSQL Table Level Restore Options dialog box is displayed.

5. Select Restore in place. Click Next.

   The PostgreSQL Table Level Restore Options dialog box appears.

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

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

8. To perform staged restore of table data, 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.

9. To perform stage-free restore of table data, 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.

10. In the Table Import Options section, do the following:

    1. Select the Skip Import check box.

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

11. The Staging Path box is visible when you select stage-free restore.

12. 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 and click View Content.

4. Select the databases you want to restore and click Recover All Selected.

   The PostgreSQL Table Level Restore Options dialog box appears.

5. Select Restore in place, and then click Next.

   The PostgreSQL Table Level Restore Options dialog box appears.

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

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

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

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

10. In the Table Import Options section, do the following:

    1. select the Skip Import check box.

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

11. Click OK.