thCelebrating its 15
Anniversary:
Pgpool-II Past, Present andFuture
PgPooI Global Development Group
Tatsuo Ishii

The history of Pgpool-II

thJune 27 , 2003 pgpool was born
2003年6月27日(金) 22:54:46 JST[pgsql-jp: 30256] PostgreSQL用コネクションプールコネクションプール
サーバ pgpool

Connection pooling●Failover
石井です．です．
●
PHPをはじめ，Perlなど，言語を問わず使える「を問わず使える「わず使える「使える「える「pgpool」とOnly up to 2 PostgreSQL
いPostgreSQL用コネクションプールのコネクションプールサーバを作ったのコネクションプールサーバを作ったのったのコネクションプールサーバを作ったのservers were supportedで公開します．できたなのでまだアルファ版程度のクォリします．できたなのコネクションプールサーバを作ったのでまだアルファ版程度のクォリアルファ版程度のクォリ版程度のクォリのコネクションプールサーバを作ったのクォリ
ティですが，よろしかったらお試し下さい．ですが，よろしかったらお試し下さい．試し下さい．し下さい．さい．
●
Support Version 2 of
ftp://ftp.sra.co.jp/pub/cmd/postgres/pgpool/pgpool-0.1.tar.gz
frontend/backend
protocol# もちろんpgpoolはオープンソースで，ライセンスはで，ライセンスで，ライセンスははPostgreSQLのコネクションプールサーバを作ったのBSDライセンスで，ライセンスはと同様のものにしていまのコネクションプールサーバを作ったのものコネクションプールサーバを作ったのにしていま●す．
Version 3 protocol (themodern protocol currently pgpoolを作ったのった動機は，は，PHPでコネクションプールが使える「えないことに不満ををused) did not exist
持ったからです．ったからです．
一応PHPには「パーシスで，ライセンスはタントコネクション」というものコネクションプールサーバを作ったのがbecause PostgreSQL 7.4
あってDBへのコネクションプールサーバを作ったの接続への接続をキャッシュできますが，少なへのコネクションプールサーバを作ったの接続への接続をキャッシュできますが，少なをキャッシュできますが，少なできますが，少なな
was not released yet
くともapacheのコネクションプールサーバを作ったのプロセスで，ライセンスはのコネクションプールサーバを作ったの数だけコネクションができるのだアルファ版程度のクォリけコネクションができるのコネクションができるのコネクションプールサーバを作ったので，DBへ過大な負荷がかかりがちです．な負荷がかかりがちです．がかかりがちです．
●
pgpoolを使える「うとコネクションをキャッシュできますが，少なできるだアルファ版程度のクォリけコネクションができるのでなく，Only 4,719 lines of code
DBへのコネクションプールサーバを作ったの接続への接続をキャッシュできますが，少な数だけコネクションができるのを適切な数に制限できるので，な数だけコネクションができるのに制限できるので，できるのコネクションプールサーバを作ったので，DBのコネクションプールサーバを作ったの性能を引き出すことができます．き出すことができます．すことができます．
in C language●

3Copyright(c) 2018 SRA OSS, Inc. Japan

September 2006 Pgpool-II 1.0
released
From personal project to publicOSS project●
Number of features were added●

Virtually unlimited number ofPostgreSQL supported●
Built-in SQL parser to precisely
analyze query●
Dedicated management command(pcp)●
Dedicated GUI management tool(pgpoolAdmin)●
Parallel query mode
●73,511 lines of codes, which is almost
x10 larger than pgpool●

4Copyright(c) 2018 SRA OSS, Inc. Japan

In September 2010 Pgpool-II 3.0released●

Adopt PostgreSQL 9.0
Streaming replication mode●delay_threshold●
log_standby_delay●
white_function_list/black_function_list●

5Copyright(c) 2018 SRA OSS, Inc. JapanIn 2011 pgpool.net was born
Moved from othersite to our own site●
Moved from CVS toGit●
Official languages
are English andJapanese●

6Copyright(c) 2018 SRA OSS, Inc. Japan

Pgpool-II from v3.2 to v3.7●

Pgpool-II 3.2 (2012)●

Faster extended querymode●
Enhanced watchdog

Watchdog●In memory query cache

●

●

●

Pgpool-II 3.3 (2013)●●Heartbeat mode inwatchdog●Reorganize source tree
●Memory manager,Exception manager
7Pgpool-II 3.6 (2016)
SGML documents●
Enhanced watchdog●

Pgpool-II 3.4 (2014)●

Pgpool-II 3.5 (2015)●

Pgpool-II 3.7 (2017)
Quorum failover●AWS Aurora●Copyright(c) 2018 SRA OSS, Inc. Japan

Our team

8Copyright(c) 2018 SRA OSS, Inc. Japan

Team members●

Tatsuo Ishii●

Original author●Japan●●Benchmarking,research on user
requirements●Pakistan●

Muhammad Usama
Pgpool-II committer since 3.4●Pakistan●
Watchdog Authentication●●Ahsan Hadi

Peng, Bo

●Takuma Hoshiai
Pgpool-II committersince 4.0●Japan●Documents, bug fixes●

Pgpool-II committer since 3.3●
China●Release management, SQL
parser, PgpoolAdmin●

9Copyright(c) 2018 SRA OSS, Inc. Japan

Development activities
Coding and debugging using Git repository served byPostgreSQL●
Discussions on mailing list●
Bug reports are accepted using Mantis●
Static code checking using Coverity●
Weekly teleconference●Major releases once per year●
Minor releases every 2-3 months●
Concurrently maintain 4-5 stable branches anddevelopment branches – five years support for each major
release●10Copyright(c) 2018 SRA OSS, Inc. Japan

Pgpool-II: now
Clustering management tool for PostgreSQL●Focus on streaming replication●

Automatic query dispatch to primary and standby
servers●
Automatic read only query load balancing●
Automatic failover●●High availability for Pgpool-II itself●Watchdog

11Copyright(c) 2018 SRA OSS, Inc. JapanthAbout 15
aniversary
edition: Pgpool-II4.0

12Copyright(c) 2018 SRA OSS, Inc. Japan

Pgpool-II 4.0 major features
Completely renewed authentication system●Consistency checking for primary/standbyrole●More flexible control over read query loadbalancing●PostgreSQL 11 compatible SQL parser●13Copyright(c) 2018 SRA OSS, Inc. Japan

Completely renewed
authentication system
It is possible to use “trust” between clients and Pgpool-II,
while using “SCRAM” between Pgpool-II and PostgreSQL

trust

trust

md5

md5

SCRAM

SCRAM

SCRAM auth support is new in 4.0!

14Copyright(c) 2018 SRA OSS, Inc. Japan

Allow to handle multiple password
format in pool_passwd
pool_passwd stores user name/password info for
database users●Possible password formats●AES256– username:AESAxjUF2clVQvZn0QvnVzBgg==●
Clear text●–●

username:TEXTmypass

MD5
username:md56e854442cd2a940c9e95941dce4ad598– MD5 password format can only be used for MD5 auth between
Pgpool-II and PostgreSQL–15

Copyright(c) 2018 SRA OSS, Inc. Japan

Eliminating pool_passwd
Since pool_passwd needs to sync with PostgreSQL
password, it could be a headache for admins●If auth method between frontend and Pgpool-II istrust (no auth), pool_passwd can be eliminated●Password provided by user is checked by PostgreSQL●new parameter “allow_clear_text_frontend_auth” mustbe enabled●●

In this case it is recommended to use SSL for theconnection between frontend and Pgpool-II

16

Copyright(c) 2018 SRA OSS, Inc. Japan

Allow to use secure AES256
encrypted password●

Allow to store AES256 encrypted password in
health_check password and sr_check_password
health_check_password ='AESAxjUF2clVQvZn0QvnVzBgg=='●
Decryption key must be stored in$HOME/.pgpoolkey●or in a file specified by PGPOOLKEYFILE environment
variable– or “--key-file” option of pgpool command–●To create AES encrypted password, new tool
“pg_enc” can be used
17Copyright(c) 2018 SRA OSS, Inc. Japan

Allow to use certificate
authentication
As of 4.0, only between frontend and PgpoolII is allowed to use cert auth●We plan to enable cert auth between PgpoolII and PostgreSQL in the next major release●18Copyright(c) 2018 SRA OSS, Inc. JapanFuture plans●

Near future (Pgpool-II 4.1)
Support LDAP authentication●
Shared relation cache for faster system catalog look up●and more…
●●Distant future●

More testing!
More regression tests– Faster regression tests–●

Faster processing–●●

Reduction of protocol overhead

Adopting to sharding (if PostgreSQL implemts it)

Ideas are always welcome!
19

Copyright(c) 2018 SRA OSS, Inc. Japan

More Pgpool-II 4.0
New Features
will come in the next
session...

Please welcome Peng Bo!
20

Copyright(c) 2018 SRA OSS, Inc. Japan