PostgreSQL复制以及监控

PostgreSQL复制的类型

?

1

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25

+----------------------------------------------------+                   |                                                    |                   |             Replication in PostgreSQL              |                   |                                                    |                   +--+----------------------------------------------+--+                      |                                              |                      |                                              |               +------v-----+                                  +-----v-----+               |  Physical  |                                  |  Logical  |               +-+--------+-+                                  +-----+-----+               |          |                                          |               |          |                                          | +-------------v--+    +--v----------+                      +--------v--------+ |  Log Shipping  |    |  Streaming  |                      |     Logical     | +----------------+    +-+---------+-+                      |   Replication   |                         |         |                        |      Slots      |                         |         |                        +--------+--------+                         |         |                                 |                         |         |                                 |            +------------v--+   +--v--------------+         +--------v--------+            |    Without    |   |  With Physical  |         |  Subscriptions  |            |  Replication  |   |   Replication   |         +-----------------+            |     Slots     |   |      Slots      |            +---------------+   +-----------------+

　　

 

PostgreSQL复制的监控

1.standby端

监控wal接收状态：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

postgres=# select * from pg_stat_wal_receiver; -[ RECORD 1 ]---------+---------------------------------------------------------------------------------------------------- pid                   | 341627 status                | streaming receive_start_lsn     | 3F/D7000000 receive_start_tli     | 10 received_lsn          | 7E/76964000 received_tli          | 10 last_msg_send_time    | 2020-10-26 14:04:38.760469+08 last_msg_receipt_time | 2020-10-26 14:04:38.760872+08 latest_end_lsn        | 7E/76964000 latest_end_time       | 2020-10-26 14:04:38.760469+08 slot_name             | pgs sender_host           | 20.10.20.10 sender_port           | 5433 conninfo              | user=repl password=Pg20 host=20.10.20.10 port=5432 sslmode=prefer application_name=pgs   postgres=#

　　

监控wal应用状态：

?

1 2 3 4 5 6 7

postgres=# select pg_last_wal_receive_lsn(),pg_last_wal_replay_lsn(),pg_last_xact_replay_timestamp(); -[ RECORD 1 ]-----------------+------------------------------ pg_last_wal_receive_lsn       | 7E/7D0813B8 pg_last_wal_replay_lsn        | 7E/7D0813B8 pg_last_xact_replay_timestamp | 2020-10-26 14:10:24.973869+08   postgres=#

　　

2.监控primary端

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

postgres=# select * from pg_stat_replication; -[ RECORD 1 ]----+----------------------------- pid              | 210004 usesysid         | 16384 usename          | replicator application_name | pgs client_addr      | 20.10.21.11 client_hostname  | client_port      | 42526 backend_start    | 2020-10-11 02:20:08.99414+08 backend_xmin     | state            | streaming sent_lsn         | 7E/7EAAF568 write_lsn        | 7E/7EAAF568 flush_lsn        | 7E/7EAAF568 replay_lsn       | 7E/7EAAF568 write_lag        | 00:00:00.000789 flush_lag        | 00:00:00.00126 replay_lag       | 00:00:00.001683 sync_priority    | 0 sync_state       | async   postgres=#

　　

3.监控物理/逻辑复制槽

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29

postgres=# select * from pg_replication_slots; -[ RECORD 1 ]-------+-------------- slot_name           | mylogslot plugin              | test_decoding slot_type           | logical datoid              | 16384 database            | bench temporary           | f active              | t active_pid          | 2607 xmin                | catalog_xmin        | 356948 restart_lsn         | 0/3B44A5A0 confirmed_flush_lsn | 0/3B44A5A0 -[ RECORD 2 ]-------+-------------- slot_name           | myreplslot1 plugin              | slot_type           | physical datoid              | database            | temporary           | f active              | t active_pid          | 2598 xmin                | catalog_xmin        | restart_lsn         | 0/3B44A5D8 confirmed_flush_lsn |   postgres=#