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