Greenplum: Restore backups with a different name

Products

• VMware Tanzu Data Suite

Issue/Introduction

This articles provides the steps to restore a partitioned table to a different name by re-directing to a new schema.

 

Resolution

 

1. Confirm that a table exists with partitions as shown in the example below.

SELECT schemaname, tablename FROM pg_tables WHERE schemaname =<old_schema>;

For example:

postgres=# select schemaname, tablename from pg_tables where schemaname='demo_src';

 schemaname |     tablename

------------+--------------------

 demo_src   | sales_data

 demo_src   | sales_data_1_prt_1

 demo_src   | sales_data_1_prt_2

(3 rows)

2.  Take a backup  of the table created in step 1 using gpbackup.

  gpbackup --dbname <your_db>  --include-table <schema.table>

 

[gpadmin@gpdb_04 greenplum-db-6.31.0]$ gpbackup --dbname postgres --include-table demo_src.sales_data
20251219:14:40:13 gpbackup:gpadmin:gpdb_04:2028779-[INFO]:-Backup Command: [gpbackup --dbname postgres --include-table demo_src.sales_data]
20251219:14:40:13 gpbackup:gpadmin:gpdb_04:2028779-[INFO]:-gpbackup version = 1.32.2
20251219:14:40:13 gpbackup:gpadmin:gpdb_04:2028779-[INFO]:-Greenplum Database Version = 6.31.0 build commit:c6c750a88aaa0907fcd9e3c08cbec057d70ef806
20251219:14:40:13 gpbackup:gpadmin:gpdb_04:2028779-[INFO]:-Starting backup of database postgres
20251219:14:40:14 gpbackup:gpadmin:gpdb_04:2028779-[INFO]:-Backup Timestamp = 20251219144013
20251219:14:40:14 gpbackup:gpadmin:gpdb_04:2028779-[INFO]:-Backup Database = postgres
20251219:14:40:14 gpbackup:gpadmin:gpdb_04:2028779-[INFO]:-Gathering table state information
20251219:14:40:14 gpbackup:gpadmin:gpdb_04:2028779-[INFO]:-Acquiring ACCESS SHARE locks on tables

 

3. Create a new schema to restore this backup to.

CREATE SCHEMA <new_schema>;

CREATE SCHEMA demo_restore;

4. Run the restore using the gprestore command shown below. 

gprestore --timestamp <YYYYMMDDHHMMSS> --include-table <schema.table> --redirect-schema <new_schema>

[gpadmin@gpdb_04 greenplum-db-6.31.0]$ gprestore --timestamp 20251219144013 \

>   --include-table demo_src.sales_data \

>   --redirect-schema demo_restore

 

5. Rename table.

 

ALTER TABLE <new_schema>.<table_name> rename to <new_table_name>;

 

postgres=# ALTER TABLE demo_restore.sales_data rename to sales_data_renamed;



  schemaname  |         tablename

--------------+----------------------------

 demo_src     | sales_data

 demo_src     | sales_data_1_prt_1

 demo_src     | sales_data_1_prt_2

 demo_restore | sales_data_renamed

 demo_restore | sales_data_renamed_1_prt_1

 demo_restore | sales_data_renamed_1_prt_2

 

6. Revert to old schema

# ALTER TABLE <new_schema>.<new_table_name>  SET SCHEMA <old_schema>;