configuration of recovery of a standby server This chapter describes the settings available in the recovery.confrecovery.conf file. They apply only for the duration of the recovery. They must be reset for any subsequent recovery you wish to perform. They cannot be changed once recovery has begun. Settings in recovery.conf are specified in the format name = 'value'. One parameter is specified per line. Hash marks (#) designate the rest of the line as a comment. To embed a single quote in a parameter value, write two quotes (''). A sample file, share/recovery.conf.sample, is provided in the installation's share/ directory. restore_command (string) restore_command recovery parameter The local shell command to execute to retrieve an archived segment of the WAL file series. This parameter is required for archive recovery, but optional for streaming replication. Any %f in the string is replaced by the name of the file to retrieve from the archive, and any %p is replaced by the copy destination path name on the server. (The path name is relative to the current working directory, i.e., the cluster's data directory.) Any %r is replaced by the name of the file containing the last valid restart point. That is the earliest file that must be kept to allow a restore to be restartable, so this information can be used to truncate the archive to just the minimum required to support restarting from the current restore. %r is typically only used by warm-standby configurations (see ). Write %% to embed an actual % character. It is important for the command to return a zero exit status only if it succeeds. The command will be asked for file names that are not present in the archive; it must return nonzero when so asked. Examples: restore_command = 'cp /mnt/server/archivedir/%f "%p"' restore_command = 'copy "C:\\server\\archivedir\\%f" "%p"' # Windows An exception is that if the command was terminated by a signal (other than SIGTERM, which is used as part of a database server shutdown) or an error by the shell (such as command not found), then recovery will abort and the server will not start up. archive_cleanup_command (string) archive_cleanup_command recovery parameter This optional parameter specifies a shell command that will be executed at every restartpoint. The purpose of archive_cleanup_command is to provide a mechanism for cleaning up old archived WAL files that are no longer needed by the standby server. Any %r is replaced by the name of the file containing the last valid restart point. That is the earliest file that must be kept to allow a restore to be restartable, and so all files earlier than %r may be safely removed. This information can be used to truncate the archive to just the minimum required to support restart from the current restore. The module is often used in archive_cleanup_command for single-standby configurations, for example: archive_cleanup_command = 'pg_archivecleanup /mnt/server/archivedir %r' Note however that if multiple standby servers are restoring from the same archive directory, you will need to ensure that you do not delete WAL files until they are no longer needed by any of the servers. archive_cleanup_command would typically be used in a warm-standby configuration (see ). Write %% to embed an actual % character in the command. If the command returns a nonzero exit status then a warning log message will be written. An exception is that if the command was terminated by a signal or an error by the shell (such as command not found), a fatal error will be raised. recovery_end_command (string) recovery_end_command recovery parameter This parameter specifies a shell command that will be executed once only at the end of recovery. This parameter is optional. The purpose of the recovery_end_command is to provide a mechanism for cleanup following replication or recovery. Any %r is replaced by the name of the file containing the last valid restart point, like in . If the command returns a nonzero exit status then a warning log message will be written and the database will proceed to start up anyway. An exception is that if the command was terminated by a signal or an error by the shell (such as command not found), the database will not proceed with startup. By default, recovery will recover to the end of the WAL log. The following parameters can be used to specify an earlier stopping point. At most one of recovery_target, recovery_target_name, recovery_target_time, or recovery_target_xid can be specified. recovery_target = 'immediate' recovery_target recovery parameter This parameter specifies that recovery should end as soon as a consistent state is reached, i.e. as early as possible. When restoring from an online backup, this means the point where taking the backup ended. Technically, this is a string parameter, but 'immediate' is currently the only allowed value. recovery_target_name (string) recovery_target_name recovery parameter This parameter specifies the named restore point, created with pg_create_restore_point() to which recovery will proceed. recovery_target_time (timestamp) recovery_target_time recovery parameter This parameter specifies the time stamp up to which recovery will proceed. The precise stopping point is also influenced by . recovery_target_xid (string) recovery_target_xid recovery parameter This parameter specifies the transaction ID up to which recovery will proceed. Keep in mind that while transaction IDs are assigned sequentially at transaction start, transactions can complete in a different numeric order. The transactions that will be recovered are those that committed before (and optionally including) the specified one. The precise stopping point is also influenced by . The following options further specify the recovery target, and affect what happens when the target is reached: recovery_target_inclusive (boolean) recovery_target_inclusive recovery parameter Specifies whether we stop just after the specified recovery target (true), or just before the recovery target (false). Applies to both and , whichever one is specified for this recovery. This indicates whether transactions having exactly the target commit time or ID, respectively, will be included in the recovery. Default is true. recovery_target_timeline (string) recovery_target_timeline recovery parameter Specifies recovering into a particular timeline. The default is to recover along the same timeline that was current when the base backup was taken. Setting this to latest recovers to the latest timeline found in the archive, which is useful in a standby server. Other than that you only need to set this parameter in complex re-recovery situations, where you need to return to a state that itself was reached after a point-in-time recovery. See for discussion. pause_at_recovery_target (boolean) pause_at_recovery_target recovery parameter Specifies whether recovery should pause when the recovery target is reached. The default is true. This is intended to allow queries to be executed against the database to check if this recovery target is the most desirable point for recovery. The paused state can be resumed by using pg_xlog_replay_resume() (See ), which then causes recovery to end. If this recovery target is not the desired stopping point, then shutdown the server, change the recovery target settings to a later target and restart to continue recovery. This setting has no effect if is not enabled, or if no recovery target is set. standby_mode (boolean) standby_mode recovery parameter Specifies whether to start the PostgreSQL server as a standby. If this parameter is on, the server will not stop recovery when the end of archived WAL is reached, but will keep trying to continue recovery by fetching new WAL segments using restore_command and/or by connecting to the primary server as specified by the primary_conninfo setting. primary_conninfo (string) primary_conninfo recovery parameter Specifies a connection string to be used for the standby server to connect with the primary. This string is in the format described in . If any option is unspecified in this string, then the corresponding environment variable (see ) is checked. If the environment variable is not set either, then defaults are used. The connection string should specify the host name (or address) of the primary server, as well as the port number if it is not the same as the standby server's default. Also specify a user name corresponding to a suitably-privileged role on the primary (see ). A password needs to be provided too, if the primary demands password authentication. It can be provided in the primary_conninfo string, or in a separate ~/.pgpass file on the standby server (use replication as the database name). Do not specify a database name in the primary_conninfo string. This setting has no effect if standby_mode is off. primary_slot_name (string) primary_slot_name recovery parameter Optionally specifies an existing replication slot to be used when connecting to the primary via streaming replication to control resource removal on the upstream node (see ). This setting has no effect if primary_conninfo is not set. trigger_file (string) trigger_file recovery parameter Specifies a trigger file whose presence ends recovery in the standby. Even if this value is not set, you can still promote the standby using pg_ctl promote. This setting has no effect if standby_mode is off. recovery_min_apply_delay (integer) recovery_min_apply_delay recovery parameter By default, a standby server restores WAL records from the primary as soon as possible. It may be useful to have a time-delayed copy of the data, offering various options to correct data loss errors. This parameter allows you to delay recovery by a fixed period of time, specified in milliseconds if no unit is specified. For example, if you set this parameter to 5min, the standby will replay each transaction commit only when the system time on the standby is at least five minutes past the commit time reported by the master. It is possible that the replication delay between servers exceeds the value of this parameter, in which case no delay is added. Note that the delay is calculated between the WAL timestamp as written on master and the time on the current standby. Delays in transfer because of networks or cascading replication configurations may reduce the actual wait time significantly. If the system clocks on master and standby are not synchronised, this may lead to recovery applying records earlier than expected; but that is not a major issue because useful settings of the parameter are much larger than typical time deviations between servers. Be careful to allow for different timezone settings on master and standby. The delay occurs only on WAL records for COMMIT and Restore Points. Other records may be replayed earlier than the specified delay, which is not an issue for MVCC though it may potentially increase the number of recovery conflicts generated. The delay occurs until the standby is promoted or triggered. After that the standby will end recovery without further waiting. This parameter is intended for use with streaming replication deployments, however, if the parameter is specified it will be honoured in all cases. Synchronous replication is not affected by this setting because there is not yet any setting to request synchronous apply of transaction commits. hot_standby_feedback will be delayed by use of this feature which could lead to bloat on the master; use both together with care.