How to make a copy of a schema in the same database in Greenplum?
Post date: Jan 09, 2015 12:22:56 AM
Many times we need to make a copy of the schema to preserve the test data. In oracle we use remap_schema option to make a copy.
Since Greenplum database restore command (gpdbrestore) does not give you option to remap the schema so here is the trick that you can use. Lets make a copy of schema sachi2014 to sachi2015 on database sachi.
1. Backup sachi2014 schema.
2. make a checksum report (no of tables etc in the sachi2014 schema).
3. Rename schema sachi2014 to sachi2015.
4. restore sachi2014 schema.
5. run the checksum report to validate the schema objects and data.
[gpadmin@sachi db_dumps]$ gpcrondump -x sachi -s sachi2014
20150108:18:46:31:017728 gpcrondump:sachi:gpadmin-[INFO]:-Starting gpcrondump with args: -x sachi -s sachi2014
20150108:18:46:31:017728 gpcrondump:sachi:gpadmin-[INFO]:----------------------------------------------------
20150108:18:46:31:017728 gpcrondump:sachi:gpadmin-[INFO]:-Master Greenplum Instance dump parameters
20150108:18:46:31:017728 gpcrondump:sachi:gpadmin-[INFO]:----------------------------------------------------
20150108:18:46:31:017728 gpcrondump:sachi:gpadmin-[INFO]:-Dump type = Single database
20150108:18:46:31:017728 gpcrondump:sachi:gpadmin-[INFO]:-Database to be dumped = sachi
20150108:18:46:31:017728 gpcrondump:sachi:gpadmin-[INFO]:-Schema to be dumped = sachi2014
20150108:18:46:31:017728 gpcrondump:sachi:gpadmin-[INFO]:-Master port = 5432
20150108:18:46:31:017728 gpcrondump:sachi:gpadmin-[INFO]:-Master data directory = /home/gpmaster/gpsne-1
20150108:18:46:31:017728 gpcrondump:sachi:gpadmin-[INFO]:-Run post dump program = Off
20150108:18:46:31:017728 gpcrondump:sachi:gpadmin-[INFO]:-Rollback dumps = Off
20150108:18:46:31:017728 gpcrondump:sachi:gpadmin-[INFO]:-Dump file compression = On
20150108:18:46:31:017728 gpcrondump:sachi:gpadmin-[INFO]:-Clear old dump files = Off
20150108:18:46:31:017728 gpcrondump:sachi:gpadmin-[INFO]:-Update history table = Off
20150108:18:46:31:017728 gpcrondump:sachi:gpadmin-[INFO]:-Secure config files = Off
20150108:18:46:31:017728 gpcrondump:sachi:gpadmin-[INFO]:-Dump global objects = Off
20150108:18:46:31:017728 gpcrondump:sachi:gpadmin-[INFO]:-Vacuum mode type = Off
20150108:18:46:31:017728 gpcrondump:sachi:gpadmin-[INFO]:-Ensuring remaining free disk > 10
Continue with Greenplum dump Yy|Nn (default=N):
> y
20150108:18:46:35:017728 gpcrondump:sachi:gpadmin-[INFO]:-Directory /home/gpmaster/gpsne-1/db_dumps/20150108 exists
20150108:18:46:35:017728 gpcrondump:sachi:gpadmin-[INFO]:-Checked /home/gpmaster/gpsne-1 on master
20150108:18:46:35:017728 gpcrondump:sachi:gpadmin-[INFO]:-Configuring for single database dump
20150108:18:46:36:017728 gpcrondump:sachi:gpadmin-[INFO]:-Adding compression parameter
20150108:18:46:36:017728 gpcrondump:sachi:gpadmin-[INFO]:-Adding schema name sachi2014
20150108:18:46:36:017728 gpcrondump:sachi:gpadmin-[INFO]:-Dump command line gp_dump -p 5432 -U gpadmin --gp-d=db_dumps/20150108 --gp-r=/home/gpmaster/gpsne-1/db_dumps/20150108 --gp-s=p --gp-c -n "\"sachi2014\"" sachi
20150108:18:46:36:017728 gpcrondump:sachi:gpadmin-[INFO]:-Starting dump process
20150108:18:46:48:017728 gpcrondump:sachi:gpadmin-[INFO]:-Dump process returned exit code 0
20150108:18:46:48:017728 gpcrondump:sachi:gpadmin-[INFO]:-Timestamp key = 20150108184636
20150108:18:46:48:017728 gpcrondump:sachi:gpadmin-[INFO]:-Checked master status file and master dump file.
20150108:18:46:48:017728 gpcrondump:sachi:gpadmin-[INFO]:-Dump status report
20150108:18:46:48:017728 gpcrondump:sachi:gpadmin-[INFO]:----------------------------------------------------
20150108:18:46:48:017728 gpcrondump:sachi:gpadmin-[INFO]:-Target database = sachi
20150108:18:46:48:017728 gpcrondump:sachi:gpadmin-[INFO]:-Dump subdirectory = 20150108
20150108:18:46:48:017728 gpcrondump:sachi:gpadmin-[INFO]:-Clear old dump directories = Off
20150108:18:46:48:017728 gpcrondump:sachi:gpadmin-[INFO]:-Dump start time = 18:46:36
20150108:18:46:48:017728 gpcrondump:sachi:gpadmin-[INFO]:-Dump end time = 18:46:48
20150108:18:46:48:017728 gpcrondump:sachi:gpadmin-[INFO]:-Status = COMPLETED
20150108:18:46:48:017728 gpcrondump:sachi:gpadmin-[INFO]:-Dump key = 20150108184636
20150108:18:46:48:017728 gpcrondump:sachi:gpadmin-[INFO]:-Dump file compression = On
20150108:18:46:48:017728 gpcrondump:sachi:gpadmin-[INFO]:-Vacuum mode type = Off
20150108:18:46:48:017728 gpcrondump:sachi:gpadmin-[INFO]:-Exit code zero, no warnings generated
20150108:18:46:48:017728 gpcrondump:sachi:gpadmin-[INFO]:----------------------------------------------------
20150108:18:46:48:017728 gpcrondump:sachi:gpadmin-[WARNING]:-Found neither /usr/local/greenplum-db/./bin/mail_contacts nor /home/gpadmin/mail_contacts
20150108:18:46:48:017728 gpcrondump:sachi:gpadmin-[WARNING]:-Unable to send dump email notification
20150108:18:46:48:017728 gpcrondump:sachi:gpadmin-[INFO]:-To enable email notification, create /usr/local/greenplum-db/./bin/mail_contacts or /home/gpadmin/mail_contacts containing required email addresses
[gpadmin@sachi db_dumps]$ psql -d sachi
psql (8.2.15)
Type "help" for help.
sachi=# alter schema sachi2014 rename to sachi2015;
ALTER SCHEMA
sachi=# \q
[gpadmin@sachi db_dumps]$ gpdbrestore -s sachi
20150108:18:48:09:018102 gpdbrestore:sachi:gpadmin-[INFO]:-Starting gpdbrestore with args: -s sachi
20150108:18:48:09:018102 gpdbrestore:sachi:gpadmin-[INFO]:-Scanning Master host for latest dump file set for database sachi
20150108:18:48:09:018102 gpdbrestore:sachi:gpadmin-[INFO]:-Located dump file gp_cdatabase_1_1_20150108183946 for database sachi, adding to list
20150108:18:48:09:018102 gpdbrestore:sachi:gpadmin-[INFO]:-Located dump file gp_cdatabase_1_1_20150108184636 for database sachi, adding to list
20150108:18:48:09:018102 gpdbrestore:sachi:gpadmin-[INFO]:-Identified latest dump timestamp for sachi as 20150108184636
20150108:18:48:09:018102 gpdbrestore:sachi:gpadmin-[INFO]:-------------------------------------------
20150108:18:48:09:018102 gpdbrestore:sachi:gpadmin-[INFO]:-Greenplum database restore parameters
20150108:18:48:09:018102 gpdbrestore:sachi:gpadmin-[INFO]:-------------------------------------------
20150108:18:48:09:018102 gpdbrestore:sachi:gpadmin-[INFO]:-Restore type = Full Database
20150108:18:48:09:018102 gpdbrestore:sachi:gpadmin-[INFO]:-Database to be restored = sachi
20150108:18:48:09:018102 gpdbrestore:sachi:gpadmin-[INFO]:-Drop and re-create db = Off
20150108:18:48:09:018102 gpdbrestore:sachi:gpadmin-[INFO]:-Restore method = Search for latest
20150108:18:48:09:018102 gpdbrestore:sachi:gpadmin-[INFO]:-Restore timestamp = 20150108184636
20150108:18:48:09:018102 gpdbrestore:sachi:gpadmin-[INFO]:-Restore compressed dump = On
20150108:18:48:09:018102 gpdbrestore:sachi:gpadmin-[INFO]:-Restore global objects = Off
20150108:18:48:09:018102 gpdbrestore:sachi:gpadmin-[INFO]:-Array fault tolerance = n
Continue with Greenplum restore Yy|Nn (default=N):
> y
20150108:18:48:28:018102 gpdbrestore:sachi:gpadmin-[INFO]:-gp_restore -i -h sachi -p 5432 -U gpadmin --gp-d=db_dumps/20150108 --gp-i --gp-k=20150108184636 --gp-r=db_dumps/20150108 --gp-l=p --gp-c -d sachi
20150108:18:51:41:018102 gpdbrestore:sachi:gpadmin-[INFO]:-Commencing analyze of sachi database, please wait
20150108:18:52:14:018102 gpdbrestore:sachi:gpadmin-[INFO]:-Analyze of sachi completed without error
[gpadmin@sachi db_dumps]$