![Page 1: Using streaming replication of PostgreSQL with pgpool-II · SRA group is an independent software house in Japan – Employees: 1,700 – Sales: 34 billion yen SRA OSS Established](https://reader033.vdocuments.us/reader033/viewer/2022052612/5f0a91767e708231d42c458c/html5/thumbnails/1.jpg)
Using streaming replication of PostgreSQL with pgpool-II
Tatsuo IshiiPresident/PostgreSQL committer
SRA OSS, Inc. Japan
![Page 2: Using streaming replication of PostgreSQL with pgpool-II · SRA group is an independent software house in Japan – Employees: 1,700 – Sales: 34 billion yen SRA OSS Established](https://reader033.vdocuments.us/reader033/viewer/2022052612/5f0a91767e708231d42c458c/html5/thumbnails/2.jpg)
2
Copyright(c)2011 Tatsuo Ishii
● Congratulations to the huge success of the very first PGCon in China!
● Thanks to Galy Lee, who is the organizer of this conference and he kindly invited me to the conference!
● It's my great honor to give a talk in this old and beautiful city 広州
![Page 3: Using streaming replication of PostgreSQL with pgpool-II · SRA group is an independent software house in Japan – Employees: 1,700 – Sales: 34 billion yen SRA OSS Established](https://reader033.vdocuments.us/reader033/viewer/2022052612/5f0a91767e708231d42c458c/html5/thumbnails/3.jpg)
3
Copyright(c)2011 Tatsuo Ishii
About SRA OSS
● Part of SRA group● SRA group is an independent software house in Japan
– Employees: 1,700– Sales: 34 billion yen
● SRA OSS● Established in 2005● Specialized in OSS support and consultations● Has over 1,000 support customers
● Closely working with PostgreSQL community
![Page 4: Using streaming replication of PostgreSQL with pgpool-II · SRA group is an independent software house in Japan – Employees: 1,700 – Sales: 34 billion yen SRA OSS Established](https://reader033.vdocuments.us/reader033/viewer/2022052612/5f0a91767e708231d42c458c/html5/thumbnails/4.jpg)
4
Copyright(c)2011 Tatsuo Ishii
About me(石井 達夫)● PostgreSQL &
pgpool-II developer● Working as a
PostgreSQL evangelist(16 talks in 2010)
● Working for SRA OSS, Inc. Japan
![Page 5: Using streaming replication of PostgreSQL with pgpool-II · SRA group is an independent software house in Japan – Employees: 1,700 – Sales: 34 billion yen SRA OSS Established](https://reader033.vdocuments.us/reader033/viewer/2022052612/5f0a91767e708231d42c458c/html5/thumbnails/5.jpg)
5
Copyright(c)2011 Tatsuo Ishii
Why need replication?● Because it:
● Saves your life when one of your data is lost due to hard disk crash
● Prevents the database system from accidental stopping due to hardware or network troubles. Thus allow you to continue business
● Increases database performance by adding database servers
![Page 6: Using streaming replication of PostgreSQL with pgpool-II · SRA group is an independent software house in Japan – Employees: 1,700 – Sales: 34 billion yen SRA OSS Established](https://reader033.vdocuments.us/reader033/viewer/2022052612/5f0a91767e708231d42c458c/html5/thumbnails/6.jpg)
6
Copyright(c)2011 Tatsuo Ishii
How to make database replicas?
database
buffers
database
buffers
X
![Page 7: Using streaming replication of PostgreSQL with pgpool-II · SRA group is an independent software house in Japan – Employees: 1,700 – Sales: 34 billion yen SRA OSS Established](https://reader033.vdocuments.us/reader033/viewer/2022052612/5f0a91767e708231d42c458c/html5/thumbnails/7.jpg)
7
Copyright(c)2011 Tatsuo Ishii
Transaction log
database
buffers
transactionlog
![Page 8: Using streaming replication of PostgreSQL with pgpool-II · SRA group is an independent software house in Japan – Employees: 1,700 – Sales: 34 billion yen SRA OSS Established](https://reader033.vdocuments.us/reader033/viewer/2022052612/5f0a91767e708231d42c458c/html5/thumbnails/8.jpg)
8
Copyright(c)2011 Tatsuo Ishii
Idea of streaming replication
Primarydatabase
buffers
transactionlog Standby
database
buffers
transactionlog
Read/Write queries Read only queries
![Page 9: Using streaming replication of PostgreSQL with pgpool-II · SRA group is an independent software house in Japan – Employees: 1,700 – Sales: 34 billion yen SRA OSS Established](https://reader033.vdocuments.us/reader033/viewer/2022052612/5f0a91767e708231d42c458c/html5/thumbnails/9.jpg)
9
Copyright(c)2011 Tatsuo Ishii
Architecture of streaming replication
WALlog
WALlog
WAL sender
WAL receiver
read/write query
read query
Primary
Standby
archivelog
archivelog
![Page 10: Using streaming replication of PostgreSQL with pgpool-II · SRA group is an independent software house in Japan – Employees: 1,700 – Sales: 34 billion yen SRA OSS Established](https://reader033.vdocuments.us/reader033/viewer/2022052612/5f0a91767e708231d42c458c/html5/thumbnails/10.jpg)
10
Copyright(c)2011 Tatsuo Ishii
Promoting standby
WALlog
WALlog
WAL sender
WAL receiver
read/write query
read query
Primary
Standby
X
New primary
![Page 11: Using streaming replication of PostgreSQL with pgpool-II · SRA group is an independent software house in Japan – Employees: 1,700 – Sales: 34 billion yen SRA OSS Established](https://reader033.vdocuments.us/reader033/viewer/2022052612/5f0a91767e708231d42c458c/html5/thumbnails/11.jpg)
11
Copyright(c)2011 Tatsuo Ishii
Benefits of streaming replicaton
● PostgreSQL built-in replication available since 9.0
● Easy to setup● Low overhead● Everything can be replicated
● Slony-I does not replicate DDLs, DCLs and large objects
● No application changes necessary as long as accessing primary
![Page 12: Using streaming replication of PostgreSQL with pgpool-II · SRA group is an independent software house in Japan – Employees: 1,700 – Sales: 34 billion yen SRA OSS Established](https://reader033.vdocuments.us/reader033/viewer/2022052612/5f0a91767e708231d42c458c/html5/thumbnails/12.jpg)
12
Copyright(c)2011 Tatsuo Ishii
Things you might care about
● Replication lag(so called “asynchronous replication”)
● No automatic failover● No built-in load balancing● Can't send write queries to standby
● DDLs, DCLs, DMLs● SELECTs including functions which write to
databases● Some locking commands etc.
![Page 13: Using streaming replication of PostgreSQL with pgpool-II · SRA group is an independent software house in Japan – Employees: 1,700 – Sales: 34 billion yen SRA OSS Established](https://reader033.vdocuments.us/reader033/viewer/2022052612/5f0a91767e708231d42c458c/html5/thumbnails/13.jpg)
13
Copyright(c)2011 Tatsuo Ishii
Why pgpool-II?
● Some of problems can be solved or at least moderated by using pgpool-II
![Page 14: Using streaming replication of PostgreSQL with pgpool-II · SRA group is an independent software house in Japan – Employees: 1,700 – Sales: 34 billion yen SRA OSS Established](https://reader033.vdocuments.us/reader033/viewer/2022052612/5f0a91767e708231d42c458c/html5/thumbnails/14.jpg)
14
Copyright(c)2011 Tatsuo Ishii
What is pgpool-II?
● A 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
● Working as proxy between database clients and PostgreSQL
![Page 15: Using streaming replication of PostgreSQL with pgpool-II · SRA group is an independent software house in Japan – Employees: 1,700 – Sales: 34 billion yen SRA OSS Established](https://reader033.vdocuments.us/reader033/viewer/2022052612/5f0a91767e708231d42c458c/html5/thumbnails/15.jpg)
15
Copyright(c)2011 Tatsuo Ishii
Basic idea of pgpool-II
PostgreSQL client pgpool-II
PostgreSQL server
PostgreSQL server
PostgreSQL server
query
query
query
query
Transparent against both PostgreSQLserver and client
![Page 16: Using streaming replication of PostgreSQL with pgpool-II · SRA group is an independent software house in Japan – Employees: 1,700 – Sales: 34 billion yen SRA OSS Established](https://reader033.vdocuments.us/reader033/viewer/2022052612/5f0a91767e708231d42c458c/html5/thumbnails/16.jpg)
16
Copyright(c)2011 Tatsuo Ishii
The architecture of pgpool-II
pgpool-IIparent
PostgreSQL
PostgreSQL
pcpprocess
Admin
workerprocess
pgpool-IIchildpgpool-II
childClient
ReplicationDelay checking
HealthChecking
QueryQuery
Query
fork fork
fork
![Page 17: Using streaming replication of PostgreSQL with pgpool-II · SRA group is an independent software house in Japan – Employees: 1,700 – Sales: 34 billion yen SRA OSS Established](https://reader033.vdocuments.us/reader033/viewer/2022052612/5f0a91767e708231d42c458c/html5/thumbnails/17.jpg)
17
Copyright(c)2011 Tatsuo Ishii
Pgpool-II helpsStreaming replication
pgpool-II
Primary
Standby
Read/writequeries
Writequery
Read query
Streaming replication
Pgpool-II does query dispatchingPgpool-II does query dispatchingand failoverand failover
![Page 18: Using streaming replication of PostgreSQL with pgpool-II · SRA group is an independent software house in Japan – Employees: 1,700 – Sales: 34 billion yen SRA OSS Established](https://reader033.vdocuments.us/reader033/viewer/2022052612/5f0a91767e708231d42c458c/html5/thumbnails/18.jpg)
18
Copyright(c)2011 Tatsuo Ishii
Pgpool-II helps users who use streaming replication
● Automatic query dispatching● Redirect DDLs, DCLs, DMLs and some commands(e.g. LOCK) to primary ● Allow to specify functions which are writing to DB(they need to be
executed on the primary)● Allow to load balance SELECTs
● Boost performance by enabling connection cache
● Automatic failover if one of database servers goes down
● Recover down node without stopping pgpool-II(online recovery)● Adding standby servers without stopping pgpool-II
![Page 19: Using streaming replication of PostgreSQL with pgpool-II · SRA group is an independent software house in Japan – Employees: 1,700 – Sales: 34 billion yen SRA OSS Established](https://reader033.vdocuments.us/reader033/viewer/2022052612/5f0a91767e708231d42c458c/html5/thumbnails/19.jpg)
19
Copyright(c)2011 Tatsuo Ishii
Load balance SELECTs in an explicit transaction
BEGIN;
SELECT
SELECT
UPDATE
SELECT::
transactionTransaction starts
Load balance ok
Load balance ok
Write to database
Load balance is not ok anymore becausethe table might be changed
![Page 20: Using streaming replication of PostgreSQL with pgpool-II · SRA group is an independent software house in Japan – Employees: 1,700 – Sales: 34 billion yen SRA OSS Established](https://reader033.vdocuments.us/reader033/viewer/2022052612/5f0a91767e708231d42c458c/html5/thumbnails/20.jpg)
20
Copyright(c)2011 Tatsuo Ishii
Monitoring replication delay
2010-06-28 15:51:32 LOG: pid 13223: Replication of node:1 is behind 1228800 bytes from the primary server (node:0)2010-06-28 15:51:42 LOG: pid 13223: Replication of node:1 is behind 3325952 bytes from the primary server (node:0)2010-06-28 15:51:52 LOG: pid 13223: Replication of node:1 is behind 974848 bytes from the primary server (node:0)2010-06-28 15:52:02 LOG: pid 13223: Replication of node:1 is behind 2990080 bytes from the primary server (node:0)2010-06-28 15:52:12 LOG: pid 13223: Replication of node:1 is behind 901120 bytes from the primary server (node:0)2010-06-28 15:52:22 LOG: pid 13223: Replication of node:1 is behind 2433024 bytes from the primary server (node:0)
● log_standby_delay● 'none': no logging delay● 'if_over_threshold': log only when delay exceeds delay_threshold● 'always': always log delay
● If replication delay is too much, do not send SELECTs to standby
![Page 21: Using streaming replication of PostgreSQL with pgpool-II · SRA group is an independent software house in Japan – Employees: 1,700 – Sales: 34 billion yen SRA OSS Established](https://reader033.vdocuments.us/reader033/viewer/2022052612/5f0a91767e708231d42c458c/html5/thumbnails/21.jpg)
21
Copyright(c)2011 Tatsuo Ishii
What's new in pgpool-II next version (3.1)?
● Allow to use regular expression in black/white_function_list
● Allow to use syslog● Better handling for more than 1 standby case● After finishing online recovery, pgpool-II does
not restart child process any more(thus client sessions are not disturbed)
● Import PostgreSQL 9.0 SQL parser● Currently alpha 3 released
![Page 22: Using streaming replication of PostgreSQL with pgpool-II · SRA group is an independent software house in Japan – Employees: 1,700 – Sales: 34 billion yen SRA OSS Established](https://reader033.vdocuments.us/reader033/viewer/2022052612/5f0a91767e708231d42c458c/html5/thumbnails/22.jpg)
22
Copyright(c)2011 Tatsuo Ishii
Demonstration
DB client Pgpool-II 3.1
Primary PostgreSQL
Standby PostgreSQL
Streamingreplication
![Page 23: Using streaming replication of PostgreSQL with pgpool-II · SRA group is an independent software house in Japan – Employees: 1,700 – Sales: 34 billion yen SRA OSS Established](https://reader033.vdocuments.us/reader033/viewer/2022052612/5f0a91767e708231d42c458c/html5/thumbnails/23.jpg)
23
Copyright(c)2011 Tatsuo Ishii
URL etc.● Pgpool-II can be downloaded here:
● http://pgfoundry.org/projects/pgpool/● Twitter
● @pgpool2● SRA OSS's website
● http://www.srasoss.co.jp/index_en.php
![Page 24: Using streaming replication of PostgreSQL with pgpool-II · SRA group is an independent software house in Japan – Employees: 1,700 – Sales: 34 billion yen SRA OSS Established](https://reader033.vdocuments.us/reader033/viewer/2022052612/5f0a91767e708231d42c458c/html5/thumbnails/24.jpg)
24
Copyright(c)2011 Tatsuo Ishii
多謝