Re: Split read/write queries between 2 servers (one master and oneslave with streaming replication)? - Mailing list pgsql-general

From Pierre Timmermans
Subject Re: Split read/write queries between 2 servers (one master and oneslave with streaming replication)?
Date
Msg-id 1852095905.899017.1518638100059@mail.yahoo.com
Whole thread Raw
In response to Re: Split read/write queries between 2 servers (one master and oneslave with streaming replication)?  (Melvin Davidson <melvin6925@gmail.com>)
List pgsql-general
Thanks. Indeed pgBouncer is the usual candidate in front of postgres streaming replication, together with pgpool. Take care that your link on pgBouncer dates back from 2008 !. 

I had a look at pgBouncer in the past, but it was not actively maintained at that time. So I settled on a combination of pgpool and repmgr. People on pgpool mailing list are very reactive and helpful and the doc on the project is continuously improving.

Pierre


On Wednesday, February 14, 2018, 8:44:47 PM GMT+1, Melvin Davidson <melvin6925@gmail.com> wrote:




On Wed, Feb 14, 2018 at 2:27 PM, Pierre Timmermans <ptim007@yahoo.com> wrote:
pgpool does just that: it enables you to use the standby database as a read only while other queries are sent to the primary database only (http://www.pgpool.net/). Good product and good support (on the list°

This looks a very interesting possibility, although it is more related to automated failover than to load balancing of read only queries : http://paquier.xyz/ postgresql-2/postgres-10- libpq-read-write/, depending on your client it might be supported. It is not supported - yet ? - by the node-postgres driver.

Regards, 

Pierre


On Wednesday, February 14, 2018, 3:29:03 PM GMT+1, Venkateswaran <venkateswaran.srec@gmail.com> wrote:


Hi,

I am also trying to split read and write queries, where reads should go to
the slave server (streaming replication) and writes to the master server.
Any tool available to perform this activity instead of two connection pool?



--


Actually, if you are looking for load balancing PgBouncer is better than PgPool. There is a nice article about that.
https://www.last.fm/user/Russ/journal/2008/02/21/zd_postgres_connection_pools:_pgpool_vs._pgbouncer

I used PgBouncer in a few of ny previous positions and found it to be very fast and efficient.

--
Melvin Davidson
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

pgsql-general by date:

Previous
From: Melvin Davidson
Date:
Subject: Re: Split read/write queries between 2 servers (one master and oneslave with streaming replication)?
Next
From: Yogesh Sharma
Date:
Subject: Re: Regarding pg_multixact/members folder size cleanup in postgres 9.3.6.