PostgreSQL 最佳实践 - 块级增量备份(ZFS篇)单个数据库采用多个zfs卷(如表空间)时如何一致性备份

6 minute read





基于文件的物理备份,为了保证备份的一致性,在备份开始时,需要做一个检查点,同时打开FULL PAGE WRTIE,同时还会生成backup_label文件记录备份开始时的WAL文件,检查点位置等信息。


START WAL LOCATION: 42/EB000060 (file 0000000100000042000000EB)      
BACKUP METHOD: pg_start_backup      
BACKUP FROM: master      
START TIME: 2016-08-23 19:43:32 CST      
LABEL: a      

同时backup_label也指出了当前备份是从主节点还是从 standby备份的




 * read_backup_label: check to see if a backup_label file is present      
 * If we see a backup_label during recovery, we assume that we are recovering      
 * from a backup dump file, and we therefore roll forward from the checkpoint      
 * identified by the label file, NOT what pg_control says.  This avoids the      
 * problem that pg_control might have been archived one or more checkpoints      
 * later than the start of the dump, and so if we rely on it as the start      
 * point, we will fail to restore a consistent database state.      
 * Returns TRUE if a backup_label was found (and fills the checkpoint      
 * location and its REDO location into *checkPointLoc and RedoStartLSN,      
 * respectively); returns FALSE if not. If this backup_label came from a      
 * streamed backup, *backupEndRequired is set to TRUE. If this backup_label      
 * was created during recovery, *backupFromStandby is set to TRUE.      
static bool      
read_backup_label(XLogRecPtr *checkPointLoc, bool *backupEndRequired,      
                                  bool *backupFromStandby)      
        char            startxlogfilename[MAXFNAMELEN];      
        TimeLineID      tli;      
        FILE       *lfp;      
        char            ch;      
        char            backuptype[20];      
        char            backupfrom[20];      
        uint32          hi,      
        *backupEndRequired = false;      
        *backupFromStandby = false;      
         * See if label file is present      
        lfp = AllocateFile(BACKUP_LABEL_FILE, "r");      
        if (!lfp)      
                if (errno != ENOENT)      
                                         errmsg("could not read file \"%s\": %m",      
                return false;                   /* it's not there, all is fine */      
         * Read and parse the START WAL LOCATION and CHECKPOINT lines (this code      
         * is pretty crude, but we are not expecting any variability in the file      
         * format).      
        if (fscanf(lfp, "START WAL LOCATION: %X/%X (file %08X%16s)%c",      
                           &hi, &lo, &tli, startxlogfilename, &ch) != 5 || ch != '\n')      
                                 errmsg("invalid data in file \"%s\"", BACKUP_LABEL_FILE)));      
        RedoStartLSN = ((uint64) hi) << 32 | lo;      
        if (fscanf(lfp, "CHECKPOINT LOCATION: %X/%X%c",      
                           &hi, &lo, &ch) != 3 || ch != '\n')      
                                 errmsg("invalid data in file \"%s\"", BACKUP_LABEL_FILE)));      
        *checkPointLoc = ((uint64) hi) << 32 | lo;      
         * BACKUP METHOD and BACKUP FROM lines are new in 9.2. We can't restore      
         * from an older backup anyway, but since the information on it is not      
         * strictly required, don't error out if it's missing for some reason.      
        if (fscanf(lfp, "BACKUP METHOD: %19s\n", backuptype) == 1)      
                if (strcmp(backuptype, "streamed") == 0)      
                        *backupEndRequired = true;      
        if (fscanf(lfp, "BACKUP FROM: %19s\n", backupfrom) == 1)      
                if (strcmp(backupfrom, "standby") == 0)      
                        *backupFromStandby = true;      
        if (ferror(lfp) || FreeFile(lfp))      
                                 errmsg("could not read file \"%s\": %m",      
        return true;      



 * There are two kind of backups: exclusive and non-exclusive. An exclusive      
 * backup is started with pg_start_backup(), and there can be only one active      
 * at a time. The backup and tablespace map files of an exclusive backup are      
 * written to $PGDATA/backup_label and $PGDATA/tablespace_map, and they are      
 * removed by pg_stop_backup().      
 * A non-exclusive backup is used for the streaming base backups (see      
 * src/backend/replication/basebackup.c). The difference to exclusive backups      
 * is that the backup label and tablespace map files are not written to disk.      
 * Instead, their would-be contents are returned in *labelfile and *tblspcmapfile,      
 * and the caller is responsible for including them in the backup archive as      
 * 'backup_label' and 'tablespace_map'. There can be many non-exclusive backups      
 * active at the same time, and they don't conflict with an exclusive backup      
 * either.      
 * tblspcmapfile is required mainly for tar format in windows as native windows      
 * utilities are not able to create symlinks while extracting files from tar.      
 * However for consistency, the same is used for all platforms.      
 * needtblspcmapfile is true for the cases (exclusive backup and for      
 * non-exclusive backup only when tar format is used for taking backup)      
 * when backup needs to generate tablespace_map file, it is used to      
 * embed escape character before newline character in tablespace path.      
 * Returns the minimum WAL position that must be present to restore from this      
 * backup, and the corresponding timeline ID in *starttli_p.      
 * Every successfully started non-exclusive backup must be stopped by calling      
 * do_pg_stop_backup() or do_pg_abort_backup().      
 * It is the responsibility of the caller of this function to verify the      
 * permissions of the calling user!      



do_pg_start_backup(const char *backupidstr, bool fast, TimeLineID *starttli_p,      
                                   char **labelfile, DIR *tblspcdir, List **tablespaces,      
                                   char **tblspcmapfile, bool infotbssize,      
                                   bool needtblspcmapfile)      
        bool            exclusive = (labelfile == NULL);      
         * Currently only non-exclusive backup can be taken during recovery.      
        if (backup_started_in_recovery && exclusive)      
                                 errmsg("recovery is in progress"),      
                                 errhint("WAL control functions cannot be executed during recovery.")));      




        startptr = do_pg_start_backup(opt->label, opt->fastcheckpoint, &starttli,      
                                                                  &labelfile, tblspcdir, &tablespaces,      
                                                                  opt->progress, opt->sendtblspcmapfile);      


                        if (ti->path == NULL)      
                                struct stat statbuf;      
                                /* In the main tar, include the backup_label first... */      
                                sendFileWithContent(BACKUP_LABEL_FILE, labelfile);      
                                 * Send tablespace_map file if required and then the bulk of      
                                 * the files.      
                                if (tblspc_map_file && opt->sendtblspcmapfile)      
                                        sendFileWithContent(TABLESPACE_MAP, tblspc_map_file);      
                                        sendDir(".", 1, false, tablespaces, false);      
                                        sendDir(".", 1, false, tablespaces, true);      
                                /* ... and pg_control after everything else. */      
                                if (lstat(XLOG_CONTROL_FILE, &statbuf) != 0)      
                                                         errmsg("could not stat control file \"%s\": %m",      
                                sendFile(XLOG_CONTROL_FILE, XLOG_CONTROL_FILE, &statbuf, false);      
                                sendTablespace(ti->path, false);      



zfs快照, 如何保证多个zfs文件系统的一致性





回到我写的《PostgreSQL 最佳实践 - 块级增量备份(ZFS篇)方案与实战》

以上案例中一个standby集群用的是一个ZFS(所有表空间, 以及$PGDATA), 当数据库集群使用多个ZFS时, 因为快照只能基于单个volume或filesystem产生, 所以当我们使用了多个zfs filesystem是要得到数据库一致的备份, 怎么办呢?

例如 :

1. $PGDATA使用文件系统 zfs/pg_root

2. tbs1使用文件系统 zfs/tbs1

3. tbs2使用文件系统 zfs/tbs2

4. pg_xlog使用文件系统 zfs/pg_xlog

要使用zfs snapshot来备份这个PostgreSQL, 应该怎么做?


首选创建控制文件所在zfs的快照, 再创建其他文件系统的快照.

pg_xlog的快照可以不创建, 而通过归档来恢复.

那么就比较清晰了, 以上快照的步骤如下 :

STIME=`date +%F%T`      
1. zfs snapshot zp1/pg_root@$STIME      
2. zfs snapshot zp1/tbs1@$STIME      
3. zfs snapshot zp1/tbs2@$STIME      




某数据库的流复制standby因为主库产生的XLOG过多, 延迟后触发了recovery.conf的restore命令, 但是restore里面用了sudo 进行copy并未保持源文件的owner, 属性等特征, 使用了root owner, 导致COPY完的xlog不能被postgresql 正常读取.

同事在处理这个事情, 因为没有了解实际情况, 上去就使用了pg_resetxlog修改standby的nextXID(resetxlog在这个场景属于扯淡的操作, 千万不要这么干), 接着大家知道的, standby无法正常完成standby的工作了.

这个数据库有10几个T, 要重新做standby的话, 拷贝的数据量太大了, 即使使用rsync重做, 工作量也比较大(表空间过多, 目录过多).



1\. xlog不能被postgresql 正常读取(这个很好修复, 修改一下restore command就可以了, 或者不要用sudo 来拷贝).    
2014-11-30 10:05:54.505 CST,,,3695,,5435f48b.e6f,6,,2014-10-09 10:35:55 CST,1/0,0,LOG,00000,"restored log file ""0000000300002A9D0000004D"" from archive",,,,,,,,"RestoreArchivedFile, xlogarchive.c:254",""    
2014-11-30 10:05:54.534 CST,,,3695,,5435f48b.e6f,7,,2014-10-09 10:35:55 CST,1/0,0,PANIC,42501,"could not open file ""pg_xlog/0000000300002A9D0000004D"": Permission denied",,,,,,,,"XLogFileRead, xlog.c:2696","" ```  
    2\. pg_resetxlog对控制文件造成了持久性伤害, 没有办法修复.    
 pg_resetxlog -x 90010254 -f $PGDATA     ```  
为了快速恢复, 可以找到更早的控制文件, 刚好这套数据库的备份系统使用的是ZFS快照做的, 每天会创建一个快照, 所以可以取早些时间的快照, 拿到控制文件, 替换掉standby被人为"破坏"的控制文件, 启动standby后, 将从控制文件开始恢复需要的xlog. (只要这些xlog归档还在就可以了).    

操作过程 :     

1\. shutdown standby    

2\. mount zfs snapshot old then standby crashed time.    

3\. copy $PGDATA/global/pg_control from snapshot to standby.    
4\. startup standby.    
5\. umount snapshot.    
注意, 拿到的控制文件必须是standby crash之前的控制文件, 并且控制文件至今的所有xlog归档必须都在. postgresql standby将从控制文件需要的xlog开始恢复数据块. 所以这么做是完全可以的.    
  882 postgres  20   0 2337m  15m  14m S  0.0  0.1   0:00.10 /opt/pgsql9.3.2/bin/postgres                                               
  883 postgres  20   0  157m 1072  476 S  0.0  0.0   0:00.14 postgres: logger process                                                   
  884 postgres  20   0 2338m 2.0g 2.0g S  0.0  8.6   5:16.35 postgres: startup process   recovering 0000000300002AA50000003F            
  889 postgres  20   0 2338m 2.0g 2.0g S  0.0  8.6   0:35.69 postgres: checkpointer process                                             
  890 postgres  20   0 2338m 2.0g 2.0g S  0.0  8.6   0:34.98 postgres: writer process                                                   
  905 postgres  20   0  159m 1212  456 S  0.0  0.0   0:02.62 postgres: stats collector process                                          
 1922 postgres  20   0  105m 1632 1180 S  0.0  0.0   0:00.01 -bash                                                                      
 1959 postgres  20   0  137m 1764 1292 S  0.0  0.0   0:00.00 psql                                                                       
 1960 postgres  20   0 2340m 6780 4684 S  0.0  0.0   0:00.01 postgres: postgres postgres [local] idle                                   
 4309 postgres  20   0 2346m 7080 1748 S  0.0  0.0   0:00.67 postgres: wal receiver process   streaming 2AA5/3F0429C0     

Flag Counter

digoal’s 大量PostgreSQL文章入口