3.7. Temporarily Shutting Down PostgreSQL

Sometimes you want to temporarily stop or restart PostgreSQL to maintain or version up it. In this section how to perform the task with minimum downtime.

3.7.1. Using pcp_detach_node command

If you stop PostgreSQL by using pg_ctl, failover will not happen until Pgpool-II detects it by the health check depending on the health check settings and it will take sometime to detach PostgreSQL. Especially if Watchdog is enabled and failover_require_consensus is on, Pgpool-II will not start failover until more than half of watchdog nodes agree that PostgreSQL is stopped. If you detach the node by using pcp_detach_node, failover will immediately start regardless the settings of health check. Please note that the detached PostgreSQL node is not actually stopped and if necessary, you need to manually stop it.

3.7.2. Using backend_flag

Stopping or restarting PostgreSQL causes failover. If the running mode is not streaming replication mode, or the server is a standby server in streaming replication mode, probably that's not a big deal because clients can always use other servers in the cluster. However if the server is primary server, it will cause change of primary server by promoting one of the standby servers. Moreover if there's only one server remains in the cluster, there are no alternative server or standby server which can be promoted.

In this case you can use backend_flag to avoid failover. By setting below in pgpool.conf will avoid failover of backend0.

backend_flag0 = DISALLOW_TO_FAILOVER

This will take effect by reloading or restarting Pgpool-II. If this flag is set, failover will not happen if the backend is not available. While the backend is not available, clients will get error message:

psql: error: could not connect to server: FATAL:  failed to create a backend connection
DETAIL:  executing failover on backend

After restarting the backend, clients can connect as usual. To allow failover on the backend again, you can set:

backend_flag0 = ALLOW_TO_FAILOVER

and reload or restart Pgpool-II.