Pgpool-II

PgBouncer relays bytes. Pgpool-II reads them. It speaks the PostgreSQL frontend and backend protocol in both directions, parses every statement with an imported copy of PostgreSQL’s own SQL parser, and uses what it finds to decide which backend the statement should go to. That single design decision is the source of everything Pgpool-II can do that PgBouncer cannot - read/write splitting, automatic failover, query cache - and of every failure mode it adds.

Pgpool-II gives you four things beyond pooling:

The cost is that Pgpool-II is a multi-process server with its own clustering state, its own quorum logic, its own SQL parser, and its own version-coupled understanding of PostgreSQL grammar. If the parser mis-classifies a statement, the query goes to the wrong node. If the watchdog partitions, you can get two virtual IP holders. Neither failure exists in a PgBouncer deployment.

Many teams therefore run PgBouncer for pooling and Patroni for failover instead, with HAProxy or keepalived owning the VIP. That stack has more moving parts on paper but each part does one job, and Patroni’s failover decisions are grounded in a real consensus store (etcd, Consul, ZooKeeper) rather than in Pgpool-II’s own watchdog. Pgpool-II earns its place when you specifically want in-process read/write splitting without teaching the application about replicas, or want the query cache, or want one component instead of three.

Clustering mode is chosen once and never changed

backend_clustering_mode selects the model. The modes are mutually exclusive and cannot be changed after the server starts, so this is an early design decision, not a tunable.

Use streaming_replication. It is the mode the documentation calls the most recommended way to use Pgpool-II, and it is the only one where PostgreSQL remains the authority on replication. The trade-off is stated plainly upstream: visibility consistency among nodes is not guaranteed, so a read routed to a standby immediately after a write may not see it.

snapshot_isolation is the interesting outlier: Pgpool-II performs the writes itself against every node and does guarantee visibility consistency across the cluster. The price is that the transaction isolation level must be REPEATABLE READ (default_transaction_isolation = 'repeatable read' in postgresql.conf) and that throughput is worse than streaming replication mode because of the consistency bookkeeping.

logical_replication has a sharp edge: logical replication does not replicate every table, but Pgpool-II load balances every table. A table you forgot to publish will be read stale from the subscriber, silently.

Pooling and load balancing are separate switches

These are two independent booleans, and the names invite confusion.

You can run with balancing and no pooling, pooling and no balancing, both, or neither. They interact only through sizing. Connections to the template0, template1, postgres and regression databases are never cached even when connection_cache = on.

Pgpool-II’s pooling model is not PgBouncer’s. Each child process handles exactly one client at a time and holds up to max_pool cached backend connections of its own; there is no shared pool. The ceiling on backend connections is therefore num_init_children * max_pool, which with the defaults of 32 and 4 is 128 connections per Pgpool-II instance. The documented constraint is:

max_pool * num_init_children <= (max_connections - superuser_reserved_connections)

and doubled if you need query cancellation to work, because cancelling a query opens another connection to the backend. Health checking opens one more connection per backend node on top of that.

num_init_children is also the client concurrency limit. Clients beyond it are blocked, not rejected, up to listen_backlog_multiplier * num_init_children (default multiplier 2) queued in the kernel listen queue. Set reserved_connections to 1 or more if you would rather they get “Sorry, too many clients already” than block; the default is 0.

This is the honest weakness relative to PgBouncer. max_pool = 4 and num_init_children = 32 do not give you 5000 clients over 25 backend connections. Pgpool-II’s pooling is a per-process cache, not a multiplexer, and pooling is not the reason to deploy it.

What actually gets load balanced

The balancing node is chosen once at session start and does not change for the life of the session unless statement_level_load_balance is on (default off). For long-lived application pools that matters: without it, one connection means one standby, forever.

A query is eligible for balancing only if all of the following hold:

• The mode is streaming replication, native replication, snapshot isolation, or logical replication, never raw.
• It is not SELECT INTO, not SELECT ... FOR UPDATE, not SELECT ... FOR SHARE.
• It starts with SELECT, or is COPY TO STDOUT, EXPLAIN, or EXPLAIN ANALYZE SELECT.
• It is not inside an explicit BEGIN ... END block (with exceptions, below).
• In streaming replication mode additionally: no temporary tables, no unlogged tables, no system catalogues. None of the three are replicated, so reading them from a standby is wrong.

The explicit-transaction rule is softer than it looks. Inside a BEGIN block, load balancing is still possible provided the isolation level is not SERIALIZABLE and no write query has been issued yet. A write query here means a non-SELECT DML or DDL statement. Once one appears, subsequent reads stop being balanced according to disable_load_balance_on_write.

That parameter is the real read-your-writes control:

Functions are the other thing that quietly disables balancing. If both write_function_list and read_only_function_list are empty (the recommended configuration), Pgpool-II checks the function’s volatility in the system catalogue and treats any VOLATILE function as a writing function, so a SELECT calling it is not balanced. The lookup costs one extra catalogue query the first time per function; the result is cached afterwards.

Two more routing controls worth knowing. backend_weightX sets the relative share of balanced queries each node receives. database_redirect_preference_list overrides per database:

database_redirect_preference_list = 'postgres:primary,mydb[01]:1(0.3),mydb2:standby'

That sends all SELECTs on postgres to the primary, 30% of SELECTs on mydb0/mydb1 to node 1, and all SELECTs on mydb2 to a randomly chosen standby. There are sibling parameters for user and application name; the precedence is app_name_redirect_preference_list > database_redirect_preference_list > user_redirect_preference_list.

Finally, replication lag. With delay_threshold (bytes) or delay_threshold_by_time (milliseconds, and it wins if greater than 0) set above 0, Pgpool-II stops sending SELECTs to a standby that has fallen behind and routes them to the primary instead, even with load_balance_mode on. Both default to 0, which means lag checking is off and a badly lagged standby will keep serving reads. Turn one of them on.

Health checks and failover

Health checking is disabled by default: health_check_period is 0. Without it, failover is driven only by errors on live sessions, which means a backend that dies while no client is connected is not noticed at all.

A runnable streaming-replication fragment:

backend_clustering_mode = 'streaming_replication'

listen_addresses = '*'
port = 9999
pcp_listen_addresses = '*'
pcp_port = 9898

num_init_children = 64
max_pool = 4
connection_cache = on
reserved_connections = 1

backend_hostname0 = '10.0.1.10'
backend_port0 = 5432
backend_weight0 = 1
backend_data_directory0 = '/var/lib/pgsql/17/data'
backend_flag0 = 'ALLOW_TO_FAILOVER'
backend_application_name0 = 'pg0'

backend_hostname1 = '10.0.1.11'
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/var/lib/pgsql/17/data'
backend_flag1 = 'ALLOW_TO_FAILOVER'
backend_application_name1 = 'pg1'

load_balance_mode = on
statement_level_load_balance = off
disable_load_balance_on_write = 'transaction'

sr_check_period = 10
sr_check_user = 'pgpool_check'
sr_check_database = 'postgres'
delay_threshold_by_time = 5000
prefer_lower_delay_standby = on

health_check_period = 10
health_check_timeout = 20
health_check_user = 'pgpool_check'
health_check_max_retries = 3
health_check_retry_delay = 2
failover_on_backend_error = off
failover_on_backend_shutdown = off

failover_command = '/etc/pgpool-II/failover.sh %d %h %p %D %m %H %M %P %r %R'
follow_primary_command = '/etc/pgpool-II/follow_primary.sh %d %h %p %D %m %H %M %P %r %R'

failover_on_backend_error and failover_on_backend_shutdown are both off here deliberately. SQLSTATE 57P01 is emitted not only on shutdown but whenever a backend is killed by pg_terminate_backend or SIGTERM, so leaving failover_on_backend_shutdown = on turns a routine session kill into a cluster failover. With retries enabled, health checking is the more trustworthy signal; the documentation also advises disabling failover_on_backend_error if you use health_check_max_retries.

failover_command is a script you write. Pgpool-II substitutes these before running it:

“Main node” is not “primary”. It means the live node with the smallest node ID, which in streaming replication mode is frequently a standby. If every standby is down and the primary fails, the command is invoked with %m = -1 and %H, %R, %r empty. Your script must handle that case, not promote whatever %H happens to contain.

A detached node never comes back on its own by default. Reattach with pcp_attach_node, or set auto_failback (4.1+). Confirm replication_state is streaming before you do. follow_primary_command runs only for primary failover, in a forked child so it does not block service, and typically calls pcp_recovery_node to rebuild each standby from the new primary.

Watchdog, the delegate IP, and split brain

One Pgpool-II is a single point of failure, so watchdog runs several and elects a leader. The leader holds delegate_ip, the virtual IP clients actually connect to, brought up and down with if_up_cmd and if_down_cmd (defaults use sudo ip addr add/del $_IP_$/24 dev eth0) and announced with a gratuitous ARP via arping_cmd. delegate_ip defaults to empty, which means no VIP is ever brought up.

You need at least three Pgpool-II nodes to avoid split brain. Quorum means a majority of watchdog nodes are live: three of five, two of three. The VIP will not be brought up without quorum.

failover_require_consensus is the setting that makes Pgpool-II’s HA defensible. With it on, a single node that mistakenly believes a backend is dead cannot fail it over; it must persuade a majority. What happens instead is quarantine: that node stops using the backend locally, does not propagate the state to its peers, and does not run failover_command. The consequence to internalise is that a quarantined primary is not replaced: the standby is never promoted, and that Pgpool-II node has no usable primary until the situation resolves. From 4.1 a leader that fails to build consensus for a primary failover resigns and lowers its wd_priority so a different node is elected. Quarantined nodes stay health-checked and reattach automatically once reachable (4.1+).

Because consensus requires votes, enable health checking on every Pgpool-II node. A node that cannot independently detect the failure cannot vote for it, and the failover stalls.

enable_consensus_with_half_votes only matters with an even number of nodes and the documentation is blunt about the risk: in a four-node cluster split into two pairs, both pairs have “quorum”, both elect a leader, and both raise the VIP. Leave it off. The related 4.3 dynamic-membership settings (wd_remove_shutdown_nodes, wd_lost_node_removal_timeout, wd_no_show_node_removal_timeout, all defaulting to off/0) shrink the cluster definition so quorum is computed against surviving members. That is useful, and it carries the same split-brain hazard. Defaults preserve pre-4.3 behaviour.

Set wd_authkey. Empty means watchdog authentication is disabled entirely, and watchdog packets control your VIP.

Diagnostics

SHOW POOL_NODES is the first command to run and usually the last one you need. It is answered by Pgpool-II itself over the Postgres protocol:

psql -h 10.0.1.100 -p 9999 -U app appdb -c 'SHOW POOL_NODES'

The status/pg_status and role/pg_role pairs exist precisely to expose disagreement between Pgpool-II’s model and reality. status = down with pg_status = up is normal after pcp_detach_node. role disagreeing with pg_role means Pgpool-II is routing writes to the wrong node. Pgpool-II scans backends in node-ID order and assumes the last non-standby it finds is the primary, so two primaries after a botched promotion produce exactly this. detach_false_primary (default off, streaming replication mode, PostgreSQL 9.6+) uses pg_stat_wal_receiver to detach the impostor automatically.

Empty replication_state and replication_sync_state usually mean configuration, not failure: sr_check_period must be non-zero, backend_application_nameX must match the application_name in that standby’s primary_conninfo, and sr_check_user must be a superuser or a member of pg_monitor.

Administration is out-of-band over the PCP protocol on port 9898, authenticated from pcp.conf with passwords optionally in ~/.pcppass (mode 0600 or it is ignored).

Note that PCP detach and attach deliberately bypass the quorum and consensus rules: they are operator intent, not detection. That is also why they are the escape hatch when quarantine has deadlocked a node.

What changed, and in which release

Four of these break on upgrade rather than merely adding something. The 4.6 user defaults turn a working 4.5 config into a startup error. The 4.7 recovery_database change breaks online recovery for anyone whose extension lives in template1 - set recovery_database = 'template1' or create the extension in postgres. The 4.7 watchdog bind change will break a cluster where hostname or heartbeat_hostname was set to an address the node does not actually own. And 4.5’s load balancing of prepared statements moves traffic that used to be guaranteed to hit the primary.

Where Pgpool-II is the wrong tool

If all you need is to stop PostgreSQL forking a backend per idle application connection, this is the wrong tool. Pgpool-II’s pooling is a per-child-process cache bounded by num_init_children * max_pool, not a multiplexer, and it does not deliver PgBouncer’s thousands-of-clients-over-tens-of-connections ratio. Use PgBouncer.

If you need failover but the application already knows about replicas (separate read and write DSNs, or a driver that handles target_session_attrs), then the query-routing half of Pgpool-II is dead weight that can only misroute. Patroni plus HAProxy gives you failover grounded in a real consensus store, without a SQL parser in the data path.

If your workload leans on session-scoped state, prepared statements built as plain SQL, or grammar newer than the PostgreSQL parser your Pgpool-II version imported, expect friction. Pgpool-II’s routing correctness depends on its parser understanding your SQL; 4.6 imported PostgreSQL 17’s parser and 4.7 imported 18’s, so running a newer PostgreSQL than your Pgpool-II was built against means statements it cannot parse fall back to being sent to the primary.

And Windows is not supported. Linux, FreeBSD and other UNIX-like systems only.