异版本pg_resetxlog后导致的控制文件差异问题处理

6 minute read

背景

数据库的redo日志损坏时,或者控制文件损坏时,可能导致数据库无法启动。

如果存放pg_xlog或者pg_control文件的块设备遇到问题,可能引发这种情况。

遇到xlog或者控制文件损坏的时候,怎么处理呢?

数据库正常关闭时会写控制文件,redo是在数据库crash后需要用来恢复数据库的,如果数据库正常的关闭,实际上不需要从redo恢复。

PostgreSQL 提供了一个工具,用来生成或改写控制文件,抹除指定的pg_xlog。

在数据库因为控制文件损坏,或者pg_xlog损坏,导致数据库不能正常启动时使用。

使用后,数据库起来之后,请务必逻辑导出后再导入一个新的集群。

导出时最好设置跳过错误的块(设置zero_damaged_pages=true),因为这种情况下十有八九块会损坏。

同时,由于pg_resetxlog会改写控制文件,如果你使用的pg_resetxlog和数据库的版本不一致,会导致生成的控制文件版本跟随pg_resetxlog的版本,导致后面一系列的问题。

控制文件的内容

查看控制文件的头文件,可以了解到控制文件的内容定义

src/include/catalog/pg_control.h  

控制文件的版本

/* Version identifier for this pg_control format */  
#define PG_CONTROL_VERSION      942  

控制文件的检查点信息

/*  
 * Body of CheckPoint XLOG records.  This is declared here because we keep  
 * a copy of the latest one in pg_control for possible disaster recovery.  
 * Changing this struct requires a PG_CONTROL_VERSION bump.  
 */  
typedef struct CheckPoint  
{  
        XLogRecPtr      redo;                   /* next RecPtr available when we began to  
                                                                 * create CheckPoint (i.e. REDO start point) */  
        TimeLineID      ThisTimeLineID; /* current TLI */  
        TimeLineID      PrevTimeLineID; /* previous TLI, if this record begins a new  
                                                                 * timeline (equals ThisTimeLineID otherwise) */  
        bool            fullPageWrites; /* current full_page_writes */  
        uint32          nextXidEpoch;   /* higher-order bits of nextXid */  
        TransactionId nextXid;          /* next free XID */  
        Oid                     nextOid;                /* next free OID */  
        MultiXactId nextMulti;          /* next free MultiXactId */  
        MultiXactOffset nextMultiOffset;        /* next free MultiXact offset */  
        TransactionId oldestXid;        /* cluster-wide minimum datfrozenxid */  
        Oid                     oldestXidDB;    /* database with minimum datfrozenxid */  
        MultiXactId oldestMulti;        /* cluster-wide minimum datminmxid */  
        Oid                     oldestMultiDB;  /* database with minimum datminmxid */  
        pg_time_t       time;                   /* time stamp of checkpoint */  
        TransactionId oldestCommitTsXid;        /* oldest Xid with valid commit  
                                                                                 * timestamp */  
        TransactionId newestCommitTsXid;        /* newest Xid with valid commit  
                                                                                 * timestamp */  
  
        /*  
         * Oldest XID still running. This is only needed to initialize hot standby  
         * mode from an online checkpoint, so we only bother calculating this for  
         * online checkpoints and only when wal_level is hot_standby. Otherwise  
         * it's set to InvalidTransactionId.  
         */  
        TransactionId oldestActiveXid;  
} CheckPoint;  

控制文件数据

/*  
 * Contents of pg_control.  
 *  
 * NOTE: try to keep this under 512 bytes so that it will fit on one physical  
 * sector of typical disk drives.  This reduces the odds of corruption due to  
 * power failure midway through a write.  
 */  
  
typedef struct ControlFileData  
{  
...  
} ControlFileData;  

通过pg_controldata命令则可以输出控制文件的内容,源码如下。

src/bin/pg_controldata/pg_controldata.c

例子

$ export PGDATA=/home/digoal/pgdata  
$ pg_controldata   
pg_control version number:            942  
Catalog version number:               201510051  
Database system identifier:           6318621837015461309  
Database cluster state:               in production  
pg_control last modified:             Sun 14 Aug 2016 06:54:51 PM CST  
Latest checkpoint location:           42/E20000E0  
Prior checkpoint location:            42/E2000028  
Latest checkpoint's REDO location:    42/E20000E0  
Latest checkpoint's REDO WAL file:    0000000100000042000000E2  
Latest checkpoint's TimeLineID:       1  
Latest checkpoint's PrevTimeLineID:   1  
Latest checkpoint's full_page_writes: on  
Latest checkpoint's NextXID:          0/3896508417  
Latest checkpoint's NextOID:          10000  
Latest checkpoint's NextMultiXactId:  1  
Latest checkpoint's NextMultiOffset:  0  
Latest checkpoint's oldestXID:        3800764117  
Latest checkpoint's oldestXID's DB:   1  
Latest checkpoint's oldestActiveXID:  0  
Latest checkpoint's oldestMultiXid:   1  
Latest checkpoint's oldestMulti's DB: 13294  
Latest checkpoint's oldestCommitTsXid:0  
Latest checkpoint's newestCommitTsXid:0  
Time of latest checkpoint:            Sun 14 Aug 2016 06:54:51 PM CST  
Fake LSN counter for unlogged rels:   0/1  
Minimum recovery ending location:     0/0  
Min recovery ending loc's timeline:   0  
Backup start location:                0/0  
Backup end location:                  0/0  
End-of-backup record required:        no  
wal_level setting:                    minimal  
wal_log_hints setting:                off  
max_connections setting:              100  
max_worker_processes setting:         8  
max_prepared_xacts setting:           0  
max_locks_per_xact setting:           64  
track_commit_timestamp setting:       off  
Maximum data alignment:               8  
Database block size:                  8192  
Blocks per segment of large relation: 131072  
WAL block size:                       8192  
Bytes per WAL segment:                16777216  
Maximum length of identifiers:        64  
Maximum columns in an index:          32  
Maximum size of a TOAST chunk:        1996  
Size of a large-object chunk:         2048  
Date/time type storage:               64-bit integers  
Float4 argument passing:              by value  
Float8 argument passing:              by value  
Data page checksum version:           0  

pg_resetxlog 与数据库集群版本不一致时会怎样

如果数据库集群时9.5,而你使用其他版本的pg_resetxlog修改其控制文件,结果会怎样呢?

例如使用9.2的pg_resetxlog设置9.5集群的pg_control文件。

/data/temp/pgdata/pgsql9.2/bin/pg_resetxlog -D /data/temp/pgdata/main  
pg_resetxlog: pg_control exists but is broken or unknown version; ignoring it  
Guessed pg_control values:    

如果你使用-f选项强制刷了这个pg_control文件,则版本号会变成9.2的,这个时候,你再使用9.5去启动数据库是会失败的。

那么使用9.2就能启动成功了吗?

当然也不行,因为数据库还有其他地方记录了版本号,那就是在$PGDATA以及数据库的数据文件目录对应的PG_VERSION文件。

这个文件是不会被pg_resetxlog纂改的,因为可以用来追溯真正的版本。

cat PG_VERSION  
9.5  

被不同版本的pg_resetxlog强制重建控制文件后,数据库将无法启动。

怎么办呢?

方法很简单,使用PG_VERSION对应版本的pg_resetxlog重新生成一遍控制文件即可。

pg_resetxlog 的参数计算方法

pg_resetxlog参数有几个,分别用来设置控制文件中对应的next xid , next oid, next multi-xact xid, ……

这些值有固定的算法,而且有安全范围,例如,不能设置比实际已创建事务更小的事务号,否则会造成对应事务产生的数据在事务号未消耗前数据不可见。

安全值的计算方法,可以参考pg_resetxlog的说明。

其实就是使用xlog中的文件名推算next xid。使用pg_multixact中的members , offsets推断oldest multixact id, next multi-xact。等等。

       The -o, -x, -e, -m, -O, -c and -l options allow the next OID, next transaction ID, next transaction ID's epoch, next and oldest multitransaction ID, next multitransaction offset, oldest and newest transaction IDs for which  
       the commit time can be retrieved, and WAL starting address values to be set manually. These are only needed when pg_resetxlog is unable to determine appropriate values by reading pg_control. Safe values can be determined as  
       follows:  
  
       ·   A safe value for the next transaction ID (-x) can be determined by looking for the numerically largest file name in the directory pg_clog under the data directory, adding one, and then multiplying by 1048576. Note that  
           the file names are in hexadecimal. It is usually easiest to specify the option value in hexadecimal too. For example, if 0011 is the largest entry in pg_clog, -x 0x1200000 will work (five trailing zeroes provide the  
           proper multiplier).  
  
       ·   A safe value for the next multitransaction ID (first part of -m) can be determined by looking for the numerically largest file name in the directory pg_multixact/offsets under the data directory, adding one, and then  
           multiplying by 65536. Conversely, a safe value for the oldest multitransaction ID (second part of -m) can be determined by looking for the numerically smallest file name in the same directory and multiplying by 65536. As  
           above, the file names are in hexadecimal, so the easiest way to do this is to specify the option value in hexadecimal and append four zeroes.  
  
       ·   A safe value for the next multitransaction offset (-O) can be determined by looking for the numerically largest file name in the directory pg_multixact/members under the data directory, adding one, and then multiplying  
           by 52352. As above, the file names are in hexadecimal. There is no simple recipe such as the ones above of appending zeroes.  
  
       ·   A safe value for the oldest transaction ID for which the commit time can be retrieved (first part of -c) can be determined by looking for the numerically smallest file name in the directory pg_commit_ts under the data  
           directory. Conversely, a safe value for the newest transaction ID for which the commit time can be retrieved (second part of -c) can be determined by looking for the numerically greatest file name in the same directory.  
           As above, the file names are in hexadecimal.  
  
       ·   The WAL starting address (-l) should be larger than any WAL segment file name currently existing in the directory pg_xlog under the data directory. These names are also in hexadecimal and have three parts. The first part  
           is the “timeline ID” and should usually be kept the same. For example, if 00000001000000320000004A is the largest entry in pg_xlog, use -l 00000001000000320000004B or higher.  
  
               Note  
               pg_resetxlog itself looks at the files in pg_xlog and chooses a default -l setting beyond the last existing file name. Therefore, manual adjustment of -l should only be needed if you are aware of WAL segment files  
               that are not currently present in pg_xlog, such as entries in an offline archive; or if the contents of pg_xlog have been lost entirely.  
  
       ·   There is no comparably easy way to determine a next OID that's beyond the largest one in the database, but fortunately it is not critical to get the next-OID setting right.  
  
       ·   The transaction ID epoch is not actually stored anywhere in the database except in the field that is set by pg_resetxlog, so any value will work so far as the database itself is concerned. You might need to adjust this  
           value to ensure that replication systems such as Slony-I and Skytools work correctly — if so, an appropriate value should be obtainable from the state of the downstream replicated database.  
  
       The -n (no operation) option instructs pg_resetxlog to print the values reconstructed from pg_control and values about to be changed, and then exit without modifying anything. This is mainly a debugging tool, but can be  
       useful as a sanity check before allowing pg_resetxlog to proceed for real.  

推断出正确的值后,就可以开始通过pg_resetxlog设置控制文件了。

如何从$PGDATA查看正确的数据库版本

除了pg_controldata以外,还可以查看文件PG_VERSION得到。

控制文件修复例子

http://blog.163.com/digoal@126/blog/static/163877040201171233710582/

http://blog.163.com/digoal@126/blog/static/16387704020130109400557/

小结

使用与数据文件异版本的pg_resetxlog,将导致数据文件对应到 控制文件损坏。 无法启动数据库。

解决版本,使用与数据文件版本一致的pg_resetxlog,重新生成控制文件,需要加-f强制执行。

pg_resetxlog参数的安全值计算方法,见pg_resetxlog的参考手册。

祝大家玩得开心,欢迎随时来 阿里云促膝长谈业务需求 ,恭候光临。

阿里云的小伙伴们加油,努力 做好内核与服务,打造最贴地气的云数据库

Flag Counter

digoal’s 大量PostgreSQL文章入口