How to backup a table/set of tables in greenplum

Post date: Nov 18, 2013 3:12:5 AM

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