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

• Create roles and tablespaces in the destination before attempting database or table level restore.

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

Restore Tables

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

2. Right-click the <Backup Set> 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.

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

   The PostgreSQL Table Level Restore Options dialog box appears.

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

7. 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 <Backup Set> 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.

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

   The PostgreSQL Table Level Restore Options dialog box appears.

6. Select the Skip Import 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.

7. Type or click Browse to select the staging path to where the table data is to be exported.

8. 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. To restore to an auxiliary database, on the Target Restore Options tab, select the Specify Destination Database for Table Import check box.

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

8. 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. To restore to an auxiliary database, on the Target Restore Options tab, select the Specify Destination Database for Table Import check box.

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

8. Click OK.