FAQ

Pgpool-II Frequently Asked Questions

Why configure fails by "pg_config not found" on my Ubuntu box?

pg_config is in libpq-dev package. You need to install it before running configure.

Why records inserted on the primary node do not appear on the standby nodes?

Are you using streaming replication and a hash index on the table? Then it's a known limitation of streaming replication. The inserted record is there. But if you SELECT the record using the hash index, it will not appear. Hash index changes do not produce WAL record thus they are not reflected to the standby nodes. Solutions are: 1) use btree index instead 2) use pgpool-II native replication.

Can I mix different versions of PostgreSQL as pgpool-II backends?

You cannot mix different major versions of PostgreSQL, for example 8.4.x and 9.0.x. On the other hand you can mix different minor versions of PostgreSQL, for example 9.0.3 and 9.0.4. Pgpool-II assumes messages from PostgreSQL to pgpool-II are identical anytime. Different major version of PostgreSQL may send out different messages and this would cause trouble for Pgpool-II.

Can I mix different platforms of PostgreSQL as pgpool-II backends, for example Linux and Windows?

In streaming replication mode, no. Because streaming replication requires that primary and standby platforms are phsyically identical. On the other hand, pgpool-II's replication mode only requires logically database clusters identical. Beware, however, that online recovery script does not use rsync or some such, which do phical copying among database clusters. You want to use pg_dumpall instead.

It seems my pgpool-II does not do load balancing. Why?

First of all, pgpool-II' load balancing is "session base", not "statement base". That means, DB node selection for load balancing is decided at the beginning of session. So all SQL statements are sent to the same DB node until the session ends.

Another point is, whether statement is in an explicit transaction or not. If the statement is in a transaction, it will not be load balanced in the replication mode. In pgpool-II 3.0 or later, SELECT will be load balanced even in a transaction if operated in the master/slave mode.

Note the method to choose DB node is not LRU or some such. Pgpool-II chooses DB node randomly considering the "weight" parameter in pgpool.conf. This means that the chosen DB node is not uniformly distributed among DB nodes in short term. You might want to inspect the effect of load balancing after ~100 queries have been sent.

How can I observe the effect of load balancing?

We recommend to enable "log_per_node_statement" directive in pgpool.conf for this. Here is an example of the log:

2011-05-07 08:42:42 LOG:   pid 22382: DB node id: 1 backend pid: 22409 statement: SELECT abalance FROM pgbench_accounts WHERE aid = 62797;

The "DB node id: 1" shows which DB node was chosen for this loadbalancing session.

Please make sure that you start pgpool-II with "-n" option to get pgpool-II log. (or you can use syslog in pgpool-II 3.1 or later)

Why am I getting "ProcessFrontendResponse: failed to read kind from frontend. frontend abnormally exited" in my pgool log?

Well, your clients might be ill-behaved:-) PostgreSQL's protocol requires clients to send particular packet before they disconnect the connection. pgpool-II complains that clients disconnect without sending the packet. You could reprodcude the problem by using psql. Connect to pgpool using psql. Kill -9 psql. You will silimar message in the log. The message will not appear if you quit psql normaly. Another possibility is unstable network connection between your client machine and pgpool-II. Check the cable and network interface card.

pgpoolAdmin does not show any node in pgpool status and node status. Why?

pgpoolAdmin uses PHP's PostgreSQL extention (pg_connect and pg_query etc.). Probably the extention does not work as expected. Please check apache error log. Also please check the FAQ item below.

Why does node status in pgpoolAdmin show "down" status even if PostgreSQL is up and running?

pgpoolAdmin checks PostgreSQL status by connecting with user = "health_check_user" and database = template1. Thus you should allow pgpoolAdmin to access PostgreSQL with those user and database without password. You can check PostgreSQL log to verify this. If health_check_user does not exist, you will see something like:

20148 2011-07-06 16:41:59 JST FATAL:  role "foo" does not exist

If the user is protected by password, you will see:

20220 2011-07-06 16:42:16 JST FATAL:  password authentication failed for user "foo"
20221 2011-07-06 16:42:16 JST LOG:  could not receive data from client: Connection reset by peer
20221 2011-07-06 16:42:16 JST LOG:  unexpected EOF within message length word
20246 2011-07-06 16:42:26 JST LOG:  could not receive data from client: Connection reset by peer
20246 2011-07-06 16:42:26 JST LOG:  unexpected EOF within message length word

I'm running pgpool-II in streaming replication mode. It seems it works but I find following errors in the log. Why?

2011-07-19 08:21:59 ERROR: pid 10727: s_do_auth: unknown response "E" before processing BackendKeyData
2011-07-19 08:21:59 ERROR: pid 10727: s_do_auth: unknown response "" before processing BackendKeyData
2011-07-19 08:21:59 ERROR: pid 10727: s_do_auth: unknown response "" before processing BackendKeyData
2011-07-19 08:21:59 ERROR: pid 10727: s_do_auth: unknown response "" before processing BackendKeyData
2011-07-19 08:21:59 ERROR: pid 10727: s_do_auth: unknown response "[" before processing BackendKeyData
2011-07-19 08:21:59 ERROR: pid 10727: pool_read2: EOF encountered with backend
2011-07-19 08:21:59 ERROR: pid 10727: make_persistent_db_connection: s_do_auth failed
2011-07-19 08:21:59 ERROR: pid 10727: find_primary_node: make_persistent_connection failed

pgpool-II tries to connect to PostgreSQL to execute some functions such as pg_current_xlog_location(), which is used for detecting primary server or checking replication delay. The messages above indicate that pgpool-II failed to connect with user = health_check_user and password = health_check_password. You need to set them properly even if health_check_period = 0.

Note that pgpool-II 3.1 or later will use sr_check_user and sr_check_password for it instead.

When I run pgbench to test pgpool-II, pgbench hangs. If I directly run pgbench against PostgreSQL, it works fine. Why?

pgbench creates concurrent connections (the number of connections is specified by "-c" option) before starting actual transactions. So if the number of concurrent transactions specified by "-c" exceeds num_init_children, pgbench will stuck because it will wait for pgpool accepting connections forever (remember that pgpool-II accepts up to num_init_children concurrent sessions. If the number of concurrent sessions reach num_init_children, new session will be queued). On the other hand PostgreSQL does not accept concurrent sessions more than max_connections. So in this case you will just see PostgreSQL errors, rather than connection blocking. If you want to test pgpool-II's connection queuing, you can use psql instead of pgbench. In the example session below, num_init_children = 1 (this is not a recommended setting in the real world. This is just for simplicity).

$ psql test <-- connect to pgpool from terminal #1
psql (9.1.1)
Type "help" for help.
test=# 
$ psql test <-- tries to connect to pgpool from terminal #2 but it is blocked.
test=# SELECT 1; <--- do something from terminal #1 psql
test=# \q <-- quit psql session on terminal #1
psql (9.1.1) <-- now psql on terminal #2 accepts session
Type "help" for help.
test=# 

• I created pool_hba.conf and pool_passwd to enable md5 authentication through pgpool-II but it does not work. Why?

Probably you made mistake somewhere. For your help here is a table which describes error patterns depending on the setting of pg_hba.conf, pool_hba.conf and pool_passwd.

pg_hba.conf	pool_hba.conf	pool_passwd	result
md5	md5	yes	md5 auth
md5	md5	no	"MD5" authentication with pgpool failed for user "XX"
md5	trust	yes/no	MD5 authentication is unsupported in replication, master-slave and parallel mode
trust	md5	yes	no auth
trust	md5	no	"MD5" authentication with pgpool failed for user "XX"
trust	trust	yes/no	no auth

How can I set up SSL for pgpool-II?

SSL support for pgpool-II consists of two parts: 1)between client and pgpool-II 2)pgpool-II and PostgreSQL. #1 and #2 are independent each other. For example, you can only enable SSL connection of #1, or #2. Or you can enable both #1 and #2. I explain #1 (for #2, please take a look at PostgreSQL documentation).
Make sure that pgpool is built with openssl. If you build from source code, use --with-openssl option.
First create server certificate. In the command below you will be asked PEM pass phrase(It will be asked when pgpool starts up). If you want to start pgpool without being asked pass phrase, you can remove it later. (sample server certficate create session)

openssl req -new -text -out server.req

Remove PEM pass phrase if you want.

$ openssl rsa -in privkey.pem -out server.key
Enter pass phrase for privkey.pem:
writing RSA key
$ rm privkey.pem

Turn the certificate into a self-signed certificate.

$ openssl req -x509 -in server.req -text -key server.key -out server.crt

Copy server.key and server.crt to appropreate place. Suppose we copy to /usr/local/etc. Make sure that you use cp -p to retain appropreate permission of server.key. Alternatively you can set permission later.

$ chmod og-rwx /usr/local/etc/server.key

Set the certificate and key location in pgpool.conf.

ssl = on
ssl_key = '/usr/local/etc/server.key'
ssl_cert = '/usr/local/etc/server.crt'

Restart pgpool. To confirm SSL connection between client and pgpool is working, connect to pgpool using psql.

psql -h localhost -p 9999 test
psql (9.1.1)
SSL connection (cipher: AES256-SHA, bits: 256)
Type "help" for help.

test=# \q

If you see "SSL connection...", SSL connection between client and pgpool is working. Please make sure that use "-h localhost" option. Because SSL only works with TCP/IP, with Unix domain socket SSL does not work.

I'm using pgpool-II in replication mode. I expected that pgpool-II replaces current_timestamp call with time constants in my INSERT query, but actually it doesn't. Why?

Probably your INSERT query uses schema qualied table name (like public.mytable) and you did not install pool_regclass function coming pgpool. Without pgpool_reglclass, pgpool-II only deals with table names without schema qualification.

Why max_connection must satisfy this formula max_connection >= (num_init_children * max_pool) and not max_connection >= num_init_children?

Probably you need to understand how pgpool uses these variables. Here is internal processing inside pgpool.

1. Wait for connection request from clients.
2. pgpool child receives connection request from a client.
3. The pgpool child looks for existing connection in the pool which has requested database/user pair up to max_pool.
4. If found, reuse it.
5. If not found, opens a new connection to PostgreSQL and registers to the pool. If the pool has no empty slot, closes the oldest connection to PostgreSQL and reuse the slot.
6. Do some query processing until the client sends session close request.
7. Close the connection to client but keeps the connection to PostgreSQL for future use.
8. Go to #1

Is connection pool cache shared among pgpool process?

No, the connection pool cache is in pgpool's process private memory and is not shared by other pgpool. This is how the connection cache is managed: Suppose pgpool process 12345 has connection cache for database A/user B but process 12346 does not have connection cache for database A/user B and both 12345 and 12346 are in idle state(no client is connecting at this point). If client connects to pgpool process 12345 with database A/user B, then the exisiting connection of 12345 is reused. On the other hand, If client connects to pgpool process 12346, 12346 needs to create new connection. Whether 12345 or 12346 is chosen, is not under control of pgpool. However in the long run, each pgpool child process will be equally chosen and it is expected that each process's pool will be resued equally.