How to backup a table/set of tables in greenplum

posted Apr 28, 2017, 3:56 PM by Sachchida Ojha
You can do it in 2 ways. 

If no of tables are few then you can use -t option in the gpcrondump utility. 
For example

1) backup 1 table

[gpadmin@sachi ~]$ gpcrondump -x sachi -t sachi.countries
20131117:22:08:23:030358 gpcrondump:sachi:gpadmin-[INFO]:-Starting gpcrondump with args: -x sachi -t sachi.countries
20131117:22:08:23:030358 gpcrondump:sachi:gpadmin-[INFO]:----------------------------------------------------
20131117:22:08:23:030358 gpcrondump:sachi:gpadmin-[INFO]:-Master Greenplum Instance dump parameters
20131117:22:08:23:030358 gpcrondump:sachi:gpadmin-[INFO]:----------------------------------------------------
20131117:22:08:23:030358 gpcrondump:sachi:gpadmin-[INFO]:-Dump type                            = Single database, specific table
20131117:22:08:23:030358 gpcrondump:sachi:gpadmin-[INFO]:----------------------------------------------------
20131117:22:08:23:030358 gpcrondump:sachi:gpadmin-[INFO]:-Table inclusion list 
20131117:22:08:23:030358 gpcrondump:sachi:gpadmin-[INFO]:----------------------------------------------------
20131117:22:08:23:030358 gpcrondump:sachi:gpadmin-[INFO]:-Table name                             = sachi.countries
20131117:22:08:23:030358 gpcrondump:sachi:gpadmin-[INFO]:----------------------------------------------------
20131117:22:08:23:030358 gpcrondump:sachi:gpadmin-[INFO]:-Database to be dumped                = sachi
20131117:22:08:23:030358 gpcrondump:sachi:gpadmin-[INFO]:-Master port                          = 5432
20131117:22:08:23:030358 gpcrondump:sachi:gpadmin-[INFO]:-Master data directory                = /home/gpmaster/gpsne-1
20131117:22:08:23:030358 gpcrondump:sachi:gpadmin-[INFO]:-Run post dump program                = Off
20131117:22:08:23:030358 gpcrondump:sachi:gpadmin-[INFO]:-Rollback dumps                       = Off
20131117:22:08:23:030358 gpcrondump:sachi:gpadmin-[INFO]:-Dump file compression                = On
20131117:22:08:23:030358 gpcrondump:sachi:gpadmin-[INFO]:-Clear old dump files                 = Off
20131117:22:08:23:030358 gpcrondump:sachi:gpadmin-[INFO]:-Update history table                 = Off
20131117:22:08:23:030358 gpcrondump:sachi:gpadmin-[INFO]:-Secure config files                  = Off
20131117:22:08:23:030358 gpcrondump:sachi:gpadmin-[INFO]:-Dump global objects                  = Off
20131117:22:08:23:030358 gpcrondump:sachi:gpadmin-[INFO]:-Vacuum mode type                     = Off
20131117:22:08:23:030358 gpcrondump:sachi:gpadmin-[INFO]:-Ensuring remaining free disk         > 10

Continue with Greenplum dump Yy|Nn (default=N):
> y
20131117:22:08:24:030358 gpcrondump:sachi:gpadmin-[INFO]:-Directory /home/gpmaster/gpsne-1/db_dumps/20131117 exists
20131117:22:08:24:030358 gpcrondump:sachi:gpadmin-[INFO]:-Checked /home/gpmaster/gpsne-1 on master
20131117:22:08:25:030358 gpcrondump:sachi:gpadmin-[INFO]:-Configuring for single-database, include-table dump
20131117:22:08:25:030358 gpcrondump:sachi:gpadmin-[INFO]:-Located table sachi.countries in sachi database
20131117:22:08:25:030358 gpcrondump:sachi:gpadmin-[INFO]:-Adding compression parameter
20131117:22:08:25:030358 gpcrondump:sachi:gpadmin-[INFO]:-Dump command line gp_dump -p 5432 -U gpadmin --gp-d=db_dumps/20131117 --gp-r=/home/gpmaster/gpsne-1/db_dumps/20131117 --gp-s=p --gp-c sachi --table="\"sachi\""."\"countries\""
20131117:22:08:25:030358 gpcrondump:sachi:gpadmin-[INFO]:-Starting dump process
20131117:22:08:28:030358 gpcrondump:sachi:gpadmin-[INFO]:-Dump process returned exit code 0
20131117:22:08:28:030358 gpcrondump:sachi:gpadmin-[INFO]:-Timestamp key = 20131117220825
20131117:22:08:28:030358 gpcrondump:sachi:gpadmin-[INFO]:-Checked master status file and master dump file.
20131117:22:08:28:030358 gpcrondump:sachi:gpadmin-[INFO]:-Dump status report
20131117:22:08:28:030358 gpcrondump:sachi:gpadmin-[INFO]:----------------------------------------------------
20131117:22:08:28:030358 gpcrondump:sachi:gpadmin-[INFO]:-Target database                          = sachi
20131117:22:08:28:030358 gpcrondump:sachi:gpadmin-[INFO]:-Dump subdirectory                        = 20131117
20131117:22:08:28:030358 gpcrondump:sachi:gpadmin-[INFO]:-Clear old dump directories               = Off
20131117:22:08:28:030358 gpcrondump:sachi:gpadmin-[INFO]:-Dump start time                          = 22:08:25
20131117:22:08:28:030358 gpcrondump:sachi:gpadmin-[INFO]:-Dump end time                            = 22:08:28
20131117:22:08:28:030358 gpcrondump:sachi:gpadmin-[INFO]:-Status                                   = COMPLETED
20131117:22:08:28:030358 gpcrondump:sachi:gpadmin-[INFO]:-Dump key                                 = 20131117220825
20131117:22:08:28:030358 gpcrondump:sachi:gpadmin-[INFO]:-Dump file compression                    = On
20131117:22:08:28:030358 gpcrondump:sachi:gpadmin-[INFO]:-Vacuum mode type                         = Off
20131117:22:08:28:030358 gpcrondump:sachi:gpadmin-[INFO]:-Exit code zero, no warnings generated
20131117:22:08:28:030358 gpcrondump:sachi:gpadmin-[INFO]:----------------------------------------------------
20131117:22:08:28:030358 gpcrondump:sachi:gpadmin-[WARNING]:-Found neither /usr/local/greenplum-db/./bin/mail_contacts nor /home/gpadmin/mail_contacts
20131117:22:08:28:030358 gpcrondump:sachi:gpadmin-[WARNING]:-Unable to send dump email notification
20131117:22:08:28:030358 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


Backup 2 tables

[gpadmin@sachi ~]$ gpcrondump -x sachi -t sachi.countries -t sachi.emp1
20131117:22:10:39:030935 gpcrondump:sachi:gpadmin-[INFO]:-Starting gpcrondump with args: -x sachi -t sachi.countries -t sachi.emp1
20131117:22:10:39:030935 gpcrondump:sachi:gpadmin-[INFO]:----------------------------------------------------
20131117:22:10:39:030935 gpcrondump:sachi:gpadmin-[INFO]:-Master Greenplum Instance dump parameters
20131117:22:10:39:030935 gpcrondump:sachi:gpadmin-[INFO]:----------------------------------------------------
20131117:22:10:39:030935 gpcrondump:sachi:gpadmin-[INFO]:-Dump type                            = Single database, specific table
20131117:22:10:39:030935 gpcrondump:sachi:gpadmin-[INFO]:----------------------------------------------------
20131117:22:10:39:030935 gpcrondump:sachi:gpadmin-[INFO]:-Table inclusion list 
20131117:22:10:39:030935 gpcrondump:sachi:gpadmin-[INFO]:----------------------------------------------------
20131117:22:10:39:030935 gpcrondump:sachi:gpadmin-[INFO]:-Table name                             = sachi.countries
20131117:22:10:39:030935 gpcrondump:sachi:gpadmin-[INFO]:-Table name                             = sachi.emp1
20131117:22:10:39:030935 gpcrondump:sachi:gpadmin-[INFO]:----------------------------------------------------
20131117:22:10:39:030935 gpcrondump:sachi:gpadmin-[INFO]:-Database to be dumped                = sachi
20131117:22:10:39:030935 gpcrondump:sachi:gpadmin-[INFO]:-Master port                          = 5432
20131117:22:10:39:030935 gpcrondump:sachi:gpadmin-[INFO]:-Master data directory                = /home/gpmaster/gpsne-1
20131117:22:10:39:030935 gpcrondump:sachi:gpadmin-[INFO]:-Run post dump program                = Off
20131117:22:10:39:030935 gpcrondump:sachi:gpadmin-[INFO]:-Rollback dumps                       = Off
20131117:22:10:39:030935 gpcrondump:sachi:gpadmin-[INFO]:-Dump file compression                = On
20131117:22:10:39:030935 gpcrondump:sachi:gpadmin-[INFO]:-Clear old dump files                 = Off
20131117:22:10:39:030935 gpcrondump:sachi:gpadmin-[INFO]:-Update history table                 = Off
20131117:22:10:39:030935 gpcrondump:sachi:gpadmin-[INFO]:-Secure config files                  = Off
20131117:22:10:39:030935 gpcrondump:sachi:gpadmin-[INFO]:-Dump global objects                  = Off
20131117:22:10:39:030935 gpcrondump:sachi:gpadmin-[INFO]:-Vacuum mode type                     = Off
20131117:22:10:39:030935 gpcrondump:sachi:gpadmin-[INFO]:-Ensuring remaining free disk         > 10

Continue with Greenplum dump Yy|Nn (default=N):
> y
20131117:22:10:40:030935 gpcrondump:sachi:gpadmin-[INFO]:-Directory /home/gpmaster/gpsne-1/db_dumps/20131117 exists
20131117:22:10:40:030935 gpcrondump:sachi:gpadmin-[INFO]:-Checked /home/gpmaster/gpsne-1 on master
20131117:22:10:41:030935 gpcrondump:sachi:gpadmin-[INFO]:-Configuring for single-database, include-table dump
20131117:22:10:41:030935 gpcrondump:sachi:gpadmin-[INFO]:-Located table sachi.countries in sachi database
20131117:22:10:41:030935 gpcrondump:sachi:gpadmin-[INFO]:-Located table sachi.emp1 in sachi database
20131117:22:10:41:030935 gpcrondump:sachi:gpadmin-[INFO]:-Adding compression parameter
20131117:22:10:41:030935 gpcrondump:sachi:gpadmin-[INFO]:-Dump command line gp_dump -p 5432 -U gpadmin --gp-d=db_dumps/20131117 --gp-r=/home/gpmaster/gpsne-1/db_dumps/20131117 --gp-s=p --gp-c sachi --table="\"sachi\""."\"countries\"" --table="\"sachi\""."\"emp1\""
20131117:22:10:41:030935 gpcrondump:sachi:gpadmin-[INFO]:-Starting dump process
20131117:22:10:44:030935 gpcrondump:sachi:gpadmin-[INFO]:-Dump process returned exit code 0
20131117:22:10:44:030935 gpcrondump:sachi:gpadmin-[INFO]:-Timestamp key = 20131117221042
20131117:22:10:44:030935 gpcrondump:sachi:gpadmin-[INFO]:-Checked master status file and master dump file.
20131117:22:10:44:030935 gpcrondump:sachi:gpadmin-[INFO]:-Dump status report
20131117:22:10:44:030935 gpcrondump:sachi:gpadmin-[INFO]:----------------------------------------------------
20131117:22:10:44:030935 gpcrondump:sachi:gpadmin-[INFO]:-Target database                          = sachi
20131117:22:10:44:030935 gpcrondump:sachi:gpadmin-[INFO]:-Dump subdirectory                        = 20131117
20131117:22:10:44:030935 gpcrondump:sachi:gpadmin-[INFO]:-Clear old dump directories               = Off
20131117:22:10:44:030935 gpcrondump:sachi:gpadmin-[INFO]:-Dump start time                          = 22:10:41
20131117:22:10:44:030935 gpcrondump:sachi:gpadmin-[INFO]:-Dump end time                            = 22:10:44
20131117:22:10:44:030935 gpcrondump:sachi:gpadmin-[INFO]:-Status                                   = COMPLETED
20131117:22:10:44:030935 gpcrondump:sachi:gpadmin-[INFO]:-Dump key                                 = 20131117221042
20131117:22:10:44:030935 gpcrondump:sachi:gpadmin-[INFO]:-Dump file compression                    = On
20131117:22:10:44:030935 gpcrondump:sachi:gpadmin-[INFO]:-Vacuum mode type                         = Off
20131117:22:10:44:030935 gpcrondump:sachi:gpadmin-[INFO]:-Exit code zero, no warnings generated
20131117:22:10:44:030935 gpcrondump:sachi:gpadmin-[INFO]:----------------------------------------------------
20131117:22:10:44:030935 gpcrondump:sachi:gpadmin-[WARNING]:-Found neither /usr/local/greenplum-db/./bin/mail_contacts nor /home/gpadmin/mail_contacts
20131117:22:10:44:030935 gpcrondump:sachi:gpadmin-[WARNING]:-Unable to send dump email notification
20131117:22:10:44:030935 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

3. Multiple tables from a file

[gpadmin@sachi ~]$ pwd
/home/gpadmin
[gpadmin@sachi ~]$ cat table_list.txt 
sachi.countries
sachi.departments
sachi.emp1
sachi.employees
sachi.ext_countries

[gpadmin@sachi ~]$ gpcrondump -x sachi --table-file=/home/gpadmin/table_list.txt
20131118:08:44:21:018143 gpcrondump:sachi:gpadmin-[INFO]:-Starting gpcrondump with args: -x sachi --table-file=/home/gpadmin/table_list.txt
20131118:08:44:21:018143 gpcrondump:sachi:gpadmin-[INFO]:----------------------------------------------------
20131118:08:44:21:018143 gpcrondump:sachi:gpadmin-[INFO]:-Master Greenplum Instance dump parameters
20131118:08:44:21:018143 gpcrondump:sachi:gpadmin-[INFO]:----------------------------------------------------
20131118:08:44:21:018143 gpcrondump:sachi:gpadmin-[INFO]:-Dump type                            = Single database, specific table
20131118:08:44:21:018143 gpcrondump:sachi:gpadmin-[INFO]:----------------------------------------------------
20131118:08:44:21:018143 gpcrondump:sachi:gpadmin-[INFO]:-Table file name                      = /home/gpadmin/table_list.txt
20131118:08:44:21:018143 gpcrondump:sachi:gpadmin-[INFO]:----------------------------------------------------
20131118:08:44:21:018143 gpcrondump:sachi:gpadmin-[INFO]:-Database to be dumped                = sachi
20131118:08:44:21:018143 gpcrondump:sachi:gpadmin-[INFO]:-Master port                          = 5432
20131118:08:44:21:018143 gpcrondump:sachi:gpadmin-[INFO]:-Master data directory                = /home/gpmaster/gpsne-1
20131118:08:44:21:018143 gpcrondump:sachi:gpadmin-[INFO]:-Run post dump program                = Off
20131118:08:44:21:018143 gpcrondump:sachi:gpadmin-[INFO]:-Rollback dumps                       = Off
20131118:08:44:21:018143 gpcrondump:sachi:gpadmin-[INFO]:-Dump file compression                = On
20131118:08:44:21:018143 gpcrondump:sachi:gpadmin-[INFO]:-Clear old dump files                 = Off
20131118:08:44:21:018143 gpcrondump:sachi:gpadmin-[INFO]:-Update history table                 = Off
20131118:08:44:21:018143 gpcrondump:sachi:gpadmin-[INFO]:-Secure config files                  = Off
20131118:08:44:21:018143 gpcrondump:sachi:gpadmin-[INFO]:-Dump global objects                  = Off
20131118:08:44:21:018143 gpcrondump:sachi:gpadmin-[INFO]:-Vacuum mode type                     = Off
20131118:08:44:21:018143 gpcrondump:sachi:gpadmin-[INFO]:-Ensuring remaining free disk         > 10

Continue with Greenplum dump Yy|Nn (default=N):
> y
20131118:08:44:23:018143 gpcrondump:sachi:gpadmin-[INFO]:-Directory /home/gpmaster/gpsne-1/db_dumps/20131118 exists
20131118:08:44:23:018143 gpcrondump:sachi:gpadmin-[INFO]:-Checked /home/gpmaster/gpsne-1 on master
20131118:08:44:24:018143 gpcrondump:sachi:gpadmin-[INFO]:-Configuring for single-database, include-table dump
20131118:08:44:24:018143 gpcrondump:sachi:gpadmin-[INFO]:-Located table sachi.countries in sachi database
20131118:08:44:24:018143 gpcrondump:sachi:gpadmin-[INFO]:-Located table sachi.departments in sachi database
20131118:08:44:24:018143 gpcrondump:sachi:gpadmin-[INFO]:-Located table sachi.emp1 in sachi database
20131118:08:44:24:018143 gpcrondump:sachi:gpadmin-[INFO]:-Located table sachi.employees in sachi database
20131118:08:44:24:018143 gpcrondump:sachi:gpadmin-[INFO]:-Located table sachi.ext_countries in sachi database
20131118:08:44:25:018143 gpcrondump:sachi:gpadmin-[INFO]:-Adding compression parameter
20131118:08:44:25:018143 gpcrondump:sachi:gpadmin-[INFO]:-Dump command line gp_dump -p 5432 -U gpadmin --gp-d=db_dumps/20131118 --gp-r=/home/gpmaster/gpsne-1/db_dumps/20131118 --gp-s=p --gp-c sachi --table-file=/home/gpadmin/table_list.txt
20131118:08:44:25:018143 gpcrondump:sachi:gpadmin-[INFO]:-Starting dump process
20131118:08:44:33:018143 gpcrondump:sachi:gpadmin-[INFO]:-Dump process returned exit code 0
20131118:08:44:33:018143 gpcrondump:sachi:gpadmin-[INFO]:-Timestamp key = 20131118084430
20131118:08:44:33:018143 gpcrondump:sachi:gpadmin-[INFO]:-Checked master status file and master dump file.
20131118:08:44:34:018143 gpcrondump:sachi:gpadmin-[INFO]:-Dump status report
20131118:08:44:34:018143 gpcrondump:sachi:gpadmin-[INFO]:----------------------------------------------------
20131118:08:44:34:018143 gpcrondump:sachi:gpadmin-[INFO]:-Target database                          = sachi
20131118:08:44:34:018143 gpcrondump:sachi:gpadmin-[INFO]:-Dump subdirectory                        = 20131118
20131118:08:44:34:018143 gpcrondump:sachi:gpadmin-[INFO]:-Clear old dump directories               = Off
20131118:08:44:34:018143 gpcrondump:sachi:gpadmin-[INFO]:-Dump start time                          = 08:44:25
20131118:08:44:34:018143 gpcrondump:sachi:gpadmin-[INFO]:-Dump end time                            = 08:44:33
20131118:08:44:34:018143 gpcrondump:sachi:gpadmin-[INFO]:-Status                                   = COMPLETED
20131118:08:44:34:018143 gpcrondump:sachi:gpadmin-[INFO]:-Dump key                                 = 20131118084430
20131118:08:44:34:018143 gpcrondump:sachi:gpadmin-[INFO]:-Dump file compression                    = On
20131118:08:44:34:018143 gpcrondump:sachi:gpadmin-[INFO]:-Vacuum mode type                         = Off
20131118:08:44:34:018143 gpcrondump:sachi:gpadmin-[INFO]:-Exit code zero, no warnings generated
20131118:08:44:34:018143 gpcrondump:sachi:gpadmin-[INFO]:----------------------------------------------------
20131118:08:44:34:018143 gpcrondump:sachi:gpadmin-[WARNING]:-Found neither /usr/local/greenplum-db/./bin/mail_contacts nor /home/gpadmin/mail_contacts
20131118:08:44:34:018143 gpcrondump:sachi:gpadmin-[WARNING]:-Unable to send dump email notification
20131118:08:44:34:018143 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

--- List of tables in Sachi Schema
sachi=> \dt
      1                  List of relations
      2  Schema |          Name           | Type  |  Owner  
      3 --------+-------------------------+-------+---------
      4  public | test                    | table | gpadmin
      5  sachi  | countries               | table | sachi
      6  sachi  | departments             | table | sachi
      7  sachi  | emp1                    | table | sachi
      8  sachi  | employees               | table | sachi
      9  sachi  | ext_countries           | table | sachi
     10  sachi  | ext_countries_error     | table | sachi
     11  sachi  | ext_dc97_1099patr       | table | sachi
     12  sachi  | ext_dc97_1099patr_error | table | sachi
     13  sachi  | ext_departments         | table | sachi
     14  sachi  | ext_departments_error   | table | sachi
     15  sachi  | ext_employees           | table | sachi
     16  sachi  | ext_employees_error     | table | sachi
     17  sachi  | ext_job_history         | table | sachi
     18  sachi  | ext_job_history_error   | table | sachi
     19  sachi  | ext_jobs                | table | sachi
     20  sachi  | ext_jobs_error          | table | sachi
     21  sachi  | ext_locations           | table | sachi
     22  sachi  | ext_locations_error     | table | sachi
     23  sachi  | ext_regions             | table | sachi
     24  sachi  | ext_regions_error       | table | sachi
     25  sachi  | job_history             | table | sachi
     26  sachi  | jobs                    | table | sachi
     27  sachi  | lineitem_ext            | table | sachi
     28  sachi  | locations               | table | sachi
     29  sachi  | myregion                | table | sachi
     30  sachi  | regions                 | table | sachi
     31  sachi  | test1                   | table | sachi
     32 (28 rows)
     33 
Comments