I've done some preliminary work on the PITR patch that J.R. Nield
developed last summer. I've applied parts of it (in rather edited
form) but there are big chunks I think are not ready to go. You
can see the original patch in pgsql-patches --- I sent it there
mostly to get it into the archives.

I have committed the parts that have to do with making the contents of
WAL more robust for PITR purposes; specifically, labeling WAL segment
files clearly, and adding WAL logging of file creation/deletion.

I have not committed the massive reorganization of xlog.c that appears
in J.R.'s patch; I think it's unnecessary and likely to introduce bugs
(certainly it would revert some recent bug fixes).

I have also not committed the ALTER SYSTEM BACKUP / ALTER SYSTEM RECOVER
commands that appear in the patch. It is not clear to me that this
functionality belongs in the backend rather than separate management
utilities, and even if it does belong there, this doesn't seem a clean
way to do it. On the backup side, it seems like this code is basically
reinventing the tar(1) command. On the restore side, I don't care for
the "interactive command" implementation of ALTER SYSTEM RECOVER ---
that seems like it makes it unnecessarily hard to drive the recovery
process from another program. I envision this process being controlled
by some kind of higher-level management utility, so I'd prefer to see a
program-friendly API instead of one designed for manual use.

Anyway, I'm hoping to see some discussion of what to do next and what
the PITR functionality ought to look like from a user's standpoint.

regards, tom lane

Search Discussions

  • Fred Moyer at Feb 12, 2004 at 8:22 pm

    On Thu, 2004-02-12 at 02:41, Tom Lane wrote:
    I've done some preliminary work on the PITR patch that J.R. Nield
    developed last summer. I've applied parts of it (in rather edited
    form) but there are big chunks I think are not ready to go. You
    can see the original patch in pgsql-patches --- I sent it there
    mostly to get it into the archives.

    I have committed the parts that have to do with making the contents of
    WAL more robust for PITR purposes; specifically, labeling WAL segment
    files clearly, and adding WAL logging of file creation/deletion.

    I have not committed the massive reorganization of xlog.c that appears
    in J.R.'s patch; I think it's unnecessary and likely to introduce bugs
    (certainly it would revert some recent bug fixes).

    I have also not committed the ALTER SYSTEM BACKUP / ALTER SYSTEM RECOVER
    commands that appear in the patch. It is not clear to me that this
    functionality belongs in the backend rather than separate management
    utilities, and even if it does belong there, this doesn't seem a clean
    way to do it. On the backup side, it seems like this code is basically
    reinventing the tar(1) command. On the restore side, I don't care for
    the "interactive command" implementation of ALTER SYSTEM RECOVER ---
    that seems like it makes it unnecessarily hard to drive the recovery
    process from another program. I envision this process being controlled
    by some kind of higher-level management utility, so I'd prefer to see a
    program-friendly API instead of one designed for manual use.

    Anyway, I'm hoping to see some discussion of what to do next and what
    the PITR functionality ought to look like from a user's standpoint.
    As a user of this functionality the first thing I would want to do is
    look at a certain point in time, say yesterday around 3:30 pm and get a
    window of transactions made at that time. So maybe an object method or
    function call which passes the following parameters: 1) Time, 2) number
    of transactions to return with time being the middle of that window.

    i.e. $transaction_list = $transaction_log_object->transaction_window({
    time => '23:59:59.99-12', window => '150' });

    Which returns 150 transactions centered around 11:59:59. If the
    transaction window exceeds the end of the transaction log move the
    window back accordingly. Once I have that list I would want to look at
    the individual transactions and determine which one I want to roll the
    database to.
  • Cott Lang at Feb 13, 2004 at 12:48 am

    On Wed, 2004-02-11 at 19:41, Tom Lane wrote:

    Anyway, I'm hoping to see some discussion of what to do next and what
    the PITR functionality ought to look like from a user's standpoint.
    As previously discussed on general ...

    * WAL files archived to a different location instead of recycling.

    * The ability to force a WAL log switch to ensure all changes during the
    backup are flushed to archived logs and copied.

    * Ability to easily apply WAL logs to a standby database. I'd love be
    able to take a hot backup of my production database, bring it up on
    another computer and keep it a log or two behind production by
    continually copying and applying logs to it.

    * Although not PITR, on a related note, having the ability to do
    incremental pg_dumps would be a huge boon for those relying on pg_dumps
    for backups.

    * Oracle 10g's Flashback feature is interesting. You can roll the
    entire database back to a point in time with:
    flashback database to '3:00 pm';
    I would have to say it's hardly critical. :)
  • Tom Lane at Feb 13, 2004 at 1:14 am

    Cott Lang writes:
    * The ability to force a WAL log switch to ensure all changes during the
    backup are flushed to archived logs and copied.
    Why does that require a log switch? You can copy the active log file in
    any case. (There was actually code to do that in J.R.'s patch, which I
    disregarded because I see no point in it ...)

    regards, tom lane
  • Cott Lang at Feb 13, 2004 at 1:25 am

    On Thu, 2004-02-12 at 18:14, Tom Lane wrote:
    Cott Lang <cott@internetstaff.com> writes:
    * The ability to force a WAL log switch to ensure all changes during the
    backup are flushed to archived logs and copied.
    Why does that require a log switch? You can copy the active log file in
    any case. (There was actually code to do that in J.R.'s patch, which I
    disregarded because I see no point in it ...)
    Maybe it doesn't, I'm certainly no expert in PG internals. :)

    It just seems like a Good Thing (TM) to ensure that any possible changes
    to the data files during the backup are in the logs that are copied to
    the archive destination and backed up as part of the hot backup.
  • Mark Kirkwood at Feb 13, 2004 at 4:28 am
    The big feature I am being pestered for is :

    - hot backup that (subsequent) logs can be applied to during recovery

    The patch in its current form provides this - which is excellent.

    However, some "easyness" enhancements could be in order :

    e.g :

    - automatically archiving logs after a hot backup to <somewhere>
    - some sort of ability to detect if the archived log sequence is
    "broken" anywhere after the lastest hot backup.


    I would like to see PITR as straightforward as possible to setup.
    Backups that are actually unrestorable - because the Dba didn't
    understand the product's convoluted "design" - are still way too
    frequent AFAICS.

    cheers

    Mark

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-hackers-pitr @
categoriespostgresql
postedFeb 12, '04 at 2:41a
activeFeb 13, '04 at 4:28a
posts6
users4
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2021 Grokbase