PostgreSQL Replication in 2017 PGDay.RU St Petersburg, Russia - - PowerPoint PPT Presentation

▶
postgresql replication in 2017
SMART_READER_LITE
LIVE PREVIEW

PostgreSQL Replication in 2017 PGDay.RU St Petersburg, Russia - - PowerPoint PPT Presentation

Feb 20, 2023 329 likes •776 views

PostgreSQL Replication in 2017 PGDay.RU St Petersburg, Russia Magnus Hagander magnus@hagander.net Magnus Hagander Redpill Linpro Infrastructure services Principal database consultant PostgreSQL Core Team member Committer PostgreSQL

slide-1
SLIDE 1

PostgreSQL Replication in 2017

PGDay.RU St Petersburg, Russia Magnus Hagander magnus@hagander.net

slide-2
SLIDE 2

Magnus Hagander

Redpill Linpro Infrastructure services Principal database consultant PostgreSQL Core Team member Committer PostgreSQL Europe

slide-3
SLIDE 3

Replication

slide-4
SLIDE 4

"PostgreSQL doesn't have replication"

slide-5
SLIDE 5

"PostgreSQL doesn't have replication"

"So we have to use MySQL"

slide-6
SLIDE 6

Replication

Wasn't true back then Even less true now! Now there are too many choices? But you have to pick one And can be hard to use

slide-7
SLIDE 7

Replication

Can be done at different layers From hardware (ish) To application

slide-8
SLIDE 8

Replication layers

↓ Application ↓ App-in-database ↓ Database logical ↓ Database physical ↓ Operating system ↓ Hardware

slide-9
SLIDE 9

Start from the bottom

slide-10
SLIDE 10

SAN replication

Hardware takes care of replication Block level Transparent to OS And to PostgreSQL Common enterprise solution Especially with VMs

slide-11
SLIDE 11

SAN replication

From single rack To multi-site Synchronous Guaranteed to never fail Riiiiight...

slide-12
SLIDE 12

Replication layers

↓ Application ↓ App-in-database ↓ Database logical ↓ Database physical ↓ Operating system ↓ Hardware

slide-13
SLIDE 13

DRBD

Similar in style to SAN Implementation in OS driver Performance?

slide-14
SLIDE 14

Replication layers

↓ Application ↓ App-in-database ↓ Database logical ↓ Database physical ↓ Operating system ↓ Hardware

slide-15
SLIDE 15

Database physical

WAL based replication File based from 8.3 Streaming since 9.0 Synchronous since 9.1 Transaction level mixing (etc)

slide-16
SLIDE 16

wal_level = 'replica'

slide-17
SLIDE 17

Synchronous mode

  • ff

local

  • n

remote_apply

slide-18
SLIDE 18

Streaming replication

Primary choice today Easy to set up Hard to get wrong Efficient Built-in

slide-19
SLIDE 19

Streaming replication

$ pg_basebackup -D /var/lib/pgsql \

  • h master -U replica \
  • X stream -R -P \
  • S replica1

$ sudo service postgresql-9.6 start

slide-20
SLIDE 20

Streaming replication

Architecture/compile flag dependent Whole cluster only Standby completely read-only Master → standby only Excellent for availability

slide-21
SLIDE 21

Streaming replication

No built-in cluster management Manual or automatic Provides infrastructure No fail-back (no easy one) Easy to get started, harder to maintain

slide-22
SLIDE 22

Cluster management

Patroni

Designed for automatic management Including automatic failover Uses etcd, zookeeper, or consul Integrates with haproxy

slide-23
SLIDE 23

Cluster management

repmgr

Fewer pre-requisites Easier for manual management Comes with repmgrd that does automatic Does not handle connection management Use e.g. rebouncer Or haproxy

slide-24
SLIDE 24

Replication layers

↓ Application ↓ App-in-database ↓ Database logical ↓ Database physical ↓ Operating system ↓ Hardware

slide-25
SLIDE 25

Database logical

Logical decoding since 9.4 Logical replication since 10 Built-in, that is Piggy-backs on WAL Very low overhead

slide-26
SLIDE 26

wal_level = 'logical'

slide-27
SLIDE 27

Logical replication

Reconstructs changes by row Replicates row content not SQL statements Fully transactional

slide-28
SLIDE 28

Logical replication

Table-level partial replication Table-level bi-directional replication

slide-29
SLIDE 29

Logical replication

CREATE TABLE testtable (a int PRIMARY KEY, b text); CREATE PUBLICATION testpub FOR TABLE testtable;

slide-30
SLIDE 30

Logical replication

CREATE TABLE testtable (a int PRIMARY KEY, b text); CREATE SUBSCRIPTION testsub CONNECTION 'host=/tmp port=5500 dbname=postgres user=mha' PUBLICATION testpub;

slide-31
SLIDE 31

Logical replication

Data replication only No schema No sequences Suitable for data distribution But not for HA Lacks failover slots!

slide-32
SLIDE 32

pglogical

External version of logical replication Merged piece by piece More capabilities! Not as deeply integrated

slide-33
SLIDE 33

pglogical

Sequence replication Row based filtering Column based filtering Merging and conflict resolution ...

slide-34
SLIDE 34

pglogical

Supports PostgreSQL 9.4 Zero (or close to zero) downtime upgrades!

slide-35
SLIDE 35

Replication layers

↓ Application ↓ App-in-database ↓ Database logical ↓ Database physical ↓ Operating system ↓ Hardware

slide-36
SLIDE 36

App-in-database

Trigger based systems Slony Bucardo Londiste ...

slide-37
SLIDE 37

Trigger based

For a long time the only choice Now mostly superseded Much higher overhead than logical Complex scenarios

slide-38
SLIDE 38

Replication layers

↓ Application ↓ App-in-database ↓ Database logical ↓ Database physical ↓ Operating system ↓ Hardware

slide-39
SLIDE 39

Application

Replication done entirely in application Very difficult for transactional Useful in limited cases

slide-40
SLIDE 40

Summary

slide-41
SLIDE 41

High Availability

Use streaming replication Mix of sync and async Consider patroni or repmgr

slide-42
SLIDE 42

Data distribution

Logical replication in 10 pglogical in 9.4+ Or in 10 if built-in is not enough Upgrade away from your Slony...

slide-43
SLIDE 43

Need both?

Use both!

slide-44
SLIDE 44

Thank you!

Magnus Hagander magnus@hagander.net @magnushagander http://www.hagander.net/talks/

This material is licensed