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

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 Contributor for 15 years PostgreSQL Committer 2

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

Replication for High Availability

Physical Streaming Replication 5

Physical Replication Features 6

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

Remote Data Access

PostgreSQL Interoperability 9

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; 10

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 11

PostgreSQL Pub/Sub Replication (Push) <manage DDL copying> CREATE PUBLICATION mypublisheddata ; CREATE SUBSCRIPTION mysub PUBLICATION mypublisheddata CONNECTION ‘service=node1’; 12

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

Multi-node Access

Topics Sharding Multi-node Query Multi-master Database 15

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

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 17

Massively Parallel Processing (MPP) with Postgres-XL 18

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 19

20

21

22

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 23

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

Rate My Session 25

Thank You! Simon Riggs, 2ndQuadrant