PostgreSQL HA with Pgpool-II and whats been
happening in Pgpool world lately...

© 2016 EDB. All rights reserved.

!1About Me• Muhammad Usama• Database Architect with EnterpriseDB• Pgpool developer and committer• Watchdog overhauling• New PCP system for Pgpool-II• New authentication method support• Quorum support for backend failovermuhammad.usama@enterprisedb.com
https://www.linkedin.com/in/muhammadusama© 2018 EDB. All rights reserved.

!2

Pets Vs Cattle Herd
• We treat our servers likepets (e.g. Sunshine the cow).
If Sunshine gets sick, It feelslike the end of the world andeverything stops there• We treat our servers like cattle
herd (C01, C02, … C99). If
some cattle gets sick, there issystem in place to isolate itfrom the herd and things go on.

© 2018 EDB. All rights reserved.

!3

Pets Vs Cattle Herd
• Sunshine the cow is unique andindispensable• Sunshine the cow is hand fed and hand
crafted• Cannot handle failure• Herd of similar cattle (C01,C02..)• No cattle is special andindispensable• Can handle failures© 2018 EDB. All rights reserved.

!4

Using the herd approach for HA indatabase• Herd approach can be usedon database servers toachieve performance
scalability and highavailability.
How?

• Make a homogeneous
copies of database servers(replicated databases)• Have a system to manage
the herd and failures.

© 2018 EDB. All rights reserved.

!5Challenges in the herd approach• Herd needs a leader (Primary server).
• A system is required to elect new leader
if the current leader becomes
unavailable (Primary node failure).
• Needs a mechanism to make herd follow
the new leader.
• We need a system to seamlessly retire
the sick nodes (Standby node failure).
• A system to add new nodes without
effecting the service.
• Require a system to make the wholeherd work in collaboration to efficiently
utilise the resources (Load balancing)© 2018 EDB. All rights reserved.

!6

The Solution© 2018 EDB. All rights reserved.

!7

What is Pgpool-II?
• Cluster management tool
dedicated for PostgreSQL• Rich in features• Connection pooling• Load balancing• Automatic failover• Query caching• Watchdog (Highavailability)• OSS project, BSDLicense© 2018 EDB. All rights reserved.

!8

Solving the problem using Pgpool-II• Pgpool-II make the cluster
appears as a single
PostgreSQL instance• All standard PostgreSQL
clients work seamlessly• Automatic failover• Provides flexibility andcontrol to select theprimary node when oldprimary fails• Load balancing© 2018 EDB. All rights reserved.

!9

Basic idea of Pgpool-II© 2018 EDB. All rights reserved.

!10Primary server fails• If primary goes
down, one of thestandby is promoted
to the new primary• Other standby
nodes are madeto follow newprimary© 2018 EDB. All rights reserved.

!11

Standby server fails• If the standby
goes down, It issimply taken off
from the cluster• Users continue touse the service
without disruption© 2018 EDB. All rights reserved.

!12Automatic failover• Mechanism in Pgpool-II to detach problematicnodes• Reconfigure the standby nodes to follow newprimary node
• Automatically triggers• When health check monitor the node failure• Reading/writing failure to PostgreSQL backend(failover_on_backend_error is on)• By remote Pgpool-II node (Watchdog)© 2018 EDB. All rights reserved.

!13

Does that solves the problem?

• Using Pgpool-II with failover andhealth check ensures the service
availability when PostgreSQL nodefails• But there is still a problem© 2018 EDB. All rights reserved.

!14

What if Pgpool-II fails• Pgpool-II node failure
can cause service
unavailability evenwhen all db servers arehealthy• Pgpool-II
becomes thesingle point of
failure (SPOF)© 2018 EDB. All rights reserved.

!15

Watchdog© 2018 EDB. All rights reserved.

!16

What is Watchdog?
• A sub process of Pgpool-II to handle failures• Life checking of Pgpool-II service• Mutual monitoring of Pgpool-II nodes in thecluster• Leader election to select best master node
• Virtual-IP control• Ensuring same view of PostgreSQL backend
states across all Pgpool-II nodes• Distributed failover management© 2018 EDB. All rights reserved.

!17

Pgpool-II with watchdog© 2018 EDB. All rights reserved.

!18

Pgpool-II node failure© 2018 EDB. All rights reserved.

!19

Recipe for PostgreSQL HAPgpool-II with Watchdog© 2018 EDB. All rights reserved.

!20

Whats been happening in Pgpool-II
world lately...
© 2018 EDB. All rights reserved.

!21

Whats been happening in Pgpool-II lately..
• Pgpool-II is becoming morereliable• Watchdog is getting smarter• PCP commandenhancements• Failover is getting better• Performance improvements• New authentication methods
support• Continuously improving
everyday© 2018 EDB. All rights reserved.

!22

Watchdog is getting smarter• Watchdog was very basic
until Pgpool-II 3.5• So many complaints
around split-brainsyndrome• Scalability andmaintainability were bigissues• Rewritten in Pgpool-II 3.5© 2018 EDB. All rights reserved.

!23

No more split-brain syndrome• Ensures thequorum to avoid
spilt-brain
syndrome duringleader election• Actively look out
for split-brainsyndrome© 2018 EDB. All rights reserved.

!24

Better voting and leader election
mechanism• Watchdog priority(wd_priority configparameter) can be
assigned to thePgpool-II node
• Weighs various
node attributes(number ofconnections, uptime,age, priority)© 2018 EDB. All rights reserved.

!25

Watchdog now uses sockets and JSON
data format for IPC• Allow third-party
integrations• Expose interfaces forexternal health checksystems to integratewith watchdog• Extendable andmaintainable© 2018 EDB. All rights reserved.

!26

Watchdog side enhancements in thefailover• Pgpool-II nodes collaborate to validate backend
node failure• Failover is performed democratically using quorum• Tolerate network glitches to avoid unnecessary
failover• The behaviour is configurable• failover_when_quorum_exists• failover_require_consensus• enable_multiple_failover_requests_from_node© 2018 EDB. All rights reserved.

!27

Security and authentication updates in
Pgpool-II 4.0• SCRAMauthentication
support• Certificate based
authentication
support• Encrypted password
file (pool_passwd)• Encrypted passwords
in pgpool.conf© 2018 EDB. All rights reserved.

!28

SCRAM authentication• Supports SCRAM-SHA-256• Works for both frontend and backend
connections• Works by storing user passwords in
pool_passwd file• Can work with plain text andencrypted passwords in pool_passwd
file© 2018 EDB. All rights reserved.

!29

Certificate based authentication• Available for SSL connections• Currently works for frontend
connections only
• CN (common name) attribute of clientcertificate compared with databaseuser name. (similar to PostgreSQL)• Requires same server sidecertificates used in backends© 2018 EDB. All rights reserved.

!30

Secure passwords in Pgpool-II• pool_passwd and pgpool.conf
supports encrypted passwords• pg_enc utility to create
encrypted passwords• Uses strong AES256
encryption• Pgpool-II requires pool_key fileat startup to decrypt
passwords• Invalid or missing key filemakes encrypted password
unusable• One pool_passwd file for all
passwords (optional)© 2018 EDB. All rights reserved.

!31

More authentication related updates• Backend and frontend connections
can now use different authentication
methods for same session© 2018 EDB. All rights reserved.

!32

Some recent performance updates• Large SELECT
query performance
improvement• Improvements inextended queryperformance• Thundering herd
problem fix© 2018 EDB. All rights reserved.

!33

Some recent notable enhancements in
Pgpool-II• pool_hba enhancements• Advancement of backend node health checking• Support AWS Aurora• New PGPOOL SET command to change Pgpool-II
configurations on the fly• Minimal session disconnections with improved
failover mechanism• Pg_terminate_backend handling• Allow to specify load balance weights ratio• Documentation improvement© 2018 EDB. All rights reserved.

!34

Thank you for listening

Special thanks toSRA OSS and Tatsuo Ishii© 2018 EDB. All rights reserved.

!35