Backup a table or list of tables in Greenplum using gpcrondump

posted Jan 9, 2015, 2:16 PM by Sachchida Ojha   [ updated May 20, 2016, 5:51 PM ]
gpcrondump allows you to dump a specific table or list of tables.  
Note: By default (if no location option -u is specified) dump files will be created on master and segment server master data directories. If location option is specified, backup files is created in that directory.
[gpadmin@sachi disk4]$ mkdir backup
[gpadmin@sachi disk4]$ cd backup
[gpadmin@sachi backup]$ ls
[gpadmin@sachi backup]$ pwd
/disk4/backup
[gpadmin@sachi backup]$ cd
[gpadmin@sachi ~]$ cat tablist
public.DIM_TAXPAYER
public.Foo
public.a
public.abc
public.abctest
public.dynamic_analyze_config
public.h
public.i
public.sales
[gpadmin@sachi ~]$ gpcrondump --table-file="tablist" -u /disk4/backup
20150109:16:22:18:013929 gpcrondump:sachi:gpadmin-[INFO]:-Starting gpcrondump with args: --table-file=tablist -u /disk4/backup
20150109:16:22:18:013929 gpcrondump:sachi:gpadmin-[INFO]:-Setting dump database to value of $PGDATABASE which is sachi
20150109:16:22:18:013929 gpcrondump:sachi:gpadmin-[INFO]:----------------------------------------------------
20150109:16:22:18:013929 gpcrondump:sachi:gpadmin-[INFO]:-Master Greenplum Instance dump parameters
20150109:16:22:18:013929 gpcrondump:sachi:gpadmin-[INFO]:----------------------------------------------------
20150109:16:22:18:013929 gpcrondump:sachi:gpadmin-[INFO]:-Dump type                            = Single database, specific table
20150109:16:22:18:013929 gpcrondump:sachi:gpadmin-[INFO]:----------------------------------------------------
20150109:16:22:18:013929 gpcrondump:sachi:gpadmin-[INFO]:-Table file name                      = tablist
20150109:16:22:18:013929 gpcrondump:sachi:gpadmin-[INFO]:----------------------------------------------------
20150109:16:22:18:013929 gpcrondump:sachi:gpadmin-[INFO]:-Database to be dumped                = sachi
20150109:16:22:18:013929 gpcrondump:sachi:gpadmin-[INFO]:-Dump directory                       = /disk4/backup
20150109:16:22:18:013929 gpcrondump:sachi:gpadmin-[INFO]:-Master port                          = 5432
20150109:16:22:18:013929 gpcrondump:sachi:gpadmin-[INFO]:-Master data directory                = /home/gpmaster/gpsne-1
20150109:16:22:18:013929 gpcrondump:sachi:gpadmin-[INFO]:-Run post dump program                = Off
20150109:16:22:18:013929 gpcrondump:sachi:gpadmin-[INFO]:-Rollback dumps                       = Off
20150109:16:22:18:013929 gpcrondump:sachi:gpadmin-[INFO]:-Dump file compression                = On
20150109:16:22:18:013929 gpcrondump:sachi:gpadmin-[INFO]:-Clear old dump files                 = Off
20150109:16:22:18:013929 gpcrondump:sachi:gpadmin-[INFO]:-Update history table                 = Off
20150109:16:22:18:013929 gpcrondump:sachi:gpadmin-[INFO]:-Secure config files                  = Off
20150109:16:22:18:013929 gpcrondump:sachi:gpadmin-[INFO]:-Dump global objects                  = Off
20150109:16:22:18:013929 gpcrondump:sachi:gpadmin-[INFO]:-Vacuum mode type                     = Off
20150109:16:22:18:013929 gpcrondump:sachi:gpadmin-[INFO]:-Ensuring remaining free disk         > 10
Continue with Greenplum dump Yy|Nn (default=N):
> y
20150109:16:22:36:013929 gpcrondump:sachi:gpadmin-[INFO]:-Directory /disk4/backup/db_dumps/20150109 not found, will try to create
20150109:16:22:36:013929 gpcrondump:sachi:gpadmin-[INFO]:-Created /disk4/backup/db_dumps/20150109
20150109:16:22:36:013929 gpcrondump:sachi:gpadmin-[INFO]:-Checked /disk4/backup on master
20150109:16:22:36:013929 gpcrondump:sachi:gpadmin-[INFO]:-Directory /home/gpmaster/gpsne-1/db_dumps/20150109 not found, will try to create
20150109:16:22:36:013929 gpcrondump:sachi:gpadmin-[INFO]:-Created /home/gpmaster/gpsne-1/db_dumps/20150109
20150109:16:22:36:013929 gpcrondump:sachi:gpadmin-[INFO]:-Checked /home/gpmaster/gpsne-1 on master
20150109:16:22:37:013929 gpcrondump:sachi:gpadmin-[INFO]:-Configuring for single-database, include-table dump
20150109:16:22:38:013929 gpcrondump:sachi:gpadmin-[INFO]:-Located table public.DIM_TAXPAYER in sachi database
20150109:16:22:38:013929 gpcrondump:sachi:gpadmin-[INFO]:-Located table public.Foo in sachi database
20150109:16:22:38:013929 gpcrondump:sachi:gpadmin-[INFO]:-Located table public.a in sachi database
20150109:16:22:38:013929 gpcrondump:sachi:gpadmin-[INFO]:-Located table public.abc in sachi database
20150109:16:22:38:013929 gpcrondump:sachi:gpadmin-[INFO]:-Located table public.abctest in sachi database
20150109:16:22:38:013929 gpcrondump:sachi:gpadmin-[INFO]:-Located table public.dynamic_analyze_config in sachi database
20150109:16:22:38:013929 gpcrondump:sachi:gpadmin-[INFO]:-Located table public.h in sachi database
20150109:16:22:39:013929 gpcrondump:sachi:gpadmin-[INFO]:-Located table public.i in sachi database
20150109:16:22:39:013929 gpcrondump:sachi:gpadmin-[INFO]:-Located table public.sales in sachi database
20150109:16:22:39:013929 gpcrondump:sachi:gpadmin-[ERROR]:-gpcrondump error: No schema name supplied for table 
This error was reported due to the fact that there is a blank line at the bottom of the tablist file. Once I removed it error was gone.
[gpadmin@sachi ~]$ cat tablist
public.DIM_TAXPAYER
public.Foo
public.a
public.abc
public.abctest
public.dynamic_analyze_config
public.h
public.i
public.sales

[gpadmin@sachi ~]$ vi tablist 
remove the blank line.
[gpadmin@sachi ~]$ gpcrondump  -c --table-file="tablist" -u /disk4/backup
20150109:16:29:19:014787 gpcrondump:sachi:gpadmin-[INFO]:-Starting gpcrondump with args: -c --table-file=tablist -u /disk4/backup
20150109:16:29:19:014787 gpcrondump:sachi:gpadmin-[INFO]:-Setting dump database to value of $PGDATABASE which is sachi
20150109:16:29:19:014787 gpcrondump:sachi:gpadmin-[INFO]:----------------------------------------------------
20150109:16:29:19:014787 gpcrondump:sachi:gpadmin-[INFO]:-Master Greenplum Instance dump parameters
20150109:16:29:19:014787 gpcrondump:sachi:gpadmin-[INFO]:----------------------------------------------------
20150109:16:29:19:014787 gpcrondump:sachi:gpadmin-[INFO]:-Dump type                            = Single database, specific table
20150109:16:29:19:014787 gpcrondump:sachi:gpadmin-[INFO]:----------------------------------------------------
20150109:16:29:19:014787 gpcrondump:sachi:gpadmin-[INFO]:-Table file name                      = tablist
20150109:16:29:19:014787 gpcrondump:sachi:gpadmin-[INFO]:----------------------------------------------------
20150109:16:29:19:014787 gpcrondump:sachi:gpadmin-[INFO]:-Database to be dumped                = sachi
20150109:16:29:19:014787 gpcrondump:sachi:gpadmin-[INFO]:-Dump directory                       = /disk4/backup
20150109:16:29:19:014787 gpcrondump:sachi:gpadmin-[INFO]:-Master port                          = 5432
20150109:16:29:19:014787 gpcrondump:sachi:gpadmin-[INFO]:-Master data directory                = /home/gpmaster/gpsne-1
20150109:16:29:19:014787 gpcrondump:sachi:gpadmin-[INFO]:-Run post dump program                = Off
20150109:16:29:19:014787 gpcrondump:sachi:gpadmin-[INFO]:-Rollback dumps                       = Off
20150109:16:29:19:014787 gpcrondump:sachi:gpadmin-[INFO]:-Dump file compression                = On
20150109:16:29:19:014787 gpcrondump:sachi:gpadmin-[INFO]:-Clear old dump files                 = On
20150109:16:29:19:014787 gpcrondump:sachi:gpadmin-[INFO]:-Update history table                 = Off
20150109:16:29:19:014787 gpcrondump:sachi:gpadmin-[INFO]:-Secure config files                  = Off
20150109:16:29:19:014787 gpcrondump:sachi:gpadmin-[INFO]:-Dump global objects                  = Off
20150109:16:29:19:014787 gpcrondump:sachi:gpadmin-[INFO]:-Vacuum mode type                     = Off
20150109:16:29:19:014787 gpcrondump:sachi:gpadmin-[INFO]:-Ensuring remaining free disk         > 10
Continue with Greenplum dump Yy|Nn (default=N):
> y
20150109:16:29:24:014787 gpcrondump:sachi:gpadmin-[INFO]:-Directory /disk4/backup/db_dumps/20150109 exists
20150109:16:29:24:014787 gpcrondump:sachi:gpadmin-[INFO]:-Checked /disk4/backup on master
20150109:16:29:24:014787 gpcrondump:sachi:gpadmin-[INFO]:-Directory /home/gpmaster/gpsne-1/db_dumps/20150109 exists
20150109:16:29:24:014787 gpcrondump:sachi:gpadmin-[INFO]:-Checked /home/gpmaster/gpsne-1 on master
20150109:16:29:24:014787 gpcrondump:sachi:gpadmin-[INFO]:-Configuring for single-database, include-table dump
20150109:16:29:24:014787 gpcrondump:sachi:gpadmin-[INFO]:-Located table public.DIM_TAXPAYER in sachi database
20150109:16:29:24:014787 gpcrondump:sachi:gpadmin-[INFO]:-Located table public.Foo in sachi database
20150109:16:29:24:014787 gpcrondump:sachi:gpadmin-[INFO]:-Located table public.a in sachi database
20150109:16:29:24:014787 gpcrondump:sachi:gpadmin-[INFO]:-Located table public.abc in sachi database
20150109:16:29:25:014787 gpcrondump:sachi:gpadmin-[INFO]:-Located table public.abctest in sachi database
20150109:16:29:25:014787 gpcrondump:sachi:gpadmin-[INFO]:-Located table public.dynamic_analyze_config in sachi database
20150109:16:29:25:014787 gpcrondump:sachi:gpadmin-[INFO]:-Located table public.h in sachi database
20150109:16:29:25:014787 gpcrondump:sachi:gpadmin-[INFO]:-Located table public.i in sachi database
20150109:16:29:25:014787 gpcrondump:sachi:gpadmin-[INFO]:-Located table public.sales in sachi database
20150109:16:29:26:014787 gpcrondump:sachi:gpadmin-[INFO]:-Adding compression parameter
20150109:16:29:26:014787 gpcrondump:sachi:gpadmin-[INFO]:-Dump command line gp_dump -p 5432 -U gpadmin --gp-d=/disk4/backup/db_dumps/20150109 --gp-r=/home/gpmaster/gpsne-1/db_dumps/20150109 --gp-s=p --gp-c sachi --table-file=tablist
20150109:16:29:26:014787 gpcrondump:sachi:gpadmin-[INFO]:-Starting dump process
20150109:16:29:37:014787 gpcrondump:sachi:gpadmin-[INFO]:-Dump process returned exit code 0
20150109:16:29:37:014787 gpcrondump:sachi:gpadmin-[INFO]:-Timestamp key = 20150109162932
20150109:16:29:37:014787 gpcrondump:sachi:gpadmin-[INFO]:-Checked master status file and master dump file.
20150109:16:29:37:014787 gpcrondump:sachi:gpadmin-[INFO]:-Preparing to remove dump 20150108 from all hosts
20150109:16:29:38:014787 gpcrondump:sachi:gpadmin-[INFO]:-Dump status report
20150109:16:29:38:014787 gpcrondump:sachi:gpadmin-[INFO]:----------------------------------------------------
20150109:16:29:38:014787 gpcrondump:sachi:gpadmin-[INFO]:-Target database                          = sachi
20150109:16:29:38:014787 gpcrondump:sachi:gpadmin-[INFO]:-Dump subdirectory                        = 20150109
20150109:16:29:38:014787 gpcrondump:sachi:gpadmin-[INFO]:-Clear old dump directories               = On
20150109:16:29:38:014787 gpcrondump:sachi:gpadmin-[INFO]:-Backup set deleted                       = 20150108
20150109:16:29:38:014787 gpcrondump:sachi:gpadmin-[INFO]:-Dump start time                          = 16:29:26
20150109:16:29:38:014787 gpcrondump:sachi:gpadmin-[INFO]:-Dump end time                            = 16:29:37
20150109:16:29:38:014787 gpcrondump:sachi:gpadmin-[INFO]:-Status                                   = COMPLETED
20150109:16:29:38:014787 gpcrondump:sachi:gpadmin-[INFO]:-Dump key                                 = 20150109162932
20150109:16:29:38:014787 gpcrondump:sachi:gpadmin-[INFO]:-Dump file compression                    = On
20150109:16:29:38:014787 gpcrondump:sachi:gpadmin-[INFO]:-Vacuum mode type                         = Off
20150109:16:29:38:014787 gpcrondump:sachi:gpadmin-[INFO]:-Exit code zero, no warnings generated
20150109:16:29:38:014787 gpcrondump:sachi:gpadmin-[INFO]:----------------------------------------------------
20150109:16:29:38:014787 gpcrondump:sachi:gpadmin-[WARNING]:-Found neither /usr/local/greenplum-db/./bin/mail_contacts nor /home/gpadmin/mail_contacts
20150109:16:29:38:014787 gpcrondump:sachi:gpadmin-[WARNING]:-Unable to send dump email notification
20150109:16:29:38:014787 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 ~]$ 
[gpadmin@sachi disk4]$ cd backup
[gpadmin@sachi backup]$ ls
db_dumps
[gpadmin@sachi backup]$ cd db_dumps/
[gpadmin@sachi db_dumps]$ ls
20150109
[gpadmin@sachi db_dumps]$ cd 20150109/
[gpadmin@sachi 20150109]$ ls
[gpadmin@sachi 20150109]$ cd ..
[gpadmin@sachi db_dumps]$ ls
20150109
[gpadmin@sachi db_dumps]$ cd 20150109/
[gpadmin@sachi 20150109]$ ls
gp_cdatabase_1_1_20150109162932  gp_dump_0_3_20150109162932.gz  gp_dump_1_1_20150109162932_post_data.gz  gp_dump_status_0_3_20150109162932
gp_dump_0_2_20150109162932.gz    gp_dump_1_1_20150109162932.gz  gp_dump_status_0_2_20150109162932        gp_dump_status_1_1_20150109162932
[gpadmin@sachi 20150109]$ ls -ltr
total 40
-rw-------. 1 gpadmin gpadmin  111 Jan  9 16:29 gp_cdatabase_1_1_20150109162932
-rw-------. 1 gpadmin gpadmin 4916 Jan  9 16:29 gp_dump_1_1_20150109162932.gz
-rw-------. 1 gpadmin gpadmin 2061 Jan  9 16:29 gp_dump_status_1_1_20150109162932
-rw-------. 1 gpadmin gpadmin  324 Jan  9 16:29 gp_dump_1_1_20150109162932_post_data.gz
-rw-------. 1 gpadmin gpadmin  944 Jan  9 16:29 gp_dump_status_0_3_20150109162932
-rw-------. 1 gpadmin gpadmin  944 Jan  9 16:29 gp_dump_status_0_2_20150109162932
-rw-------. 1 gpadmin gpadmin 5209 Jan  9 16:29 gp_dump_0_3_20150109162932.gz
-rw-------. 1 gpadmin gpadmin 3614 Jan  9 16:29 gp_dump_0_2_20150109162932.gz
[gpadmin@sachi 20150109]$ 





Comments