Greenplum 4.x 백업

1. 특정 파티션 백업 

## 백업 받을 리스트
[gpadmin@mdw command]$ cat backup_tables_list_p1997
edu_sch.lineitem_1_prt_p1997
edu_sch.orders_1_prt_p1997
[gpadmin@mdw command]$

## 사전 테이블/파티션 건수 체크
[gpadmin@mdw command]$ psql -ef cnt.sql
Timing is on.
select 'edu_sch.lineitem', count(*) from edu_sch.lineitem;
     ?column?     |  count
------------------+---------
 edu_sch.lineitem | 6001215
(1 row)

Time: 1026.143 ms
select 'edu_sch.orders', count(*) from edu_sch.orders;
    ?column?    |  count
----------------+---------
 edu_sch.orders | 1500000
(1 row)

Time: 268.886 ms
select 'edu_sch.lineitem_1_prt_p1997',  count(*) from edu_sch.lineitem_1_prt_p1997;
           ?column?           | count
------------------------------+--------
 edu_sch.lineitem_1_prt_p1997 | 911395
(1 row)

Time: 98.645 ms
select 'edu_sch.orders_1_prt_p1997', count(*) from edu_sch.orders_1_prt_p1997;
          ?column?          | count
----------------------------+--------
 edu_sch.orders_1_prt_p1997 | 227783
(1 row)

Time: 23.260 ms

### 백업 
[gpadmin@mdw command]$ gpcrondump -x edu --table-file=/home/gpadmin/utilities/command/backup_tables_list_p1997 --prefix p1997 -u /data/backup -a   --rsyncable
20171013:10:39:43:103761 gpcrondump:mdw:gpadmin-[INFO]:-Starting gpcrondump with args: -x edu --table-file=/home/gpadmin/utilities/command/backup_tables_list_p1997 --prefix p1997 -u /data/backup -a --rsyncable
20171013:10:39:44:103761 gpcrondump:mdw:gpadmin-[INFO]:-Directory /data/backup/db_dumps/20171013 exists
20171013:10:39:44:103761 gpcrondump:mdw:gpadmin-[INFO]:-Checked /data/backup on master
20171013:10:39:44:103761 gpcrondump:mdw:gpadmin-[INFO]:-Configuring for single-database, include-table dump
20171013:10:39:44:103761 gpcrondump:mdw:gpadmin-[INFO]:-Validating disk space
20171013:10:39:44:103761 gpcrondump:mdw:gpadmin-[INFO]:-Creating filter file: /data/backup/db_dumps/20171013/p1997_gp_dump_20171013103943_filter
20171013:10:39:44:103761 gpcrondump:mdw:gpadmin-[INFO]:-Creating filter file: /data/backup/db_dumps/20171013/p1997_gp_dump_20171013103943_table
20171013:10:39:44:103761 gpcrondump:mdw:gpadmin-[INFO]:-Adding compression parameter
20171013:10:39:44:103761 gpcrondump:mdw:gpadmin-[INFO]:-Adding --prefix
20171013:10:39:44:103761 gpcrondump:mdw:gpadmin-[INFO]:-Adding --no-expand-children
20171013:10:39:44:103761 gpcrondump:mdw:gpadmin-[INFO]:-Dump process command line gp_dump -p 5432 -U gpadmin --gp-d=/data/backup/db_dumps/20171013 --gp-r=/data/backup/db_dumps/20171013 --gp-s=p --gp-k=20171013103943 --no-lock --gp-c --prefix=p1997_ --no-expand-children "edu" --table-file=/tmp/include_dump_tables_fileAUmHhc --rsyncable
20171013:10:39:44:103761 gpcrondump:mdw:gpadmin-[INFO]:-Starting Dump process
20171013:10:39:53:103761 gpcrondump:mdw:gpadmin-[INFO]:-Releasing pg_class lock
20171013:10:39:54:103761 gpcrondump:mdw:gpadmin-[INFO]:-Dump process returned exit code 0
20171013:10:39:54:103761 gpcrondump:mdw:gpadmin-[INFO]:-Timestamp key = 20171013103943
20171013:10:39:54:103761 gpcrondump:mdw:gpadmin-[INFO]:-Checked master status file and master dump file.
20171013:10:39:54:103761 gpcrondump:mdw:gpadmin-[INFO]:-Inserted dump record into public.gpcrondump_history in edu database
20171013:10:39:54:103761 gpcrondump:mdw:gpadmin-[INFO]:-Dump status report
20171013:10:39:54:103761 gpcrondump:mdw:gpadmin-[INFO]:----------------------------------------------------
20171013:10:39:54:103761 gpcrondump:mdw:gpadmin-[INFO]:-Target database                          = edu
20171013:10:39:54:103761 gpcrondump:mdw:gpadmin-[INFO]:-Dump subdirectory                        = 20171013
20171013:10:39:54:103761 gpcrondump:mdw:gpadmin-[INFO]:-Dump type                                = Full database
20171013:10:39:54:103761 gpcrondump:mdw:gpadmin-[INFO]:-Clear old dump directories               = Off
20171013:10:39:54:103761 gpcrondump:mdw:gpadmin-[INFO]:-Dump start time                          = 10:39:43
20171013:10:39:54:103761 gpcrondump:mdw:gpadmin-[INFO]:-Dump end time                            = 10:39:54
20171013:10:39:54:103761 gpcrondump:mdw:gpadmin-[INFO]:-Status                                   = COMPLETED
20171013:10:39:54:103761 gpcrondump:mdw:gpadmin-[INFO]:-Dump key                                 = 20171013103943
20171013:10:39:54:103761 gpcrondump:mdw:gpadmin-[INFO]:-Dump file compression                    = On
20171013:10:39:54:103761 gpcrondump:mdw:gpadmin-[INFO]:-Vacuum mode type                         = Off
20171013:10:39:54:103761 gpcrondump:mdw:gpadmin-[INFO]:-Exit code zero, no warnings generated
20171013:10:39:55:103761 gpcrondump:mdw:gpadmin-[INFO]:----------------------------------------------------
20171013:10:39:55:103761 gpcrondump:mdw:gpadmin-[INFO]:-Sending mail to slee@pivotal.io
You have new mail in /var/spool/mail/gpadmin
[gpadmin@mdw command]$

### 백업 정상여부 확인
[gpadmin@mdw 20171013]$ pwd
/data/backup/db_dumps/20171013
[gpadmin@mdw 20171013]$ cat p1997_gp_dump_20171013103810.rpt

Greenplum Database Backup Report
Timestamp Key: 20171013103810
gp_dump Command Line: -p 5432 -U gpadmin --gp-d=/data/backup/db_dumps/20171013 --gp-r=/data/backup/db_dumps/20171013 --gp-s=p --gp-k=20171013103810 --no-lock --gp-c --prefix=p1997_ --no-expand-children edu --table-file=/tmp/include_dump_tables_filewXn_1e --rsyncable
Pass through Command Line Options: --prefix p1997_ --table-file /tmp/include_dump_tables_filewXn_1e --rsyncable
Compression Program: gzip
Backup Type: Full

Individual Results
        segment 2 (dbid 4) Host sdw3.gphd.local Port 40000 Database edu BackupFile /data/backup/db_dumps/20171013/p1997_gp_dump_2_4_20171013103810.gz: Succeeded
        segment 1 (dbid 3) Host sdw2.gphd.local Port 40000 Database edu BackupFile /data/backup/db_dumps/20171013/p1997_gp_dump_1_3_20171013103810.gz: Succeeded
        segment 0 (dbid 2) Host sdw1.gphd.local Port 40000 Database edu BackupFile /data/backup/db_dumps/20171013/p1997_gp_dump_0_2_20171013103810.gz: Succeeded
        Master (dbid 1) Host mdw Port 5432 Database edu BackupFile /data/backup/db_dumps/20171013/p1997_gp_dump_-1_1_20171013103810.gz: Succeeded
        Master (dbid 1) Host mdw Port 5432 Database edu BackupFile /data/backup/db_dumps/20171013/p1997_gp_dump_-1_1_20171013103810.gz_post_data: Succeeded

gp_dump utility finished successfully.
[gpadmin@mdw 20171013]$

### 특정 파티션 Truncate
[gpadmin@mdw command]$ cat trunc.sql
truncate table edu_sch.lineitem_1_prt_p1997;
truncate table edu_sch.orders_1_prt_p1997;

[gpadmin@mdw command]$ psql -ef trunc.sql
Timing is on.
truncate table edu_sch.lineitem_1_prt_p1997;
TRUNCATE TABLE
Time: 120.545 ms
truncate table edu_sch.orders_1_prt_p1997;
TRUNCATE TABLE
Time: 65.295 ms
[gpadmin@mdw command]$

### 복구
[gpadmin@mdw 20171013]$ gpdbrestore -t 20171013103943 --prefix p1997 -u /data/backup -v
20171013:10:57:56:119601 gpdbrestore:mdw:gpadmin-[INFO]:-Starting gpdbrestore with args: -t 20171013103943 --prefix p1997 -u /data/backup -v
20171013:10:57:56:119601 gpdbrestore:mdw:gpadmin-[DEBUG]:-Checking if MASTER_DATA_DIRECTORY env variable is set.
20171013:10:57:56:119601 gpdbrestore:mdw:gpadmin-[DEBUG]:-Connecting to dbname='template1'
20171013:10:57:56:119601 gpdbrestore:mdw:gpadmin-[DEBUG]:-Checking if MASTER_DATA_DIRECTORY env variable is set.
20171013:10:57:56:119601 gpdbrestore:mdw:gpadmin-[DEBUG]:-Starting GpdbRestore
20171013:10:57:56:119601 gpdbrestore:mdw:gpadmin-[DEBUG]:-Starting ValidateTimestamp
20171013:10:57:56:119601 gpdbrestore:mdw:gpadmin-[DEBUG]:-Ending ValidateTimestamp
20171013:10:57:56:119601 gpdbrestore:mdw:gpadmin-[DEBUG]:-Connecting to dbname='edu'
20171013:10:57:56:119601 gpdbrestore:mdw:gpadmin-[INFO]:-------------------------------------------
20171013:10:57:56:119601 gpdbrestore:mdw:gpadmin-[INFO]:-Greenplum database restore parameters
20171013:10:57:56:119601 gpdbrestore:mdw:gpadmin-[INFO]:-------------------------------------------
20171013:10:57:56:119601 gpdbrestore:mdw:gpadmin-[INFO]:-Restore type               = Full Database
20171013:10:57:56:119601 gpdbrestore:mdw:gpadmin-[INFO]:-Database to be restored    = edu
20171013:10:57:56:119601 gpdbrestore:mdw:gpadmin-[INFO]:-Drop and re-create db      = Off
20171013:10:57:56:119601 gpdbrestore:mdw:gpadmin-[INFO]:-Restore method             = Restore specific timestamp
20171013:10:57:56:119601 gpdbrestore:mdw:gpadmin-[INFO]:-Restore timestamp          = 20171013103943
20171013:10:57:56:119601 gpdbrestore:mdw:gpadmin-[INFO]:-Restore compressed dump    = On
20171013:10:57:56:119601 gpdbrestore:mdw:gpadmin-[INFO]:-Restore global objects     = Off
20171013:10:57:56:119601 gpdbrestore:mdw:gpadmin-[INFO]:-Array fault tolerance      = f
20171013:10:57:56:119601 gpdbrestore:mdw:gpadmin-[INFO]:-------------------------------------------

Continue with Greenplum restore Yy|Nn (default=N):
> y
20171013:10:57:59:119601 gpdbrestore:mdw:gpadmin-[DEBUG]:-Starting RestoreDatabase
20171013:10:57:59:119601 gpdbrestore:mdw:gpadmin-[DEBUG]:-Starting ValidateSegments
20171013:10:58:00:119601 gpdbrestore:mdw:gpadmin-[DEBUG]:-Ending ValidateSegments
20171013:10:58:00:119601 gpdbrestore:mdw:gpadmin-[INFO]:-Adding --prefix
20171013:10:58:00:119601 gpdbrestore:mdw:gpadmin-[INFO]:-gp_restore commandline: gp_restore -i -h mdw.gphd.local -p 5432 -U gpadmin --gp-d=/data/backup/db_dumps/20171013 --gp-i --gp-k=20171013103943 --gp-l=p --gp-r=/data/backup/db_dumps/20171013 --status=/data/backup/db_dumps/20171013 --prefix=p1997_ --gp-c -d "edu":
20171013:10:58:08:119601 gpdbrestore:mdw:gpadmin-[INFO]:-gpdbrestore finished successfully
20171013:10:58:08:119601 gpdbrestore:mdw:gpadmin-[INFO]:-Updating AO/CO statistics on master
20171013:10:58:08:119601 gpdbrestore:mdw:gpadmin-[DEBUG]:-Connecting to dbname='edu'
20171013:10:58:08:119601 gpdbrestore:mdw:gpadmin-[DEBUG]:-Connecting to dbname='edu'
20171013:10:58:08:119601 gpdbrestore:mdw:gpadmin-[INFO]:-Commencing analyze of edu database, please wait
20171013:10:58:08:119601 gpdbrestore:mdw:gpadmin-[DEBUG]:-Connecting to dbname='edu'
20171013:10:58:41:119601 gpdbrestore:mdw:gpadmin-[INFO]:-Analyze of edu completed without error
20171013:10:58:41:119601 gpdbrestore:mdw:gpadmin-[DEBUG]:-Ending RestoreDatabase
You have new mail in /var/spool/mail/gpadmin
[gpadmin@mdw 20171013]$

### 복구후 건수 점검
[gpadmin@mdw command]$ psql -ef cnt.sql
Timing is on.
select 'edu_sch.lineitem', count(*) from edu_sch.lineitem;
     ?column?     |  count
------------------+---------
 edu_sch.lineitem | 6001215
(1 row)

Time: 1079.016 ms
select 'edu_sch.orders', count(*) from edu_sch.orders;
    ?column?    |  count
----------------+---------
 edu_sch.orders | 1500000
(1 row)

Time: 345.850 ms
select 'edu_sch.lineitem_1_prt_p1997',  count(*) from edu_sch.lineitem_1_prt_p1997;
           ?column?           | count
------------------------------+--------
 edu_sch.lineitem_1_prt_p1997 | 911395
(1 row)

Time: 130.952 ms
select 'edu_sch.orders_1_prt_p1997', count(*) from edu_sch.orders_1_prt_p1997;
          ?column?          | count
----------------------------+--------
 edu_sch.orders_1_prt_p1997 | 227783
(1 row)

Time: 12.603 ms
[gpadmin@mdw command]$