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连接到主库.
... 其他略.