0: add info about pg_xlog/pg_wal

[NikolayS]

It's worth to see how many files / WAL segments are currently located in pg_xlog/pg_wal and what's the total size.

select now()::timestamptz(0), count(1), pg_size_pretty(sum((pg_stat_file('pg_xlog/'||fname)).size)) as total_size
from pg_ls_dir('pg_xlog') as t(fname);


select pg_last_xlog_replay_location(), pg_last_xlog_receive_location(); -- on replica


select slot_name, slot_type, active, active_pid as pid, (select pg_size_pretty(pg_xlog_location_diff(pg_current_xlog_insert_location(), flush_location)) from pg_stat_replication where pid = active_pid) as lag from pg_replication_slots;

select pg_current_xlog_location(); -- on master

[NikolayS]

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;