StreSd332g_ReplADSSAon_aerasd333333.pptx

post

1. Module-3 Streaming Replication

2. Module Objective Introduction to Streaming Replication Streaming Architecture Replication Parameters Replication Slots Synchronous Replication Replication Monitoring Failover Demo

3. Streaming Replication WAL record chunks are streamed by database servers to keep data in sync. The standby server connects to the master to receive the WAL chunks. The WAL records are streamed as they are generated. The streaming of WAL records need not wait for the WAL file to be filled. This allows a standby server to stay more up-to-date than is possible with file-based log shipping. By default, streaming replication is asynchronous even though it also supports synchronous replication.

4. Streaming Architecture Master Server Slave Server Read-Only Wal Sender Wal Receiver

5. Replication Parameters Wal_level  : Replica - determines how much information is written to the WAL. The default value is replica, which writes enough data to support WAL archiving and replication, including running read-only queries on a standby server. W al_log_hints  = on - required for pg_rewind capability when standby goes out of sync with master. M ax_wal_senders  = integer - Specifies the maximum number of concurrent connections from standby servers or streaming base backup clients (i.e., the maximum number of simultaneously running WAL sender processes). The default is 10. value 0 means replication is disabled.

6. Cont …. W al_keep_segments  :integer - Specifies the minimum number of past log file segments kept in the pg_wal directory, in case a standby server needs to fetch them for streaming replication. If a standby server connected to the sending server falls behind by more than wal_keep_segments segments, the sending server might remove a WAL segment still needed by the standby, in which case the replication connection will be terminated. hot_standby  = on - Enables read only connection on the node when it is in standby role. This is ignored when the server is running as master. Standby server will begin accepting read only connections once the recovery has brought the system to a consistent state . 

7. Replication Slots   Replication slot is used to retain the WAL files when the standby goes offline or disconnected. Master server uses replication slots to keeps track of how much the standby lags and retain the WAL it needs files until the standby reconnects again . R eplication slots came in with PostgreSQL 9.4, before that wal_keep_segment parameter use to govern how many wal files need to be maintained. Replication slots have to been created manually and the default value is 10. PostgreSQL Replication slots are of two types: Physical replication slots Logical replication slots

8. Cont …. How to create a physical replication slot. Syntax : select pg_create_physical_replication_slot (‘Standby ’); How to Monitor a replication slot. Syntax : select * from pg_replication_slots ; How to delete a replication slot. Syntax : select pg_drop_replication_slot (‘standby ’); How to create logical replication slot. Syntax : select pg_create_logical_replication_slot (‘Standby’);

9. Monitoring Basics Constant Monitoring is a key to ensure healthy replication between primary and standby. Replication failure can happen any time due to multiple issue: Network failure Heavy Load on Primary Heavy Load on Standby Other issues

10. Monitoring Scripts P g_stat_replication view (primary server) pid : Process id of walsender process usesysid : OID of user which is used for Streaming replication. usename : Name of user which is used for Streaming replication application_name : Application name connected to Primary client_addr : Address of standby/streaming replication client_hostname : Hostname of standby. client_port : TCP port number on which standby communicating with WAL sender backend_start : Start time when SR connected to Primary. state: Current WAL sender state i.e streaming sent_location : Last transaction location sent to standby. write_location : Last transaction written on disk at standby flush_location : Last transaction flush on disk at standby. replay_location : Last transaction flush on disk at standby. sync_priority : Priority of standby server being chosen as synchronous standby sync_state : Sync State of standby (is it async or synchronous).

11. Cont …. Function to check whether standby is in recovery mode or not. Select pg_is_in_recovery (); The stats related to the incoming replication can be monitored using Select * FROM pg_stat_wal_receiver ; Get last write-ahead log location received and synced to disk by streaming replication. (Standby). Select pg_last_wal_receive__ lsn (); Get last write-ahead log location replayed during recovery. (Standby) Select pg_last_wal_replay_lsn (); Get time stamp of last transaction replayed during recovery. (Standby) Select pg_last_xact_replay_timestamp ();

12. Synchronous Replication Synchronous replication offers the ability to commit a transaction (or write data) to the primary database and the standby/replica simultaneously.  Transaction are considered successful when all changes made by the transaction have been transferred to one or more synchronous standby servers. Syntax to enable Synchronous ALTER SYSTEM SET synchronous_standby_names TO '*‘ Reload PostgreSQL 12 service to apply the new changes.

13. Streaming Failover Failover is the ability of a system to continue functioning even if some failure occurs. Functioning of the system are assumed by secondary components if the primary components fail. PostgreSQL in itself does not provide an automatic failover solution. We can manually failover postgresql from master to server using below mentioned methods: ./ pg_ctl promote -D / var /lib/ pgsql /12/data Create a trigger file with the file name and path specified by the promote_trigger_file . SELECT pg_promote ();

14. Thank you. 