代码改变世界

PostgreSQL复制以及监控

2020-10-28 08:55  abce  阅读(316)  评论(0)    收藏  举报

PostgreSQL复制的类型







                    +----------------------------------------------------+
                    |                                                    |
                    |             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接收状态：




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应用状态：




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端




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.监控物理/逻辑复制槽




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=#