Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1Zg0hN-0002L1-Ha for pgsql-docs@arkaria.postgresql.org; Sun, 27 Sep 2015 01:24:17 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84) (envelope-from ) id 1Zg0hM-0007sO-R3 for pgsql-docs@arkaria.postgresql.org; Sun, 27 Sep 2015 01:24:16 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84) (envelope-from ) id 1Zg0hL-0007rQ-4m for pgsql-docs@postgresql.org; Sun, 27 Sep 2015 01:24:15 +0000 Received: from mout.gmx.com ([74.208.4.200]) by makus.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.84) (envelope-from ) id 1Zg0hH-0003xm-TO for pgsql-docs@postgresql.org; Sun, 27 Sep 2015 01:24:13 +0000 Received: from anaphora.localdomain ([5.102.218.194]) by mail.gmx.com (mrgmxus001) with ESMTPSA (Nemesis) id 0Ma1dZ-1Zxoxi0NOZ-00LpUT for ; Sun, 27 Sep 2015 03:24:09 +0200 Subject: Re: Updated docs on base backups To: pgsql-docs@postgresql.org References: <56065F0E.3080808@mail.com> From: Amir Rohan Message-ID: <56074536.9080101@mail.com> Date: Sun, 27 Sep 2015 04:24:06 +0300 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:38.0) Gecko/20100101 Thunderbird/38.2.0 MIME-Version: 1.0 In-Reply-To: <56065F0E.3080808@mail.com> Content-Type: multipart/mixed; boundary="------------020100020201090206040700" X-Provags-ID: V03:K0:lcedLesHAzgYYh8t6oSvRvqW2B83NP4NwXeWPBoFX2Z7hWBmi3F SEKSAlBQbaC+5ZdlJaVeoSoCFqxTZeVOF7E18U/dLLp38HFdZqPwkp8BQ7KBcaXg44Cqvhe gQO4zjeUP5xlco+RGtCPBmcdiXufqvdzGfZNSguEMnK4jCQyPZt+wfuanqz82NTCSuGRWog +H8X8erTWXlUuGqW2HOzA== X-UI-Out-Filterresults: notjunk:1;V01:K0:Y98sqjIVyuA=:cUnF4x6WLgYItxJjTxyXDT KhGpVkbmwIftp9aKnO+A/hbD7yXVm9law9pYaqe+gmQr4O4eAukjCGv/gjymQJISOoMEElKcC KYTLdJd07w4/amzsUfqvlo/OWsjCulIN3kAurWsbDF479+OU/mu1mXH5BRZ7JiaehEGTWhnDy KMKdxPpFzSMZk7YKqp1RQ6me+O84lqGd/nGa4NHZqZy76XFk4Lj+EY/43KYwQMiFg/4uc/Tp3 rXhEVXAnQp2W/5PqG4Ql1dRLlEDuYcVNIA19BH/I4R7r6HCpSy9Hl/m+g+lLLIn4S3JicwvZR 6n3U2NaSEIz9M6H7wzxgJGtBwyA/ynCOcXwG0PglPmE3SVu4wOG4aGdmIHjv1JpwI3Cc/6bfy 4ZzUSMDZz8BYDqwTNppPlki6OP8x62WRaQ9Vkieeq1D2kM0H/CW6mowQx1MH2KUnaDdfj4rBg w/gRRBBXiLZoi0c0sknOr0YZxI75xuYLfYCDPfC50BGerw2xeS14k4HV8DSFqC20ZFcRGg0Xn NZ6rJ5vixjJz90B4jlFFULjIM+r7WUVx+JKdNqMv/wgolMkHPXEKVvFZut6F5zJKo27w8tsV7 5FfORg9v+fXcJrW6goHfCtPb7Egk52hSVX4cW0P7MVQGLT7dpekcG44eNeoykQvDy08hadFFW jY0eyLirJjQa7LJfeHsWakixokBxe9/xLQtxTN0YS81LSx+6luQUaue1lSj1kR1YMJ8EKqkyM OkxwMAuqqTBxcZr2 X-Pg-Spam-Score: -2.6 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-docs Precedence: bulk Sender: pgsql-docs-owner@postgresql.org This is a multi-part message in MIME format. --------------020100020201090206040700 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: 7bit On 09/26/2015 12:02 PM, Amir Rohan wrote: > Hi all, > > See attached changed to current docs on subject. They have clearly > been reworked by multiple people piecemeal and had many issues > which made less than a joy to read, in fact they were damn frustrating > to read. Updated version, with missing step for file system level backup revised and added back. Amir --------------020100020201090206040700 Content-Type: text/x-patch; name="20150827_base_backups_docs_amir_v2.patch" Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename="20150827_base_backups_docs_amir_v2.patch" From fcae4deeda20621b04c8ca33bb5269ba40251b77 Mon Sep 17 00:00:00 2001 From: root Date: Sat, 26 Sep 2015 11:50:43 +0300 Subject: [PATCH] Rewritten documentation on base backups V2 diff --git a/doc/src/sgml/backup.sgml b/doc/src/sgml/backup.sgml index 7413666..5b6d71c 100644 --- a/doc/src/sgml/backup.sgml +++ b/doc/src/sgml/backup.sgml @@ -427,8 +427,8 @@ tar -cf backup.tar /usr/local/pgsql/data If simultaneous snapshots are not possible, one option is to shut down the database server long enough to establish all the frozen snapshots. - Another option is to perform a continuous archiving base backup () because such backups are immune to file + Another option is to create a base backup using the continuous archiving feature + () because such backups are immune to file system changes during the backup. This requires enabling continuous archiving just during the backup process; restore is done using continuous archive recovery (). @@ -752,60 +752,65 @@ test ! -f /mnt/server/archivedir/00000001000000A900000065 && cp pg_xlog/ Making a Base Backup - The easiest way to perform a base backup is to use the - tool. It can create - a base backup either as regular files or as a tar archive. If more - flexibility than can provide is - required, you can also make a base backup using the low level API - (see ). + A base backup consists of one or more WAL files and a small textual + file containing associated metadata. Together with a file system + level backup, a base backup is all that's required to recreate the + database's state at some point in the past. Once a base backup is made, + the WAL files that precede its creation are no longer necessary in order + to recover the database to some later point in time. + + + + The interval between base backups should usually be + chosen based on how much storage you want to expend on archived WAL + files, since you must keep all the archived WAL files back to your + last base backup. + You should also consider how long you are prepared to spend + recovering, if recovery should be necessary — the system will have to + replay all those WAL segments, and that could take awhile if it has + been a long time since the last base backup. - It is not necessary to be concerned about the amount of time it takes - to make a base backup. However, if you normally run the - server with full_page_writes disabled, you might notice a drop - in performance while the backup runs since full_page_writes is - effectively forced on during backup mode. + Creating a base backup may be a lengthy process if you have a lots of data. + Be aware that If you normally run the server with full_page_writes + disabled, you might notice a drop in performance while the backup runs since + full_page_writes is effectively forced on during backup mode. + To make use of the backup, you will need to keep all the WAL segment files generated during and after the file system backup. - To aid you in doing this, the base backup process - creates a backup history file that is immediately - stored into the WAL archive area. This file is named after the first - WAL segment file that you need for the file system backup. - For example, if the starting WAL file is - 0000000100001234000055CD the backup history file will be - named something like - 0000000100001234000055CD.007C9330.backup. (The second - part of the file name stands for an exact position within the WAL - file, and can ordinarily be ignored.) Once you have safely archived - the file system backup and the WAL segment files used during the - backup (as specified in the backup history file), all archived WAL - segments with names numerically less are no longer needed to recover - the file system backup and can be deleted. However, you should - consider keeping several backup sets to be absolutely certain that - you can recover your data. + To aid you in doing this, the base backup process creates a + a text file, termed a backup history file, which details + the range of WAL files making up the base backup, together with other + useful information such as the date of the backup, and the text label + associated with the backup which you provide when initiating the backup. - The backup history file is just a small text file. It contains the - label string you gave to , as well as - the starting and ending times and WAL segments of the backup. - If you used the label to identify the associated dump file, - then the archived history file is enough to tell you which dump file to - restore. + The location of this file depends on the method used to perform the backup. + The simplest way to perform a base backup is by using , which uses the postgres's replication mechanism to + connect to a running database and create a backup archive or directory. When + using pg_basebackup, the backup history file is named + backup_label and can be found the root of the backup + archive/directory created by pg_basebackup. Note that, because pg_basebackup + creates a single archive (or directory) to hold the backup, the + backup_label file lists only the backup's first WAL file, and + does not list the last WAL in the series. Ass long as you provide the + -x switch to pg_basebackup, it will fetch a copy of all the + required WAL files in the backup and save them in the created + archive/directory. It is recommended that you use the -l switch to + set a label for the backup. This will become part of the backup history file, + for future reference. - Since you have to keep around all the archived WAL files back to your - last base backup, the interval between base backups should usually be - chosen based on how much storage you want to expend on archived WAL - files. You should also consider how long you are prepared to spend - recovering, if recovery should be necessary — the system will have to - replay all those WAL segments, and that could take awhile if it has - been a long time since the last base backup. + If more flexibility than can provide is + required, you can also make a base backup using the low level API + (see ). @@ -833,82 +838,118 @@ SELECT pg_start_backup('label'); where label is any string you want to use to uniquely identify this backup operation. (One good practice is to use the full path where you intend to put the backup dump file.) - pg_start_backup creates a backup label file, - called backup_label, in the cluster directory with - information about your backup, including the start time and label - string. The function also creates a tablespace map file, - called tablespace_map, in the cluster directory with + After you initiate pg_start_backup, The backup history file + (See previous section) describing your backup will be created in the root + of your cluster directory with the name backup_label . + This file will be moved to the archive directory under a new name + when the backup ends (if archiving is not enabled, it is simply deleted then). + pg_start_backup also creates a tablespace map file, + called tablespace_map, in the cluster directory, with information about tablespace symbolic links in pg_tblspc/ if one or more such link is present. Both files are critical to the integrity of the backup, should you need to restore from it. - It does not matter which database within the cluster you connect to to - issue this command. You can ignore the result returned by the function; - but if it reports an error, deal with that before proceeding. + It does not matter which database within the cluster you are connected + to when you issue this command, the base backup always backs up an entire + server. + If pg_start_backup reports an error, you should resolve the + issue before proceeding. The result returned by the function is + an identifier for the first WAL file in the base backup. You can + use the pg_xlogfile_name function to get the filename + for the actual WAL file it identifies. - By default, pg_start_backup can take a long time to finish. - This is because it performs a checkpoint, and the I/O - required for the checkpoint will be spread out over a significant - period of time, by default half your inter-checkpoint interval + pg_start_backup performs a checkpoint in preparation + for the backup, spreading the I/O involved over a a period + of time to minimize the impact on queries during the backup process. + By default, this period is set at half your inter-checkpoint interval (see the configuration parameter - ). This is - usually what you want, because it minimizes the impact on query - processing. If you want to start the backup as soon as - possible, use: + ). + This can make the backup process lengthy, but is usually desirable + because it minimizes the impact on query processing. + If you prefer to start the backup as soon as possible, with + possible a larger impact on your server's performance during + the backup, use: SELECT pg_start_backup('label', true); - This forces the checkpoint to be done as quickly as possible. + Which forces the checkpoint to be done as quickly as possible. - Perform the backup, using any convenient file-system-backup tool + Perform file system backup of your cluster directory, + see . + You can use any convenient file-system-backup tool such as tar or cpio (not pg_dump or - pg_dumpall). It is neither - necessary nor desirable to stop normal operation of the database - while you do this. - - + pg_dumpall). Note that, when used in conjunction + with a base backup, it is neither necessary nor desirable to stop normal + operation of the database while you create the file system backup. + + - Again connect to the database as a superuser, and issue the command: + Once pg_start_backup finished, connect to the database + as a superuser again, and and issue the command: SELECT pg_stop_backup(); - This terminates the backup mode and performs an automatic switch to - the next WAL segment. The reason for the switch is to arrange for - the last WAL segment file written during the backup interval to be - ready to archive. + This completes the backup and forces a switch to the the next WAL segment. + The reason for the switch is so that the last WAL segment file written to + during the backup interval is released and becomes the last WAL in the + sequence comprising the base backup, which can now be backed up. + Again, the result return is an identifier for a WAL file, this time the + last in the sequence making up the base backup, and again you can use the + pg_xlogfile_name function to get the filename for the actual + WAL file it identifies. - Once the WAL segment files active during the backup are archived, you are - done. The file identified by pg_stop_backup's result is - the last segment that is required to form a complete set of backup files. - If archive_mode is enabled, - pg_stop_backup does not return until the last segment has - been archived. - Archiving of these files happens automatically since you have - already configured archive_command. In most cases this - happens quickly, but you are advised to monitor your archive - system to ensure there are no delays. - If the archive process has fallen behind - because of failures of the archive command, it will keep retrying - until the archive succeeds and the backup is complete. - If you wish to place a time limit on the execution of - pg_stop_backup, set an appropriate - statement_timeout value. + Because the base backup was created with continuous archiving enabled, + the WAL files comprising the base backup and the associated backup + history file should now be archived in the usual way by + postgres's continuous archiving feature. Note that during + archiving the backup history file, backup_label, that + appeared in your cluster directory during the backup is moved to the + archive directory and renamed. This file is an + of the base backup. The file's extension changed to .backup, + and the filename is changed to match the first WAL segment file in + the base backup. For example, if the first WAL file in the range is + 0000000100001234000055CD the backup history file will be + named similarly to 0000000100001234000055CD.007C9330.backup. + (The second part of the file name stands for an exact position within + the WAL file, and can ordinarily be ignored.) - - + + + Once all the WAL files included in the range listed inside the backup + history file, as well as the backup history file itself have been + archived, the base backup is complete. pg_stop_backup will + not return until the last segment has been archived. In most cases this + happens quickly, but you are advised to monitor your archive system to + nsure there are no delays. If the archive process has fallen behind because + of failures of the archive command, it will keep retrying until the archive + succeeds and the backup is complete. + If you wish to place a time limit on the execution of + pg_stop_backup, set an appropriate + statement_timeout value. + + + + Once archiving is complete, all archived WAL segments with names + numerically less are no longer needed to recover the file system + backup and can be deleted. However, you should consider keeping + several backup sets to be absolutely certain that you can recover + your data. + + + Some file system backup tools emit warnings or errors -- 2.4.3 --------------020100020201090206040700 Content-Type: text/plain Content-Disposition: inline Content-Transfer-Encoding: 8bit MIME-Version: 1.0 -- Sent via pgsql-docs mailing list (pgsql-docs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-docs --------------020100020201090206040700--