[pgpool-general: 6120] Re: Confused between Running Modes !

Tatsuo Ishii ishii at sraoss.co.jp
Fri Jun 8 20:07:41 JST 2018


> Hi Team,
> 
> Kindly guide me ! I am heavily confused between Running modes of PgPool II 3.7.3.
> 
> I am having 3 PostgreSQL 10 Servers running in Streaming replication Hot Standby mode. In which 1 is master and 2 are slaves. 
> The 1 master PostgreSQL 10 Server supports both read/write and 2 slave PostgreSQL 10 Servers supports only read queries.
> 
> I am setting PgPool II 3.7.3 to load balance all my queries among these 3 servers based on query type i.e read queries should be distributed between
> all the 3 PostgreSQL 10 servers and write query should only go to Master PostgreSQL 10 server. I don't want PgPool to do replication ! I just want PgPool to
> do smart load-balancing. 
> 
> Now for this above configuration of mine I am not able to decide PgPool II 3.7.3. Running Modes. I am confused between Master-Slave(Stream) Mode 
> and Streaming Replication mode !
> 
> Since the documentation for both the modes suggests that the streaming/replication and synchronization of data will be done by PostgreSQL 10 servers.
> 
> Please elaborate any technical details of both the modes if I am missing anything. And most importantly considering my PostgreSQL 10 Servers infrastructure 
> which mode should I use ???
> 
> I may missing something. Any suggestions are welcome.
> 
> Regards,
> Nitish Kumar

> I am confused between Master-Slave(Stream) Mode and Streaming Replication mode !

Actually they are identical, meaning:

master_slave_mode = on
master_slave_sub_mode = 'stream'

In this mode replication are done by PostgreSQL. Pgpool-II does load
balancing.

Some background of this:

In the early days of Pgpool-II, there was only master_slave_mode,
which supposed to use Slony-I, since streaming replication did not
exist yet. Then the streaming replication comes up. From a Pgpool-II's
point of veiw, there were subtle differences in the way to handle
Slony-I and streaming replication. So we invented
master_slave_sub_mode to distinguish them.

> Please elaborate any technical details of both the modes if I am missing anything. And most importantly considering my PostgreSQL 10 Servers infrastructure 
> which mode should I use ???

I think for most uses cases streaming replication mode should be best
for you.  However if your application is sensitive to replication
delay between PostgreSQL primary and standby, you might want to set
synchronous_commit = remote_apply in postgresql.conf, which eliminate
the replication delay sacrificing performance. Because primary has to
wait before commit until standby applies the WAL.

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp


More information about the pgpool-general mailing list