안녕하세요.
Database 운영에 있어서 백업/복구는 장애 발생 시 업무 중단을 최소화 하면서 데이터를 복구 할 수 있는 방법 중 한가지 입니다.
장애 시 복구를 위해서는 반듯이 백업이 필요하기 때문에 DBA는 백업도 관리 해야 합니다.
Greenplum에서 backup & restore를 위한 몇가지 기능을 제공하는데, 오늘은 그 중에서 gpcrondump를 이용한 백업과 gpdbrestore를 이용한 복구 시나리오에 대해서 테스트를 진행 했습니다.
테스트는 6단계로 진행 했으며 시나리오는 다음과 같습니다.
- adw DB를 full backup 후 DB를 삭제
- restore 후에 해당 DB가 복구 된것을 확인
테스트 command 및 상세 내용은 아래와 같습니다.
gpcrondump 및 gpdbrestore의 추가적인 옵션은 Greenplum 메뉴얼을 참고 하시기 바랍니다.
- gpcrondump : https://gpdb.docs.pivotal.io/530/utility_guide/admin_utilities/gpcrondump.html
- gpdbrestore : https://gpdb.docs.pivotal.io/530/utility_guide/admin_utilities/gpdbrestore.html#topic1
Database 운영에 있어서 백업/복구는 장애 발생 시 업무 중단을 최소화 하면서 데이터를 복구 할 수 있는 방법 중 한가지 입니다.
장애 시 복구를 위해서는 반듯이 백업이 필요하기 때문에 DBA는 백업도 관리 해야 합니다.
Greenplum에서 backup & restore를 위한 몇가지 기능을 제공하는데, 오늘은 그 중에서 gpcrondump를 이용한 백업과 gpdbrestore를 이용한 복구 시나리오에 대해서 테스트를 진행 했습니다.
테스트는 6단계로 진행 했으며 시나리오는 다음과 같습니다.
- adw DB를 full backup 후 DB를 삭제
- restore 후에 해당 DB가 복구 된것을 확인
테스트 command 및 상세 내용은 아래와 같습니다.
gpcrondump 및 gpdbrestore의 추가적인 옵션은 Greenplum 메뉴얼을 참고 하시기 바랍니다.
- gpcrondump : https://gpdb.docs.pivotal.io/530/utility_guide/admin_utilities/gpcrondump.html
- gpdbrestore : https://gpdb.docs.pivotal.io/530/utility_guide/admin_utilities/gpdbrestore.html#topic1
##################################################
# 1. DB list check
##################################################
[gpadmin@mdw ~]$ psql
psql (8.2.15)
Type "help" for help.
gpadmin=# \l
List of databases
Name | Owner | Encoding | Access privileges
-----------+---------+----------+---------------------
adw | gpadmin | UTF8 |
gpadmin | gpadmin | UTF8 |
gpperfmon | gpadmin | UTF8 | gpadmin=CTc/gpadmin
: =c/gpadmin
postgres | gpadmin | UTF8 |
template0 | gpadmin | UTF8 | =c/gpadmin
: gpadmin=CTc/gpadmin
template1 | gpadmin | UTF8 | =c/gpadmin
: gpadmin=CTc/gpadmin
(6 rows)
gpadmin=# \q
##################################################
# 2. DB backup using gpcrondump
# options
# -x : DBNAME
# -g : configuration file backup
# -G : global object backup (such as role and tablespace)
# -u : backup directory
##################################################
[gpadmin@mdw ~]$ gpcrondump -x adw -g -G -u /data/backup
20190416:22:37:03:005768 gpcrondump:mdw:gpadmin-[INFO]:-Starting gpcrondump with args: -x adw -g -G -u /data/backup
20190416:22:37:03:005768 gpcrondump:mdw:gpadmin-[INFO]:----------------------------------------------------
20190416:22:37:03:005768 gpcrondump:mdw:gpadmin-[INFO]:-Master Greenplum Instance dump parameters
20190416:22:37:03:005768 gpcrondump:mdw:gpadmin-[INFO]:----------------------------------------------------
20190416:22:37:03:005768 gpcrondump:mdw:gpadmin-[INFO]:-Dump type = Full database
20190416:22:37:03:005768 gpcrondump:mdw:gpadmin-[INFO]:-Database to be dumped = adw
20190416:22:37:03:005768 gpcrondump:mdw:gpadmin-[INFO]:-Dump directory = /data/backup
20190416:22:37:03:005768 gpcrondump:mdw:gpadmin-[INFO]:-Master port = 5432
20190416:22:37:03:005768 gpcrondump:mdw:gpadmin-[INFO]:-Master data directory = /data/master/gpseg-1
20190416:22:37:03:005768 gpcrondump:mdw:gpadmin-[INFO]:-Run post dump program = Off
20190416:22:37:03:005768 gpcrondump:mdw:gpadmin-[INFO]:-Rollback dumps = Off
20190416:22:37:03:005768 gpcrondump:mdw:gpadmin-[INFO]:-Dump file compression = On
20190416:22:37:03:005768 gpcrondump:mdw:gpadmin-[INFO]:-Clear old dump files = Off
20190416:22:37:03:005768 gpcrondump:mdw:gpadmin-[INFO]:-Update history table = On
20190416:22:37:03:005768 gpcrondump:mdw:gpadmin-[INFO]:-Secure config files = On
20190416:22:37:03:005768 gpcrondump:mdw:gpadmin-[INFO]:-Dump global objects = On
20190416:22:37:03:005768 gpcrondump:mdw:gpadmin-[INFO]:-Vacuum mode type = Off
20190416:22:37:03:005768 gpcrondump:mdw:gpadmin-[INFO]:-Ensuring remaining free disk > 10
Continue with Greenplum dump Yy|Nn (default=N):
> y
20190416:22:37:35:005768 gpcrondump:mdw:gpadmin-[INFO]:-Directory /data/backup/db_dumps/20190416 not found, will try to create
20190416:22:37:35:005768 gpcrondump:mdw:gpadmin-[INFO]:-Created /data/backup/db_dumps/20190416
20190416:22:37:35:005768 gpcrondump:mdw:gpadmin-[INFO]:-Checked /data/backup on master
20190416:22:37:35:005768 gpcrondump:mdw:gpadmin-[INFO]:-Configuring for single database dump
20190416:22:37:35:005768 gpcrondump:mdw:gpadmin-[INFO]:-Validating disk space
20190416:22:37:36:005768 gpcrondump:mdw:gpadmin-[INFO]:-Adding compression parameter
20190416:22:37:36:005768 gpcrondump:mdw:gpadmin-[INFO]:-Adding --no-expand-children
20190416:22:37:36:005768 gpcrondump:mdw:gpadmin-[INFO]:-Dump process command line gp_dump -p 5432 -U gpadmin --gp-d=/data/backup/db_dumps/20190416 --gp-r=/data/backup/db_dumps/20190416 --gp-s=p --gp-k=20190416223703 --no-lock --gp-c --no-expand-children "adw"
20190416:22:37:36:005768 gpcrondump:mdw:gpadmin-[INFO]:-Starting Dump process
20190416:22:37:38:005768 gpcrondump:mdw:gpadmin-[INFO]:-Dump process returned exit code 0
20190416:22:37:38:005768 gpcrondump:mdw:gpadmin-[INFO]:-Timestamp key = 20190416223703
20190416:22:37:38:005768 gpcrondump:mdw:gpadmin-[INFO]:-Checked master status file and master dump file.
20190416:22:37:39:005768 gpcrondump:mdw:gpadmin-[INFO]:-Created public.gpcrondump_history in adw database
20190416:22:37:39:005768 gpcrondump:mdw:gpadmin-[INFO]:-Inserted dump record into public.gpcrondump_history in adw database
20190416:22:37:39:005768 gpcrondump:mdw:gpadmin-[INFO]:-Commencing pg_catalog dump
20190416:22:37:39:005768 gpcrondump:mdw:gpadmin-[INFO]:-Dump status report
20190416:22:37:39:005768 gpcrondump:mdw:gpadmin-[INFO]:----------------------------------------------------
20190416:22:37:39:005768 gpcrondump:mdw:gpadmin-[INFO]:-Target database = adw
20190416:22:37:39:005768 gpcrondump:mdw:gpadmin-[INFO]:-Dump subdirectory = 20190416
20190416:22:37:39:005768 gpcrondump:mdw:gpadmin-[INFO]:-Dump type = Full database
20190416:22:37:39:005768 gpcrondump:mdw:gpadmin-[INFO]:-Clear old dump directories = Off
20190416:22:37:39:005768 gpcrondump:mdw:gpadmin-[INFO]:-Dump start time = 22:37:03
20190416:22:37:39:005768 gpcrondump:mdw:gpadmin-[INFO]:-Dump end time = 22:37:38
20190416:22:37:39:005768 gpcrondump:mdw:gpadmin-[INFO]:-Status = COMPLETED
20190416:22:37:39:005768 gpcrondump:mdw:gpadmin-[INFO]:-Dump key = 20190416223703
20190416:22:37:39:005768 gpcrondump:mdw:gpadmin-[INFO]:-Dump file compression = On
20190416:22:37:39:005768 gpcrondump:mdw:gpadmin-[INFO]:-Vacuum mode type = Off
20190416:22:37:39:005768 gpcrondump:mdw:gpadmin-[INFO]:-Exit code zero, no warnings generated
20190416:22:37:39:005768 gpcrondump:mdw:gpadmin-[INFO]:----------------------------------------------------
20190416:22:37:39:005768 gpcrondump:mdw:gpadmin-[INFO]:-Dumping master config files
20190416:22:37:39:005768 gpcrondump:mdw:gpadmin-[INFO]:-Dumping segment config files
20190416:22:37:40:005768 gpcrondump:mdw:gpadmin-[WARNING]:-Found neither /usr/local/greenplum-db/./bin/mail_contacts nor /home/gpadmin/mail_contacts
20190416:22:37:40:005768 gpcrondump:mdw:gpadmin-[WARNING]:-Unable to send dump email notification
20190416:22:37:40:005768 gpcrondump:mdw:gpadmin-[INFO]:-To enable email notification, create /usr/local/greenplum-db/./bin/mail_contacts or /home/gpadmin/mail_contacts containing required email addresses
##################################################
# 3. DB backup result check
##################################################
[gpadmin@mdw ~]$ cd /data/backup
[gpadmin@mdw backup]$ ls -al
total 12
drwxr-xr-x 3 gpadmin gpadmin 4096 Apr 16 22:37 .
drwxr-xr-x 4 gpadmin gpadmin 4096 Apr 16 22:07 ..
drwxrwxr-x 3 gpadmin gpadmin 4096 Apr 16 22:37 db_dumps
[gpadmin@mdw backup]$ cd db_dumps
[gpadmin@mdw db_dumps]$ ls
20190416
[gpadmin@mdw db_dumps]$ cd 20190416/
[gpadmin@mdw 20190416]$ ls -al
total 108
drwxrwxr-x 2 gpadmin gpadmin 4096 Apr 16 22:37 .
drwxrwxr-x 3 gpadmin gpadmin 4096 Apr 16 22:37 ..
-rw------- 1 gpadmin gpadmin 109 Apr 16 22:37 gp_cdatabase_1_1_20190416223703
-rw------- 1 gpadmin gpadmin 37856 Apr 16 22:37 gp_dump_1_1_20190416223703.gz
-rw------- 1 gpadmin gpadmin 811 Apr 16 22:37 gp_dump_1_1_20190416223703_post_data.gz
-rw-rw-r-- 1 gpadmin gpadmin 0 Apr 16 22:37 gp_dump_20190416223703_ao_state_file
-rw-rw-r-- 1 gpadmin gpadmin 0 Apr 16 22:37 gp_dump_20190416223703_co_state_file
-rw-rw-r-- 1 gpadmin gpadmin 0 Apr 16 22:37 gp_dump_20190416223703_last_operation
-rw-rw-r-- 1 gpadmin gpadmin 1502 Apr 16 22:37 gp_dump_20190416223703.rpt
-rw------- 1 gpadmin gpadmin 2399 Apr 16 22:37 gp_dump_status_1_1_20190416223703
-rw-rw-r-- 1 gpadmin gpadmin 1583 Apr 16 22:37 gp_global_1_1_20190416223703
-rw-rw-r-- 1 gpadmin gpadmin 40960 Apr 16 22:37 gp_master_config_files_20190416223703.tar
[gpadmin@mdw 20190416]$ ssh sdw1
Last login: Tue Apr 16 22:05:33 2019 from mdw.domain.com
[gpadmin@sdw1 ~]$ cd /data/backup/db_dumps/
[gpadmin@sdw1 db_dumps]$ ls -al
total 12
drwxrwxr-x 3 gpadmin gpadmin 4096 Apr 16 22:37 .
drwxr-xr-x 3 gpadmin gpadmin 4096 Apr 16 22:37 ..
drwxrwxr-x 2 gpadmin gpadmin 4096 Apr 16 22:37 20190416
[gpadmin@sdw1 db_dumps]$ cd 20190416/
[gpadmin@sdw1 20190416]$ ls -al
total 512
drwxrwxr-x 2 gpadmin gpadmin 4096 Apr 16 22:37 .
drwxrwxr-x 3 gpadmin gpadmin 4096 Apr 16 22:37 ..
-rw------- 1 gpadmin gpadmin 220228 Apr 16 22:37 gp_dump_0_2_20190416223703.gz
-rw------- 1 gpadmin gpadmin 220251 Apr 16 22:37 gp_dump_0_3_20190416223703.gz
-rw------- 1 gpadmin gpadmin 1474 Apr 16 22:37 gp_dump_status_0_2_20190416223703
-rw------- 1 gpadmin gpadmin 1474 Apr 16 22:37 gp_dump_status_0_3_20190416223703
-rw-rw-r-- 1 gpadmin gpadmin 30720 Apr 16 22:37 gp_segment_config_files_0_2_20190416223703.tar
-rw-rw-r-- 1 gpadmin gpadmin 30720 Apr 16 22:37 gp_segment_config_files_0_3_20190416223703.tar
[gpadmin@sdw1 primary]$ exit
logout
Connection to sdw1 closed.
##################################################
# 4. DB drop (issue happen)
##################################################
[gpadmin@mdw 20190416]$ dropdb adw
[gpadmin@mdw 20190416]$ psql
psql (8.2.15)
Type "help" for help.
gpadmin=# \l
List of databases
Name | Owner | Encoding | Access privileges
-----------+---------+----------+---------------------
gpadmin | gpadmin | UTF8 |
gpperfmon | gpadmin | UTF8 | gpadmin=CTc/gpadmin
: =c/gpadmin
postgres | gpadmin | UTF8 |
template0 | gpadmin | UTF8 | =c/gpadmin
: gpadmin=CTc/gpadmin
template1 | gpadmin | UTF8 | =c/gpadmin
: gpadmin=CTc/gpadmin
(5 rows)
gpadmin=# \q
##################################################
# 5. DB restore using gpdbrestore
# options
# -b : look for dumpfile directory in db_dumps/YYYYMMDD
# -u : backup directory
# -e : create target database before restore
##################################################
[gpadmin@mdw 20190416]$ gpdbrestore -b 20190416 -u /data/backup -e
20190416:22:48:56:007171 gpdbrestore:mdw:gpadmin-[INFO]:-Starting gpdbrestore with args: -b 20190416 -u /data/backup -e
20190416:22:48:56:007171 gpdbrestore:mdw:gpadmin-[INFO]:-------------------------------------------
20190416:22:48:56:007171 gpdbrestore:mdw:gpadmin-[INFO]:-Greenplum database restore parameters
20190416:22:48:56:007171 gpdbrestore:mdw:gpadmin-[INFO]:-------------------------------------------
20190416:22:48:56:007171 gpdbrestore:mdw:gpadmin-[INFO]:-Restore type = Full Database
20190416:22:48:56:007171 gpdbrestore:mdw:gpadmin-[INFO]:-Database to be restored = adw
20190416:22:48:56:007171 gpdbrestore:mdw:gpadmin-[INFO]:-Drop and re-create db = On
20190416:22:48:56:007171 gpdbrestore:mdw:gpadmin-[INFO]:-Restore method = Restore specific timestamp
20190416:22:48:56:007171 gpdbrestore:mdw:gpadmin-[INFO]:-Restore method = Restore specific date
20190416:22:48:56:007171 gpdbrestore:mdw:gpadmin-[INFO]:-Restore timestamp = 20190416223703
20190416:22:48:56:007171 gpdbrestore:mdw:gpadmin-[INFO]:-Restore compressed dump = On
20190416:22:48:56:007171 gpdbrestore:mdw:gpadmin-[INFO]:-Restore global objects = Off
20190416:22:48:56:007171 gpdbrestore:mdw:gpadmin-[INFO]:-Array fault tolerance = f
20190416:22:48:56:007171 gpdbrestore:mdw:gpadmin-[INFO]:-------------------------------------------
Continue with Greenplum restore Yy|Nn (default=N):
> y
20190416:22:49:09:007171 gpdbrestore:mdw:gpadmin-[INFO]:-Dropping Database adw
20190416:22:49:09:007171 gpdbrestore:mdw:gpadmin-[INFO]:-Dropped Database adw
20190416:22:49:09:007171 gpdbrestore:mdw:gpadmin-[INFO]:-Invoking sql file: /data/backup/db_dumps/20190416/gp_cdatabase_1_1_20190416223703
20190416:22:49:11:007171 gpdbrestore:mdw:gpadmin-[INFO]:-Creating gp_toolkit schema for database "adw"
20190416:22:49:12:007171 gpdbrestore:mdw:gpadmin-[INFO]:-gp_restore commandline: gp_restore -i -h mdw -p 5432 -U gpadmin --gp-i --gp-k=20190416223703 --gp-l=p --gp-d=/data/backup/db_dumps/20190416 --gp-r=/data/backup/db_dumps/20190416 --status=/data/backup/db_dumps/20190416 --gp-c -d "adw":
20190416:22:49:39:007171 gpdbrestore:mdw:gpadmin-[INFO]:-gpdbrestore finished successfully
20190416:22:49:39:007171 gpdbrestore:mdw:gpadmin-[INFO]:-Updating AO/CO statistics on master
20190416:22:49:39:007171 gpdbrestore:mdw:gpadmin-[INFO]:-No AO/CO tables restored, skipping statistics update...
20190416:22:49:39:007171 gpdbrestore:mdw:gpadmin-[INFO]:-Commencing analyze of adw database, please wait
20190416:22:49:44:007171 gpdbrestore:mdw:gpadmin-[INFO]:-Analyze of adw completed without error
##################################################
# 6. DB restore result check
##################################################
[gpadmin@mdw 20190416]$ psql
psql (8.2.15)
Type "help" for help.
gpadmin=# \l
List of databases
Name | Owner | Encoding | Access privileges
-----------+---------+----------+---------------------
adw | gpadmin | UTF8 |
gpadmin | gpadmin | UTF8 |
gpperfmon | gpadmin | UTF8 | gpadmin=CTc/gpadmin
: =c/gpadmin
postgres | gpadmin | UTF8 |
template0 | gpadmin | UTF8 | =c/gpadmin
: gpadmin=CTc/gpadmin
template1 | gpadmin | UTF8 | =c/gpadmin
: gpadmin=CTc/gpadmin
(6 rows)
gpadmin=# \c adw
You are now connected to database "adw" as user "gpadmin".
adw=# \d
List of relations
Schema | Name | Type | Owner | Storage
--------+-------------------+-------+---------+---------
public | geography_columns | view | gpadmin | none
public | geometry_columns | view | gpadmin | none
public | spatial_ref_sys | table | gpadmin | heap
(3 rows)
adw=# \q
댓글
댓글 쓰기