GPDBRESTORE Example 1- backup set of tables and restore it to a new database

posted Jan 11, 2015, 4:22 PM by Sachchida Ojha   [ updated May 20, 2016, 5:49 PM ]
a) Backup list of tables using gpcrondump and a specified location

[gpadmin@sachi ~]$ gpcrondump  -c --table-file="tablist" -u /disk4/backup
20150111:17:23:47:022685 gpcrondump:sachi:gpadmin-[INFO]:-Starting gpcrondump with args: -c --table-file=tablist -u /disk4/backup
20150111:17:23:47:022685 gpcrondump:sachi:gpadmin-[INFO]:-Setting dump database to value of $PGDATABASE which is sachi
20150111:17:23:47:022685 gpcrondump:sachi:gpadmin-[INFO]:----------------------------------------------------
20150111:17:23:47:022685 gpcrondump:sachi:gpadmin-[INFO]:-Master Greenplum Instance dump parameters
20150111:17:23:47:022685 gpcrondump:sachi:gpadmin-[INFO]:----------------------------------------------------
20150111:17:23:47:022685 gpcrondump:sachi:gpadmin-[INFO]:-Dump type                            = Single database, specific table
20150111:17:23:47:022685 gpcrondump:sachi:gpadmin-[INFO]:----------------------------------------------------
20150111:17:23:47:022685 gpcrondump:sachi:gpadmin-[INFO]:-Table file name                      = tablist
20150111:17:23:47:022685 gpcrondump:sachi:gpadmin-[INFO]:----------------------------------------------------
20150111:17:23:47:022685 gpcrondump:sachi:gpadmin-[INFO]:-Database to be dumped                = sachi
20150111:17:23:47:022685 gpcrondump:sachi:gpadmin-[INFO]:-Dump directory                       = /disk4/backup
20150111:17:23:47:022685 gpcrondump:sachi:gpadmin-[INFO]:-Master port                          = 5432
20150111:17:23:47:022685 gpcrondump:sachi:gpadmin-[INFO]:-Master data directory                = /home/gpmaster/gpsne-1
20150111:17:23:47:022685 gpcrondump:sachi:gpadmin-[INFO]:-Run post dump program                = Off
20150111:17:23:47:022685 gpcrondump:sachi:gpadmin-[INFO]:-Rollback dumps                       = Off
20150111:17:23:47:022685 gpcrondump:sachi:gpadmin-[INFO]:-Dump file compression                = On
20150111:17:23:47:022685 gpcrondump:sachi:gpadmin-[INFO]:-Clear old dump files                 = On
20150111:17:23:47:022685 gpcrondump:sachi:gpadmin-[INFO]:-Update history table                 = Off
20150111:17:23:47:022685 gpcrondump:sachi:gpadmin-[INFO]:-Secure config files                  = Off
20150111:17:23:47:022685 gpcrondump:sachi:gpadmin-[INFO]:-Dump global objects                  = Off
20150111:17:23:47:022685 gpcrondump:sachi:gpadmin-[INFO]:-Vacuum mode type                     = Off
20150111:17:23:47:022685 gpcrondump:sachi:gpadmin-[INFO]:-Ensuring remaining free disk         > 10

Continue with Greenplum dump Yy|Nn (default=N):
> y
20150111:17:23:49:022685 gpcrondump:sachi:gpadmin-[INFO]:-Directory /disk4/backup/db_dumps/20150111 exists
20150111:17:23:49:022685 gpcrondump:sachi:gpadmin-[INFO]:-Checked /disk4/backup on master
20150111:17:23:49:022685 gpcrondump:sachi:gpadmin-[INFO]:-Directory /home/gpmaster/gpsne-1/db_dumps/20150111 exists
20150111:17:23:49:022685 gpcrondump:sachi:gpadmin-[INFO]:-Checked /home/gpmaster/gpsne-1 on master
20150111:17:23:50:022685 gpcrondump:sachi:gpadmin-[INFO]:-Configuring for single-database, include-table dump
20150111:17:23:50:022685 gpcrondump:sachi:gpadmin-[INFO]:-Located table public.DIM_TAXPAYER in sachi database
20150111:17:23:50:022685 gpcrondump:sachi:gpadmin-[INFO]:-Located table public.Foo in sachi database
20150111:17:23:50:022685 gpcrondump:sachi:gpadmin-[INFO]:-Located table public.a in sachi database
20150111:17:23:50:022685 gpcrondump:sachi:gpadmin-[INFO]:-Located table public.abc in sachi database
20150111:17:23:50:022685 gpcrondump:sachi:gpadmin-[INFO]:-Located table public.abctest in sachi database
20150111:17:23:51:022685 gpcrondump:sachi:gpadmin-[INFO]:-Located table public.dynamic_analyze_config in sachi database
20150111:17:23:51:022685 gpcrondump:sachi:gpadmin-[INFO]:-Located table public.h in sachi database
20150111:17:23:51:022685 gpcrondump:sachi:gpadmin-[INFO]:-Located table public.i in sachi database
20150111:17:23:51:022685 gpcrondump:sachi:gpadmin-[INFO]:-Located table public.sales in sachi database
20150111:17:23:51:022685 gpcrondump:sachi:gpadmin-[INFO]:-Adding compression parameter
20150111:17:23:51:022685 gpcrondump:sachi:gpadmin-[INFO]:-Dump command line gp_dump -p 5432 -U gpadmin --gp-d=/disk4/backup/db_dumps/20150111 --gp-r=/home/gpmaster/gpsne-1/db_dumps/20150111 --gp-s=p --gp-c sachi --table-file=tablist
20150111:17:23:51:022685 gpcrondump:sachi:gpadmin-[INFO]:-Starting dump process
20150111:17:24:02:022685 gpcrondump:sachi:gpadmin-[INFO]:-Dump process returned exit code 0
20150111:17:24:02:022685 gpcrondump:sachi:gpadmin-[INFO]:-Timestamp key = 20150111172357
20150111:17:24:02:022685 gpcrondump:sachi:gpadmin-[INFO]:-Checked master status file and master dump file.
20150111:17:24:02:022685 gpcrondump:sachi:gpadmin-[INFO]:-Preparing to remove dump 20150109 from all hosts
20150111:17:24:03:022685 gpcrondump:sachi:gpadmin-[WARNING]:-Error encountered during deletion of /disk1/gpdata1/gpsne0/db_dumps/20150109 on sachi
20150111:17:24:03:022685 gpcrondump:sachi:gpadmin-[WARNING]:-Error encountered during deletion of /disk2/gpdata2/gpsne1/db_dumps/20150109 on sachi
20150111:17:24:03:022685 gpcrondump:sachi:gpadmin-[INFO]:-Dump status report
20150111:17:24:03:022685 gpcrondump:sachi:gpadmin-[INFO]:----------------------------------------------------
20150111:17:24:03:022685 gpcrondump:sachi:gpadmin-[INFO]:-Target database                          = sachi
20150111:17:24:03:022685 gpcrondump:sachi:gpadmin-[INFO]:-Dump subdirectory                        = 20150111
20150111:17:24:03:022685 gpcrondump:sachi:gpadmin-[INFO]:-Clear old dump directories               = On
20150111:17:24:03:022685 gpcrondump:sachi:gpadmin-[INFO]:-Backup set deleted                       = 20150109
20150111:17:24:03:022685 gpcrondump:sachi:gpadmin-[INFO]:-Dump start time                          = 17:23:51
20150111:17:24:03:022685 gpcrondump:sachi:gpadmin-[INFO]:-Dump end time                            = 17:24:02
20150111:17:24:03:022685 gpcrondump:sachi:gpadmin-[INFO]:-Status                                   = COMPLETED
20150111:17:24:03:022685 gpcrondump:sachi:gpadmin-[INFO]:-Dump key                                 = 20150111172357
20150111:17:24:03:022685 gpcrondump:sachi:gpadmin-[INFO]:-Dump file compression                    = On
20150111:17:24:03:022685 gpcrondump:sachi:gpadmin-[INFO]:-Vacuum mode type                         = Off
20150111:17:24:03:022685 gpcrondump:sachi:gpadmin-[INFO]:-Exit code zero, no warnings generated
20150111:17:24:03:022685 gpcrondump:sachi:gpadmin-[INFO]:----------------------------------------------------
20150111:17:24:03:022685 gpcrondump:sachi:gpadmin-[WARNING]:-Found neither /usr/local/greenplum-db/./bin/mail_contacts nor /home/gpadmin/mail_contacts
20150111:17:24:03:022685 gpcrondump:sachi:gpadmin-[WARNING]:-Unable to send dump email notification
20150111:17:24:03:022685 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

b) Restore tables using gpdbrestore from a specified location and create a new database

[gpadmin@sachi ~]$ gpdbrestore -R sachi:/disk4/backup/db_dumps/20150111
20150111:17:31:13:023833 gpdbrestore:sachi:gpadmin-[INFO]:-Starting gpdbrestore with args: -R sachi:/disk4/backup/db_dumps/20150111
20150111:17:31:14:023833 gpdbrestore:sachi:gpadmin-[ERROR]:-gpdbrestore error: Database test1 does not exist and -e option not supplied

Solution:
1. Open gp_cdatabase_1_1_20150111172357 and change the database name to new database test1.

[gpadmin@sachi 20150111]$ cat gp_cdatabase_1_1_20150111172357
--
-- Database creation
--
CREATE DATABASE test1 WITH TEMPLATE = template0 ENCODING = 'UTF8' OWNER = gpadmin;
[gpadmin@sachi 20150111]$ 

2. use -e option to create the database.

Now run the restore
[gpadmin@sachi ~]$ gpdbrestore -e test1 -R sachi:/disk4/backup/db_dumps/20150111
20150111:17:32:57:023990 gpdbrestore:sachi:gpadmin-[INFO]:-Starting gpdbrestore with args: -e test1 -R sachi:/disk4/backup/db_dumps/20150111
20150111:17:32:58:023990 gpdbrestore:sachi:gpadmin-[INFO]:-------------------------------------------
20150111:17:32:58:023990 gpdbrestore:sachi:gpadmin-[INFO]:-Greenplum database restore parameters
20150111:17:32:58:023990 gpdbrestore:sachi:gpadmin-[INFO]:-------------------------------------------
20150111:17:32:58:023990 gpdbrestore:sachi:gpadmin-[INFO]:-Restore type               = Full Database
20150111:17:32:58:023990 gpdbrestore:sachi:gpadmin-[INFO]:-Database to be restored    = test1
20150111:17:32:58:023990 gpdbrestore:sachi:gpadmin-[INFO]:-Drop and re-create db      = On
20150111:17:32:58:023990 gpdbrestore:sachi:gpadmin-[INFO]:-Restore method             = Remote host
20150111:17:32:58:023990 gpdbrestore:sachi:gpadmin-[INFO]:-Recovery hostname          = sachi
20150111:17:32:58:023990 gpdbrestore:sachi:gpadmin-[INFO]:-Remote recovery path       = /disk4/backup/db_dumps/20150111
20150111:17:32:58:023990 gpdbrestore:sachi:gpadmin-[INFO]:-Restore timestamp          = 20150111172357
20150111:17:32:58:023990 gpdbrestore:sachi:gpadmin-[INFO]:-Restore compressed dump    = On
20150111:17:32:58:023990 gpdbrestore:sachi:gpadmin-[INFO]:-Restore global objects     = Off
20150111:17:32:58:023990 gpdbrestore:sachi:gpadmin-[INFO]:-Array fault tolerance      = n

Continue with Greenplum restore Yy|Nn (default=N):
> y
20150111:17:33:11:023990 gpdbrestore:sachi:gpadmin-[INFO]:-Commencing remote database dump file recovery process, please wait...
20150111:17:33:11:023990 gpdbrestore:sachi:gpadmin-[INFO]:-Commencing remote copy from sachi to sachi:/home/gpmaster/gpsne-1/db_dumps/20150111
20150111:17:33:11:023990 gpdbrestore:sachi:gpadmin-[INFO]:-Creating directory /disk1/gpdata1/gpsne0/db_dumps/20150111 on sachi
20150111:17:33:11:023990 gpdbrestore:sachi:gpadmin-[INFO]:-Commencing remote copy from sachi to sachi:/disk1/gpdata1/gpsne0/db_dumps/20150111
20150111:17:33:11:023990 gpdbrestore:sachi:gpadmin-[INFO]:-Creating directory /disk2/gpdata2/gpsne1/db_dumps/20150111 on sachi
20150111:17:33:12:023990 gpdbrestore:sachi:gpadmin-[INFO]:-Commencing remote copy from sachi to sachi:/disk2/gpdata2/gpsne1/db_dumps/20150111
20150111:17:33:13:023990 gpdbrestore:sachi:gpadmin-[INFO]:-Invoking /home/gpmaster/gpsne-1/db_dumps/20150111/gp_cdatabase_1_1_20150111172357
20150111:17:33:34:023990 gpdbrestore:sachi:gpadmin-[INFO]:-gp_restore -i -h sachi -p 5432 -U gpadmin --gp-d=db_dumps/20150111 --gp-i --gp-k=20150111172357 --gp-r=db_dumps/20150111 --gp-l=p --gp-c -d test1
20150111:17:34:11:023990 gpdbrestore:sachi:gpadmin-[INFO]:-Commencing analyze of test1 database, please wait
20150111:17:34:17:023990 gpdbrestore:sachi:gpadmin-[INFO]:-Analyze of test1 completed without error
[gpadmin@sachi ~]$ 



Comments