An introduction to pgpool-II
Version 3
SRA OSS, Inc. Japan
Tatsuo Ishii

About me●●●PostgreSQL &pgpool-II developer
Working as aPostgreSQLevangelist(16 talksin 2010)
Working for SRA
OSS, Inc. Japan
Copyright(c)2011 Tatsuo Ishii

2

More PostgreSQL books
written by my colleagues“PostgreSQL complete guide
Third edition”“Road to the database expert”

Copyright(c)2011 Tatsuo Ishii

3What is pgpool-II?
●Yet another cluster management tool
dedicated for PostgreSQL●OSS project(pgfoundry, BSD License)●Rich features●●●Synchronous replication
Load balancing, automatic failover, connection
pooling etc.
Collaborating with other replication tools●Streaming replication, Slony-I
Copyright(c)2011 Tatsuo Ishii

4

Basic idea of pgpool-II
Transparent against both PostgreSQL
server and client

PostgreSQL
serverquery
PostgreSQL
serverquery
query
PostgreSQL client

pgpool-II

Copyright(c)2011 Tatsuo Ishii

query

PostgreSQL
server5

The architecture of pgpool-II
pcp
processfork

pgpool-II
parentfork

Admin

ClientQueryfork

HealthCheckingpgpool-II
pgpool-II
childchild

New in pgpool-II 3.0
workerprocess

PostgreSQL

Replication
Delay checking

Query

Query
Copyright(c)2011 Tatsuo Ishii

PostgreSQL
6Streaming replication+Hot Standby
Primary
read/write query

XWALlog
WAL sender

read query

WAL receiver
WALlog
NewStandby
primary

Copyright(c)2011 Tatsuo Ishii

7

Pgpool-II helps
Streaming replication
Primary

Pgpool-II does query dispatching
and failover
Write
query

Streaming replication

Read/writequeries
pgpool-IIStandby
Read query

Copyright(c)2011 Tatsuo Ishii

8Pgpool-II V3 helps PostgreSQL●New “stream” mode dedicated to SR/HS●Automatic query dispatching●●●

Allow to load balance SELECTs in an explicit
transaction
Allow to specify functions which are writing
to DB(they need to be executed on theprimary)Adding standby servers without stopping
pgpool-II
Copyright(c)2011 Tatsuo Ishii

9Comparison between various
replication systems

Query restriction
Replication delay
Replication overhead

pgpool-II native
replication△◎△

pgpoolII+SR+HS◎○◎

Copyright(c)2011 Tatsuo Ishii

pgpoolII+Slony-I△△○10

Load balance SELECTs in an explicit
transaction
transaction
BEGIN;

Transaction starts

SELECTLoad balance okSELECT

Load balance okUPDATE

Write to database

SELECT::

Load balance is not ok anymore because
the table might be changed

Copyright(c)2011 Tatsuo Ishii

11

Replication delay detection: how itworks
Primary PostgreSQLStandby PostgreSQLSELECT pg_current_xlog_location()

SELECT pg_last_xlog_receive_location()call the functions during each “health check period”
second and calculate the difference.
if it exceeds the delay threshold, sends readqueries to the primary sever only.
worker process
Copyright(c)2011 Tatsuo Ishii

12

Automatic promoting standby
health checking

pgpoolparentXssh host touch trigger file
failover_command

Primary goes down

kill and restart
all pgpool childStandby server promotes to primary
Copyright(c)2011 Tatsuo Ishii

13

Logging replication delay●

log_standby_delay●●●'none': no logging delay'if_over_threshold': log only when delay exceeds
delay_threshold'always': always log delay

2010-06-28 15:51:32 LOG:
2010-06-28 15:51:42 LOG:
2010-06-28 15:51:52 LOG:
2010-06-28 15:52:02 LOG:
2010-06-28 15:52:12 LOG:
2010-06-28 15:52:22 LOG:

pid 13223: Replication of node:1 is behind 1228800 bytes from the primary server (node:0)
pid 13223: Replication of node:1 is behind 3325952 bytes from the primary server (node:0)
pid 13223: Replication of node:1 is behind 974848 bytes from the primary server (node:0)
pid 13223: Replication of node:1 is behind 2990080 bytes from the primary server (node:0)
pid 13223: Replication of node:1 is behind 901120 bytes from the primary server (node:0)
pid 13223: Replication of node:1 is behind 2433024 bytes from the primary server (node:0)

Copyright(c)2011 Tatsuo Ishii

14

New show commands●

show pool_nodes●●

show pool_processes●●

show pgpool child process information

show pool_pools●●

show DB node information

show connection pool information

show pool_version●

show pgpool-II version
Copyright(c)2011 Tatsuo Ishii

15

Plans for pgpool-II 3.1●●●

Import PostgreSQL 9.0 SQL parser
Allow to switch back/force primary node andstandby node without restarting pgpool-II
Allow to use regular expression inblack/white_function_list●

Allow to use syslog●

Better handling for more than 1 standby●French translation
Copyright(c)2011 Tatsuo Ishii

16

Switch back/force failover
Pgpool-II 3.0
Node 0Node 1Primary
Down

Pgpool-II 3.1
Node 0Node 1

StandbyPrimaryStandbyPrimary

Down

PrimaryStandbyPrimary

Primary

Down

PrimaryStandbyPrimary

Node 2Standby

Cannot make node 0 as primary.
Because primary needs to be“younger” than standby node.
Copyright(c)2011 Tatsuo Ishii

Switch back/force failover!
17More than 1 standby servers.
What's the problem anyway?

XX

PrimaryStandby A
Standby B

Primary
Which standby shouldbe promoted?
Current rule is “theleast position in theconfig file”. This is
not too intelligent.

Standby ANewprimary

Copyright(c)2011 Tatsuo Ishii

XThe old standby
cannot connect
to new primary.
So there's nostandby serverwhich can backup
the system.
Moreover, if the client
connects to theold standby, it
will read outdated
data.

18

Demonstration
Primary
PostgreSQLStreaming
replication
DB client

Pgpool-II 3.1

Copyright(c)2011 Tatsuo Ishii

StandbyPostgreSQL

19

URL etc.
●

Pgpool-II can be downloaded here:●●Twitter●●

http://pgfoundry.org/projects/pgpool/@pgpool2

SRA OSS's website●

http://www.srasoss.co.jp/index_en.php

Copyright(c)2011 Tatsuo Ishii

20