Greenplum的全量备份介绍, gpcrondump

# Replace with symlink path if it is present and correct if [ -h ${GPHOME}/../greenplum-db ]; then GPHOME_BY_SYMLINK=`(cd ${GPHOME}/../greenplum-db/ pwd -P)` if [ x"${GPHOME_BY_SYMLINK}" = x"${GPHOME}" ]; then GPHOME=`(cd ${GPHOME}/../greenplum-db/ pwd -L)`/. unset GPHOME_BY_SYMLINK #setup PYTHONHOME if [ -x $GPHOME/ext/python/bin/python ]; then PYTHONHOME="$GPHOME/ext/python" PYTHONPATH=$GPHOME/lib/python PATH=$GPHOME/bin:$PYTHONHOME/bin:$PATH LD_LIBRARY_PATH=$GPHOME/lib:$PYTHONHOME/lib:$LD_LIBRARY_PATH OPENSSL_CONF=$GPHOME/etc/openssl.cnf export GPHOME export PATH export LD_LIBRARY_PATH export PYTHONPATH export PYTHONHOME export OPENSSL_CONF export MASTER_DATA_DIRECTORY=/data01/digoal/gpdatalocal/gpseg-1 export PGHOST=127.0.0.1 export PGPORT=1922 export PGUSER=digoal export PGDATABASE=postgres export PGPASSWORD=digoal
dat=`psql -A -q -t -h $PGHOST -p $PGPORT -U $PGUSER -c "select -x ||string_agg(datname, -x ) from pg_database where datname template0"` gpcrondump -a -C --dump-stats -g -G -h -r --use-set-session-authorization $dat -u $backupdir --prefix $dbid -l $logdir -d $masterdir

或者

backupdir="/data01/digoal/gpbackup"

logdir=$backupdir

masterdir="/data01/digoal/gpdatalocal/gpseg-1"

dbid="digoal"

for dbname in `psql -A -q -t -h $PGHOST -p $PGPORT -U $PGUSER -c "select datname from pg_database where datname template0"`

now=`date +%Y%m%d%H%M%S`

gpcrondump -a -C --dump-stats -g -G -h -r --use-set-session-authorization -x $dbname -u $backupdir --prefix $dbid -l $logdir -d $masterdir -K $now

done

gpcrondump会检查-K提供的时间戳，如果该时间对应的YYYYMMDD目录中存在比这个时间更未来的备份，则报错。 因此，不同的数据库不能使用同一个时间戳来备份。

$for dbname in `psql -A -q -t -h $PGHOST -p $PGPORT -U $PGUSER -c "select datname from pg_database where datname template0"`

 gpcrondump -a -C --dump-stats -g -G -h -r --use-set-session-authorization -x $dbname -u $backupdir --prefix $dbid -l $logdir -d $masterdir -K $now

 done

20160416:17:25:55:016061 gpcrondump:db153175032:digoal-[INFO]:-Starting gpcrondump with args: -a -C --dump-stats -g -G -h -r --use-set-session-authorization -x digoal -u /data01/digoal/gpbackup --prefix digoal -l /data01/digoal/gpbackup -d /data01/digoal/gpdatalocal/gpseg-1 -K 20160416171907

20160416:17:25:55:016061 gpcrondump:db153175032:digoal-[CRITICAL]:-gpcrondump failed. (Reason=There is a future dated backup on the system preventing new backups) exiting...

20160416:17:25:55:016151 gpcrondump:db153175032:digoal-[INFO]:-Starting gpcrondump with args: -a -C --dump-stats -g -G -h -r --use-set-session-authorization -x template1 -u /data01/digoal/gpbackup --prefix digoal -l /data01/digoal/gpbackup -d /data01/digoal/gpdatalocal/gpseg-1 -K 20160416171907

20160416:17:25:55:016151 gpcrondump:db153175032:digoal-[CRITICAL]:-gpcrondump failed. (Reason=There is a future dated backup on the system preventing new backups) exiting...

20160416:17:25:55:016241 gpcrondump:db153175032:digoal-[INFO]:-Starting gpcrondump with args: -a -C --dump-stats -g -G -h -r --use-set-session-authorization -x postgres -u /data01/digoal/gpbackup --prefix digoal -l /data01/digoal/gpbackup -d /data01/digoal/gpdatalocal/gpseg-1 -K 20160416171907

20160416:17:25:55:016241 gpcrondump:db153175032:digoal-[CRITICAL]:-gpcrondump failed. (Reason=There is a future dated backup on the system preventing new backups) exiting...

20160416:17:25:55:016331 gpcrondump:db153175032:digoal-[INFO]:-Starting gpcrondump with args: -a -C --dump-stats -g -G -h -r --use-set-session-authorization -x db2 -u /data01/digoal/gpbackup --prefix digoal -l /data01/digoal/gpbackup -d /data01/digoal/gpdatalocal/gpseg-1 -K 20160416171907

20160416:17:25:55:016331 gpcrondump:db153175032:digoal-[CRITICAL]:-gpcrondump failed. (Reason=There is a future dated backup on the system preventing new backups) exiting...

20160416:17:25:55:016421 gpcrondump:db153175032:digoal-[INFO]:-Starting gpcrondump with args: -a -C --dump-stats -g -G -h -r --use-set-session-authorization -x db3 -u /data01/digoal/gpbackup --prefix digoal -l /data01/digoal/gpbackup -d /data01/digoal/gpdatalocal/gpseg-1 -K 20160416171907

20160416:17:25:55:016421 gpcrondump:db153175032:digoal-[CRITICAL]:-gpcrondump failed. (Reason=There is a future dated backup on the system preventing new backups) exiting...

20160416:17:25:56:016511 gpcrondump:db153175032:digoal-[INFO]:-Starting gpcrondump with args: -a -C --dump-stats -g -G -h -r --use-set-session-authorization -x db1 -u /data01/digoal/gpbackup --prefix digoal -l /data01/digoal/gpbackup -d /data01/digoal/gpdatalocal/gpseg-1 -K 20160416171907

20160416:17:25:56:016511 gpcrondump:db153175032:digoal-[CRITICAL]:-gpcrondump failed. (Reason=There is a future dated backup on the system preventing new backups) exiting...

备份日志输出到

/data01/digoal/gpbackup

备份数据，自动生成子目录，输出到

/data01/digoal/gpbackup/db_dumps/$YYYYMMDD

每个数据库中都会记录对应数据库的备份历史信息。

postgres=# select * from gpcrondump_history ;

-[ RECORD 14 ]-----+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

rec_date | 2016-04-16 15:37:02.364166

start_time | 15:33:19

end_time | 15:37:00

options | -a --dump-stats -g -G -h -r -x digoal -x template1 -x postgres -x db2 -x db3 -x db1 -u /data01/digoal/gpbackup --prefix digoal -l /data01/digoal/gpbackup -d /data01/digoal/gpdatalocal/gpseg-1

dump_key | 20160416153319 -- 备份开始时间戳, 使用gpdbrestore进行恢复时，要用到这个KEY

dump_exit_status | 0

script_exit_status | 0

exit_text | COMPLETED

在gpcrondump标准输出的信息中，也包含了dump key

20160416:16:37:00:020201 gpcrondump:db153175032:digoal-[INFO]:-Target database = digoal

20160416:16:37:00:020201 gpcrondump:db153175032:digoal-[INFO]:-Dump subdirectory = 20160416

20160416:16:37:00:020201 gpcrondump:db153175032:digoal-[INFO]:-Dump type = Full database

20160416:16:37:00:020201 gpcrondump:db153175032:digoal-[INFO]:-Clear old dump directories = Off

20160416:16:37:00:020201 gpcrondump:db153175032:digoal-[INFO]:-Dump start time = 16:36:55

20160416:16:37:00:020201 gpcrondump:db153175032:digoal-[INFO]:-Dump end time = 16:36:59

20160416:16:37:00:020201 gpcrondump:db153175032:digoal-[INFO]:-Status = COMPLETED

20160416:16:37:00:020201 gpcrondump:db153175032:digoal-[INFO]:-Dump key = 20160416163655

20160416:16:37:00:020201 gpcrondump:db153175032:digoal-[INFO]:-Dump file compression = On

20160416:16:37:00:020201 gpcrondump:db153175032:digoal-[INFO]:-Vacuum mode type = Off

20160416:16:37:00:020201 gpcrondump:db153175032:digoal-[INFO]:-Exit code zero, no warnings generated

以上digoal库，对应的备份文件：

$cd /data01/digoal/gpbackup/db_dumps/20160416

$ll *20160416163655*

-rw------- 1 digoal users 113 Apr 16 16:36 digoal_gp_cdatabase_1_1_20160416163655

-rw------- 1 digoal users 3.2K Apr 16 16:36 digoal_gp_dump_0_2_20160416163655.gz

-rw------- 1 digoal users 3.3K Apr 16 16:36 digoal_gp_dump_0_3_20160416163655.gz

-rw------- 1 digoal users 3.4K Apr 16 16:36 digoal_gp_dump_0_4_20160416163655.gz

-rw------- 1 digoal users 3.3K Apr 16 16:36 digoal_gp_dump_0_5_20160416163655.gz

-rw------- 1 digoal users 3.4K Apr 16 16:36 digoal_gp_dump_0_6_20160416163655.gz

-rw------- 1 digoal users 3.3K Apr 16 16:36 digoal_gp_dump_0_7_20160416163655.gz

-rw------- 1 digoal users 3.4K Apr 16 16:36 digoal_gp_dump_0_8_20160416163655.gz

-rw------- 1 digoal users 3.3K Apr 16 16:36 digoal_gp_dump_0_9_20160416163655.gz

-rw------- 1 digoal users 889 Apr 16 16:36 digoal_gp_dump_1_1_20160416163655.gz

-rw------- 1 digoal users 196 Apr 16 16:36 digoal_gp_dump_1_1_20160416163655_post_data.gz

-rw-r--r-- 1 digoal users 0 Apr 16 16:36 digoal_gp_dump_20160416163655_ao_state_file

-rw-r--r-- 1 digoal users 0 Apr 16 16:36 digoal_gp_dump_20160416163655_co_state_file

-rw-r--r-- 1 digoal users 0 Apr 16 16:36 digoal_gp_dump_20160416163655_last_operation

-rw-r--r-- 1 digoal users 2.3K Apr 16 16:36 digoal_gp_dump_20160416163655.rpt

-rw------- 1 digoal users 1.3K Apr 16 16:36 digoal_gp_dump_status_0_2_20160416163655

-rw------- 1 digoal users 1.3K Apr 16 16:36 digoal_gp_dump_status_0_3_20160416163655

-rw------- 1 digoal users 1.3K Apr 16 16:36 digoal_gp_dump_status_0_4_20160416163655

-rw------- 1 digoal users 1.3K Apr 16 16:36 digoal_gp_dump_status_0_5_20160416163655

-rw------- 1 digoal users 1.3K Apr 16 16:36 digoal_gp_dump_status_0_6_20160416163655

-rw------- 1 digoal users 1.3K Apr 16 16:36 digoal_gp_dump_status_0_7_20160416163655

-rw------- 1 digoal users 1.3K Apr 16 16:36 digoal_gp_dump_status_0_8_20160416163655

-rw------- 1 digoal users 1.3K Apr 16 16:36 digoal_gp_dump_status_0_9_20160416163655

-rw------- 1 digoal users 2.3K Apr 16 16:36 digoal_gp_dump_status_1_1_20160416163655

-rw-r--r-- 1 digoal users 1.0K Apr 16 16:37 digoal_gp_global_1_1_20160416163655

-rw-r--r-- 1 digoal users 4.8K Apr 16 16:36 digoal_gp_statistics_1_1_20160416163655

下一节讲一下增量备份.

Greenplum gpcrondump存在的BUG：
.1. 不支持指定备份用的超级用户名,默认在gpcrondump中会让pg_dump去调用OS对应的用户名.
.2. 备份language handler, create database的DDL时，没有使用双引号引用。 如果用户名包含除小写字母和下划线以外的字符，在还原是会报错。

可能还有其他地方有类似的BUG。 

.3. 不支持删除模板库，在使用gpdbrestore恢复时，如果使用了-e来清除库，会导致失败。
.4. copy需要大量的内存，可能触发OOM。
.5. 执行gpcrondump时，会使用getcwd获得当前目录，所以不能在一个不存在的目录环境下执行.

gpcrondump 以上用到的参数解释

**********************

Return Codes

**********************

The following is a list of the codes that gpcrondump returns.

 0 - Dump completed with no problems

 1 - Dump completed, but one or more warnings were generated

 2 - Dump failed with a fatal error

-a (do not prompt) 

 Do not prompt the user for confirmation. 

-d master_data_directory 

 The master host data directory. If not specified, the value set for 

 $MASTER_DATA_DIRECTORY will be used. 

--dump-stats

 Dump optimizer statistics from pg_statistic. Statistics are dumped in the

 master data directory to db_dumps/YYYYMMDD/gp_statistics_1_1_ timestamp .

-g (copy config files) 

 Secure a copy of the master and segment configuration files 

 postgresql.conf, pg_ident.conf, and pg_hba.conf. These configuration 

 files are dumped in the master or segment data directory to 

 db_dumps/YYYYMMDD/config_files_ timestamp .tar. 

 If --ddboost is specified, the backup is located on the default storage 

 unit in the directory specified by --ddboost-backupdir when the Data 

 Domain Boost credentials were set.

-G (dump global objects) 

 Use pg_dumpall to dump global objects such as roles and tablespaces. 

 Global objects are dumped in the master data directory to 

 db_dumps/YYYYMMDD/gp_global_1_1_ timestamp . 

-h (record dump details) 

 Record details of database dump in database table 

 public.gpcrondump_history in database supplied via -x option. Utility 

 will create table if it does not currently exist. 

--incremental (backup changes to append-optimized tables)

 Adds an incremental backup to a backup set. When performing an 

 incremental backup, the complete backup set created prior to the 

 incremental backup must be available. The complete backup set includes 

 the following backup files: 

 * The last full backup before the current incremental backup 

 * All incremental backups created between the time of the full backup 

 the current incremental backup 

 An incremental backup is similar to a full back up except for 

 append-optimized tables, including column-oriented tables. An 

 append-optimized table is backed up only if at least one of the 

 following operations was performed on the table after the last backup. 

 ALTER TABLE 

 INSERT 

 UPDATE

 DELETE

 TRUNCATE 

 DROP and then re-create the table

 For partitioned append-optimized tables, only the changed table 

 partitions are backed up. 

 The -u option must be used consistently within a backup set that 

 includes a full and incremental backups. If you use the -u option with a 

 full backup, you must use the -u option when you create incremental 

 backups that are part of the backup set that includes the full backup. 

 You can create an incremental backup for a full backup of set of 

 database tables. When you create the full backup, specify the --prefix 

 option to identify the backup. To include a set of tables in the full 

 backup, use either the -t option or --table-file option. To exclude a 

 set of tables, use either the -T option or the --exclude-table-file 

 option. See the description of the option for more information on its 

 use. 

 To create an incremental backup based on the full backup of the set of 

 tables, specify the option --incremental and the --prefix option with 

 the string specified when creating the full backup. The incremental 

 backup is limited to only the tables in the full backup. 

 WARNING: gpcrondump does not check for available disk space prior to 

 performing an incremental backup.

 IMPORTANT: An incremental back up set, a full backup and associated 

 incremental backups, must be on a single device. For example, a the 

 backups in a backup set must all be on a file system or must all be on a 

 Data Domain system. 

--prefix prefix_string [--list-filter-tables ]

 Prepends prefix_string followed by an underscore character (_) to the 

 names of all the backup files created during a backup. 

-r (rollback on failure) 

 Rollback the dump files (delete a partial dump) if a failure is 

 detected. The default is to not rollback. 

-u backup_directory 

 Specifies the absolute path where the backup files will be placed on 

 each host. If the path does not exist, it will be created, if possible. 

 If not specified, defaults to the data directory of each instance to be 

 backed up. Using this option may be desirable if each segment host has 

 multiple segment instances as it will create the dump files in a 

 centralized location rather than the segment data directories. 

 Note: This option is not supported if --ddboost is specified. 

--use-set-session-authorization 

 Use SET SESSION AUTHORIZATION commands instead of ALTER OWNER commands 

 to set object ownership. 

-x database_name 

 Required. The name of the Greenplum database to dump. Specify multiple times for 

 multiple databases. 

yugong阿里巴巴去Oracle数据迁移同步工具(全量+增量,目标支持MySQL/DRDS) 2008年，阿里巴巴开始尝试使用 MySQL 支撑其业务，开发了围绕 MySQL 相关的中间件和工具，Cobar/TDDL(目前为阿里云DRDS产品)，解决了单机 Oracle 无法满足的扩展性问题，当时也掀起一股去IOE项目的浪潮，愚公这项目因此而诞生，其要解决的目标就是帮助用户完成从 Oracle 数据迁移到 MySQL 上，完成去 IOE 的重要一步工作。
备份！备份！备份！ 看阿里云HBase的企业级备份恢复如何设计 数据安全是生命线，一线人员辛苦一年的努力可能因为一时疏忽而废，升职加薪无望；一个公司或者团队辛苦几年的打拼可能一夜回到解放前。所以面对重要的事情要说三遍：备份！备份！备份！
MongoShake全量迁移功能 从v1.5版本开始，MongoShake新增了全量迁移功能，该功能可以让用户更快地对数据量较大的MongoDB数据库做数据复制。MongoShake不再需要源数据库保留全部的oplog，只要依赖当前的oplog就能做数据同步复制。
数据库异地备份及不还原快速查询备份集最佳实践 传统数据库异地备份和查询中，有两个大的痛点，一个是备份数据集的管理，另外一个是备份数据的查询，本方案将通过阿里云DBS、OSS、DLA的组合，搭建一整套数据库本地/异地自动化备份和管理的方案。在备份的基础上，实现分钟级全备数据集的查询，节省大量数据库还原时间。
DTS增量/同步支持DDL迁移的说明     DTS目前并不支持所有数据库类型时间的DDL迁移,特别是异构数据库之间的迁移.不得不承认DDL的迁移是非常的,因为这涉及到DDL的解析(DDL的过滤)及转换(库表列映射及异构数据库).毕竟DDL是一个高危操作,稍有不慎就肯能造成不可恢复的故障.