PostgreSQL 資料庫資料檔案BLOCK一緻性校驗、備份集恢複後的有效性快速校驗 - pg_verify_checksums

标簽

PostgreSQL , checksum , pg_verify_checksums , 備份集校驗 , 恢複校驗

https://github.com/digoal/blog/blob/master/201902/20190213_02.md#%E8%83%8C%E6%99%AF 背景

使用PostgreSQL pitr，資料庫恢複到一個時間點後，這個資料庫的所有BLOCK是否都是一緻的？

資料庫在DOWN機恢複後，資料檔案所有BLOCK是否一緻？

定期抽查資料庫的資料檔案是否BLOCK級一緻？

以上需求如何快速的滿足呢？

PostgreSQL允許使用者開啟block checksum功能，使用pg_verify_checksums工具，可以對整個資料庫或指定的資料檔案進行checksum校驗，確定資料檔案邏輯上一緻。

https://github.com/digoal/blog/blob/master/201902/20190213_02.md#pg_verify_checksums-%E6%A0%A1%E9%AA%8C%E6%95%B0%E6%8D%AE%E5%9D%97%E4%B8%80%E8%87%B4%E6%80%A7 pg_verify_checksums 校驗資料塊一緻性

1、停庫，目前不支援OPEN狀态下的校驗。

2、使用pg_verify_checksums校驗

pg_verify_checksums verifies data checksums in a PostgreSQL database cluster.  
  
Usage:  
  pg_verify_checksums [OPTION]... [DATADIR]  
  
Options:  
 [-D, --pgdata=]DATADIR  data directory  
  -v, --verbose          output verbose messages  
  -r RELFILENODE         check only relation with specified relfilenode  
  -V, --version          output version information, then exit  
  -?, --help             show this help, then exit  
  
If no data directory (DATADIR) is specified, the environment variable PGDATA  
is used.  
  
Report bugs to <[email protected]>.  
           

pg_verify_checksums -D /data01/digoal/pg_root8009  
Checksum scan completed  
Data checksum version: 1  
Files scanned:  932  
Blocks scanned: 2909  
Bad checksums:  0  
           

3、目前pg_verify_checksums識别到錯誤會直接退出程式

pg_verify_checksums -D /data01/digoal/pg_root8009   
pg_verify_checksums: could not read block 0 in file "/data01/digoal/pg_root8009/base/13285/13120_fsm": read 1023 of 8192  
           

static void  
scan_file(const char *fn, BlockNumber segmentno)  
{  
        PGAlignedBlock buf;  
        PageHeader      header = (PageHeader) buf.data;  
        int                     f;  
        BlockNumber blockno;  
  
        f = open(fn, O_RDONLY | PG_BINARY);  
        if (f < 0)  
        {  
                fprintf(stderr, _("%s: could not open file \"%s\": %s\n"),  
                                progname, fn, strerror(errno));  
                exit(1);  
        }  
  
        files++;  
  
        for (blockno = 0;; blockno++)  
        {  
                uint16          csum;  
                int                     r = read(f, buf.data, BLCKSZ);  
  
                if (r == 0)  
                        break;  
                if (r != BLCKSZ)  
                {  
                        fprintf(stderr, _("%s: could not read block %u in file \"%s\": read %d of %d\n"),  
                                        progname, blockno, fn, r, BLCKSZ);  
                        exit(1);  
                }  
                blocks++;  
  
                /* New pages have no checksum yet */  
                if (PageIsNew(header))  
                        continue;  
  
                csum = pg_checksum_page(buf.data, blockno + segmentno * RELSEG_SIZE);  
                if (csum != header->pd_checksum)  
                {  
                        if (ControlFile->data_checksum_version == PG_DATA_CHECKSUM_VERSION)  
                                fprintf(stderr, _("%s: checksum verification failed in file \"%s\", block %u: calculated checksum %X but block contains %X\n"),  
                                                progname, fn, blockno, csum, header->pd_checksum);  
                        badblocks++;  
                }  
        }  
  
        if (verbose)  
                fprintf(stderr,  
                                _("%s: checksums verified in file \"%s\"\n"), progname, fn);  
  
        close(f);  
}  
           

如果期望掃描完所有檔案，并将所有有錯誤的檔案列印出來，需要修改一下pg_verify_checksums的代碼

https://github.com/digoal/blog/blob/master/201902/20190213_02.md#%E6%B3%A8%E6%84%8F 注意

版本要求，PostgreSQL 11以上。

低于11的版本，需要将pg_verify_checksums的功能向下PORT一下。

https://github.com/digoal/blog/blob/master/201902/20190213_02.md#%E5%8F%82%E8%80%83 參考

《PostgreSQL 11 preview - Allow on-line enabling and disabling of data checksums (含pg_verify_checksums工具，離線檢查資料檔案有誤塊錯誤)》 https://www.postgresql.org/docs/11/pgverifychecksums.html

https://github.com/digoal/blog/blob/master/201902/20190213_02.md#%E5%85%8D%E8%B4%B9%E9%A2%86%E5%8F%96%E9%98%BF%E9%87%8C%E4%BA%91rds-postgresql%E5%AE%9E%E4%BE%8Becs%E8%99%9A%E6%8B%9F%E6%9C%BA 免費領取阿裡雲RDS PostgreSQL執行個體、ECS虛拟機