From f18dfc48356945d684fe74abaa7d5f5033b2af46 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Wed, 26 Sep 2007 22:36:30 +0000 Subject: Minor improvements in backup and recovery: - create a separate archive_mode GUC, on which archive_command is dependent - %r option in recovery.conf sends last restartpoint to recovery command - %r used in pg_standby, updated README - minor other code cleanup in pg_standby - doc on Warm Standby now mentions pg_standby and %r - log_restartpoints recovery option emits LOG message at each restartpoint - end of recovery now displays last transaction end time, as requested by Warren Little; also shown at each restartpoint - restart archiver if needed to carry away WAL files at shutdown Simon Riggs --- doc/src/sgml/backup.sgml | 63 ++++++++++++++++++++++++++++++++++++++-------- doc/src/sgml/config.sgml | 30 +++++++++++++++++++--- doc/src/sgml/perform.sgml | 20 ++++++++------- doc/src/sgml/ref/show.sgml | 4 +-- 4 files changed, 92 insertions(+), 25 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/backup.sgml b/doc/src/sgml/backup.sgml index 3040f3187dc..e34fc0f3a67 100644 --- a/doc/src/sgml/backup.sgml +++ b/doc/src/sgml/backup.sgml @@ -1,4 +1,4 @@ - + Backup and Restore @@ -518,10 +518,13 @@ tar -cf backup.tar /usr/local/pgsql/data - The shell command to use is specified by the configuration parameter, which in practice - will always be placed in the postgresql.conf file. - In this string, + To enable WAL archiving, set the configuration parameter to on, + and specify the shell command to use in the configuration parameter. In practice + these settings will always be placed in the + postgresql.conf file. + In archive_command, any %p is replaced by the path name of the file to archive, while any %f is replaced by the file name only. (The path name is relative to the working directory of the server, @@ -1030,8 +1033,15 @@ restore_command = 'cp /mnt/server/archivedir/%f %p' it to on the server. (The path name is relative to the working directory of the server, i.e., the cluster's data directory.) + Any %r is replaced by the name of the file containing the + last valid restartpoint. 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 + restart of the current restore. %r would only be used in a + warm-standby configuration (see ). Write %% to embed an actual % character - in the command. + in the command. It is important for the command to return a zero exit status if and @@ -1118,6 +1128,20 @@ restore_command = 'copy /mnt/server/archivedir/%f "%p"' # Windows + + log_restartpoints + (boolean) + + + + Specifies whether to log each restart point as it occurs. This + can be helpful to track the progress of a long recovery. + Default is false. + + + + @@ -1389,7 +1413,8 @@ restore_command = 'copy /mnt/server/archivedir/%f "%p"' # Windows contact between the two database servers is the archive of WAL files that both share: primary writing to the archive, standby reading from the archive. Care must be taken to ensure that WAL archives for separate - primary servers do not become mixed together or confused. + primary servers do not become mixed together or confused. The archive + need not be large, if it is only required for the standby operation. @@ -1426,6 +1451,12 @@ if (!triggered) + + A working example of a waiting restore_command is provided + as a contrib module, named pg_standby. This can be + extended as needed to support specific configurations or environments. + + PostgreSQL does not provide the system software required to identify a failure on the primary and notify @@ -1449,6 +1480,15 @@ if (!triggered) as the explicit creation of a trigger file is less error prone, if this can be arranged. + + + The size of the WAL archive can be minimized by using the %r + option of the restore_command. This option specifies the + last archive filename that needs to be kept to allow the recovery to + restart correctly. This can be used to truncate the archive once + files are no longer required, if the archive is writable from the + standby server. + @@ -1468,8 +1508,10 @@ if (!triggered) Set up continuous archiving from the primary to a WAL archive located - in a directory on the standby server. Ensure that and + in a directory on the standby server. Ensure that + , + and + are set appropriately on the primary (see ). @@ -1631,7 +1673,8 @@ if (!triggered) far back you need to keep WAL segment files to have a recoverable backup. You can do this by running pg_controldata on the standby server to inspect the control file and determine the - current checkpoint WAL location. + current checkpoint WAL location, or by using the + log_restartpoints option to print values to the server log. diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml index 3cc911b265b..3d7dddc1ace 100644 --- a/doc/src/sgml/config.sgml +++ b/doc/src/sgml/config.sgml @@ -1,4 +1,4 @@ - + Server Configuration @@ -1620,6 +1620,24 @@ SET ENABLE_SEQSCAN TO OFF; Archiving + + archive_mode (boolean) + + archive_mode configuration parameter + + + + When archive_mode is enabled, completed WAL segments + can be sent to archive storage by setting + . + archive_mode and archive_command are + separate variables so that archive_command can be + changed without leaving archiving mode. + This parameter can only be set at server start. + + + + archive_command (string) @@ -1628,8 +1646,7 @@ SET ENABLE_SEQSCAN TO OFF; The shell command to execute to archive a completed segment of - the WAL file series. If this is an empty string (the default), - WAL archiving is disabled. Any %p in the string is + the WAL file series. Any %p in the string is replaced by the path name of the file to archive, and any %f is replaced by the file name only. (The path name is relative to the working directory of the server, @@ -1638,7 +1655,12 @@ SET ENABLE_SEQSCAN TO OFF; command. For more information see . This parameter can only be set in the postgresql.conf - file or on the server command line. + file or on the server command line. It is ignored unless + archive_mode was enabled at server start. + If archive_command is an empty string (the default) while + archive_mode is enabled, then WAL archiving is temporarily + disabled, but the server continues to accumulate WAL segment files in + the expectation that a command will soon be provided. It is important for the command to return a zero exit status if diff --git a/doc/src/sgml/perform.sgml b/doc/src/sgml/perform.sgml index dcd0d1d2d33..df90f37438b 100644 --- a/doc/src/sgml/perform.sgml +++ b/doc/src/sgml/perform.sgml @@ -1,4 +1,4 @@ - + Performance Tips @@ -819,7 +819,7 @@ SELECT * FROM x, y, a, b, c WHERE something AND somethingelse; needs to be written, because in case of an error, the files containing the newly loaded data will be removed anyway. However, this consideration does not apply when - is set, as all commands + is set, as all commands must write WAL in that case. @@ -892,22 +892,24 @@ SELECT * FROM x, y, a, b, c WHERE something AND somethingelse; - Turn off <varname>archive_command</varname> + Turn off <varname>archive_mode</varname> When loading large amounts of data into an installation that uses - WAL archiving, you might want to disable archiving (unset the - configuration variable) + WAL archiving, you might want to disable archiving (turn off the + configuration variable) while loading. It might be faster to take a new base backup after the load has completed than to process a large amount of incremental WAL data. + But note that turning archive_mode on or off + requires a server restart. Aside from avoiding the time for the archiver to process the WAL data, doing this will actually make certain commands faster, because they - are designed not to write WAL at all if archive_command - is unset. (They can guarantee crash safety more cheaply by doing an + are designed not to write WAL at all if archive_mode + is off. (They can guarantee crash safety more cheaply by doing an fsync at the end than by writing WAL.) This applies to the following commands: @@ -989,8 +991,8 @@ SELECT * FROM x, y, a, b, c WHERE something AND somethingelse; If using WAL archiving, consider disabling it during the restore. - To do that, unset archive_command before loading the - dump script, and afterwards restore archive_command + To do that, turn off archive_mode before loading the + dump script, and afterwards turn it back on and take a fresh base backup. diff --git a/doc/src/sgml/ref/show.sgml b/doc/src/sgml/ref/show.sgml index ab64f859428..14ddd6744f8 100644 --- a/doc/src/sgml/ref/show.sgml +++ b/doc/src/sgml/ref/show.sgml @@ -1,5 +1,5 @@ @@ -171,7 +171,7 @@ SHOW ALL; name | setting | description --------------------------------+--------------------------------+---------------------------------------------------------------------------------------------- add_missing_from | off | Automatically adds missing table references to FROM clauses. - archive_command | unset | WAL archiving command. + allow_system_table_mods | off | Allows modifications of the structure of system tables. . . . -- cgit v1.2.3