Thread: [GENERAL] PgPool or alternatives

[GENERAL] PgPool or alternatives

From
Simon Windsor
Date:
Hi

My employer wants to move from an in house Oracle solution to a cloud
based Postgres system. The system will involve a number of data loaders
running 24x7 feeding several Postgres Databases that will be used by
internal applications and external customer applications.

For the record, internal and external applications make heavy use of
Temporary tables, that are session related. This requirement means I
cannot consider normal replication methods.

Is PgPool the only viable that will allow the system the data loaders to
feed [n] databases that will be functional identical?

Simon

--
Simon Windsor

Eml: simon.windsor@cornfield.me.uk
Tel: 01454 617689
Mob: 0755 197 9733


“There is nothing in the world that some man cannot make a little worse and sell a little cheaper, and he who considers
priceonly is that man's lawful prey.” 



Re: [GENERAL] PgPool or alternatives

From
Stephen Frost
Date:
Simon,

* Simon Windsor (simon.windsor@cornfield.me.uk) wrote:
> My employer wants to move from an in house Oracle solution to a
> cloud based Postgres system. The system will involve a number of
> data loaders running 24x7 feeding several Postgres Databases that
> will be used by internal applications and external customer
> applications.
>
> For the record, internal and external applications make heavy use of
> Temporary tables, that are session related. This requirement means I
> cannot consider normal replication methods.
>
> Is PgPool the only viable that will allow the system the data
> loaders to feed [n] databases that will be functional identical?

I'm not sure what you mean by 'functional identical', but I wouldn't
generally consider that to be a property of pgpool (or pgbouncer, or any
other connection pooler, really).

That said, my general feeling is that pgbouncer tends to be simpler,
faster, and less likely to introduce oddities that you don't expect.
The 'session' mode might work for you, though it might be debatable if
that really helps you all that much.  'transaction' mode is what I
usually recommend as it allows idle connections to be handled by
pgbouncer (unlike 'session' mode), but there are caveats to using that
mode, of course.

I'm a bit curious where you're thinking of using the connection pooler
also though.  If you have data loaders running 24x7 feeding data
constantly to PG, do you really need a connection pooler for those?
Connection poolers make a lot of sense for environments where there's
lots of down-time on the connection, but the less down-time, the less
they make sense.

Thanks!

Stephen

Attachment

Re: [GENERAL] PgPool or alternatives

From
Simon Windsor
Date:
Hi

Thanks for the reply. We were not planning to use pgPools connection
pool mode, but its replication mode.

Our tests with pgPool allow us to install a backup db via pgPool to each
node, and tests loads overnight of 10+GB of inserts/updates/deletes all
work fine, with only a slight loss of performance vs a standalone DB.

I was wondering if there is another option that will allow me to spool
all ALTER|CREATE|DELETE|DROP|INSERT|UPDATE commands to all nodes, and
SELECTs to any of the connected nodes. The apllication can actually
handle separate READ|WRITE nodes from how it was written for Oracle.

Simon

On 21/01/2017 20:09, Stephen Frost wrote:
> Simon,
>
> * Simon Windsor (simon.windsor@cornfield.me.uk) wrote:
>> My employer wants to move from an in house Oracle solution to a
>> cloud based Postgres system. The system will involve a number of
>> data loaders running 24x7 feeding several Postgres Databases that
>> will be used by internal applications and external customer
>> applications.
>>
>> For the record, internal and external applications make heavy use of
>> Temporary tables, that are session related. This requirement means I
>> cannot consider normal replication methods.
>>
>> Is PgPool the only viable that will allow the system the data
>> loaders to feed [n] databases that will be functional identical?
> I'm not sure what you mean by 'functional identical', but I wouldn't
> generally consider that to be a property of pgpool (or pgbouncer, or any
> other connection pooler, really).
>
> That said, my general feeling is that pgbouncer tends to be simpler,
> faster, and less likely to introduce oddities that you don't expect.
> The 'session' mode might work for you, though it might be debatable if
> that really helps you all that much.  'transaction' mode is what I
> usually recommend as it allows idle connections to be handled by
> pgbouncer (unlike 'session' mode), but there are caveats to using that
> mode, of course.
>
> I'm a bit curious where you're thinking of using the connection pooler
> also though.  If you have data loaders running 24x7 feeding data
> constantly to PG, do you really need a connection pooler for those?
> Connection poolers make a lot of sense for environments where there's
> lots of down-time on the connection, but the less down-time, the less
> they make sense.
>
> Thanks!
>
> Stephen

--
Simon Windsor

Eml: simon.windsor@cornfield.me.uk
Tel: 01454 617689
Mob: 0755 197 9733


“There is nothing in the world that some man cannot make a little worse and sell a little cheaper, and he who considers
priceonly is that man's lawful prey.” 



Re: [GENERAL] PgPool or alternatives

From
Thomas Kellerer
Date:
Simon Windsor schrieb am 21.01.2017 um 21:26:
> I was wondering if there is another option that will allow me to
> spool all ALTER|CREATE|DELETE|DROP|INSERT|UPDATE commands to all
> nodes, and SELECTs to any of the connected nodes. The apllication can
> actually handle separate READ|WRITE nodes from how it was written for
> Oracle.

You could logical replication: https://2ndquadrant.com/en/resources/pglogical/


Re: [GENERAL] PgPool or alternatives

From
Alvaro Aguayo Garcia-Rada
Date:
Hi.

I had to implement something similar some time ago. Basically, a group of database servers (postgres) geographically
distributed,with each one having a group of servers in each datacenter, and each server preferring the nearest database
server,but allowing connections to a further one if the nearest is down. 

After going through different solutions (pgpool between them), I got to Postgres BDR with HAProxy. Each app server
connectsto the local HAProxy, which forwards the connection to the nearest available database server(preference is set
directlyin the HAProxy configuration). That way, I get high availability and replication happens really fast, right
afterthe transaction is committed. 

The only drawback with Postgres BDR is it has some limitations:

- New databases are NOT replicated; but you can have any number of databases with no problem.
- Users & roles must be replicated manually, as BDR works at database-level.
- There are some DDL restrictions: mostly due to how BDR works internally. In my experience, none of them has been a
realproblem. Full list: http://bdr-project.org/docs/stable/ddl-replication-statements.html 
- DDL replication may sometimes mean automatic cancellation of running transactions. so it must be carefully planned.
However,consider TEMPORARY TABLES are not replicated, so DDL on them is not affected by BDR restrictions. 
- Even when BDR documentations says nothing about this, it can have trouble replicating really large transactions. In
myexperience, my BDR cluster stopped replicating (had to rebuild it) when an app made a 8 million records update in a
singletransaction. Since that app was corrected, nothing similar has ever happened, and I think most apps should not
haveproblems with this. 

Alvaro Aguayo
Jefe de Operaciones
Open Comb Systems E.I.R.L.

Oficina: (+51-1) 3377813 | RPM: #034252 / (+51) 995540103  | RPC: (+51) 954183248
Website: www.ocs.pe

----- Original Message -----
From: "Simon Windsor" <simon.windsor@cornfield.me.uk>
To: pgsql-general@postgresql.org
Sent: Saturday, 21 January, 2017 2:38:59 PM
Subject: [GENERAL] PgPool or alternatives

Hi

My employer wants to move from an in house Oracle solution to a cloud
based Postgres system. The system will involve a number of data loaders
running 24x7 feeding several Postgres Databases that will be used by
internal applications and external customer applications.

For the record, internal and external applications make heavy use of
Temporary tables, that are session related. This requirement means I
cannot consider normal replication methods.

Is PgPool the only viable that will allow the system the data loaders to
feed [n] databases that will be functional identical?

Simon

--
Simon Windsor

Eml: simon.windsor@cornfield.me.uk
Tel: 01454 617689
Mob: 0755 197 9733


“There is nothing in the world that some man cannot make a little worse and sell a little cheaper, and he who considers
priceonly is that man's lawful prey.” 



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