Streaming Replication

in PostgreSQL

Günce Sarıkaya

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
  • Streaming Replication (SR) Topology
  • Setup SR Replication
  • Monitor Replication
  • All changes are not write to disk directly
  • WAL files keep transactional information as binary
  • 16MB by default (but possible increase)

Write Ahead Log Files

  • It reduces disk writes
  • For debugging WAL files, use pg_waldump tool

Write Ahead Log Files

Streaming Replication (SR) Topology

  • First copy all files than start to streaming
  • Possible to archiving WAL files
    • archive_command
  • Standby Modes
    • Hot standby
    • Warm standby

Streaming Replication (SR)

Setup SR Replication

Master Postgres Server:

  • postgresql.conf
    • listen_address  = '*' or use IP
    • wal_level = 'replica'
    • max_wal_senders
    • wal_keep_segments
    • archive_mode = on
    • archive_command
    • hot_standby = on

WAL Levels

  • minimal
  • logical
  • replica

Setup SR Replication

Master Postgres Server:

  • Replication user for replication
    • CREATE USER user_name REPLICATION LOGIN;
  • Add replciation rule in pg_hba.conf file
    • host replication <<user>> <IP> <method>

Setup SR Replication

Standby Postgres Server:

not anymore..

  • postgresql.conf
    • Same as primary's postgresql.conf​
  • recovery.conf
    • primary_conninfo
    • standby_mode
  • Run pg_basebackup command on standby
  • Start standby Postgres service

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:

  • startup

  • 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

Thanks!

twitter: @gguncesi

blog: guncekaya.blogspot.com

How to Replicate Your Postgres

By Günce Kaya

Made with Slides.com

How to Replicate Your Postgres

  • 927

More from Günce Kaya