查看复制状态

在master上执行

sky=# select * from pg_stat_replication;
-[ RECORD 1 ]----+------------------------------
pid              | 13321
usesysid         | 17019
usename          | replication
application_name | walreceiver
client_addr      | 10.0.0.81
client_hostname  | 
client_port      | 42809
backend_start    | 2016-08-11 10:57:35.856289+08
backend_xmin     | 
state            | streaming
sent_location    | 1/E0CE9750
write_location   | 1/E0CE9750
flush_location   | 1/E0CE9750
replay_location  | 1/E0CE9750
sync_priority    | 0
sync_state       | async

sky=#

pid：　master的sender进程：

╭─sky@sky-linux /ihome/db/postgresql  
╰─➤  ps aux | grep 13321
sky      13321  0.1  0.0 172320  2992 ?        Ss   10:57   0:03 postgres: wal sender process replication 10.0.0.81(42809) streaming 1/E0CE9750

usesysid: 复制的用户ID(17019) usename : 用户名

sky=# select * from pg_user;
-[ RECORD 1 ]+------------
usename      | sky
usesysid     | 10
usecreatedb  | t
usesuper     | t
userepl      | t
usebypassrls | t
passwd       | ********
valuntil     | 
useconfig    | 
-[ RECORD 2 ]+------------
usename      | replication
usesysid     | 17019
usecreatedb  | f
usesuper     | f
userepl      | t
usebypassrls | f
passwd       | ********
valuntil     | 
useconfig    |

client_addr: slave的IP地址

client_port: slave的 wal receiver process的PID使用的端口

backend_start: 开始复制的时间

state: 同步状态.

  • streaming : 同步
  • startup : 连接中
  • catchup: 同步中

sent_location:Master传送的WAL位置 write_location:Slave接收WAL位置 flush_location:Slave同步到磁盘的WAL位置 replay_location:Slave同步到数据库的WAL位置

sync_priority:同步优先级.

sync_state: 同步模式.

  • async : 异步
  • sync : 同步
  • potential: 虽然现在是异步,但有可能提升到同步.

查看延迟多少

在master上执行以下SQL可以查看滞后程度,以字节为单位.

sky=# select pg_xlog_location_diff(sent_location, replay_location) from pg_stat_replication; 
 pg_xlog_location_diff 
-----------------------
                     0
(1 row)

sky=#

或者

在slave上执行以下SQL可以查看滞后时间.

sky=# SELECT CASE WHEN pg_last_xlog_receive_location() = pg_last_xlog_replay_location()
    THEN 0
    ELSE EXTRACT (EPOCH FROM now() - pg_last_xact_replay_timestamp())
    END AS log_delay;
 log_delay 
-----------
         0
(1 row)

sky=#

如果没输出就表示完全同步了.

查看是否处于recovery模式

sky=#  select pg_is_in_recovery();
 pg_is_in_recovery 
-------------------
 t
(1 row)

sky=#

如果处于复制模式的话, select pg_last_xact_replay_timestamp();和select pg_last_xlog_replay_location();会一直增加.

select pg_last_xact_replay_timestamp();

select pg_last_xlog_replay_location();