APPENDIX · POSTGRESQL LIVE DEMO · SLIDE P7
Q7. WAL Diagnostics, Replica Lag Monitoring, and Read-Write Split
Click Run—we'll check the node mode, calculate the LSN lag in bytes, and verify how requests are routed between the Primary and Replica
-- 1. Проверяем статус репликации и вычисляем отставание (lag) через pg_wal_lsn_diff:
SELECT
application_name,
state,
sync_state,
sent_lsn,
replay_lsn,
pg_wal_lsn_diff(sent_lsn, replay_lsn) AS replay_lag_bytes
FROM game.v_replication_status;
-- 2. Маршрутизация запросов игрового проекта (Read-Write Split):
SELECT workload_type, target_node, db_role, max_allowed_lag
FROM game.v_rw_routing_policy
ORDER BY policy_id;
What does this display show?
• 1. The pg_wal_lsn_diff(sent_lsn, replay_lsn) function is a built-in PostgreSQL function that calculates the exact distance in bytes between two positions in the WAL log.
• When sent_lsn = replay_lsn, the replica’s lag is 0 bytes.
• 2. The Read-Write Split routing table shows which game queries go to the Primary (5435) and which can safely be routed to the Replica (5436).
Testing in Docker (Q7)
Read-Only Protection Test
What to Include in the Report
In the local Lesson 5/dba/ container, both containers (postgres and postgres-replica) are started automatically using the command `docker compose up -d --wait`.
On port 5436 (the replica), any SELECT statement executes instantly, but an attempt to execute an INSERT is rejected with a "read-only transaction" error.
For Exercise 5, attach the pg_stat_replication output from your master and the Primary/Replica query breakdown table for your game.