Streaming Replication
in PostgreSQL
![](https://s3.amazonaws.com/media-p.slid.es/uploads/926452/images/7088687/pasted-from-clipboard.png)
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
![](https://s3.amazonaws.com/media-p.slid.es/uploads/926452/images/7084272/pasted-from-clipboard.png)
![](https://s3.amazonaws.com/media-p.slid.es/uploads/926452/images/7084278/pasted-from-clipboard.png)
- 16MB by default (but possible increase)
Write Ahead Log Files
- It reduces disk writes
- For debugging WAL files, use pg_waldump tool
![](https://s3.amazonaws.com/media-p.slid.es/uploads/926452/images/7084296/pasted-from-clipboard.png)
Write Ahead Log Files
Streaming Replication (SR) Topology
![](https://s3.amazonaws.com/media-p.slid.es/uploads/926452/images/7078213/pasted-from-clipboard.png)
- 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
How to Replicate Your Postgres
- 874