We have migrated to new Google sites!

Backup a table or list of tables in Greenplum using gpcrondump

Post date: Jan 09, 2015 10:16:29 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]$

Report abuse