PostgreSQL 恢复模式错误日志增强 - 提供正在恢复的WAL（XLOG）文件位置

作者

digoal

日期

2018-08-10

标签

PostgreSQL , the database system is starting up , the database system is in recovery mode

背景

当数据库异常停库，再次启动时。又或者由于进程CRASH后自动重启时。需要进入恢复模式，恢复完成后，数据库才能正常交互。

在启动过程中，正在恢复时，如果此时连接数据库，会报错如下：

the database system is starting up

用户只看这个信息，并不知道数据库还要启动多久，现在已经恢复到什么状态了？

内核层面可以稍作改进，报错时，同时输出正在恢复的WAL位点，以及距离最后一个文件的WAL位点差多少MB没有恢复。这样用户大概就还知道还需要多久。

src/backend/postmaster/postmaster.c

```
/
* Read a client's startup packet and do something according to it.

* Returns STATUS_OK or STATUS_ERROR, or might call ereport(FATAL) and
* not return at all.

* (Note that ereport(FATAL) stuff is sent to the client, so only use it
* if that's what you want. Return STATUS_ERROR if you don't want to
* send anything to the client, which would typically be appropriate
* if we detect a communications failure.)
/
static int
ProcessStartupPacket(Port *port, bool SSLdone)
{

.......

    /*  
     * If we're going to reject the connection due to database state, say so  
     * now instead of wasting cycles on an authentication exchange. (This also  
     * allows a pg_ping utility to be written.)  
     */  
    switch (port->canAcceptConnections)  
    {  
            case CAC_STARTUP:  
                    ereport(FATAL,  
                                    (errcode(ERRCODE_CANNOT_CONNECT_NOW),  
                                     errmsg("the database system is starting up")));  
                    break;  
            case CAC_SHUTDOWN:  
                    ereport(FATAL,  
                                    (errcode(ERRCODE_CANNOT_CONNECT_NOW),  
                                     errmsg("the database system is shutting down")));  
                    break;  
            case CAC_RECOVERY:  
                    ereport(FATAL,  
                                    (errcode(ERRCODE_CANNOT_CONNECT_NOW),  
                                     errmsg("the database system is in recovery mode")));  
                    break;  
            case CAC_TOOMANY:  
                    ereport(FATAL,  
                                    (errcode(ERRCODE_TOO_MANY_CONNECTIONS),  
                                     errmsg("sorry, too many clients already")));  
                    break;  
            case CAC_WAITBACKUP:  
                    /* OK for now, will check in InitPostgres */  
                    break;  
            case CAC_OK:  
                    break;  
    }

    return STATUS_OK;

}
```

src/backend/access/transam/xlog.c

/* * This must be called ONCE during postmaster or standalone-backend startup */ void StartupXLOG(void) {

PostgreSQL 许愿链接

您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.

9.9元购买3个月阿里云RDS PostgreSQL实例

PostgreSQL 解决方案集合

德哥 / digoal's github - 公益是一辈子的事.