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(at)momjian(dot)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(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

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(dot)tiikkaja(at)cs(dot)helsinki(dot)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(dot)tiikkaja(at)cs(dot)helsinki(dot)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(dot)tiikkaja(at)cs(dot)helsinki(dot)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(at)agliodbs(dot)com> wrote:
> On 8/24/10 12:42 PM, Tom Lane wrote:
>> Marko Tiikkaja <marko(dot)tiikkaja(at)cs(dot)helsinki(dot)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 Wed, Aug 25, 2010 at 5:44 AM, Josh Berkus <josh(at)agliodbs(dot)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.com
PostgreSQL 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(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +