Open NikolayS opened 6 years ago
select
application_name, slot_name, slot_type, active, active_pid as pid,
pg_size_pretty(pg_xlog_location_diff(pg_current_xlog_insert_location(), flush_location)) as lag
from pg_stat_replication
join pg_replication_slots on pid = active_pid
order by 1;
^^ wrong
Improved / full picture:
\set postgres_dba_wal_lsn_diff pg_xlog_location_diff
\set postgres_dba_wal_current_lsn pg_current_xlog_location
\set postgres_dba_col_sent_lsn sent_location
\set postgres_dba_col_write_lsn write_location
\set postgres_dba_col_flush_lsn flush_location
\set postgres_dba_col_replay_lsn replay_location
select
client_addr, usename, application_name, state, sync_state,
(:postgres_dba_wal_lsn_diff(:postgres_dba_wal_current_lsn(), :postgres_dba_col_sent_lsn))::int8 as pending_lag,
(:postgres_dba_wal_lsn_diff(:postgres_dba_col_sent_lsn, :postgres_dba_col_write_lsn))::int8 as write_lag,
(:postgres_dba_wal_lsn_diff(:postgres_dba_col_write_lsn, :postgres_dba_col_flush_lsn))::int8 as flush_lag,
(:postgres_dba_wal_lsn_diff(:postgres_dba_col_flush_lsn, :postgres_dba_col_replay_lsn))::int8 as replay_lag,
(:postgres_dba_wal_lsn_diff(:postgres_dba_wal_current_lsn(), coalesce(:postgres_dba_col_replay_lsn, :postgres_dba_col_flush_lsn)))::int8 as total_lag
from pg_stat_replication;
select
client_addr, usename, application_name, state, sync_state,
pg_size_pretty(:postgres_dba_wal_lsn_diff(:postgres_dba_wal_current_lsn(), :postgres_dba_col_sent_lsn)) as pending_lag,
pg_size_pretty(:postgres_dba_wal_lsn_diff(:postgres_dba_col_sent_lsn, :postgres_dba_col_write_lsn)) as write_lag,
pg_size_pretty(:postgres_dba_wal_lsn_diff(:postgres_dba_col_write_lsn, :postgres_dba_col_flush_lsn)) as flush_lag,
pg_size_pretty(:postgres_dba_wal_lsn_diff(:postgres_dba_col_flush_lsn, :postgres_dba_col_replay_lsn)) as replay_lag,
pg_size_pretty(:postgres_dba_wal_lsn_diff(:postgres_dba_wal_current_lsn(), coalesce(:postgres_dba_col_replay_lsn, :postgres_dba_col_flush_lsn))) as total_lag,
slots.*
from pg_stat_replication
left join pg_replication_slots slots on pid = active_pid;
It's worth to see how many files / WAL segments are currently located in pg_xlog/pg_wal and what's the total size.