Pivotal Knowledge Base

Follow

How to restore a table to a different Database or Schema

Goal

You want to restore a specific table to a different Database or Schema.

Solution

To restore a specific table to a different database or schema, there is no specific restore parameter in the gpdbrestore utility to do this. You will need to edit the gp_cdatabase_* file in the the backup directory on the Master i.e

$MASTER_DATA_DIRECTORY/db_dumps/20140430/gp_cdatabase_1_1_20140430115220

In this file you need to change the database Name to the new database, i.e. original line entry after the backup

CREATE DATABASE supporttest WITH TEMPLATE = template0 ENCODING = 'UTF8' OWNER = gpadmin;

where supporttest is the DB that you have backed up. You need to change this to

CREATE DATABASE NewDBName WITH TEMPLATE = template0 ENCODING = 'UTF8' OWNER = gpadmin;

where NewDBName is the database where you want to restore the table to.

The steps that you need to follow are:

  1. You will need to create the DB first and the table that you are restoring.
  2. Backup the gp_cdatabase_* file in the db_dump directory.
  3. Edit the gp_cdatabase_* file to change the database name to the destination database.
  4. Run the restore command to restore the specific table.
gpdbrestore -a -t 20140430115220 -T public.support_test

where

  • -t is the timestamp of the backup file
  • -T is the specific table that you want to restore

If you then want to copy the data to a different Schema or another Database, you will need to copy the data out to a file and then copy the data back to the schema or database.

NOTE: Once you are completed, change the Database name parameter in the gp_cdatabase_* file back to its original so that your future restores are not affected.

Comments

Powered by Zendesk