Re: PITR Functional Design v2 for 7.5 - Mailing list pgsql-hackers

>Richard Huxton
> On Monday 08 March 2004 23:28, Simon Riggs wrote:
> > PITR Functional Design v2 for 7.5
> > Review of current Crash Recovery
> 
> Is there any value in putting this section on techdocs or similar? We
do
> get a
> small but steady trickle of people asking for details on internals,
and I
> think this covers things in a different way to the WAL section of the
> manuals.

Certainly, though I would like to do all of that after it actually
works!

> > PITR Proposed Solution
> 
> > To allow this to occur, the full backup *must* occur while the
database
> > is open or "hot". This backup must include all data and clogs (and
any
> > tablespaces or logical links utilised). A continuous sequence of
xlogs
> > must also be available, stretching from the last checkpoint prior to
the
> > start of the backup through to whatever time is specified for the
> > "recovery point" or until the end of the xlogs.
> 
> So this is a standard cp/tar etc while the cluster is actually in use?

Yes. I will add a line in to clarify that.

> > XLogArchiveXlogs() returns a single XLOG filename, or NULL
> >
> > If an xlog file is waiting to be archived, then the archiver will
> > discover
> > the name of the xlog by using this API call. If more than one file
is
> > available to be archived, then it will be ignored. If the archiver
is
> > multi-threaded, it need not wait until it has executed
> > XLogArchiveComplete
> > before it executes XLogArchiveXlogs again.
> 
> So this means:
> 1. The archiver is responsible for noticing that it is already
archiving
> the
> filename returned (if it repeats the call too quickly).
> 2. The archiver can only ever archive one XLOG file at a time.

1. No: I notice I missed a line saying
" XLogArchiveXlogs()" in section 1.1.3 (corrected).
Clarification: The archiver will not need to keep track of whether it is
already archiving the same file (though sounds reasonable programming to
do so anyway). The API call will never return the same log file twice to
this call (by definition). That is implemented in my proposal by
renaming the rlog entry to .busy, so it wont show up on subsequent
calls.
2. 
a) There is no restriction on threading in the archiver; it can if it
wishes archive many files simultaneously. Since PostgreSQL produces them
one at a time, this implies a build up of xlogs, which is specifically
not encouraged. An archiver would be encouraged to multi-thread to avoid
peaks of demand where the archive process was occurring slower than
xlogs were being written.
b) The reference implementation won't be multi-threaded in its first
incarnation (if I write it!!!....be my guest, you have the API
definition).

You have also made me realise another failure condition which I have
also added, todo with a failure of the copy process after this API call.

> > The initial proposal is a simple scheme that uses file existence &
file
> > extension to pass information between PostgreSQL and the archiver.
This
> > would take place in a peer directory of pg_xlog and pg_clog which
has
> > been named the pg_rlog directory. (r as in the strong first syllable
> > "ar" in English pronunciation of "archive")
> 
> Any reason why not "pg_pitr" or "pg_pitr_log"?

None. I like pg_pitr...
Let's wait for other feedback to come in...

> > 1.2 pg_arch: simple xlog archiving tool
> 
> Does the specification of these parameters (and any others) need to be
> part of
> the API? I'm thinking about the ability to "drop in" different
archivers
> with
> each using the same pre-defined settings.

Those parameters ARE NOT part of the API. The parameters mentioned are
command line switches on the simple external archiving program pg_arch.

pg_arch is intended to be a simple archiver-side testing tool. It makes
sense to make it available also. Basically, you can do whatever you like
on the archiver side of the API...contrib beckons....

> > 2. Recovery to Point-in-Time (RPIT)
> Just to clarify:
> 1. I can identify which XLOG files I need based on their timestamp?
> 2. Can I force a checkpoint using standard PG client APIs? So I can do
> "close
> weekly payroll, force checkpoint".
> 3. We're restoring an entire cluster here, not just one database? How
> difficult would it be to strip out information for a single db - I'm
> thinking
> about the case where you may have limited backup storage and want to
save
> an
> orders db but not a catalogue db. Or perhaps a hosting company with
> "platinum" customers getting PITR.

1. Yes, the external timestamp gives that I think. Checking detail...
2. CHECKPOINT is a PostgreSQL SQL command which can be executed from any
client. Yes, your scenario fits.
3. I tried to avoid that issue, but it rears its head. You seem to be
specifying what you want though, so I'll have a think.

More response required on 1 & 3...later!

Best Regards, Simon Riggs