Built-in Physical and Logical Replication in Postgresql Frat Gle - - - PowerPoint PPT Presentation

Built-in Physical and Logical Replication in Postgresql

Fırat Güleç

• Company

Hepsiexpress

• Now

Infrastructure & Database Manager Member of Postgresql Europe Contributing to Open Source Community

• Next Talks

Austria PGDay 2019, September 6th, 2019

Agenda

• What is Replication?
• Why do we need Replication?
• How many replication layers do we have?
• Understanding milestones of built-in Database Physical Replication.
• What is the purpose of replication? and How to rescue system in case of failover?
• What is Streaming Replication and what is its advantages? Async vs Sync, Hot standby etc.
• How to configurate Master and Standby Servers? And What is the most important parameters?

Example of topoloji.

• What is Cascading Replication and how to configurate it? Live Demo on Terminal.
• Quorum Commit for Sync Replication etc.
• What is Logical Replication coming with PostgreSQL 10? And What is its advantages?
• What is the purpose of Logical replication?
• How to set up Logical Replication and What are its benefits?
• Limitations of Logical Replication
• Logical Replication vs Physical Replication in detail.
• 10 Questions quiz and giving some gifts to participants according to their success.

What is Replication?

Master Slave Slave Slave Slave Slave Slave Slave Slave Slave

Replication Layers

Application Database logical Database physical Operating System Hardware

SAN NAS DRBD Streaming Replication Logical Replication Slony Londiste Pgpool II Bucardo

High Availability, Load Balancing, a and Replication Feature Matrix

https://www.postgresql.org/docs/11/different-replication-solutions.html

Pyhsical Replication

The purpose of Streaming Replication?

High availability

App Server Client

Master Slave

The purpose of Streaming Replication?

Load Balancing

App Server Client

Master Slave %50 %50

Database Physical Replication Milestones

File based from 8.3 Streaming Replication 9.0 Synchronous since 9.1 Quorum commit since 10.0

Architecture of Streaming Replication

Master Slave WAL

WAL sender WAL receiver

WAL Record 16 MB pg_wal WAL Archieve directory

Database Physical

File based from 8.3 Streaming Replication 9.0 Synchronous since 9.1 Quorum commit since 10.0

File based Replication from Postgresql 8.3

Master Slave WAL

WAL sender WAL receiver

WAL Record 16 MB pg_wal WAL Archieve directory WAL 16 MB

Database Physical

File based from 8.3 Streaming Replication 9.0 Synchronous since 9.1 Quorum commit since 10.0

Streaming Replication

Master Slave WAL

WAL sender WAL receiver

WAL Record 16 MB pg_wal WAL Archieve directory Record-based log shipping The primary and standby servers so that they are as similar as possible 1- Major PostgreSQL release levels is not possible 2- 32-bit to a 64-bit system will not work. pg_wal

WAL

Database Physical

File based from 8.3 Streaming Replication 9.0 Synchronous since 9.1 Quorum commit since 10.0

Streaming Replication

Master Slave WAL

WAL sender WAL receiver

WAL Record 16 MB

1- Async vs Sync 2- Hot Standby or Warm Standby?

read write read pg_wal WAL Archieve directory

Database Physical

File based from 8.3 Streaming Replication 9.0 Synchronous since 9.1 Quorum commit since 10.0

Quorum Commit for Sync Replication

• 10.0: Quorum Commit

Master Slave 2 Slave 3 Slave 1 sync 10.0synchronous_standby_names ANY 2 (s1, s2, s3) sync sync 10.0synchronous_standby_names FIRST 2 (s1, s2, s3)

6 Steps for Streaming Replication

Master Slave

• 1. Replica user is created
• 2. Configuration for Master
• 3. Authentication
• 4. Configuration for Slave
• 5. Synchronization
• 6. Create Recovery.conf

Hot Standy - postgresql.conf

• wal_level  determines how much information is written

wal_level=‘minimal’ wal_level=‘replica’ wal_level=‘logical’

max_wal_senders

• max_wal_senders= specifies the maximum

number of concurrent connections

Master Slave Slave Slave

wal_keep_segments

wal_keep_segments

Master Slave WAL WAL

WAL sender WAL receiver

WAL Record 16 MB pg_wal WAL Archieve directory

1-Replication User for Master

• sudo -u postgres psql

Next, create a new user and role with the following command:

• postgres=#CREATE USER replica REPLICATION LOGIN ENCRYPTED

PASSWORD ‘*******'; postgres=#\du

• You should see the following output:

2-Hot Standby C Configuration for r Master

in postgresql.conf

• wal_level=replica
• wal_keep_segment=20
• max_wal_sender=3
• archieve_mode=on
• archive_command = 'test ! -f /var/lib/postgresql/pg_log_archive/%f

&& cp %p /var/lib/postgresql/pg_log_archive/%f'

3-pg_h _hba.conf configuration for Master

For authentication: host replication replica 10.90.82.61/32 md5

4-Hot standy configuration for slave

In Postgresql.conf

• hot standby=on

Below configuration in case of fail over

• archive_mode = on
• archive_command = 'test ! -f /var/lib/postgresql/pg_log_archive/%f && cp

%p /var/lib/postgresql/pg_log_archive/%f‘

• wal_keep_segment=20
• max_wal_sender=3

5-Syncronize Data from Master Server to Slave Server

On the slave server, stop the postgresql service:

• sudo systemctl stop postgresql and move existing data folder.
• pg_basebackup -h 10.90.82.31 -D /var/lib/postgresql/11/main/ -P -U

replica --wal-method=fetch

Master Slave

Transfering…… Data file directory

/var/lib/postgresql/11/main 10.90.82.31 10.90.82.61

6-Recovery.conf file on standby

Datafile Directory/var/lib/postgresql/11/main

Test Replication

Commandpsql -x -c "select * from pg_stat_replication;" Streaming Replication is DONE

Cascading Postgresql Replication

Master Slave Slave Slave Slave Slave Slave Upstream Slaves Downstream Slaves Replication Cascading Replicaiton hot_standby=on Syncronization 1. 2. 3. async

• 4. recovery.conf

max_standby_archive_delay for standby

Logical Replication

Lo Logical r replication

Master Slave

St Streamin ing r replic licatio ion

Master Slave DB A DB B DB A DB B DB A DB B DB A Employee

What i t is Lo Logical Replication?

Master Slave

St Streamin ing r replic licatio ion

Master Slave Recovery Mode Create index Create table Drop table Create function Insert Delete Select Hot Standby Select Warm Standby New Instance Create index Create table Drop table Create function Insert Delete Select

What i t is Lo Logical Replication?

Lo Logical r replication

Master Slave

St Streamin ing r replic licatio ion

Master Slave PostgreSQL 9.5 PostgreSQL 9.5 PostgreSQL 11 PostgreSQL 12 PostgreSQL 9.6 PostgreSQL 10 PostgreSQL 10 PostgreSQL 11

What i t is Lo Logical Replication?

Lo Logical r replication

Ex Expected use cases of

• f Log
• gical

l replicati tion

St Streamin ing r replic licatio ion

Client

• 1. High availability
• 2. Load Balancing

Master Standby

• 1. Analytical Purpose

Master A Master B DB C

• 2. Sharing a subnet of database

Ex Expected use cases of

• f Log
• gical

l replicati tion

St Streamin ing r replic licatio ion

Client

• 1. High availability
• 2. Load Balancing

Master Standby

Master A Master B

• 3. Online Upgrade

PostgreSQL 10 PostgreSQL 11 App

Dump Schema Logical Replication

Logical r replication

Master Slave

St Streamin ing r replic licatio ion

Master Slave

WAL sender WAL receiver

WAL Streaming

WAL sender

Publication Subscription

Apply Worker

WAL WAL

Decoding Decoded message Logical Replication Launcher triggers

Arch chitectu cture

What i is Publicati tion&Subscription?

St Streamin ing r replic licatio ion

Master Slave Slave Streaming Replication Cascading Replication Slave A Slave B Publication 2 Publication 1 Subscription 2 Master Subscription 1 Pub 3 Slave C Subs 3 Users Name Users Name Surname

Async vs Sync

Logical r replication

Logical Replication Slot 1 Logical Replication Slot 2

Lo Logical r replicati tion

St Streamin ing r replic licatio ion

Master Slave Slave Streaming Replication Cascading Replication DB A Sub 1 Pub 1 Surname DB B Address Pub 2 Sub 2

Lo Logical r replicati tion

St Streamin ing r replic licatio ion

Master Slave Slave Streaming Replication Cascading Replication Server A Surname Server B Surname Bi directional replication not allowed

Logical Replication Limitations in 11.0

• does not replicate schema and DDL
• does not replicate sequences
• does not replicate Large Objects
• Replication is only possible from base tables to base tables

Lo Logical r replicati tion with Postg tgreSQL 11 11

Master 10.90.82.30

wal_level = logical 1. 3. WAL 6.

Publication 1

repluser CREATE TABLE user (user_name text PRIMARY KEY, full_name text); CREATE PUBLICATION Publication1 for table user; 4. CREATE ROLE repluser WITH LOGIN PASSWORD 'admin123' REPLICATION ; user GRANT SELECT ON public.delivery TO repluser; GRANT USAGE ON SCHEMA public TO repluser; 5. 7.

Slave 10.90.82.31

Connected to db with postgres \c database1 2.

Pg_hba.conf host all repluser 10.90.82.31/0 md5

Connected to db with postgres \c database2 1. 2. CREATE SUBSCRIPTION Subscribe1 CONNECTION 'host=10.90.82.31 dbname=database1 user=repluser password=admin123' PUBLICATION Publication 1 ;

Subscribe1

CREATE TABLE user (user_name text PRIMARY KEY, full_name text); user 3.

Logical Replication is DONE

Topology(Logical & Pyhsical Replication)

App Server Client

Master Slave read write read Slave Cascading Replication Streaming Replication read

Reporting Server

Slave Logical Replication Analytical purposes

App Server

read write CPU & Memory

Monitoring on Terminal

1- pg_stat_replication 2- pg_replication_slots 3- pg_publication_tables 1- pg_subscription 2- pg_stat_subscription 3- pg_subscription_rel Master Slave(Logical) Slave(Physical) 1- pg_stat_wal_receiver

https://kahoot.it/

With an innovative service approach, We are shaping delivery and transportation system in Turkey

www.hepsiexpress.com

Hepsiexpress & Horizon 2020

ISA: Innovative logistics Solutions and data framework for the on- demand economy

We are looking for partners!

2018 Applications Focus Areas for New Projects

• Logistics Research & Innovation
• Route Optimisation
• Natural Language Processing
• Open Source Softwares

Thank you Fırat Güleç