Computer Engineer, Sakarya University
Software Specialist, Acibadem University
Database Administrator, tuttur.com
Database Administrator, GBD
Database Administrator, Doğuş Teknoloji
Addicted to Postgres and SQL
Feel free for learning new technologies
Love to automate any process
twitter: @gguncesi
blog: guncekaya.blogspot.com
Agenda
Write Ahead Log Files
Write Ahead Log Files
Streaming Replication (SR) Topology
Streaming Replication (SR)
Setup SR Replication
Master Postgres Server:
WAL Levels
Setup SR Replication
Master Postgres Server:
Setup SR Replication
Standby Postgres Server:
not anymore..
Monitor Replication
Primary Server (WAL Sender)
postgres=# SELECT * FROM pg_stat_replication ;
-[ RECORD 1 ]----+------------------------------
pid | 66724
usesysid | 16391
usename | replicauser
application_name | walreceiver
client_addr | <standby_ip>
client_hostname |
client_port | 43730
backend_start | 2020-02-10 11:02:00.392514+03
backend_xmin | 259576661
state | streaming
sent_lsn | 4642/3842E7E0
write_lsn | 4642/3842E7E0
flush_lsn | 4642/3842E7E0
replay_lsn | 4642/3842E7E0
write_lag | 00:00:00.001645
flush_lag | 00:00:00.005035
replay_lag | 00:00:00.007088
sync_priority | 0
sync_state | async
state can be:
catchup
streaming
backup
stopping
Monitor Replication
Primary Server (WAL Sender)
postgres=# select pid, client_addr,
pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(),sent_lsn)) as pending_wal,
pg_size_pretty(pg_wal_lsn_diff(sent_lsn,write_lsn)) as write,
pg_size_pretty(pg_wal_lsn_diff(write_lsn,flush_lsn)) as flush,
pg_size_pretty(pg_wal_lsn_diff(flush_lsn,replay_lsn)) as replay,
pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(),replay_lsn)) as total_lag,
write_lag,
flush_lag,
replay_lag
from pg_stat_replication;
-[ RECORD 1 ]+----------------
pid | 118312
client_addr | <standby_ip1>
pending_wal | 0 bytes
write | 0 bytes
flush | 0 bytes
replay | 1184 bytes
total_lag | 1184 bytes
write_lag | 00:00:00.002672
flush_lag | 00:00:00.004407
replay_lag | 00:00:00.005293
Monitor Replication
Standby Server (WAL Receiver)
postgres=# select pg_is_in_recovery();
pg_is_in_recovery
-------------------
t
(1 row)
Monitor Replication
Standby Server (WAL Receiver)
postgres=# SELECT * FROM pg_stat_wal_receiver;
-[ RECORD 1 ]---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
pid | 22005
status | streaming
receive_start_lsn | 45DD/10000000
receive_start_tli | 1
received_lsn | 4642/D2AD4000
received_tli | 1
last_msg_send_time | 2020-02-19 10:29:23.285403+03
last_msg_receipt_time | 2020-02-19 10:29:23.308131+03
latest_end_lsn | 4642/D32A2CE8
latest_end_time | 2020-02-19 10:29:23.282374+03
slot_name |
sender_host | <primary_ip>
sender_port | <primary_pg_port>
conninfo | user=replicauser passfile=/var/lib/pgsql/.pgpass
dbname=replication host=<primary_ip> port=<primary_pg_port>
fallback_application_name=walreceiver sslmode=prefer
sslcompression=0 krbsrvname=postgres target_session_attrs=any
Monitor Replication
Standby Server (WAL Receiver)
postgres=# select pg_last_wal_receive_lsn(),
pg_last_wal_replay_lsn(),
pg_last_xact_replay_timestamp();
-[ RECORD 1 ]-----------------+------------------------------
pg_last_wal_receive_lsn | 4642/39054000
pg_last_wal_replay_lsn | 4642/39053200
pg_last_xact_replay_timestamp | 2020-02-19 10:22:43.373636+03
Monitor Replication
Standby Server (WAL Receiver)
postgres=# SELECT NOW() - pg_last_xact_replay_timestamp() lag_time;
-[ RECORD 1 ]-------------
lag_time | 00:00:00.001074
twitter: @gguncesi
blog: guncekaya.blogspot.com