Simon,

First off, let me compliment you on such a thourough proposal.  I'm feeling 
very enthusiastic about 7.5 PITR based on the amount of thought you've given 
the project.

Based on throuroughness, I wanted to make a few comments and suggestions.   
All of the below are in the category of "extras it would be nice to have but 
are not essential to implementing PITR."   Possibly they are all features to 
consider for 7.6/8.0/whatever; the next version.   These comments are based 
on my personal experience as a professional contract DBA for PostgreSQL, MS 
SQL Server and SQL Anywhere.

> PITR features are designed to extend the existing Crash Recovery
> features so
> that a recovery can take place in situations where a crash recovery
> would
> not have been possible. These situations are:

In my personal experience, the *primary* use of PITR is recovery from User 
Error.   For example, with one SQL Server 7.0 installation for a law firm, 
I've made use of PITR 4 times over the last 4 years: once was because and HDD 
failed, the other three were all becuase of IT dept. staff running 
unconstrained UPDATE queries against the back end.   For recovery with 
minimal loss of data, there are existing solutions, such as replication 
servers, in addition to PITR; for recovery from User Error, only PITR will 
suffice.

> There are a wide range of Backup and Recovery (BAR) products on the
> market, both open source and commercially licensed programs that provide
> facilities to perform full physical backups and individual file
> archives. The best way to foster wide adoption of PostgreSQL is to allow
> it to work in conjunction with any of these products. To this end, a

Very perceptive of you.   Good idea!

> wal_archive_policy and enable/disable archiving accordingly. This
> parameter can only be changed at server start. (This is required because
> the initial step of archiving each xlog is performed by the backend; if
> this were changeable after boot, then it might be possible for an
> individual backend to override the wal_archive_policy and choose not to
> archive - which would then effect the whole system and all users, not
> just the user making that choice). It is considered less desirable to

Let me voice a real-world exception to this policy.   Imagine that you are 
running an OLAP or decision-support database that analyzes data coming from 
an external source.   Once a day you load 250MB of data via COPY and then 
does transformations on that data.   While doing the load, you do *not* want 
the archiver running, as it would quickly fill up the WAL partition and 
backlog the archive tape.    
Under the proposed PITR spec, the only way to handle this would be to:
1) Full back up
2) Shut down PG
3) Restart PG without archiving
4) Load the data
5) Shut down PG again
6) Restart PG with archiving
7) Full back-up again.
DBAs would like it much more if starting/stopping the archiver was possible 
via a superuser (not regular user) GUC.    This would allow a much faster 
cycle:
1) Full back up
2) Stop archiving
3) Load the data
4) Restart archiving
5) Full back-up

Related to the above, what I don't see in your paper or the proposed API is a 
way to coordinate full back-ups and WAL archiving.   Obviously, the PITR 
Archive is only useful in reference to an existing full backup, so it is 
important to be able to associate a set of PITR archives with a particular 
full backup, or with some kind of "backup checkpoint".   I'm sure that you 
have a solution for this, I just didn't see it explained in your proposal, or 
didn't understand it.

FWIW, I find the MSSQL PITR system awkward in the extreme and harrowing in its 
unreliability.   So it's not a good model to copy ....

> There is no requirement for the archiver to halt when PostgreSQL shuts
> down, though may choose to do so or not, e.g. it may be desirable to
> have one archiver operate for multiple postmasters simultaneously. The

I see that you've chosen the "One archiver, many databases/clusters" 
architecture.   I can also see how this strategy will be easier than the 
"many archivers" strategy.   Be prepared that, based on the needs of DBAs, 
you will get the following requests:
A) Will it be possible to have the archiver process run on a seperate machine 
from PostgreSQL and access it over the network, via NFS or some other means?
B) Will it be possible to define multiple output streams, so that database X 
and be archived to device Y and database N to device M?

> The out of space condition could therefore occur in two ways:
> 1. there is a single delay during which xlog filesystem fills
> 2. there could be a systematic delay which builds slowly until the xlog
> filesystem fills

Given how PITR, and Tablespaces, both substantially increase the risk of 
running out of space on the xlog partition(s), it would be very nice to be 
able to arrange a WARNING whenever any PostgreSQL disk resource drops below a 
pre-defined percentage of availability.    This could be done through a 
simple asynchronous process; heck, I think even I could write it in Perl.   
Maybe I'll try.
The idea would be that all of the dirs defined in Tablespaces and PGData would 
be checked every X seconds for available space, and when it drops below Y% as 
reported by the filesystem, a WARNING is issued (X and Y% would be 
configurable at start time).   Given the fluctuation of the xlog size and the 
size of the data files, this is not 100% reliabile but as a DBA it would be 
useful to be warned that xlog is more than, say, 80% full at least some of 
the time.   It would let me know that I need to look at re-partitioning 
sooner rather than later.
This asynchronous daemon would be optional, like the statistics daemon, 
allowing DBAs with large disks and small DBs not to run it.

> It is possible that an administrator may wish to choose to keep
> PostgreSQL up and to begin dropping log files rather than eventually
> crash. If that choice was made AND a full physical backup was not yet
> available, then there is a window of risk during which if a catastrophic

Frankly, the only reason I can see for keeping the DB up after out-of-space is 
to allow a full backup to be made.   In fact, I would favor a solution that 
immediately ran a full backup and then shut down the db whenever out-of-space 
happened.   If continued operation with dropping logs proves hard to 
implement, I'd say skip it.   
Also, if you allow halting the archiver process on running databases, it would 
allow the DBA to gracefully extricate themselves from circumstances where 
rlog or xlog is out of space but the rest of the DB system is not.

-- 
-Josh Berkus
 Aglio Database Solutions
 San Francisco


Tue, 9 Mar 2004 09:46:33 -0800
Josh Berkus <josh@agliodbs.com>
Re: PITR Functional Design v2 for 7.5
Josh Berkus wrote:

>5) Full back-up
>
>Related to the above, what I don't see in your paper or the proposed API is a 
>way to coordinate full back-ups and WAL archiving.   Obviously, the PITR 
>Archive is only useful in reference to an existing full backup, so it is 
>important to be able to associate a set of PITR archives with a particular 
>full backup, or with some kind of "backup checkpoint".   I'm sure that you 
>have a solution for this, I just didn't see it explained in your proposal, or 
>didn't understand it.
>

As far as I understand , full backup in the sense of pgsql means all 
data files including c_log where all transactions before the checkpoint 
are completely written to the data files. AFAICS there is a small detail 
missing so far.

When I'm doing a file level hot backup, I can't be sure about the backup 
order. To be sure the cluster is in a consistent state regarding 
checkpoints, pg_clog must be the first directory backed up. If this 
isn't made sure, the situation could arise that the backed up clog 
version contains a checkpoint which marks a transaction completed that 
has been written to a file which was backed up earlier than the data 
write took place.

This could be insured by doing the backup in two steps; first backing up 
pg_clog, and then the rest, restore being performed in the opposite 
order. But this seems to be not too fail safe, what if the admin doesn't 
know this/forgot about it? So IMHO a mechanism insuring this would be 
better. I could think of a solution where a second pg_clog directory is 
used, and a pgsql api for that which  is called right before performing 
the file backup. Josh calls this second pg_clog the "backup checkpoint".

At the moment, a restart is done from clog + WAL, where clog might be 
too new in a hot backup situation as mentioned above. There should be a 
second pgsql restart mode, where checkpoints are not taken from that 
current clog, but the "backup checkpoint clog" which was created 
explicitely at backup time. This is somewhat similar to MSSQL's backup 
behaviour, where the transaction log (=WAL) is growing until a full 
backup has been performed successfully.

Regards,
Andreas



Tue, 09 Mar 2004 22:19:05 +0100
Andreas Pflug <pgadmin@pse-consulting.de>
Re: PITR Functional Design v2 for 7.5
>Josh Berkus [mailto:josh@agliodbs.com]
> First off, let me compliment you on such a thorough proposal.  I'm
> feeling very enthusiastic about 7.5 PITR 

Thank you, though please realise that I am in many ways summarising a
wide range of suggestions and earlier work into a coherent whole.

Me too! I'm trying to pace myself through release and into subsequent
maintenance of the new features.

Best Regards, Simon



Tue, 9 Mar 2004 22:38:19 -0000
"Simon Riggs" <simon@2ndquadrant.com>
Re: PITR Functional Design v2 for 7.5
>Josh Berkus [mailto:josh@agliodbs.com]
> > wal_archive_policy and enable/disable archiving accordingly. This
> > parameter can only be changed at server start. (This is required
because
> > the initial step of archiving each xlog is performed by the backend;
if
> > this were changeable after boot, then it might be possible for an
> > individual backend to override the wal_archive_policy and choose not
to
> > archive - which would then effect the whole system and all users,
not
> > just the user making that choice). It is considered less desirable
to
> 
> Let me voice a real-world exception to this policy.   Imagine that you
are
> running an OLAP or decision-support database that analyzes data coming
> from
> an external source.   Once a day you load 250MB of data via COPY and
then
> does transformations on that data.   While doing the load, you do
*not*
> want
> the archiver running, as it would quickly fill up the WAL partition
and
> backlog the archive tape.
> Under the proposed PITR spec, the only way to handle this would be to:
> 1) Full back up
> 2) Shut down PG
> 3) Restart PG without archiving
> 4) Load the data
> 5) Shut down PG again
> 6) Restart PG with archiving
> 7) Full back-up again.
> DBAs would like it much more if starting/stopping the archiver was
> possible
> via a superuser (not regular user) GUC.    This would allow a much
faster
> cycle:
> 1) Full back up
> 2) Stop archiving
> 3) Load the data
> 4) Restart archiving
> 5) Full back-up

The scenario you mention is what I'd like to do, but don't just yet see
how.

I'd welcome input on this point, since I don't fully understand GUCs:

Thinking about this:
1. Since the backends run XLogArchiveNotify(), they must all do so
identically. One slip invalidates all the work of all the others. 
GUC Options are:
INTERNAL - not appropriate
POSTMASTER - what I originally envisaged, but not what you want
SIGHUP - seems to allow different parameter settings in each backend
BACKEND - not appropriate
SUSET - maybe what you're looking for???????
USERLIMIT - no
USERSET - absolutely no
 
2. Maybe have Postmaster run something every so often that looks for
full xlogs and then executes XLogArchiveNotify() for them?

Thoughts anyone?

Best Regards, Simon Riggs


Tue, 9 Mar 2004 22:38:19 -0000
"Simon Riggs" <simon@2ndquadrant.com>
Re: PITR Functional Design v2 for 7.5
Simon,

> SIGHUP - seems to allow different parameter settings in each backend

Nope.   SIGHUP means that you need to send a HUP to the postmaster, such as 
you would with changes to pg_hba.conf.

> SUSET - maybe what you're looking for???????

Yes.   This means that it can be changed, at runtime, but by the Superuser 
only.  This is used for several settings which are possible to change at 
runtime but take effect system-wide.

-- 
-Josh Berkus
 Aglio Database Solutions
 San Francisco


Tue, 9 Mar 2004 14:42:25 -0800
Josh Berkus <josh@agliodbs.com>
Re: PITR Functional Design v2 for 7.5
> what I don't see in your paper or the proposed API
> is a
> way to coordinate full back-ups and WAL archiving.   Obviously, the
PITR
> Archive is only useful in reference to an existing full backup, so it
is
> important to be able to associate a set of PITR archives with a
particular
> full backup, or with some kind of "backup checkpoint".   I'm sure that
you
> have a solution for this, I just didn't see it explained in your
proposal,
> or
> didn't understand it.

You are perceptive, and generous in imagining I have a good solution. I
will document this, assuming no better ideas emerge:

My understanding is this:
When crash recovery occurs, recovery starts from this last checkpoint,
not from the earliest log. Existing function caters for locating start
of xlogs for recovery purposes. 
Relying upon that, it should be possible to have a backup coordinate
with a stream of xlogs at recovery time, but not EXACTLY at backup time.
Best practice would indicate that you should always maintain 2-3 full
backups, so deleting xlogs immediately after a backup is not a very good
idea at all.

In general, the usage is going to be:
- start archiver
- start PostgreSQL
 - interval during which xlogs are backed up
- start backup

Best Regards, Simon Riggs


Tue, 9 Mar 2004 23:07:28 -0000
"Simon Riggs" <simon@2ndquadrant.com>
Re: PITR Functional Design v2 for 7.5
> From: Josh Berkus [mailto:josh@agliodbs.com]
> > SIGHUP - seems to allow different parameter settings in each backend
> 
> Nope.   SIGHUP means that you need to send a HUP to the postmaster,
such
> as
> you would with changes to pg_hba.conf.
> 
> > SUSET - maybe what you're looking for???????
> 
> Yes.   This means that it can be changed, at runtime, but by the
Superuser
> only.  This is used for several settings which are possible to change
at
> runtime but take effect system-wide.

SUSET it is then. I'll update the design doc

Thanks, Simon


Tue, 9 Mar 2004 23:17:44 -0000
"Simon Riggs" <simon@2ndquadrant.com>
Re: PITR Functional Design v2 for 7.5
>Andreas Pflug
>> Josh Berkus wrote:
> >Related to the above, what I don't see in your paper or the proposed
API
> is a
> >way to coordinate full back-ups and WAL archiving.   Obviously, the
PITR
> >Archive is only useful in reference to an existing full backup, so it
is
> >important to be able to associate a set of PITR archives with a
> particular
> >full backup, or with some kind of "backup checkpoint".   I'm sure
that
> you
> >have a solution for this, I just didn't see it explained in your
> proposal, or
> >didn't understand it.
> >
>AFAICS there is a small detail
> missing so far.
> 
> When I'm doing a file level hot backup, I can't be sure about the
backup
> order. To be sure the cluster is in a consistent state regarding
> checkpoints, pg_clog must be the first directory backed up. If this
> isn't made sure, the situation could arise that the backed up clog
> version contains a checkpoint which marks a transaction completed that
> has been written to a file which was backed up earlier than the data
> write took place.
> 
> This could be insured by doing the backup in two steps; first backing
up
> pg_clog, and then the rest, restore being performed in the opposite
> order. 

Good spot. I'll add this to the design.

Will think more on the "backup checkpoint". Don't let me off the hook...

Best Regards, Simon 


Tue, 9 Mar 2004 23:17:44 -0000
"Simon Riggs" <simon@2ndquadrant.com>
Re: PITR Functional Design v2 for 7.5
Andreas Pflug <pgadmin@pse-consulting.de> writes:
> When I'm doing a file level hot backup, I can't be sure about the backup 
> order. To be sure the cluster is in a consistent state regarding 
> checkpoints, pg_clog must be the first directory backed up.

You are going off in the wrong direction entirely.

Any hot-backup design that thinks safety can be ensured by "back up file A
before file B" considerations is wrong.  That's because Postgres doesn't
necessarily dump dirty blocks into the data area (or clog area) at any
particular time.  Therefore, a filesystem-level backup taken while the
postmaster is running is basically certain to be inconsistent.  You can
*not* avoid this by being careful about the order you dump files in.
Heck, you can't even be certain that a file you dump is internally
consistent.

The only way we can support file-level hot backup is in conjunction with
PITR-style WAL log archiving.  It is okay for the data area dump to be
inconsistent, so long as your recovery process includes replay of WAL
starting at some checkpoint before the filesystem dump started, and
extending to some point after the filesystem dump finished.  Replaying
WAL will correct the inconsistencies.

			regards, tom lane

Tue, 09 Mar 2004 18:20:15 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: PITR Functional Design v2 for 7.5 
Tom Lane wrote:

>Andreas Pflug <pgadmin@pse-consulting.de> writes:
>  
>
>>When I'm doing a file level hot backup, I can't be sure about the backup 
>>order. To be sure the cluster is in a consistent state regarding 
>>checkpoints, pg_clog must be the first directory backed up.
>>    
>>
>
>You are going off in the wrong direction entirely.
>
>Any hot-backup design that thinks safety can be ensured by "back up file A
>before file B" considerations is wrong.  That's because Postgres doesn't
>necessarily dump dirty blocks into the data area (or clog area) at any
>particular time.  Therefore, a filesystem-level backup taken while the
>postmaster is running is basically certain to be inconsistent.  You can
>*not* avoid this by being careful about the order you dump files in.
>Heck, you can't even be certain that a file you dump is internally
>consistent.
>  
>

Maybe my wording was misleading, seems Simon understood me as int was meant.
With "consistent state regarding checkpoints" I meant that all 
transactions that are marked as committed with the checkpoint are really 
present in the data files. Of course, there might be even more 
transactions which haven't been checkpointed so far, they'll need WAL 
replay.
To clarify:
I'd expect a cluster to be workable, if I
- disable VACUUM until backup completed
- issue CHECKPOINT
- backup clog (CHECKPOINT and backup clog are the "backup checkpoint")
- backup all datafiles (which include at least all completed transaction 
data at checkpoint time)
and then
- restore datafiles and clog
- bring up pgsql.
Certainly, all transactions after the backup checkpoint are lost. There 
might be fragments of newer transactions in data files, but they were 
never committed according to clog and thus rolled back.
WAL replay would add more completed transactions, making the cluster 
more up-to-date, but omitting this would be sufficient in many desaster 
recovery scenarios.
Did I miss something? If so, not only an API to get WAL data ordered out 
of pgsql is needed, but for the whole cluster.

Regards,
Andreas



Wed, 10 Mar 2004 12:16:39 +0100
Andreas Pflug <pgadmin@pse-consulting.de>
Re: PITR Functional Design v2 for 7.5
Andreas Pflug <pgadmin@pse-consulting.de> writes:
> To clarify:
> I'd expect a cluster to be workable, if I
> - disable VACUUM until backup completed
> - issue CHECKPOINT
> - backup clog (CHECKPOINT and backup clog are the "backup checkpoint")
> - backup all datafiles (which include at least all completed transaction 
> data at checkpoint time)
> and then
> - restore datafiles and clog
> - bring up pgsql.

Why is that a useful approach?  You might as well shut down the
postmaster and do a cold filesystem backup, because you are depending on
the data files (including clog) not to change after the checkpoint.  You
cannot make such an assumption in a running database.

Preventing VACUUM does not help btw.

			regards, tom lane

Wed, 10 Mar 2004 10:04:07 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: PITR Functional Design v2 for 7.5 
Tom Lane wrote:

>Andreas Pflug <pgadmin@pse-consulting.de> writes:
>  
>
>>To clarify:
>>I'd expect a cluster to be workable, if I
>>- disable VACUUM until backup completed
>>- issue CHECKPOINT
>>- backup clog (CHECKPOINT and backup clog are the "backup checkpoint")
>>- backup all datafiles (which include at least all completed transaction 
>>data at checkpoint time)
>>and then
>>- restore datafiles and clog
>>- bring up pgsql.
>>    
>>
>
>Why is that a useful approach?  You might as well shut down the
>postmaster and do a cold filesystem backup, 
>
We're talking about *hot* backup, aren't we?

Regards,
Andreas



Wed, 10 Mar 2004 17:37:34 +0100
Andreas Pflug <pgadmin@pse-consulting.de>
Re: PITR Functional Design v2 for 7.5
Andreas Pflug <pgadmin@pse-consulting.de> writes:
> Tom Lane wrote:
>> Why is that a useful approach?  You might as well shut down the
>> postmaster and do a cold filesystem backup, 
>> 
> We're talking about *hot* backup, aren't we?

Exactly.  The approach you're sketching can't work for hot backup,
because it effectively assumes that the database won't be changing.

			regards, tom lane

Wed, 10 Mar 2004 11:56:36 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: PITR Functional Design v2 for 7.5 
Tom Lane wrote:

>Andreas Pflug <pgadmin@pse-consulting.de> writes:
>  
>
>>Tom Lane wrote:
>>    
>>
>>>Why is that a useful approach?  You might as well shut down the
>>>postmaster and do a cold filesystem backup, 
>>>
>>>      
>>>
>>We're talking about *hot* backup, aren't we?
>>    
>>
>
>Exactly.  The approach you're sketching can't work for hot backup,
>because it effectively assumes that the database won't be changing.
>  
>
Well in the first place my posting was to express my suspicion that WAL 
replay relies on clog/pg_control being accurate, i.e. transactions 
marked as flushed must be on disk. AFAICS this is the consequence of WAL 
replay implementation. In case of hot backup, this means that data files 
must not be older than clog. Do you agree? So PITR needs a mechanism to 
insure this at backup time.

Next question would be: If the point in time I'd like to recover is that 
very backup checkpoint time, do I need xlog at all?

Regards,
Andreas




Wed, 10 Mar 2004 18:33:25 +0100
Andreas Pflug <pgadmin@pse-consulting.de>
Re: PITR Functional Design v2 for 7.5
Josh Berkus kirjutas T, 09.03.2004 kell 19:46:

> In my personal experience, the *primary* use of PITR is recovery from User 
> Error.   For example, with one SQL Server 7.0 installation for a law firm, 
> I've made use of PITR 4 times over the last 4 years: once was because and HDD 
> failed, the other three were all becuase of IT dept. staff running 
> unconstrained UPDATE queries against the back end.   For recovery with 
> minimal loss of data, there are existing solutions, such as replication 
> servers, in addition to PITR; for recovery from User Error, only PITR will 
> suffice.

Actually PostgreSQL used to have very good support for this until some
time in Postgres95 development by supporting additional temporal
qualifiers for queries. I.e. one could ask for 'SELECT SALARY FROM
EMPLOYEES AS IT WAS AT YESTERDAY NOON' (the syntax was not exacly this
:)

It was a very simple and logical result of PostgreSQL's MVCC storage and
was supported by VACUUM allowing dropping only deleted tuples older than
some specified time.

Oracle has recently added something similar (using their WAL's) to ver.
9.0 or 10.x of their DBMS exactly for recovery from user errors.

The support for this was dropped from postgreSQL citing performance
reasons at that time, but I still hope that it can restored some time.

--------------
Hannu

Wed, 10 Mar 2004 23:01:25 +0200
Hannu Krosing <hannu@tm.ee>
Re: PITR Functional Design v2 for 7.5
>Hannu Krosing
> Josh Berkus kirjutas T, 09.03.2004 kell 19:46:
> > In my personal experience, the *primary* use of PITR is recovery
from
> User
> > Error.   For example, with one SQL Server 7.0 installation for a law
> firm,
> > I've made use of PITR 4 times over the last 4 years: once was
because
> and HDD
> > failed, the other three were all becuase of IT dept. staff running
> > unconstrained UPDATE queries against the back end.   For recovery
with
> > minimal loss of data, there are existing solutions, such as
replication
> > servers, in addition to PITR; for recovery from User Error, only
PITR
> will
> > suffice.
> 
> Actually PostgreSQL used to have very good support for this until some
> time in Postgres95 development by supporting additional temporal
> qualifiers for queries. I.e. one could ask for 'SELECT SALARY FROM
> EMPLOYEES AS IT WAS AT YESTERDAY NOON' (the syntax was not exacly this
> :)

Shame we can't run cc -as was 4 years ago.
Or even better cc -as will be in 3 weeks; that would save me loads :)
!!

> It was a very simple and logical result of PostgreSQL's MVCC storage
and
> was supported by VACUUM allowing dropping only deleted tuples older
than
> some specified time.
> 
> Oracle has recently added something similar (using their WAL's) to
ver.
> 9.0 or 10.x of their DBMS exactly for recovery from user errors.
> 
> The support for this was dropped from postgreSQL citing performance
> reasons at that time, but I still hope that it can restored some time.

Flashback query is a new feature in Oracle 9i. I believe it is regarded
with some horror by the DBA community...

I get your idea though, though I think it is a different thing.

PITR is about the avoidance of risk, not really about fixing any
particular classes of problem. If you have PITR you can recover from
"all" problems, frequent or not, depending upon how carefully and for
how long you protect your backups.

Reading old MVCC copies won't take that away, though is a feature that
would be useful within a particular time window. It's also hard to tell
whether VACUUM has been run, and if so on which tables, since that will
also change the answer you get from those MVCC-usage type queries. 

Oracle recognise this also. Flashback hasn't replaced backup/restore.
Neither has it prevented them from enhancing log miner.

Temporal support is a different issue anyway. It is up to you to come up
with a database design that supports being able to ask that question, if
that is a business requirement.

Best regards, Simon Riggs



Fri, 12 Mar 2004 10:46:26 -0000
"Simon Riggs" <simon@2ndquadrant.com>
Re: PITR Functional Design v2 for 7.5