PostgreSQL 9.3 will add pg_isready script to test PostgreSQL server if allowed to connect
背景
使用过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