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
Streaming Replication in PostgreSQL
How to Replicate Your Postgres
By Günce Kaya
How to Replicate Your Postgres
- 987
