PostgreSQL 9.3 will add pg_isready script to test PostgreSQL server if allowed to connect

5 minute read

背景

使用过Oracle数据库的朋友大概对tnsping有过了解, 它是Oracle用来测试数据库tnsnames.ora配置项连通性的工具之一.

PostgreSQL 9.3将要加入这样一个客户端, 名为pg_idready.

也是用于检测数据库的连通性, 参数如下 :

digoal@db-172-16-3-150-> pg_isready --help  
pg_isready issues a connection check to a PostgreSQL database.  
  
Usage:  
  pg_isready [OPTION]...  
  
Options:  
  -d, --dbname=DBNAME      database name  
  -q, --quiet              run quietly  
  -V, --version            output version information, then exit  
  -?, --help               show this help, then exit  
  
Connection options:  
  -h, --host=HOSTNAME      database server host or socket directory  
  -p, --port=PORT          database server port  
  -t, --timeout=SECS       seconds to wait when attempting connection, 0 disables (default: 3)  
  -U, --username=USERNAME  database username  
Report bugs to <pgsql-bugs@postgresql.org>.  

-d 和 -U参数目前没有实际的用处, 只需要-h 和-p就够了.

一般我们在启动PostgreSQL后会看到这样一条日志 :

2013-01-27 12:50:27.494 CST,,,2574,,5104b213.0a0e,1,,2013-01-27 12:50:27 CST,,0,LOG,00000,"database system is ready to accept connections",,,,,,,,,""  

这可能也是pg_isready命名的由来.

pg_isready的返回值如下 :

EXIT STATUS  
       pg_isready returns 0 to the shell if the server is accepting connections normally, 1 if the server is rejecting  
       connections (for example during startup), 2 if there was no response to the connection attempt, and 3 if no  
       attempt was made (for example due to invalid parameters).  

测试

1. 测试正常启动的数据库

digoal@db-172-16-3-150-> pg_isready -h 127.0.0.1 -p 9300  
127.0.0.1:9300 - accepting connections  
digoal@db-172-16-3-150-> echo $?  
0  

返回值为0

2. 测试, 修改pg_hba.conf 禁止连接.

vi $PGDATA/pg_hba.conf  
host    all             all             127.0.0.1/32            reject  
pg_ctl reload  
digoal@db-172-16-3-150-> pg_isready -h 127.0.0.1 -p 9300  
127.0.0.1:9300 - accepting connections  
digoal@db-172-16-3-150-> echo $?  
0  

返回值为0, 和pg_hba.conf的配置无关. 因为pg_isready使用的是PQconnectStartParams.

3. 测试其他服务端口, 例如sshd监听端口

digoal@db-172-16-3-150-> netstat -anp|grep 22  
(Not all processes could be identified, non-owned process info  
 will not be shown, you would have to be root to see it all.)  
tcp        0      0 0.0.0.0:22                  0.0.0.0:*                   LISTEN      -   
digoal@db-172-16-3-150-> pg_isready -h 127.0.0.1 -p 22  
127.0.0.1:22 - no response  
digoal@db-172-16-3-150-> echo $?  
2  

4. 测试无监听的端口

digoal@db-172-16-3-150-> netstat -anp|grep 1234  
(Not all processes could be identified, non-owned process info  
 will not be shown, you would have to be root to see it all.)  
digoal@db-172-16-3-150-> pg_isready -h 127.0.0.1 -p 1234  
127.0.0.1:1234 - no response  
digoal@db-172-16-3-150-> echo $?  
2  

5. 测试数据库恢复中的情况

当数据库处于恢复状态, 日志输出如下时 :

2013-01-27 13:34:19.657 CST,"postgres","",3892,"127.0.0.1:49691",5104bc5b.0f34,1,"",2013-01-27 13:34:19 CST,,0,FATAL,57P03,"the database system is starting up",,,,,,,,,""  

pg_isready的返回值是1. 拒绝连接.

digoal@db-172-16-3-150-> pg_isready -h 127.0.0.1 -p 9300  
127.0.0.1:9300 - rejecting connections  
digoal@db-172-16-3-150-> echo $?  
1  

小结

1. pg_isready和pg_hba.conf的配置无关.

2. pg_isready目前还仅仅局限于端口和IP的参数可用. 不支持用户以及数据库. 因为不涉及到认证的环节.

3. pg_isready需要SERVER返回SQLSTATE, 所以并不是所有版本的数据库都适用, 7.4以前的版本可能会遇到问题, (代码注释里面提到的, 我这里没有测试过).

参考

1. src/interfaces/libpq/fe-connect.c

/*  
 *              PQpingParams  
 *  
 * check server status, accepting parameters identical to PQconnectdbParams  
 */  
PGPing  
PQpingParams(const char *const * keywords,  
                         const char *const * values,  
                         int expand_dbname)  
{  
        PGconn     *conn = PQconnectStartParams(keywords, values, expand_dbname);  
        PGPing          ret;  
  
        ret = internal_ping(conn);  
        PQfinish(conn);  
  
        return ret;  
}  
  
/*  
 * internal_ping  
 *              Determine if a server is running and if we can connect to it.  
 *  
 * The argument is a connection that's been started, but not completed.  
 */  
static PGPing  
internal_ping(PGconn *conn)  
{  
        /* Say "no attempt" if we never got to PQconnectPoll */  
        if (!conn || !conn->options_valid)  
                return PQPING_NO_ATTEMPT;  
  
        /* Attempt to complete the connection */  
        if (conn->status != CONNECTION_BAD)  
                (void) connectDBComplete(conn);  
  
        /* Definitely OK if we succeeded */  
        if (conn->status != CONNECTION_BAD)  
                return PQPING_OK;  
  
        /*  
         * Here begins the interesting part of "ping": determine the cause of the  
         * failure in sufficient detail to decide what to return.  We do not want  
         * to report that the server is not up just because we didn't have a valid  
         * password, for example.  In fact, any sort of authentication request  
         * implies the server is up.  (We need this check since the libpq side of  
         * things might have pulled the plug on the connection before getting an  
         * error as such from the postmaster.)  
         */  
        if (conn->auth_req_received)  
                return PQPING_OK;  
  
        /*  
         * If we failed to get any ERROR response from the postmaster, report  
         * PQPING_NO_RESPONSE.  This result could be somewhat misleading for a  
         * pre-7.4 server, since it won't send back a SQLSTATE, but those are long  
         * out of support.      Another corner case where the server could return a  
         * failure without a SQLSTATE is fork failure, but NO_RESPONSE isn't  
         * totally unreasonable for that anyway.  We expect that every other  
         * failure case in a modern server will produce a report with a SQLSTATE.  
         *  
         * NOTE: whenever we get around to making libpq generate SQLSTATEs for  
         * client-side errors, we should either not store those into  
         * last_sqlstate, or add an extra flag so we can tell client-side errors  
         * apart from server-side ones.  
         */  
        if (strlen(conn->last_sqlstate) != 5)  
                return PQPING_NO_RESPONSE;  
  
        /*  
         * Report PQPING_REJECT if server says it's not accepting connections. (We  
         * distinguish this case mainly for the convenience of pg_ctl.)  
         */  
        if (strcmp(conn->last_sqlstate, ERRCODE_CANNOT_CONNECT_NOW) == 0)  
                return PQPING_REJECT;  
  
        /*  
         * Any other SQLSTATE can be taken to indicate that the server is up.  
         * Presumably it didn't like our username, password, or database name; or  
         * perhaps it had some transient failure, but that should not be taken as  
         * meaning "it's down".  
         */  
        return PQPING_OK;  
}  
  
/*  
 *              PQconnectStartParams  
 *  
 * Begins the establishment of a connection to a postgres backend through the  
 * postmaster using connection information in a struct.  
 *  
 * See comment for PQconnectdbParams for the definition of the string format.  
 *  
 * Returns a PGconn*.  If NULL is returned, a malloc error has occurred, and  
 * you should not attempt to proceed with this connection.      If the status  
 * field of the connection returned is CONNECTION_BAD, an error has  
 * occurred. In this case you should call PQfinish on the result, (perhaps  
 * inspecting the error message first).  Other fields of the structure may not  
 * be valid if that occurs.  If the status field is not CONNECTION_BAD, then  
 * this stage has succeeded - call PQconnectPoll, using select(2) to see when  
 * this is necessary.  
 *  
 * See PQconnectPoll for more info.  
 */  
PGconn *  
PQconnectStartParams(const char *const * keywords,  
                                         const char *const * values,  
                                         int expand_dbname)  
{  
        PGconn     *conn;  
        PQconninfoOption *connOptions;  
  
        /*  
         * Allocate memory for the conn structure  
         */  
        conn = makeEmptyPGconn();  
        if (conn == NULL)  
                return NULL;  
  
        /*  
         * Parse the conninfo arrays  
         */  
        connOptions = conninfo_array_parse(keywords, values,  
                                                                           &conn->errorMessage,  
                                                                           true, expand_dbname);  
        if (connOptions == NULL)  
        {  
                conn->status = CONNECTION_BAD;  
                /* errorMessage is already set */  
                return conn;  
        }  
  
        /*  
         * Move option values into conn structure  
         */  
        fillPGconn(conn, connOptions);  
  
        /*  
         * Free the option info - all is in conn now  
         */  
        PQconninfoFree(connOptions);  
  
        /*  
         * Compute derived options  
         */  
        if (!connectOptions2(conn))  
                return conn;  
  
        /*  
         * Connect to the database  
         */  
        if (!connectDBStart(conn))  
        {  
                /* Just in case we failed to set it in connectDBStart */  
                conn->status = CONNECTION_BAD;  
        }  
        return conn;  
}  

2. src/bin/scripts/pg_isready.c

PQpingParams(keywords, values, 1);  

3. man pg_isready

EXIT STATUS  
       pg_isready returns 0 to the shell if the server is accepting connections normally, 1 if the server is rejecting  
       connections (for example during startup), 2 if there was no response to the connection attempt, and 3 if no  
       attempt was made (for example due to invalid parameters).  

4. http://docs.oracle.com/cd/E11882_01/network.112/e10836/connect.htm

5. http://www.postgresql.org/docs/devel/static/app-pg-isready.html

Flag Counter

digoal’s 大量PostgreSQL文章入口