Thread: Split read/write queries between 2 servers (one master and one slave with streaming replication)?

Split read/write queries between 2 servers (one master and one slave with streaming replication)?

From

Benjamin Henrion

Date:

01 March 2012, 21:06:45

Hi,

I am trying pgpool2 to split read and write queries, where reads
should go to the slave server (streaming replication) and writes to
the master server.

Anybody has a config file that works for pgpool2?

Best,

--
Benjamin Henrion <bhenrion at ffii.org>
FFII Brussels - +32-484-566109 - +32-2-3500762
"In July 2005, after several failed attempts to legalise software
patents in Europe, the patent establishment changed its strategy.
Instead of explicitly seeking to sanction the patentability of
software, they are now seeking to create a central European patent
court, which would establish and enforce patentability rules in their
favor, without any possibility of correction by competing courts or
democratically elected legislators."

Re: Split read/write queries between 2 servers (one master and one slave with streaming replication)?

From

Adam Cornett

Date:

01 March 2012, 21:30:32

On Thu, Mar 1, 2012 at 5:06 PM, Benjamin Henrion <bh@udev.org> wrote:

Hi,

I am trying pgpool2 to split read and write queries, where reads
should go to the slave server (streaming replication) and writes to
the master server.

Anybody has a config file that works for pgpool2?

Best,

--
Benjamin Henrion <bhenrion at ffii.org>
FFII Brussels - +32-484-566109 - +32-2-3500762
"In July 2005, after several failed attempts to legalise software
patents in Europe, the patent establishment changed its strategy.
Instead of explicitly seeking to sanction the patentability of
software, they are now seeking to create a central European patent
court, which would establish and enforce patentability rules in their
favor, without any possibility of correction by competing courts or
democratically elected legislators."

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

I've attached my pgpool conf.

All pgpool is doing here is the load balancing, replication is handled by streaming replication (9.0+)

PGPool can be setup to do auto failover as well, although that isn't shown in the attached config.

I'm sure some pgpool people will jump on and let me know how I'm doing it wrong, but this is working quite well in production for us.

-Adam

Attachment

pgpool.conf

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

From

Venkateswaran

Date:

14 February 2018, 19:59:47

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?



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

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

From

Pierre Timmermans

Date:

15 February 2018, 01:27:26

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?

Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

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

From

Melvin Davidson

Date:

15 February 2018, 01:43:25

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?

--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

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.

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

From

Pierre Timmermans

Date:

15 February 2018, 01:55:00

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?

--
Sent from: http://www.postgresql-archive. org/PostgreSQL-general- f1843780.html

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.