Michael Paquier - PostgreSQL committer

  • Home
  • Manuals
  • Presentations
  • Projects
  • Resume
  • Tags
  • About

Postgres 9.5 feature highlight - standby actions at the end of recovery

You liked this post or you have a comment? Contact information is available here and sponsoring is here.
04 Dec 2014
Tags: postgres, postgresql, 9.5, replication, wal

PostgreSQL offers many ways to define how a node reaches the end of recovery with many parameters related to [recovery target] (https://www.postgresql.org/docs/devel/static/recovery-target-settings.html), like a timestamp, a transaction ID, a target name and recovery timeline, using recovery_target_timeline. A parameter called pause_at_recovery_target that exists since 9.1 allows as well to put a standby in pause when the recovery target is reached. The upcoming release 9.5 has made some improvements in this area with the following commit:

commit: aedccb1f6fef988af1d1a25b78151f3773954b4c
author: Simon Riggs <simon@2ndQuadrant.com>
date: Tue, 25 Nov 2014 20:13:30 +0000
action_at_recovery_target recovery config option

action_at_recovery_target = pause | promote | shutdown

Petr Jelinek

Reviewed by Muhammad Asif Naeem, Fujji Masao and
Simon Riggs

Its use is rather simple, when a standby has hot_standby enabled in postgresql.conf, meaning that it is able to execute read queries while being in recovery, it is possible to perform the set of actions defined above using recovery_target_action in recovery.conf. Note that the former parameter name was action_at_recovery_target, it has been renamed to recovery_target_action afterwards in commit [b8e33a8] (https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=b8e33a):

  • pause, acting the same as when pause_at_recovery_target is enabled to have the standby pause any replay actions so as it is possible to check in which state it is once the recovery target has been reached. Note as well that the recovery replay can be resumed using pg_xlog_replay_resume().
  • promote, to perform automatically a promotion of the node and have it just to the next timeline, making it available for write queries as well. This is the same as when pause_at_recovery_target or recovery_target_action are not used, or when only pause_at_recovery_target is used and set to false.
  • shutdown, to simply shutdown the standby once target is reached. This is the real new addition that this feature brings in because this can be used to make a standby instance immediately ready for use after it has finished its recovery. Note that in this case the node will also need to re-apply all the WAL since last checkpoint, so there is some cost in this mode. Moreover, recovery.conf is not renamed to recovery.done automatically. So this setting need to be removed in recovery.conf (simply removing the file is not something to recommend as server would miss post-end-recovery sanity checks).

Now let’s put in recovery a standby that has the following parameters in recovery.conf:

recovery_target_time = '2014-12-04 22:21:52.922328'
recovery_target_action = 'shutdown'
restore_command = 'cp -i /path/to/archive/%f %p'

When a recovery target is reached (timestamp, XID, name), the following logs will show up if shutdown is set up for the end of recovery.

LOG:  recovery stopping before commit of transaction 1004, time 2014-12-04 22:22:19.554052+09
LOG:  shutdown at recovery target
LOG:  shutting down 

Also, note that both parameters cannot be used at the same time. An error being returned by server as follows as pause_at_recovery_target is logically deprecated (note that this old parameter may be removed in a couple of months altogether).

FATAL:  cannot set both "pause_at_recovery_target" and
        "recovery_target_action" recovery parameters
HINT:  The "pause_at_recovery_target" is deprecated.

That’s nice stuff, useful for the control of nodes to-be-promoted when checking for their data consistency.

Search

Social

Github

RSS Feeds

Main

Postgres

Sponsor

Unless otherwise specified, the contents of this website are (C)Copyright Michael Paquier 2010-2025 and are licensed for use under CC BY-NC-ND 4.0.