Distributed PostgreSQL Santa Clara, California | April 23th 25th, - - PowerPoint PPT Presentation

▶
distributed postgresql
SMART_READER_LITE
LIVE PREVIEW

Distributed PostgreSQL Santa Clara, California | April 23th 25th, - - PowerPoint PPT Presentation

Sep 13, 2023 45 likes •308 views

Distributed PostgreSQL Santa Clara, California | April 23th 25th, 2018 Simon Riggs CTO, 2ndQuadrant, leading PostgreSQL Support Company Database Architect & Consultant Author of PostgreSQL Admin Cookbook PostgreSQL Major

slide-1
SLIDE 1

Santa Clara, California | April 23th – 25th, 2018

Distributed PostgreSQL

slide-2
SLIDE 2

2

Simon Riggs

CTO, 2ndQuadrant, leading PostgreSQL Support Company Database Architect & Consultant Author of “PostgreSQL Admin Cookbook” PostgreSQL Major Contributor for 15 years PostgreSQL Committer

slide-3
SLIDE 3

3

Topics

Replication for High Availability Interoperability & Remote Data Access Multi-node Databases

slide-4
SLIDE 4

Replication for High Availability

slide-5
SLIDE 5

5

Physical Streaming Replication

slide-6
SLIDE 6

6

Physical Replication Features

slide-7
SLIDE 7

7

Notes

Streaming Replication using Write Ahead Log (WAL) data 2ndQuadrant repmgr for failover management 2ndQuadrant Barman for Backup & Recovery Management

slide-8
SLIDE 8

Remote Data Access

slide-9
SLIDE 9

9

PostgreSQL Interoperability

slide-10
SLIDE 10

10

PostgreSQL Remote Data Access (Pull)

CREATE EXTENSION postgres_fdw; CREATE SERVER red FOREIGN DATA WRAPPER postgres_fdw OPTIONS …; IMPORT FOREIGN SCHEMA myapp FROM SERVER red INTO myapp_local; SELECT * FROM myapp_local.table1;

slide-11
SLIDE 11

11

PostgreSQL Foreign Data Wrapper

postgres_fdw

  • Access to other PostgreSQL servers, even with differing release levels
  • WHERE clause, joins pushed down to Foreign Server

file_fdw

  • Allows you to access data in same way as a COPY command

External projects

  • MySQL
  • Oracle
  • ODBC
slide-12
SLIDE 12

12

PostgreSQL Pub/Sub Replication (Push)

<manage DDL copying> CREATE PUBLICATION mypublisheddata ; CREATE SUBSCRIPTION mysub PUBLICATION mypublisheddata CONNECTION ‘service=node1’;

slide-13
SLIDE 13

13

PostgreSQL Advanced Features

Push (“Logical”) or Pull Data Access (FDWs) Multi-server heterogeneous SQL Improvements in every release 2ndQuadrant pglogical for online upgrade

slide-14
SLIDE 14

Multi-node Access

slide-15
SLIDE 15

15

Topics

Sharding Multi-node Query Multi-master Database

slide-16
SLIDE 16

16

Sharding

Store data across multiple nodes in cluster Access mechanism to route queries and writes Elastically Scalability to enlarge cluster

slide-17
SLIDE 17

17

Multi-node Query

Requires consistency model to retrieve consistent data set Node redistribution required to handle all join types Multi-node aware optimizer to understand how to cope

slide-18
SLIDE 18

18

Massively Parallel Processing (MPP) with Postgres-XL

slide-19
SLIDE 19

19

Postgres-XL

Fork of PostgreSQL 10 to provide Scalable PostgreSQL Hash distributed tables and replicated tables Multi-node transactions, Multi-node consistency Multi-node planner and inter-node data redistribution High volume Data Load Queue

slide-20
SLIDE 20

20

slide-21
SLIDE 21

21

slide-22
SLIDE 22

22

slide-23
SLIDE 23

23

Postgres-BDR

BDR3 runs as a PostgreSQL Extension on PG10+ Multi-Master Replication/ Very High Availability architecture 2-way Logical Replication with conflict detection/handling Duplicated and Local tables Write scaling with local nodes

slide-24
SLIDE 24

24

Multi-node Options

Sharding – Native Postgres, Citus MPP – Postgres-XL, Greenplum Multi-master Database – 2ndQuadrant BDR

slide-25
SLIDE 25

25

Rate My Session

slide-26
SLIDE 26

Thank You!

Simon Riggs, 2ndQuadrant