distributed postgresql - percona · postgres-xl fork of postgresql 10 to provide scalable...
TRANSCRIPT
![Page 1: Distributed PostgreSQL - Percona · Postgres-XL Fork of PostgreSQL 10 to provide Scalable PostgreSQL Hash distributed tables and replicated tables Multi-node transactions, Multi-node](https://reader034.vdocuments.us/reader034/viewer/2022043004/5f86f640ce48e0489e7ac3ae/html5/thumbnails/1.jpg)
Santa Clara, California | April 23th – 25th, 2018
DistributedPostgreSQL
![Page 2: Distributed PostgreSQL - Percona · Postgres-XL Fork of PostgreSQL 10 to provide Scalable PostgreSQL Hash distributed tables and replicated tables Multi-node transactions, Multi-node](https://reader034.vdocuments.us/reader034/viewer/2022043004/5f86f640ce48e0489e7ac3ae/html5/thumbnails/2.jpg)
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
![Page 3: Distributed PostgreSQL - Percona · Postgres-XL Fork of PostgreSQL 10 to provide Scalable PostgreSQL Hash distributed tables and replicated tables Multi-node transactions, Multi-node](https://reader034.vdocuments.us/reader034/viewer/2022043004/5f86f640ce48e0489e7ac3ae/html5/thumbnails/3.jpg)
3
Topics
Replication for High Availability
Interoperability & Remote Data Access
Multi-node Databases
![Page 4: Distributed PostgreSQL - Percona · Postgres-XL Fork of PostgreSQL 10 to provide Scalable PostgreSQL Hash distributed tables and replicated tables Multi-node transactions, Multi-node](https://reader034.vdocuments.us/reader034/viewer/2022043004/5f86f640ce48e0489e7ac3ae/html5/thumbnails/4.jpg)
Replication forHigh Availability
![Page 5: Distributed PostgreSQL - Percona · Postgres-XL Fork of PostgreSQL 10 to provide Scalable PostgreSQL Hash distributed tables and replicated tables Multi-node transactions, Multi-node](https://reader034.vdocuments.us/reader034/viewer/2022043004/5f86f640ce48e0489e7ac3ae/html5/thumbnails/5.jpg)
5
Physical Streaming Replication
![Page 6: Distributed PostgreSQL - Percona · Postgres-XL Fork of PostgreSQL 10 to provide Scalable PostgreSQL Hash distributed tables and replicated tables Multi-node transactions, Multi-node](https://reader034.vdocuments.us/reader034/viewer/2022043004/5f86f640ce48e0489e7ac3ae/html5/thumbnails/6.jpg)
6
Physical Replication Features
![Page 7: Distributed PostgreSQL - Percona · Postgres-XL Fork of PostgreSQL 10 to provide Scalable PostgreSQL Hash distributed tables and replicated tables Multi-node transactions, Multi-node](https://reader034.vdocuments.us/reader034/viewer/2022043004/5f86f640ce48e0489e7ac3ae/html5/thumbnails/7.jpg)
7
Notes
Streaming Replication using Write Ahead Log (WAL) data
2ndQuadrant repmgr for failover management
2ndQuadrant Barman for Backup & Recovery Management
![Page 8: Distributed PostgreSQL - Percona · Postgres-XL Fork of PostgreSQL 10 to provide Scalable PostgreSQL Hash distributed tables and replicated tables Multi-node transactions, Multi-node](https://reader034.vdocuments.us/reader034/viewer/2022043004/5f86f640ce48e0489e7ac3ae/html5/thumbnails/8.jpg)
Remote Data Access
![Page 9: Distributed PostgreSQL - Percona · Postgres-XL Fork of PostgreSQL 10 to provide Scalable PostgreSQL Hash distributed tables and replicated tables Multi-node transactions, Multi-node](https://reader034.vdocuments.us/reader034/viewer/2022043004/5f86f640ce48e0489e7ac3ae/html5/thumbnails/9.jpg)
9
PostgreSQL Interoperability
![Page 10: Distributed PostgreSQL - Percona · Postgres-XL Fork of PostgreSQL 10 to provide Scalable PostgreSQL Hash distributed tables and replicated tables Multi-node transactions, Multi-node](https://reader034.vdocuments.us/reader034/viewer/2022043004/5f86f640ce48e0489e7ac3ae/html5/thumbnails/10.jpg)
10
PostgreSQL Remote Data Access (Pull)
CREATE EXTENSION postgres_fdw;
CREATE SERVER red FOREIGN DATA WRAPPER postgres_fdwOPTIONS …;
IMPORT FOREIGN SCHEMA myappFROM SERVER redINTO myapp_local;
SELECT * FROM myapp_local.table1;
![Page 11: Distributed PostgreSQL - Percona · Postgres-XL Fork of PostgreSQL 10 to provide Scalable PostgreSQL Hash distributed tables and replicated tables Multi-node transactions, Multi-node](https://reader034.vdocuments.us/reader034/viewer/2022043004/5f86f640ce48e0489e7ac3ae/html5/thumbnails/11.jpg)
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
![Page 12: Distributed PostgreSQL - Percona · Postgres-XL Fork of PostgreSQL 10 to provide Scalable PostgreSQL Hash distributed tables and replicated tables Multi-node transactions, Multi-node](https://reader034.vdocuments.us/reader034/viewer/2022043004/5f86f640ce48e0489e7ac3ae/html5/thumbnails/12.jpg)
12
PostgreSQL Pub/Sub Replication (Push)
<manage DDL copying>
CREATE PUBLICATION mypublisheddata;
CREATE SUBSCRIPTION mysubPUBLICATION mypublisheddataCONNECTION ‘service=node1’;
![Page 13: Distributed PostgreSQL - Percona · Postgres-XL Fork of PostgreSQL 10 to provide Scalable PostgreSQL Hash distributed tables and replicated tables Multi-node transactions, Multi-node](https://reader034.vdocuments.us/reader034/viewer/2022043004/5f86f640ce48e0489e7ac3ae/html5/thumbnails/13.jpg)
13
PostgreSQL Advanced Features
Push (“Logical”) or Pull Data Access (FDWs)
Multi-server heterogeneous SQL
Improvements in every release
2ndQuadrant pglogical for online upgrade
![Page 14: Distributed PostgreSQL - Percona · Postgres-XL Fork of PostgreSQL 10 to provide Scalable PostgreSQL Hash distributed tables and replicated tables Multi-node transactions, Multi-node](https://reader034.vdocuments.us/reader034/viewer/2022043004/5f86f640ce48e0489e7ac3ae/html5/thumbnails/14.jpg)
Multi-node Access
![Page 15: Distributed PostgreSQL - Percona · Postgres-XL Fork of PostgreSQL 10 to provide Scalable PostgreSQL Hash distributed tables and replicated tables Multi-node transactions, Multi-node](https://reader034.vdocuments.us/reader034/viewer/2022043004/5f86f640ce48e0489e7ac3ae/html5/thumbnails/15.jpg)
15
Topics
Sharding
Multi-node Query
Multi-master Database
![Page 16: Distributed PostgreSQL - Percona · Postgres-XL Fork of PostgreSQL 10 to provide Scalable PostgreSQL Hash distributed tables and replicated tables Multi-node transactions, Multi-node](https://reader034.vdocuments.us/reader034/viewer/2022043004/5f86f640ce48e0489e7ac3ae/html5/thumbnails/16.jpg)
16
Sharding
Store data across multiple nodes in cluster
Access mechanism to route queries and writes
Elastically Scalability to enlarge cluster
![Page 17: Distributed PostgreSQL - Percona · Postgres-XL Fork of PostgreSQL 10 to provide Scalable PostgreSQL Hash distributed tables and replicated tables Multi-node transactions, Multi-node](https://reader034.vdocuments.us/reader034/viewer/2022043004/5f86f640ce48e0489e7ac3ae/html5/thumbnails/17.jpg)
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
![Page 18: Distributed PostgreSQL - Percona · Postgres-XL Fork of PostgreSQL 10 to provide Scalable PostgreSQL Hash distributed tables and replicated tables Multi-node transactions, Multi-node](https://reader034.vdocuments.us/reader034/viewer/2022043004/5f86f640ce48e0489e7ac3ae/html5/thumbnails/18.jpg)
18
Massively Parallel Processing (MPP) withPostgres-XL
![Page 19: Distributed PostgreSQL - Percona · Postgres-XL Fork of PostgreSQL 10 to provide Scalable PostgreSQL Hash distributed tables and replicated tables Multi-node transactions, Multi-node](https://reader034.vdocuments.us/reader034/viewer/2022043004/5f86f640ce48e0489e7ac3ae/html5/thumbnails/19.jpg)
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
![Page 20: Distributed PostgreSQL - Percona · Postgres-XL Fork of PostgreSQL 10 to provide Scalable PostgreSQL Hash distributed tables and replicated tables Multi-node transactions, Multi-node](https://reader034.vdocuments.us/reader034/viewer/2022043004/5f86f640ce48e0489e7ac3ae/html5/thumbnails/20.jpg)
20
![Page 21: Distributed PostgreSQL - Percona · Postgres-XL Fork of PostgreSQL 10 to provide Scalable PostgreSQL Hash distributed tables and replicated tables Multi-node transactions, Multi-node](https://reader034.vdocuments.us/reader034/viewer/2022043004/5f86f640ce48e0489e7ac3ae/html5/thumbnails/21.jpg)
21
![Page 22: Distributed PostgreSQL - Percona · Postgres-XL Fork of PostgreSQL 10 to provide Scalable PostgreSQL Hash distributed tables and replicated tables Multi-node transactions, Multi-node](https://reader034.vdocuments.us/reader034/viewer/2022043004/5f86f640ce48e0489e7ac3ae/html5/thumbnails/22.jpg)
22
![Page 23: Distributed PostgreSQL - Percona · Postgres-XL Fork of PostgreSQL 10 to provide Scalable PostgreSQL Hash distributed tables and replicated tables Multi-node transactions, Multi-node](https://reader034.vdocuments.us/reader034/viewer/2022043004/5f86f640ce48e0489e7ac3ae/html5/thumbnails/23.jpg)
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
![Page 24: Distributed PostgreSQL - Percona · Postgres-XL Fork of PostgreSQL 10 to provide Scalable PostgreSQL Hash distributed tables and replicated tables Multi-node transactions, Multi-node](https://reader034.vdocuments.us/reader034/viewer/2022043004/5f86f640ce48e0489e7ac3ae/html5/thumbnails/24.jpg)
24
Multi-node Options
Sharding – Native Postgres, Citus
MPP – Postgres-XL, Greenplum
Multi-master Database – 2ndQuadrant BDR
![Page 25: Distributed PostgreSQL - Percona · Postgres-XL Fork of PostgreSQL 10 to provide Scalable PostgreSQL Hash distributed tables and replicated tables Multi-node transactions, Multi-node](https://reader034.vdocuments.us/reader034/viewer/2022043004/5f86f640ce48e0489e7ac3ae/html5/thumbnails/25.jpg)
25
Rate My Session
![Page 26: Distributed PostgreSQL - Percona · Postgres-XL Fork of PostgreSQL 10 to provide Scalable PostgreSQL Hash distributed tables and replicated tables Multi-node transactions, Multi-node](https://reader034.vdocuments.us/reader034/viewer/2022043004/5f86f640ce48e0489e7ac3ae/html5/thumbnails/26.jpg)
Thank You!Simon Riggs, 2ndQuadrant