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

On Monday 08 March 2004 23:28, Simon Riggs wrote:
> PITR Functional Design v2 for 7.5

Blimey - that's a long post :-) Thanks for explaining things simply enough 
that a non-hacker like me can understand. Well - almost understand ;-)

> 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.

> 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?


> 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.


> 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"?

> 1.2 pg_arch: simple xlog archiving tool
>
> Src/tools/ will add:
> pg_arch.c
>     a single-threaded program that uses libpgarch.c to use API, but
> offers
> a simple copy facility from pg_xlog to another directory. The program
> will
> continue to wait and watch for archived files: it is not a file-filter
> type
> of program. It may be run as a foreground process (for testing etc),
> though
> is also designed to be run as a background process, typically executed
> at
> the same time as postmaster startup (through a mechanism such as service
> autostart mechanisms following system boot).
>     pg_arch has two parameters:
>     -D data-file root for particular instance of PostgreSQL
>     -A archive directory

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.

> 2. Recovery to Point-in-Time (RPIT)
>
> Recovery to will offer these options:
>
> 2.1 Recovery to end of logs (last time)
> 2.2 Recovery of all available on-line logs
> 2.3 Point in time recovery to the checkpoint AT or the last checkpoint
> before the time specified.
>
> The administrator is expected to be responsible for placing archived
> xlogs back into the pg_xlog directory. This may be a facility provided
> by the external archiver, a manual or other automated process. If any
> mistakes are made at this point then the administrator can then reselect
> appropriate xlogs and try again. There is no enforced limit to the
> number of recovery attempts possible.

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.

--  Richard Huxton Archonet Ltd