Обсуждение: Backups from the standby (Incrementally Updated Backups), open item
All, I've been looking at the open item which belongs with this doc: http://www.postgresql.org/docs/9.0/static/backup-incremental-updated.html ... and my viewpoint is that the procedure described should be *cut* from the official docs and put on the Wiki, with warnings. It's simply far too hackish and dependant on DBA understanding of PostgreSQL internals to belong in the main docs. In 9.1 we'll probably have some machinery to make taking snapshots of the standby more reliable (like running pg_start_backup on the master), and then that procedure will belong in the main docs. -- -- Josh Berkus PostgreSQL Experts Inc. http://www.pgexperts.com
Josh Berkus wrote: > All, > > I've been looking at the open item which belongs with this doc: > > http://www.postgresql.org/docs/9.0/static/backup-incremental-updated.html > > ... and my viewpoint is that the procedure described should be *cut* > from the official docs and put on the Wiki, with warnings. It's simply > far too hackish and dependant on DBA understanding of PostgreSQL > internals to belong in the main docs. > > In 9.1 we'll probably have some machinery to make taking snapshots of > the standby more reliable (like running pg_start_backup on the master), > and then that procedure will belong in the main docs. FYI, I am working on a doc update that will use pg_start/stop backup on the master, as suggested by Fujii Masao: http://archives.postgresql.org/pgsql-bugs/2010-08/msg00237.php -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +
Bruce Momjian wrote: > Josh Berkus wrote: > > All, > > > > I've been looking at the open item which belongs with this doc: > > > > http://www.postgresql.org/docs/9.0/static/backup-incremental-updated.html > > > > ... and my viewpoint is that the procedure described should be *cut* > > from the official docs and put on the Wiki, with warnings. It's simply > > far too hackish and dependant on DBA understanding of PostgreSQL > > internals to belong in the main docs. > > > > In 9.1 we'll probably have some machinery to make taking snapshots of > > the standby more reliable (like running pg_start_backup on the master), > > and then that procedure will belong in the main docs. > > FYI, I am working on a doc update that will use pg_start/stop backup on > the master, as suggested by Fujii Masao: > > http://archives.postgresql.org/pgsql-bugs/2010-08/msg00237.php I have created a doc patch (attached) to document a clean way to take a backup of the standby. This can be used for pitr, and to create additional slaves, so I do think this will be used more during 9.0. You can see the output here: http://momjian.us/tmp/pgsql/backup-incremental-updated.html The actual list of steps only appeared in 9.0 so I suggest this only be applied to HEAD and 9.0.X. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + Index: doc/src/sgml/high-availability.sgml =================================================================== RCS file: /cvsroot/pgsql/doc/src/sgml/high-availability.sgml,v retrieving revision 1.80 diff -c -c -r1.80 high-availability.sgml *** doc/src/sgml/high-availability.sgml 24 Aug 2010 15:22:12 -0000 1.80 --- doc/src/sgml/high-availability.sgml 24 Aug 2010 19:05:06 -0000 *************** *** 1913,1921 **** <para> If we take a file system backup of the standby server's data directory while it is processing ! logs shipped from the primary, we will be able to reload that backup and ! restart the standby's recovery process from the last restart point. ! We no longer need to keep WAL files from before the standby's restart point. If recovery is needed, it will be faster to recover from the incrementally updated backup than from the original base backup. </para> --- 1913,1922 ---- <para> If we take a file system backup of the standby server's data directory while it is processing ! logs shipped from the primary, we will be able to use that new ! backup to create a new standby or for <link ! linkend="backup-pitr-recovery">continuous archive recovery</>. ! We then no longer need to keep WAL files from before the new backup. If recovery is needed, it will be faster to recover from the incrementally updated backup than from the original base backup. </para> *************** *** 1926,1984 **** <orderedlist> <listitem> <para> ! Perform the backup, without using <function>pg_start_backup</> and ! <function>pg_stop_backup</>. Note that the <filename>pg_control</> ! file must be backed up <emphasis>first</>, as in: ! <programlisting> ! cp /var/lib/pgsql/data/global/pg_control /tmp ! cp -r /var/lib/pgsql/data /path/to/backup ! mv /tmp/pg_control /path/to/backup/data/global ! </programlisting> ! <filename>pg_control</> contains the location where WAL replay will ! begin after restoring from the backup; backing it up first ensures ! that it points to the last restartpoint when the backup started, not ! some later restartpoint that happened while files were copied to the ! backup. </para> </listitem> <listitem> <para> ! Make note of the backup ending WAL location by calling the <function> ! pg_last_xlog_replay_location</> function at the end of the backup, ! and keep it with the backup. <programlisting> ! psql -c "select pg_last_xlog_replay_location();" > /path/to/backup/end_location </programlisting> - When recovering from the incrementally updated backup, the server - can begin accepting connections and complete the recovery successfully - before the database has become consistent. To avoid that, you must - ensure the database is consistent before users try to connect to the - server and when the recovery ends. You can do that by comparing the - progress of the recovery with the stored backup ending WAL location: - the server is not consistent until recovery has reached the backup end - location. The progress of the recovery can also be observed with the - <function>pg_last_xlog_replay_location</> function, though that requires - connecting to the server while it might not be consistent yet, so - care should be taken with that method. </para> <para> </para> </listitem> </orderedlist> </para> - - <para> - Since the standby server is not <quote>live</>, it is not possible to - use <function>pg_start_backup()</> and <function>pg_stop_backup()</> - to manage the backup process; it will be up to you to determine how - far back you need to keep WAL segment files to have a recoverable - backup. That is determined by the last restartpoint when the backup - was taken, any WAL older than that can be deleted from the archive - once the backup is complete. You can determine the last restartpoint - by running <application>pg_controldata</> on the standby server before - taking the backup, or by using the <varname>log_checkpoints</> option - to print values to the standby's server log. - </para> </sect1> </chapter> --- 1927,1975 ---- <orderedlist> <listitem> <para> ! Perform <function>pg_start_backup()</> on the master, noting the WAL ! filename and offset reported. </para> </listitem> <listitem> <para> ! Copy the <filename>backup_label</> file from the primary's data directory to ! a temporary location: <programlisting> ! cp /path/to/master/backup_label /tmp </programlisting> </para> + </listitem> + <listitem> <para> + Run <application>pg_controldata</> on the standby and wait for + <quote>Latest checkpoint's REDO location</> to equal or exceed + the WAL information reported by <function>pg_stop_backup()</>. + You might need to call <function>pg_switch_xlog()</> to force + the required WAL file to be sent to the standby. + </para> + </listitem> + <listitem> + <para> + Perform a file system backup of the standby's data directory. + </para> + </listitem> + <listitem> + <para> + Perform <function>pg_stop_backup()</> on the master. + </para> + </listitem> + <listitem> + <para> + Move the saved <filename>backup_label</> file to the new backup + directory: + <programlisting> + mv /tmp/backup_label /path/to/new_backup + </programlisting> </para> </listitem> </orderedlist> </para> </sect1> </chapter>
On 2010-08-24 9:04 PM +0300, Josh Berkus wrote: > ... and my viewpoint is that the procedure described should be *cut* > from the official docs and put on the Wiki, with warnings. It's simply > far too hackish and dependant on DBA understanding of PostgreSQL > internals to belong in the main docs. > > In 9.1 we'll probably have some machinery to make taking snapshots of > the standby more reliable (like running pg_start_backup on the master), > and then that procedure will belong in the main docs. Is this method not reliable then? Can something go wrong even if the user does exactly what the documentation says? Regards, Marko Tiikkaja
Marko Tiikkaja <marko.tiikkaja@cs.helsinki.fi> writes:
> Is this method not reliable then? Can something go wrong even if the
> user does exactly what the documentation says?
It is not. This whole discussion started from somebody reporting that
it didn't work.
regards, tom lane
On 8/24/10 12:42 PM, Tom Lane wrote:
> Marko Tiikkaja <marko.tiikkaja@cs.helsinki.fi> writes:
>> Is this method not reliable then? Can something go wrong even if the
>> user does exactly what the documentation says?
>
> It is not. This whole discussion started from somebody reporting that
> it didn't work.
Again, given that this is a method which is (a) fairly minority-need,
and (b) not at all tested in the field, I do not think it belongs in the
main docs. Let's put it on the wiki and blog about it, and AFTER we've
collected bug reports and have something relatively bulletproof for 9.1,
THEN we put it in the main docs.
We really shouldn't have anything in the main docs related to backup
which isn't 100% tested.
-- -- Josh Berkus PostgreSQL Experts Inc.
http://www.pgexperts.com
On Tue, 2010-08-24 at 13:44 -0700, Josh Berkus wrote: > On 8/24/10 12:42 PM, Tom Lane wrote: > > Marko Tiikkaja <marko.tiikkaja@cs.helsinki.fi> writes: > >> Is this method not reliable then? Can something go wrong even if the > >> user does exactly what the documentation says? > > > > It is not. This whole discussion started from somebody reporting that > > it didn't work. > > Again, given that this is a method which is (a) fairly minority-need, > and (b) not at all tested in the field, I do not think it belongs in the > main docs. Let's put it on the wiki and blog about it, and AFTER we've > collected bug reports and have something relatively bulletproof for 9.1, > THEN we put it in the main docs. > > We really shouldn't have anything in the main docs related to backup > which isn't 100% tested. +1 JD -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579 Consulting, Training, Support, Custom Development, Engineering http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt
On Tue, Aug 24, 2010 at 4:44 PM, Josh Berkus <josh@agliodbs.com> wrote: > On 8/24/10 12:42 PM, Tom Lane wrote: >> Marko Tiikkaja <marko.tiikkaja@cs.helsinki.fi> writes: >>> Is this method not reliable then? Can something go wrong even if the >>> user does exactly what the documentation says? >> >> It is not. This whole discussion started from somebody reporting that >> it didn't work. > > Again, given that this is a method which is (a) fairly minority-need, > and (b) not at all tested in the field, I do not think it belongs in the > main docs. Let's put it on the wiki and blog about it, and AFTER we've > collected bug reports and have something relatively bulletproof for 9.1, > THEN we put it in the main docs. > > We really shouldn't have anything in the main docs related to backup > which isn't 100% tested. Yeah. This whole procedure sounds pretty flakey to me. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company
On Tue, 2010-08-24 at 13:44 -0700, Josh Berkus wrote: > On 8/24/10 12:42 PM, Tom Lane wrote: > > Marko Tiikkaja <marko.tiikkaja@cs.helsinki.fi> writes: > >> Is this method not reliable then? Can something go wrong even if the > >> user does exactly what the documentation says? > > > > It is not. This whole discussion started from somebody reporting that > > it didn't work. > > Again, given that this is a method which is (a) fairly minority-need, > and (b) not at all tested in the field, I do not think it belongs in the > main docs. Let's put it on the wiki and blog about it, and AFTER we've > collected bug reports and have something relatively bulletproof for 9.1, > THEN we put it in the main docs. > > We really shouldn't have anything in the main docs related to backup > which isn't 100% tested. +1 JD -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579 Consulting, Training, Support, Custom Development, Engineering http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt
On Wed, Aug 25, 2010 at 5:44 AM, Josh Berkus <josh@agliodbs.com> wrote: > Again, given that this is a method which is (a) fairly minority-need, > and (b) not at all tested in the field, I do not think it belongs in the > main docs. Let's put it on the wiki and blog about it, and AFTER we've > collected bug reports and have something relatively bulletproof for 9.1, > THEN we put it in the main docs. > > We really shouldn't have anything in the main docs related to backup > which isn't 100% tested. The procedure has been since 8.2. We should remove it from the documents since 8.2? Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center
On Tue, 2010-08-24 at 11:04 -0700, Josh Berkus wrote: > I've been looking at the open item which belongs with this doc: > > http://www.postgresql.org/docs/9.0/static/backup-incremental-updated.html I'm back from holidays today, so will begin looking at this and related open-ish items. -- Simon Riggs www.2ndQuadrant.comPostgreSQL Development, 24x7 Support, Training and Services
Simon Riggs wrote: > On Tue, 2010-08-24 at 11:04 -0700, Josh Berkus wrote: > > > I've been looking at the open item which belongs with this doc: > > > > http://www.postgresql.org/docs/9.0/static/backup-incremental-updated.html > > I'm back from holidays today, so will begin looking at this and related > open-ish items. I talked to several people on IM about this today and there was general agreement that the procedure as outlined, and even as proposed with pg_start_backup(), is not known to be reliable enough to put in our docs. I have therefore removed it from the 9.0 docs, and created a wiki for it: http://wiki.postgresql.org/wiki/Incrementally_Updated_Backups I looked at the 8.4 doc mention: http://www.postgresql.org/docs/8.4/static/warm-standby.html#BACKUP-INCREMENTAL-UPDATED and it was sufficiently vague that no one has obviously implemented it or we would have gotten complaints that it does not work, so I am not sure about removing all previous mentions. I think the fact it might work with full page writes allows it to work, but it is not something we do not want to continue recommending. If we get enough research that the wiki-listed procedure is good, we can move it back into the docs. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +