Postgres - high availability using PGPool - guidance

PostgreSQL
,
1

Hi guys

Looking for some guidance.

I have a two node master-slave Postgres setup using streaming replication.

On the third server, I have installed pgpool-ii from the Postgres 14.5 TAR distribution. Installation creates a user called postgres.

In have updated pgpool.conf based on the documentation.

Question - should I be running pgpool under postgres user? ATM, when I start the service (pgpool -n &) manually under postgres, I see a PID created but nothing else. Doesn’t look like it is talking to the two Postgres clusters.

Also, is pgpool the right tool or should I look at something else?

Cheers, Puneet

1 Like
2

Q: Should I be running pgpool under postgres user?
A: While it is possible to run it under another distinct user, it is perfectly reasonable running pgpool under the postgres user. Placing it under another owner is purely from a security point of view which requires having the necessary permissions administering it.

Q: Is pgpool the right tool or should I look at something else?
A: Pgpool serves multiple purposes, the caveat though is its complexity and may be a little bit of an overkill if you just want connection pooling:
- native replication (master-slave, master-master)
- connection pooling
- load balancing
- high availability i.e. automatic failover etc
- in memory query caching

Q: Doesn’t look like it is talking to the two Postgres clusters.
A: Without belaboring the point too much, once installed, pgpool does require configuration in order to see your two nodes. Refer here for more about configuring pgpool.

Hope this helps.

3

Thanks Rob.

I will go through the configuration again.

My use case for pgpool is primarily for automated failover/failback & propotion of clusters. For replication, I have configured native streaming replication in Postgres itself.

Thanks again.

Regards, Puneet