Pglogical Replication Trouble shooting:
in general as we complete setting up pglogical replication we need to monitor the replication from source to target database.
Below Queries will Help to identify the Replication Progress and Status.
Topics Covered.
- List Tables Registered in Pglogical
- How much GB we are behind from source to target.
- How much GB we are behind from source to target.
- Replication_lag_from_source_target_in_bytes.
1.List tables registered in Pglogical.
|
1 2 3 |
select * from pglogical.local_sync_status; |
2.How much GB we are behind from source to target.
|
1 2 3 |
select redo_lsn, slot_name, restart_lsn, round((redo_lsn-restart_lsn)/1024/1024/1024, 2) AS GB_behind from pg_control_checkpoint(), pg_replication_slots; |
3.How much GB we are behind from source to target.
|
1 2 3 |
select slot_name, total_txns, total_bytes/(1024*1024) "total data (in MB)" from pg_stat_replication_slots; |
4.Replication_lag_from_source_target_in_bytes.
|
1 2 3 4 5 6 |
select pid, usename, application_name, client_addr, state, sync_state, pg_wal_lsn_diff(sent_lsn, write_lsn) as write_lag, pg_wal_lsn_diff(sent_lsn, flush_lsn) as flush_lag, pg_wal_lsn_diff(sent_lsn, replay_lsn) as replay_lag, pg_wal_lsn_diff(sent_lsn, replay_lsn) as total_lag from pg_stat_replication; |
