Postgres als Enterprise-Datenbank

Postgres als Enterprise-Datenbank
Michael Banck <[email protected]>
Linux-Stammtisch München, 25.7.2017
Michael Banck <[email protected]>
1 / 51
PostgreSQL
Michael Banck <[email protected]>
2 / 51
PostgreSQL - Überblick
I
Erweiterbares, Objekt-Relationales Datenbanksystem
I
Entstanden als Forschungsprojekt in Berkeley, Community-basierte
Entwicklung seit Mitte der 90er
I
Hersteller-Unabhängig, kommerzieller Support von mehreren
Firmen erhältlich
I
‘’Postgres Global Development Group”, 5 köpfiges Core Team, 3
köpfiges Release Team, ca. 20 Committer
I
Jährlicher Release-Zyklus, 5 Jahre Wartungszeitraum pro Release
I
Vierteljährliche Patch-Releases
I
Keine Copyright-Assignments, Open-Core oder Dual-Lizenzierung
I
BSD/MIT-artige Lizenz
I
Viele (auch proprietäre) Forks
Michael Banck <[email protected]>
3 / 51
PostgreSQL - Forks
Michael Banck <[email protected]>
4 / 51
PostgreSQL - Forks
Michael Banck <[email protected]>
5 / 51
PostgreSQL - Cloud Provider
Michael Banck <[email protected]>
6 / 51
PostgreSQL - Haupt-Features
I
Rock Solid
I
Gute und konsistente Abdeckung des SQL-Standards, sowie
sinnvolle moderne Erweiterungen
I
Kosten-basierter Query-Planer
I
Große Anzahl an Erweiterungen
I
Transaktionale Änderungen an Datenbankstruktur
I
Hohe Anzahl an Treibern für Programmiersprachen
I
Viele verschiedene prozedurale Sprachen
I
Fremddaten-Wrapper (FDW) für den Zugriff auf viele andere
Datenquellen/Datenbanken
Michael Banck <[email protected]>
7 / 51
PostgreSQL - Benutzer
Michael Banck <[email protected]>
8 / 51
Top Feature-Requests 2009
I
I
I
I
I
I
I
I
I
I
I
I
I
I
I
Einfache, eingebaute Replikation
In-Place Upgrades
Administration/Monitoring
Treiber-Qualität/Wartung
Erweiterungs-Management
Locales/Collationen pro Spalte
Materialisierte und Aktualisierbare Sichten
Parallelisierte Queries
Index-Only Scans
Merge/Upsert Anweisung
Automatische Partitionierung
Hot Standby
Rekursive Abfragen
Window Funktionen
Autonome Transaktionen
Michael Banck <[email protected]>
9 / 51
Top Feature-Requests 2009 - Stand 2017
I
I
I
I
I
I
I
I
I
I
I
I
I
I
I
Einfache, eingebaute Replikation
In-Place Upgrades
Administration/Monitoring
Treiber-Qualität/Wartung
Erweiterungs-Management
Locales/Collationen pro Spalte
Materialisierte und Aktualisierbare Sichten
Parallelisierte Queries
Index-Only Scans
Merge/Upsert Anweisung
Automatische Partitionierung
Hot Standby
Rekursive Abfragen
Window Funktionen
Autonome Transaktionen
Michael Banck <[email protected]>
10 / 51
PostgreSQL: Zitate von Analysten
I
Donald Feinberg, Gartner:
I
I
Noel Yuhanna, Forrester:
I
I
Postgres functionality has increased greatly and is now more than
sufficient to run both mission-critical and non-mission-critical
applications.
PostgreSQL has the second-largest open source community; has
competitive technology and features and continues to expand its
growth across various industries.
Matt Aslett, 451 Group:
I
I
PostgreSQL is a proven database for enterprise relational
application workloads
Increased commercial offerings and cloud-based functionality are
driving adoption
http://2013.pgconf.de/de/talks/edb-pggermany-2013-v01.pdf
Michael Banck <[email protected]>
11 / 51
Neue Features - Version 9.4
I
Binäres JSON (jsonb) inklusive Indizierung
I
Nebenläufige Aktualisierung von materialisierten Sichten
I
Reduzierte Sperren für einige ALTER TABLE Befehle
I
Konfigurationsänderungen via ALTER SYSTEM SQL Befehl
I
Logical Changeset Generierung / Change Data Capture
I
Replikations-Slots and zeitverzögerte Standbys
I
Erhöhte Skalierbarkeit
Michael Banck <[email protected]>
12 / 51
Neue Features - Version 9.5
I
INSERT .. ON CONFLICT UPDATE/IGNORE (UPSERT)
I
Row-Level Security (RLS)
I
Block-Range Indexe (BRIN)
I
GROUPING SETS, CUBE und ROLLUP für analytische Abfragen
I
Zurückspulen eines alten Masters als neuer Standby
I
ALTER TABLE ... SET LOGGED / UNLOGGED
I
TABLESAMPLE in definierter Zeit
I
IMPORT FOREIGN SCHEMA für Fremddaten-Wrapper
I
Erhöhte Skalierbarkeit
Michael Banck <[email protected]>
13 / 51
Neue Features - Version 9.6
I
Parallele Query-Ausführung
I
Mehr als ein synchroner Standby
I
Verbessertes VACUUM (automatische Freeze Map)
I
Remote Joins/Sorts/UPDATEs/DELETEs im Postgres
Fremddaten-Wrapper
I
Verbesserte Aktivitätssicht auf Locking und Vacuum
I
Erhöhte Skalierbarkeit
Michael Banck <[email protected]>
14 / 51
Neue Features - Version 10
I
Logische Replikation
I
Weitergehende Parallelisierung
I
Native, deklarative Partitionierung
I
Client-basiertes Verbindungs-Failover
I
Quorum-Commit für synchrone Standbys
I
Vereinfachte Konfiguration von Replikation
I
Salted Challenge Response Authentication Mechanism (SCRAM)
I
Verbesserte und Produktiv-Reife Hash-Indexe
I
Schnellere Ausführung von Expressions
I
Spaltenübergreifende Statistiken für den Query-Planer
Michael Banck <[email protected]>
15 / 51
Enterprise Features - Definition
I
Fehler-Toleranz und Daten-Konsistenz
I
Transaktionsisolation zwischen Benutzern
I
Unternehmens-relevante Sicherheits-Features
I
Interoperabilität und Erweiterbarkeit
I
Planbare Major- und Patch-Releases, lange Wartungszyklen
I
Major-Upgrades ohne (längere) Downtime
I
Stabile Replikation und Hochverfügbarkeit
I
Horizontale und vertikale Skalierung
Michael Banck <[email protected]>
16 / 51
Fehler-Toleranz und Daten-Konsistenz
Michael Banck <[email protected]>
17 / 51
Fehler-Toleranz und Daten-Konsistenz
I
I
‘’I manage thousands of databases (PostgreSQL, SQL Server, and
MySQL), and this past weekend we had a massive power surge
that knocked out two APC cabinets. [. . . ] Long story short, every
single PostgreSQL machine survived the failure with zero data
corruption. I had a few issues with SQL Server machines, and
virtually every MySQL machine has required data cleanup and
table scans and tweaks to get it back to ”production” status.”
‘’I had exactly the same experience 3 years ago. Complete power
failure (the stand-by generator took fire) in one small datacenter
(around 500 machines). We had Oracle, SQL Server, DB2,
MySQL, Progress, and of course PostgreSQL. The only database
engine that restarted with no operation required was PostgreSQL.
There were very minimal problems with Oracle (typing recover on
some instances), but we had quite a few problems with the other
engines.”
Michael Banck <[email protected]>
18 / 51
Transaktionsisolation zwischen Benutzern
Michael Banck <[email protected]>
19 / 51
Transaktionsisolation zwischen Benutzern Problemszenario
Es soll sichergestellt werden, dass zusammen immer mindestens 1000
Euro auf den Konten sind
BEGIN;
UPDATE meine_konten SET kstand = kstand - $abhebung
WHERE ktoid = ’Giro’;
SELECT SUM(kstand) FROM meine_konten;
IF (sum >= 1000.00)
COMMIT;
ELSE
ROLLBACK;
Angenommen wir haben je 600 Euro auf zwei Konten (= 1200 Euro)
und heben gleichzeitig von jedem 200 Euro ab (= 800 Euro).
Michael Banck <[email protected]>
20 / 51
Transaktionsisolation zwischen Benutzern Problemszenario
Client 1
Client 2
liest Girokonto, erhält 600
ändert Girokontostand in 400
liest Sparbuch, erhält 600
ändert Sparbuchstand in 400
ermittelt Summe:
400 + 600 = 1000
ermittelt Summe:
400 + 600 = 1000
commit
commit
I
I
Ergebnis: Inkonsistente Daten, Gesamtkontostand ist 800
Lösung: Postgres erlaubt echte serialisierte Transaktionen
Michael Banck <[email protected]>
21 / 51
Transaktionsisolation zwischen Benutzern Serializable Snapshot Isolation
Client 1
Client 2
liest Girokonto, erhält 600
ändert Girokontostand in 400
liest Sparbuch, erhält 600
ändert Sparbuchstand in 400
ermittelt Summe:
400 + 600 = 1000
ermittelt Summe:
400 + 600 = 1000
commit
commit
ERROR: could not serialize
access due to read/write
dependencies among transactions
Michael Banck <[email protected]>
22 / 51
Unternehmens-relevante SicherheitsFeatures
Michael Banck <[email protected]>
23 / 51
Unternehmens-relevante SicherheitsFeatures
I
Authentifizierung
I
I
I
Zugriffskontrolle innerhalb der Datenbank
I
I
I
I
Quell-IP/Nutzer/Datenbank basiert
SCRAM (ab PostgreSQL 10)
Spaltenbasierte Grants
Row-Level Security (RLS)
SELinux Erweiterung
Auditierung
I
I
PGAudit Erweiterung
Objekt-Audit-Logging
Michael Banck <[email protected]>
24 / 51
Unternehmens-relevante SicherheitsFeatures - Row-Level Security
CREATE USER bob;
CREATE USER alice;
CREATE TABLE m1(id int primary key, f1 text, app_user text);
INSERT INTO m1 VALUES(1,’a’,’bob’);
INSERT INTO m1 VALUES(2,’b’,’alice’);
ALTER TABLE m1 ENABLE ROW LEVEL SECURITY;
CREATE POLICY P ON m1 USING (app_user = current_user);
GRANT SELECT (f1, app_user) ON m1 TO bob;
GRANT SELECT (app_user) ON m1 TO alice;
Michael Banck <[email protected]>
25 / 51
Unternehmens-relevante SicherheitsFeatures - Row-Level Security
SELECT * FROM m1;
id | f1 | app_user
----+----+---------1 | a | bob
2 | b | alice
SET SESSION AUTHORIZATION bob;
SELECT f1, app_user FROM m1;
f1 | app_user
----+---------a | bob
SET SESSION AUTHORIZATION alice;
SELECT f1, app_user FROM m1;
FEHLER: keine Berechtigung für Relation m1
Michael Banck <[email protected]>
26 / 51
Unternehmens-relevante SicherheitsFeatures - PGAudit
CREATE ROLE auditor;
SET pgaudit.role = ’auditor’;
CREATE TABLE account
(
id INT,
name TEXT,
password TEXT,
description TEXT
);
GRANT SELECT (password) ON public.account TO auditor;
Michael Banck <[email protected]>
27 / 51
Unternehmens-relevante SicherheitsFeatures - PGAudit
SELECT id, name FROM account;
SELECT password FROM account;
AUDIT: OBJECT,1,1,READ,SELECT,TABLE,
public.account,SELECT password FROM account
Michael Banck <[email protected]>
28 / 51
Unternehmens-relevante SicherheitsFeatures - STIG
Michael Banck <[email protected]>
29 / 51
Interoperabilität und Erweiterbarkeit
Michael Banck <[email protected]>
30 / 51
Interoperabilität und Erweiterbarkeit
I
Federation via Foreign Data Wrappers (FDW)
SQL/MED-Standard
I
I
I
Insbesondere zu anderen Postgres-Instanzen (postgres fdw)
Andere SQL-Datenbanken: MySQL, Oracle, Informix, SQLAlchemy
Erweiterungen (Extensions)
I
I
I
I
Verfügbar seit Postgres 9.1
Reines SQL oder zusätzlich C-basierte Bibliothek
Mächtige API und Hooks
Große, wachsende Anzahl
I
I
I
I
I
I
Zusätzliche Datentypen
Prozedurale Sprachen
Administrative Hilfen
Protokollierung/Auditierung
Foreign-Data-Wrapper
Neue Index-Typen (seit 10)
Michael Banck <[email protected]>
31 / 51
Erweiterungen
Michael Banck <[email protected]>
32 / 51
Enterprise-relevante Erweiterungen Beispiele
I
pglogical - Logische Replikation
I
pgaudit - Auditierung von Ereignissen
I
orafce - Oracle Kompatibilität
I
postgis - Geographische Objekte (Spatial)
I
pg partman - Automatisiertes Management von Partitionierung
I
pgcrypto - Verschlüsselung von Tabellen
I
tsearch/pg trgm - Volltextsuche / Ähnlichkeitssuche
I
sepgsql - SELinux-basierte MAC (Mandatory Access Controls)
I
pgstrom - GPU-Offloading von rechen-intensiven Workloads
Michael Banck <[email protected]>
33 / 51
Replikation und Hochverfügbarkeit
Michael Banck <[email protected]>
34 / 51
Physikalische (Streaming) Replikation
I
I
I
I
I
I
I
I
I
I
I
Transaktionslog wird via Datenbank-Verbindung übertragen
Lesende Abfragen auf Standby möglich (Hot-Standby)
Vorhaltung der benötigten Transaktionslogs auf Primary pro
Standby via Replikations-Slots
Cloning eines neuen Standbys via Basebackup
Switchover, Switchback, Promote und Remastering
Kaskadierende und/oder Zeitverzögerte Replikation
Quorum-basierte synchrone Replikation, optional pro Transaktion
Konsistente lesende Abfragen von synchronen Standbys
Optional zentrale Archivierung von Transaktionslogs
Einfache Konfiguration
Einschränkungen:
I
I
Erfordert gleiche Major-Version auf Primary und Standby
Nur gesamte Instanz replizierbar, keine einzelnen Datenbanken oder
Tabellen
Michael Banck <[email protected]>
35 / 51
Logische Replikations-Lösungen
I
Versions-Unabhängig
I
Einzelne Datenbanken, oder Teile davon replizierbar
I
Trigger-basiert
I
I
I
I
Slony-I
Londiste
Bucardo (Master-Master)
Logical Decoding-basiert
I
I
I
Native Logische Replikation (ab PostgreSQL 10)
pglogical (Erweiterung, ab 9.4)
Bi-Directional Replication (BDR, Master-Master)
Michael Banck <[email protected]>
36 / 51
Logische Replikations-Lösungen Benchmark
Michael Banck <[email protected]>
37 / 51
Hochverfügbarkeit - Definition
I
Schutz gegen Hardware/Software-Ausfall
I
I
I
I
I
Wartung beeinträchtigt Betrieb nicht
I
I
I
I
CPU defekt
Ausfall Netzwerk-Karte
Kernel-Panik
Absturz des Postgres-Prozesses
Neustart Postgres-Prozess nach Patching oder
Konfiguration-Änderung
Major-Versions Upgrade von Postgres
Aktualisierung Betriebssystem
Anwendung ist durchgehend verfügbar
I
Keine langanhaltenden Locks während Schema-Änderungen
Michael Banck <[email protected]>
38 / 51
Hochverfügbarkeit - Failover-Lösungen
I
Pacemaker/Corosync
I
I
pgsql Ressourcen-Agent (Standard)
pgsqlms Ressourcen-Agent (PostgreSQL Automatic Failover, PAF)
I
repmgr
I
pgpool-II
I
Container-basiert:
I
I
I
I
Patroni
Compose Governor
Stolon
Client-basiertes Failover durch Angabe mehrerer Hosts
I
I
PgJDBC (seit 9.3-1100)
libpq (ab Postgres 10)
Michael Banck <[email protected]>
39 / 51
Hochverfügbarkeit - Pacemaker
Beispiel-Setup
Michael Banck <[email protected]>
40 / 51
Hochverfügbarkeit - Pacemaker
Beispiel-Setup
I
Erfahrungen:
I
I
I
I
Mehrere unerwartete Schwenks, teilweise vermutlich wegen LVM
Ressource
Aber auch mehrere erfolgreiche Schwenks bei Hardware-Problemen
bzw. Kernel Panics
Hardware-Ausfall 23.12.2015, 21:00 Uhr, 2:59 Minuten Downtime
Kernel-Panik 12.2.2017, 22:35, 1:02 Minuten Downtime:
<22:35:06>LOG:
<22:36:03>LOG:
<22:36:08>LOG:
Verbindungsende: Sitzungszeit: 0:00:00.042
Benutzer=xxx Datenbank=XYZPROD
Host=10.xxx.xxx.xxx port=54856
Datenbanksystem wurde unterbrochen; letzte
bekannte Aktion am 2017-02-12 22:34:55 CET
Verbindung autorisiert: Benutzer=xxx
Datenbank=XYZPROD SSL aus
Michael Banck <[email protected]>
41 / 51
Hochverfügbarkeit - pgBouncer für
Transparenten Postgres-Neustart
I
PostgreSQL Verbindungs-Proxy
I
I
I
I
I
I
Sehr leichtgewichtig, robust und performant
Entwickelt ursprünglich von Skype, ISC Lizenz, C
Hält eingehende Verbindungen mit PAUSE/RESUME
I
I
I
I
I
Pooler
Queuing
Router
Nach Beendigung aller aktiven Verbindungen kann Postgres z.B.
neu gestartet werden
Anwendung sieht nur verzögerte Verbindung, keine Fehlermeldung
Benötigt kurzlebige Verbindungen/Transaktionen
Kann eingehende Verbindungen je nach Konfiguration an
verschiedene Postgres-Datenbanken routen
Ermöglicht selber On-Line Neustart mit Socket-Übernahme
Michael Banck <[email protected]>
42 / 51
Hochverfügbarkeit - Near-Zero-Downtime
Major Upgrades
I
Logische Replikation (Slony-I, pglogical)
I
I
I
Benötigt doppelte Hardware / doppelten Speicherplatz
Primärschlüssel auf allen Tabellen erforderlich
Work-Flow
I
I
I
I
I
I
Aufsetzen der Replikation
Synchronisierung des Datenbestands inklusive Änderungen
Switchover
Evtl. Switchback bei Problemen
Abschalten des Altsystems
In-Place Upgrades mit pg upgrade
I
I
I
I
I
Internes Postgres Programm
Benötigt keine Primärschlüssel aber zweites Datenverzeichnis
Funktioniert nicht mit allen Erweiterungen/Datentypen
Statistiken müssen nach Upgrade neu angelegt werden
Hardlink-Modus (ohne Switchback Möglichkeit) Downtime ab 10s
I
Skaliert mit Anzahl der Datenbank-Objekte, nicht -Größe
Michael Banck <[email protected]>
43 / 51
Vertikale und Horizontale Skalierung
Michael Banck <[email protected]>
44 / 51
Vertikale und Horizontale Skalierung Definition
I
Vertikale Skalierung: Verbesserte Ausnutzung der vorhandenen
Ressourcen eines Servers
I
I
I
Mehr Transaktionen pro CPU Core
Verwendung von mehreren CPU Cores für einzelne Abfragen
Horizontale Skalierung: Verteilung der Last auf mehrere Server
I
Verteilung von Abfragen auf mehrere Server
I
I
I
Daten auf jedem Server repliziert: Load Balancing
Daten zwischen Servern verteilt: Sharding
Verwendung von mehreren Servern für einzelne Abfragen
I
Massive Parallel Processing
Michael Banck <[email protected]>
45 / 51
Vertikale Skalierung - Erhöhte
Skalierbarkeit 9.3 bis 9.6
https://gauss.credativ.com/~mme/2017/PGConfUS 2017 V01.pdf
Michael Banck <[email protected]>
46 / 51
Vertikale Skalierung - TPC-H Benchmark
20 GB 9.6 Non-Parallel vs. Parallel
Michael Banck <[email protected]>
47 / 51
Vertikale Skalierung - TPC-H Benchmark
300 GB 9.6 vs 10
Michael Banck <[email protected]>
48 / 51
Horizontale Skalierung - Load Balancing
I
Lesende Abfragen werden verteilt
I
Schreibende Abfragen auf Primary
I
Daten auf allen Knoten repliziert
I
Anwendungs-Transparent
I
I
Anwendungsunterstützung
I
I
I
I
pgpool-II
HAProxy
pgBouncer DNS Round-Robin
PgJDBC Verbindungs-Option LoadBalance=true
Parameter remote apply für konsistente Lese-Abfragen (ab 9.6)
Michael Banck <[email protected]>
49 / 51
Horizontale Skalierung - Sharding
I
Lesende und schreibende Abfragen werden verteilt
I
Daten sind (sofern sinnvoll) zwischen Knoten aufgeteilt
I
Normalerweise replizierte Fakt-Tabellen für effiziente Joins
I
Postgres-XL
I
Greenplum
I
CitusDB
I
PL/Proxy
I
In Zukunft vermutlich FDW-basiertes natives Sharding
‘’Towards Built-in Sharding in Community PostgreSQL”
https://www.pgcon.org/2017/schedule/events/1069.en.html
Michael Banck <[email protected]>
50 / 51
Danke für die Aufmerksamkeit - Kontakt
I
Fragen?
I
Michael Banck <[email protected]>
I
http://www.credativ.de
I
http://www.credativ.de/postgresql-competence-center
I
http://www.credativ.de/über-credativ/stellenangebote
I
http://www.credativ.de/blog
Michael Banck <[email protected]>
51 / 51