PostgreSQL 9.5 new feature - log replication commands

背景

PostgreSQL 9.5新增一个参数, 可以记录流复制协议中用到的命令到日志中.

log_replication_commands = on  

协议解释详见 :

http://www.postgresql.org/docs/devel/static/protocol-replication.html

测试

pg_hba.conf  
# Allow replication connections from localhost, by a user with the  
# replication privilege.  
local   replication     postgres                                trust  
host    replication     postgres        127.0.0.1/32            trust  
host    replication     postgres        ::1/128                 trust  
postgresql.conf  
wal_level = logical  
max_wal_senders = 2  
max_replication_slots = 2  
log_replication_commands = on  
  
pg95@db-172-16-3-150-> pg_ctl restart -m fast  

使用一个流复制协议command测试 :

pg95@db-172-16-3-150-> psql "dbname=postgres replication=database" -c "IDENTIFY_SYSTEM;"  
      systemid       | timeline |  xlogpos  |  dbname    
---------------------+----------+-----------+----------  
 6135538990815133068 |        1 | 0/1B186A0 | postgres  
(1 row)  

以上命令在数据库日志中可以看到.

2015-04-09 10:49:59.481 CST,"postgres","postgres",21664,"[local]",5525e8d7.54a0,3,"idle",2015-04-09 10:49:59 CST,2/0,0,LOG,00000,"received replication command: IDENTIFY_SYSTEM;",,,,,,,,"exec_replication_command, walsender.c:1274","psql"  

用于区分连接流复制的代码：

src/backend/replication/libpqwalreceiver/libpqwalreceiver.c

/*  
 * Establish the connection to the primary server for XLOG streaming  
 */  
static void  
libpqrcv_connect(char *conninfo)  
{  
        char            conninfo_repl[MAXCONNINFO + 75];  
  
        /*  
         * Connect using deliberately undocumented parameter: replication. The  
         * database name is ignored by the server in replication mode, but specify  
         * "replication" for .pgpass lookup.  
         */  
        snprintf(conninfo_repl, sizeof(conninfo_repl),  
                         "%s dbname=replication replication=true fallback_application_name=walreceiver",  
                         conninfo);  
  
        streamConn = PQconnectdb(conninfo_repl);  
        if (PQstatus(streamConn) != CONNECTION_OK)  
                ereport(ERROR,  
                                (errmsg("could not connect to the primary server: %s",  
                                                PQerrorMessage(streamConn))));  
}  

src/bin/pg_basebackup/streamutil.c

 * streamutil.c - utility functions for pg_basebackup and pg_receivelog  
 *  
 * Author: Magnus Hagander <magnus@hagander.net>  
 *  
 * Portions Copyright (c) 1996-2014, PostgreSQL Global Development Group  
 *  
 * IDENTIFICATION  
 *                src/bin/pg_basebackup/streamutil.c  
  
/*  
 * Connect to the server. Returns a valid PGconn pointer if connected,  
 * or NULL on non-permanent error. On permanent error, the function will  
 * call exit(1) directly.  
 */  
PGconn *  
GetConnection(void)  
{  
...  
        keywords[i] = "dbname";  
        values[i] = dbname == NULL ? "replication" : dbname;  
        i++;  
        keywords[i] = "replication";  
        values[i] = dbname == NULL ? "true" : "database";  
        i++;  
        keywords[i] = "fallback_application_name";  
        values[i] = progname;  
        i++;  
...  

只需要提供replication=true的连接串即可。

psql "replication=true" postgres  
psql (9.5.0)  
Type "help" for help.  
postgres=# IDENTIFY_SYSTEM;  
      systemid       | timeline |   xlogpos   | dbname   
---------------------+----------+-------------+--------  
 6243682321523937214 |        1 | 1F/FB2D7B48 |   
(1 row)  

digoal’s 大量PostgreSQL文章入口