Pivotal Greenplum Database (GPDB)
This article describes how to restore a specific table to a different Database or Schema.
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
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:
- You will need to create the DB first and the table that you are restoring.
- Backup the gp_cdatabase_* file in the db_dump directory.
- Edit the gp_cdatabase_* file to change the database name to the destination database.
- Run the restore command to restore the specific table.
gpdbrestore -a -t 20140430115220 -T public.support_test
- -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.