how to measure amazon rds postgresql replica lag

rds-pgsql-replica-lag.md

What to do when replica creation fails stopping streaming, trying to recover WAL from the archive

Calculate XLOG entries distance between master and replica with:

on master:

> select pg_xlogfile_name(pg_current_xlog_flush_location()), pg_current_xlog_insert_location(), pg_current_xlog_location();

on slave:

> select pg_last_xlog_replay_location();

use pg_lsn difference to evaluate lag

> SELECT pg_current_xlog_location() - 'lsn from pg_last_xlog_replay_location() from slave' as diff;

WAL segment file name = timelineId + (uint32)((LSN-1)/(16M*256))  + (uint32)((LSN-1) % 16M)

calculate and fix your WAL tail size / segments kept / slots requirements based on the start lag when rds replica just created.

Link

• https://www.postgresql.org/docs/current/static/functions-admin.html
• http://paquier.xyz/postgresql-2/postgres-9-4-feature-highlight-lsn-datatype/
• https://www.postgresql.org/message-id/[email protected]
• Write Ahead Log(ging) — WAL http://www.interdb.jp/pg/pgsql09.html
• https://www.postgresql.org/docs/current/static/wal-configuration.html
• https://blog.2ndquadrant.com/basics-of-tuning-checkpoints/
• http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_ReadRepl.html#USER_ReadRepl.PostgreSQL
• http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_ReadRepl.html#USER_ReadRepl.TroubleshootingPostgreSQL
• http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_WorkingWithAutomatedBackups.html

Useful functions:

|-|-:|-:| | pg_create_restore_point(name text) | pg_lsn | Create a named point for performing restore (restricted to superusers by default, but other users can be granted EXECUTE to run the function) | | pg_current_xlog_flush_location() | pg_lsn | Get current transaction log flush location | | pg_current_xlog_insert_location() | pg_lsn | Get current transaction log insert location | | pg_current_xlog_location() | pg_lsn | Get current transaction log write location | | pg_start_backup(label text [, fast boolean [, exclusive boolean ]]) | pg_lsn | Prepare for performing on-line backup (restricted to superusers by default, but other users can be granted EXECUTE to run the function) | | pg_stop_backup() | pg_lsn | Finish performing exclusive on-line backup (restricted to superusers by default, but other users can be granted EXECUTE to run the function) | | pg_stop_backup(exclusive boolean) | setof record | Finish performing exclusive or non-exclusive on-line backup (restricted to superusers by default, but other users can be granted EXECUTE to run the function) | | pg_is_in_backup() | bool | True if an on-line exclusive backup is still in progress. | | pg_backup_start_time() | timestamp with time zone | Get start time of an on-line exclusive backup in progress. | | pg_switch_xlog() | pg_lsn | Force switch to a new transaction log file (restricted to superusers by default, but other users can be granted EXECUTE to run the function) | | pg_xlogfile_name(location pg_lsn) | text | Convert transaction log location string to file name | | pg_xlogfile_name_offset(location pg_lsn) | text, integer | Convert transaction log location string to file name and decimal byte offset within file | | pg_xlog_location_diff(location pg_lsn, location pg_lsn) | numeric | Calculate the difference between two transaction log locations |