PostgreSQL Streaming Replication COMMAND used in psql

背景

CF里面在讨论是否要添加一个查看数据库system id的函数, pg_system_identifier();

http://www.postgresql.org/message-id/flat/5215346B.7070800@dalibo.com#5215346B.7070800@dalibo.com

这个函数的用途和pg_controldata输出的Database system identifier值其实是一个效果.

只是它的目的是可以用SQL来得到这个值.

看到后面发现一个很有趣的东西, Fujii Masao回复的如下 :

BTW, you can see the system identifier by executing IDENTIFY_SYSTEM  
command in replication connection as follows:  
  
1. Change the server settings so that the server can accept the  
   replication connection  
2. Connect to the server in replication mode  
3. Execute IDENTIFY_SYSTEM command in replication connection  
  
$ psql "replication=1"  
=# IDENTIFY_SYSTEM;  
      systemid       | timeline |  xlogpos  
---------------------+----------+-----------  
 5914930202950905854 |        1 | 0/183F720  
(1 row)  
  
This is not good way for a user, though ;P  
  
> I don't know if that's justification enough, which is  
> why I didn't add it to the commitfest yet.  
  
You can add the patch to CF, and then hear the opinions from other people  
during CF.  
  
Regards,  
  
--   
Fujii Masao  

原来还可以这么玩, 于是乎找了一个测试库试一试.

172.16.3.33 主库 (host replication postgres 172.16.3.0/24 md5)  
172.16.3.39 备库  

在172.16.3.39上以standby角色去连接172.16.3.33的主库.

pg94@db-172-16-3-39-> psql "replication=1" -h 172.16.3.33 -U postgres  
Password for user postgres:   
psql (9.4devel)  
Type "help" for help.  
digoal=#   
digoal=# IDENTIFY_SYSTEM;  
      systemid       | timeline |  xlogpos     
---------------------+----------+------------  
 5912195073286594075 |        1 | 6/80000668  
(1 row)  

得到的值和pg_controldata一致.

pg94@db-172-16-3-33-> pg_controldata |grep identifier  
Database system identifier:           5912195073286594075  
Maximum length of identifiers:        64  

除了使用IDENTIFY_SYSTEM, replication protocol还支持其他的命令.

详见 :

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

IDENTIFY_SYSTEM  
TIMELINE_HISTORY tli  
START_REPLICATION XXX/XXX TIMELINE tli  
BASE_BACKUP [LABEL 'label'] [PROGRESS] [FAST] [WAL] [NOWAIT]  

包括pg_basebackup , 也是使用流复制协议进行数据复制的.

另外几个命令也可以在psql命令行中使用, 例如 :

在主节点pg_xlog中创建一个history文件.

[root@db-172-16-3-33 pg_basebackup]# su - pg94  
pg94@db-172-16-3-33-> cd $PGDATA  
pg94@db-172-16-3-33-> vi pg_xlog/00000002.history   
test line 1  
test line 2  

使用流复制命令接收history文件内容.

digoal=# TIMELINE_HISTORY 2;  
     filename     |   content     
------------------+-------------  
 00000002.history | test line 1+  
                  | test line 2+  
                  |   
(1 row)  

其他命令 :

digoal=# select * from pg_current_xlog_location();  
 pg_current_xlog_location   
--------------------------  
 6/800007A8  
(1 row)  
digoal=# START_REPLICATION 6/80000700 TIMELINE 1;  
unexpected PQresultStatus: 8  
digoal=# START_REPLICATION 6/80000700 TIMELINE 1;  
PQexec not allowed during COPY BOTH  

BASE_BACKUP 是做基础备份的, 数据比较庞大.

参考

1. src/backend/replication/repl_gram.y

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

3. http://www.postgresql.org/message-id/flat/5215346B.7070800@dalibo.com#5215346B.7070800@dalibo.com

4. src/interfaces/libpq/libpq-int.h

/*  
 * PGconn stores all the state data associated with a single connection  
 * to a backend.  
 */  
struct pg_conn  
{  
        /* Saved values of connection options */  
        char       *pghost;                     /* the machine on which the server is running */  
        char       *pghostaddr;         /* the numeric IP address of the machine on  
                                                                 * which the server is running.  Takes  
                                                                 * precedence over above. */  
        char       *pgport;                     /* the server's communication port */  
        char       *pgunixsocket;       /* the Unix-domain socket that the server is  
                                                                 * listening on; if NULL, uses a default  
                                                                 * constructed from pgport */  
        char       *pgtty;                      /* tty on which the backend messages is  
                                                                 * displayed (OBSOLETE, NOT USED) */  
        char       *connect_timeout;    /* connection timeout (numeric string) */  
        char       *client_encoding_initial;            /* encoding to use */  
        char       *pgoptions;          /* options to start the backend with */  
        char       *appname;            /* application name */  
        char       *fbappname;          /* fallback application name */  
        char       *dbName;                     /* database name */  
        char       *replication;        /* connect as the replication standby? */  

replication指定是否以standby连接到主库.

… 其他略.

digoal’s 大量PostgreSQL文章入口