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.

• Why does node status in pgpoolAdmin shows "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=#