Navigation :

Online Recovery

pgpool-II 3.6.7 Documentation
Prev Up Chapter 5. Server Configuration Next

Pgpool-II can synchronize database nodes and attach a node without stopping the service. This feature is called "online recovery" .

For online recovery, the recovery target node must be detached from Pgpool-II . If you wish to add a PostgreSQL server node dynamically, reload the pgpool.conf after adding the backend_hostname and its associated parameters. This will register the new server to Pgpool-II as a detached backend node.

Note: The recovery target PostgreSQL server must not be running for performing the online recovery. If the target PostgreSQL server has already started, you must shut it down before starting the online recovery.

Online recovery is performed in two phases. Connections from cliens are not allowd only in second phase of online recovery while the data can be updated or retrieved during the first phase. Pgpool-II performs the follows steps in online recovery:

  • CHECKPOINT.

  • First stage of online recovery.

  • Wait until all client connections have disconnected.

  • CHECKPOINT.

  • Second stage of online recovery.

  • Start up postmaster (perform pgpool_remote_start )

  • Node attach

Note: There is a restriction in the online recovery. If Pgpool-II itself is installed on multiple hosts, online recovery does not work correctly, because Pgpool-II has to stop all the clients during the 2nd stage of online recovery. If there are several Pgpool-II hosts, only one of them will have received the online recovery command and will block the connections from clients.

recovery_user ( string )

Specifies the PostgreSQL user name to perform online recovery.

This parameter can be changed by reloading the Pgpool-II configurations.

recovery_password ( string )

Specifies the password for the PostgreSQL user name configured in recovery_user to perform online recovery.

This parameter can be changed by reloading the Pgpool-II configurations.

recovery_1st_stage_command ( string )

Specifies a command to be run by master(primary) node at the first stage of online recovery. The command file must be placed in the database cluster directory for security reasons. For example, if recovery_1st_stage_command = 'sync-command' , then Pgpool-II will look for the command scrit in $PGDATA directory and will try to execute $PGDATA/sync-command .

recovery_1st_stage_command receives following 4 parameters:

  • Path to the database cluster of the master(primary) node.

  • Hostname of the backend node to be recovered.

  • Path to the database cluster of the node to be recovered.

  • Port number of the master(primary) node.

Note: Pgpool-II accept connections and queries while recovery_1st_stage command is executed, so you can retrieve and update data.

Caution

recovery_1st_stage command runs as a SQL command from PostgreSQL's point of view. So recovery_1st_stage command can get prematuraly killed by PostgreSQL if the PostgreSQL's statement_time_out is configured with the value that is smaller than the time recovery_1st_stage_command takes for completion.

Typical error in such case is 

rsync used in the command is killed by signal 2 for example.

This parameter can be changed by reloading the Pgpool-II configurations.

recovery_2nd_stage_command ( string )

Specifies a command to be run by master(primary) node at the second stage of online recovery. The command file must be placed in the database cluster directory for security reasons. For example, if recovery_2nd_stage_command = 'sync-command' , then Pgpool-II will look for the command scrit in $PGDATA directory and will try to execute $PGDATA/sync-command .

recovery_2nd_stage_command receives following 4 parameters:

  • Path to the database cluster of the master(primary) node.

  • Hostname of the backend node to be recovered.

  • Path to the database cluster of the node to be recovered.

  • Port number of database to be recovered.

Note: Pgpool-II does not accept client connections and queries during the execution of recovery_2nd_stage_command command, and waits for the existing clients to close their connections before executing the command. Therefore, the recovery_2nd_stage_command may not execute if the client stays connected for a long time.

Caution

recovery_2nd_stage command runs as a SQL command from PostgreSQL's point of view. Therefore, recovery_2nd_stage command can get prematuraly killed by PostgreSQL if the PostgreSQL's statement_time_out is configured with the value that is smaller than the time recovery_2nd_stage_command takes for completion.

This parameter can be changed by reloading the Pgpool-II configurations.

recovery_timeout ( integer )

Specifies the timeout in seconds to cancel the online recovery if it does not completes within this time. Since Pgpool-II does not accepts the connections during the second stage of online recovery, this parameter can be used to cancel the online recovery to manage the service down time during the online recovery.

This parameter can be changed by reloading the Pgpool-II configurations.

client_idle_limit_in_recovery ( integer )

Specifies the time in seconds to disconnect a client if it remains idle since the last query during the online recovery. client_idle_limit_in_recovery is similar to the client_idle_limit but only takes effect during the second stage of online recovery.

This is useful for preventing the Pgpool-II recovery from being disturbed by the lazy clients or if the TCP/IP connection between the client and Pgpool-II is accidentally down (a cut cable for instance).

If set to -1, all clients get immediately disconnected when the second stage of online recovery starts. The default is 0, which turns off the feature.

This parameter can be changed by reloading the Pgpool-II configurations. You can also use PGPOOL SET command to alter the value of this parameter for a current session.