Postgres with pgpool architecture

Question

Below is an example pgpool architecture:

This implies that you only need to have pgpool on a single server; is this true? When I look at the configuration I also see that you configure backends within pgpool.conf; so it further implies this. But, it doesn't explain why I see pgpool on backend servers also.

When looking over the documentation I also see:

If you are using PostgreSQL 8.0 or later, installing pgpool_regclass function on all PostgreSQL to be accessed by pgpool-II is strongly recommended, as it is used internally by pgpool-II.

So I'm not sure what to think; if it's best practice to have pgpool on all backends or just a dedicated server?

Answer 1

Generally you would not install Pgpool on the backend servers. What you see in your picture is the most common configuration. Pgpool is a standalone server which essentially sits in front of the databases. The two Postgres servers are often configured with streaming replication; with one being the master and the other the slave.

This allows Pgpool to load balance all read queries between the two (or more) databases. Any queries which involve any writes, will be routed to the master server which in turn replicates to the slave.

As @Neil McGuigan said, you can also have multiple Pgpool servers to achieve better high availability. Technically you could install Pgpool on the database servers in this configuration, but this would be bad practice. Running multiple Pgpool servers is a much more complex configuration. If this is your first time with Pgpool, I would start with one Pgpool server before getting two to work.

In either configuration, your application server thinks it is just connecting to a single Postgres database.

About pgpool_regclass, which should really be a separate question, this is from the Pgpool FAQ:

If you are using PostgreSQL 8.0 or later, installing pgpool_regclass function on all PostgreSQL to be accessed by pgpool-II is strongly recommended, as it is used internally by pgpool-II. Without this, handling of duplicate table names in different schema might cause trouble (temporary tables aren't a problem).

If you are using PostgreSQL 9.4.0 or later and pgpool-II 3.3.4 or later, 3.4.0 or later, you don't need to install pgpool_regclass since PostgreSQL 9.4 has built-in pgpool_regclass like function "to_regclass".

If you need this, it is just some SQL code run on your Postgres master server to add a function Pgpool uses.

With regclass, there is an additional step you have to do (I was thinking of insert_lock). If you are compiling from source (generally most distributions have really outdated versions of Pgpool), you will have to compile a Postgres library as well.

If you compiled from source, you will have to go into the .../pgpool-II-3.X.X/src/sql/pgpool-regclass folder and do a ./configure; make.

Copy the pgpool-regclass.so file into the Postgres extension directory. On my Ubuntu 14.04 server (just using the Postgres 9.3 package install), it is located at: /usr/lib/postgresql/9.3/lib. Remember to do this for all Postgres servers.

Once that is complete, then you can run pgpool-regclass.sql on the master. This just maps the pgpool_regclass function to the library you copied over.

Answer 2

As with everything else, there are many ways how you can accomplish your high availability deployment. Here I'll suggest something from my experience (my own HA implementation):

1. It is always better to have multiple pgpool2 instances instead of single one. The reason is obvious: single pgpool2 is single point of failure. Since pgpool has introduced watchdog feature it is easy to accomplish.
2. Generally it is slightly better to have pgpool2 instances on separate machines than to share the same machine between PostgreSQL backend and pgpool2. But there's no significant drawback even if you run them on the same server as PostgreSQL. (In my HA implementation every machine runs one PostgreSQL instance and one pgpool2 instance.)

Finally I'll recommend this step-by-step tutorial that'll lead you from scratch (installing PostgreSQL server...) to complete high availability implementation. The mentioned tutorial describes implementation which I use.

I hope it helped.

UPDATE: Thanks @Moshe Katz - the link did changed. Now updated here, in the original post also.