Thread

Incremental backup

Martin Marques <martin@bugs.unl.edu.ar> — 2003-02-07T13:47:38Z

How's this issue going on the 7.4 development tree?
I saw it on the TODO list, but didn't find much on the archives of this 
mailing list.

-- 
Porqué usar una base de datos relacional cualquiera,
si podés usar PostgreSQL?
-----------------------------------------------------------------
Martín Marqués                  |        mmarques@unl.edu.ar
Programador, Administrador, DBA |       Centro de Telematica
                       Universidad Nacional
                            del Litoral
-----------------------------------------------------------------

Re: Incremental backup

Bruce Momjian <pgman@candle.pha.pa.us> — 2003-02-12T18:00:42Z

Someone at Red Hat is working on point-in-time recovery, also known as
incremental backups.  It will be in 7.4.

---------------------------------------------------------------------------

Martin Marques wrote:
> How's this issue going on the 7.4 development tree?
> I saw it on the TODO list, but didn't find much on the archives of this 
> mailing list.
> 
> -- 
> Porqu? usar una base de datos relacional cualquiera,
> si pod?s usar PostgreSQL?
> -----------------------------------------------------------------
> Mart?n Marqu?s                  |        mmarques@unl.edu.ar
> Programador, Administrador, DBA |       Centro de Telematica
>                        Universidad Nacional
>                             del Litoral
> -----------------------------------------------------------------
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
> 

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: Incremental backup

Christopher Kings-Lynne <chriskl@familyhealth.com.au> — 2003-02-13T01:46:30Z

> Someone at Red Hat is working on point-in-time recovery, also known as
> incremental backups.  It will be in 7.4.

Does that mean that the poor guy/gal is implementing redo for all the index
types?

Chris

Re: Incremental backup

Bruce Momjian <pgman@candle.pha.pa.us> — 2003-02-13T02:29:40Z

Christopher Kings-Lynne wrote:
> > Someone at Red Hat is working on point-in-time recovery, also known as
> > incremental backups.  It will be in 7.4.
> 
> Does that mean that the poor guy/gal is implementing redo for all the index
> types?

No idea.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: Incremental backup

Patrick Macdonald <patrickm@redhat.com> — 2003-02-13T19:26:25Z

Bruce Momjian wrote:
> 
> Someone at Red Hat is working on point-in-time recovery, also known as
> incremental backups. 

PITR and incremental backup are different beasts.  PITR deals with a backup
+ logs.  Incremental backup deals with a full backup + X smaller/incremental
backups.

So... it doesn't look like anyone is working on incremental backup at the 
moment.

Cheers,
Patrick

> It will be in 7.4.
> 
> ---------------------------------------------------------------------------
> 
> Martin Marques wrote:
> > How's this issue going on the 7.4 development tree?
> > I saw it on the TODO list, but didn't find much on the archives of this
> > mailing list.
> >
> > --
> > Porqu? usar una base de datos relacional cualquiera,
> > si pod?s usar PostgreSQL?
> > -----------------------------------------------------------------
> > Mart?n Marqu?s                  |        mmarques@unl.edu.ar
> > Programador, Administrador, DBA |       Centro de Telematica
> >                        Universidad Nacional
> >                             del Litoral
> > -----------------------------------------------------------------
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 4: Don't 'kill -9' the postmaster
> >
> 
> --
>   Bruce Momjian                        |  http://candle.pha.pa.us
>   pgman@candle.pha.pa.us               |  (610) 359-1001
>   +  If your life is a hard drive,     |  13 Roberts Road
>   +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

Re: Incremental backup

Bruce Momjian <pgman@candle.pha.pa.us> — 2003-02-13T19:38:05Z

Patrick Macdonald wrote:
> Bruce Momjian wrote:
> > 
> > Someone at Red Hat is working on point-in-time recovery, also known as
> > incremental backups. 
> 
> PITR and incremental backup are different beasts.  PITR deals with a backup
> + logs.  Incremental backup deals with a full backup + X smaller/incremental
> backups.
> 
> So... it doesn't look like anyone is working on incremental backup at the 
> moment.

But why would someone want incremental backups compared to PITR?  The
backup would be mixture of INSERTS, UPDATES, and DELETES, right?  Seems
pretty weird.  :-)

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: Incremental backup

Patrick Macdonald <patrickm@redhat.com> — 2003-02-13T19:54:39Z

Bruce Momjian wrote:
> 
> Patrick Macdonald wrote:
> > Bruce Momjian wrote:
> > >
> > > Someone at Red Hat is working on point-in-time recovery, also known as
> > > incremental backups.
> >
> > PITR and incremental backup are different beasts.  PITR deals with a backup
> > + logs.  Incremental backup deals with a full backup + X smaller/incremental
> > backups.
> >
> > So... it doesn't look like anyone is working on incremental backup at the
> > moment.
> 
> But why would someone want incremental backups compared to PITR?  The
> backup would be mixture of INSERTS, UPDATES, and DELETES, right?  Seems
> pretty weird.  :-)

Yeah, it's a different method of producing a similar outcome.  However, many
companies do not want to be concerned with the management (and space)
of archived logs.  Incremental backup allows them the option of performing 
a full backup and then only backing up the modifications on a regular basis.
When it's time to restore, they'll restore the full backup and then the 
proper sequence of incremental backups. 

Cheers,
Patrick

Re: Incremental backup

Bruce Momjian <pgman@candle.pha.pa.us> — 2003-02-13T22:56:46Z

Patrick Macdonald wrote:
> > But why would someone want incremental backups compared to PITR?  The
> > backup would be mixture of INSERTS, UPDATES, and DELETES, right?  Seems
> > pretty weird.  :-)
> 
> Yeah, it's a different method of producing a similar outcome.  However, many
> companies do not want to be concerned with the management (and space)
> of archived logs.  Incremental backup allows them the option of performing 
> a full backup and then only backing up the modifications on a regular basis.
> When it's time to restore, they'll restore the full backup and then the 
> proper sequence of incremental backups. 

Wow, I never even thought that was possible.  Do other db's support that
feature?

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: Incremental backup

Rod Taylor <rbt@rbt.ca> — 2003-02-13T23:02:49Z

> Wow, I never even thought that was possible.  Do other db's support that
> feature?

Isn't that basically what the current replication kits for Postgresql do
-- via triggers and log tables?

-- 
Rod Taylor <rbt@rbt.ca>

PGP Key: http://www.rbt.ca/rbtpub.asc

Re: Incremental backup

Patrick Macdonald <patrickm@redhat.com> — 2003-02-14T00:24:13Z

Bruce Momjian wrote:
> 
> Patrick Macdonald wrote:
>
> > Yeah, it's a different method of producing a similar outcome.  However, many
> > companies do not want to be concerned with the management (and space)
> > of archived logs.  Incremental backup allows them the option of performing
> > a full backup and then only backing up the modifications on a regular basis.
> > When it's time to restore, they'll restore the full backup and then the
> > proper sequence of incremental backups.
> 
> Wow, I never even thought that was possible.  Do other db's support that
> feature?

I know Oracle and DB2 have incremental backup in their arsenal (and iirc,
SQL Server has something called "differential backup").  Whatever the name,
it's a win at the enterprise level. 

Cheers,
Patrick

Re: Incremental backup

Manfred Koizar <mkoi-pg@aon.at> — 2003-02-14T10:28:51Z

On Thu, 13 Feb 2003 19:24:13 -0500, Patrick Macdonald
<patrickm@redhat.com> wrote:
>I know Oracle and DB2 have incremental backup in their arsenal (and iirc,
>SQL Server has something called "differential backup").  Whatever the name,
>it's a win at the enterprise level. 

"A differential backup copies only the database pages that have been
modified after the last full database backup."

This could be doable using XLogRecPtr pd_lsn in the page headers, but
I don't see an easy way to do it on a live database.

Servus
 Manfred

Re: Incremental backup

Martin Marques <martin@bugs.unl.edu.ar> — 2003-02-14T11:12:56Z

On Jue 13 Feb 2003 16:38, Bruce Momjian wrote:
> Patrick Macdonald wrote:
> > Bruce Momjian wrote:
> > > Someone at Red Hat is working on point-in-time recovery, also known as
> > > incremental backups.
> >
> > PITR and incremental backup are different beasts.  PITR deals with a
> > backup + logs.  Incremental backup deals with a full backup + X
> > smaller/incremental backups.
> >
> > So... it doesn't look like anyone is working on incremental backup at the
> > moment.
>
> But why would someone want incremental backups compared to PITR?  The
> backup would be mixture of INSERTS, UPDATES, and DELETES, right?  Seems
> pretty weird.  :-)

Good backup systems, such as Informix (it's the one I used) doesn't do a query 
backup, but a pages backup. What I mean is that it looks for pages in the 
system that has changed from the las full backup and backs them up.

That's how an incremental backup works. PITR is another thing, which is even 
more important. :-)

-- 
Porqué usar una base de datos relacional cualquiera,
si podés usar PostgreSQL?
-----------------------------------------------------------------
Martín Marqués                  |        mmarques@unl.edu.ar
Programador, Administrador, DBA |       Centro de Telematica
                       Universidad Nacional
                            del Litoral
-----------------------------------------------------------------

Re: Incremental backup

Bruce Momjian <pgman@candle.pha.pa.us> — 2003-02-14T12:52:41Z

OK, once we have PITR, will anyone want incremental backups?

---------------------------------------------------------------------------

Martin Marques wrote:
> On Jue 13 Feb 2003 16:38, Bruce Momjian wrote:
> > Patrick Macdonald wrote:
> > > Bruce Momjian wrote:
> > > > Someone at Red Hat is working on point-in-time recovery, also known as
> > > > incremental backups.
> > >
> > > PITR and incremental backup are different beasts.  PITR deals with a
> > > backup + logs.  Incremental backup deals with a full backup + X
> > > smaller/incremental backups.
> > >
> > > So... it doesn't look like anyone is working on incremental backup at the
> > > moment.
> >
> > But why would someone want incremental backups compared to PITR?  The
> > backup would be mixture of INSERTS, UPDATES, and DELETES, right?  Seems
> > pretty weird.  :-)
> 
> Good backup systems, such as Informix (it's the one I used) doesn't do a query 
> backup, but a pages backup. What I mean is that it looks for pages in the 
> system that has changed from the las full backup and backs them up.
> 
> That's how an incremental backup works. PITR is another thing, which is even 
> more important. :-)
> 
> -- 
> Porqu? usar una base de datos relacional cualquiera,
> si pod?s usar PostgreSQL?
> -----------------------------------------------------------------
> Mart?n Marqu?s                  |        mmarques@unl.edu.ar
> Programador, Administrador, DBA |       Centro de Telematica
>                        Universidad Nacional
>                             del Litoral
> -----------------------------------------------------------------
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
> 
> http://www.postgresql.org/users-lounge/docs/faq.html
> 

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: Incremental backup

Martin Marques <martin@bugs.unl.edu.ar> — 2003-02-14T13:37:32Z

On Vie 14 Feb 2003 09:52, Bruce Momjian wrote:
> OK, once we have PITR, will anyone want incremental backups?

I will probably not need it, but I know of people how have databases which 
build dumps of more then 20GB.
They are interested in live incremental backups.

-- 
Porqué usar una base de datos relacional cualquiera,
si podés usar PostgreSQL?
-----------------------------------------------------------------
Martín Marqués                  |        mmarques@unl.edu.ar
Programador, Administrador, DBA |       Centro de Telematica
                       Universidad Nacional
                            del Litoral
-----------------------------------------------------------------

Re: Incremental backup

Greg Copeland <greg@copelandconsulting.net> — 2003-02-14T15:20:09Z

On Fri, 2003-02-14 at 06:52, Bruce Momjian wrote:
> OK, once we have PITR, will anyone want incremental backups?
> 
> ---------------------------------------------------------------------------
> 
> Martin Marques wrote:
> > On Jue 13 Feb 2003 16:38, Bruce Momjian wrote:
> > > Patrick Macdonald wrote:
> > > > Bruce Momjian wrote:
> > > > > Someone at Red Hat is working on point-in-time recovery, also known as
> > > > > incremental backups.
> > > >
> > > > PITR and incremental backup are different beasts.  PITR deals with a
> > > > backup + logs.  Incremental backup deals with a full backup + X
> > > > smaller/incremental backups.
> > > >
> > > > So... it doesn't look like anyone is working on incremental backup at the
> > > > moment.
> > >
> > > But why would someone want incremental backups compared to PITR?  The
> > > backup would be mixture of INSERTS, UPDATES, and DELETES, right?  Seems
> > > pretty weird.  :-)
> > 
> > Good backup systems, such as Informix (it's the one I used) doesn't do a query 
> > backup, but a pages backup. What I mean is that it looks for pages in the 
> > system that has changed from the las full backup and backs them up.
> > 
> > That's how an incremental backup works. PITR is another thing, which is even 
> > more important. :-)

I do imagine for some people it will register high on their list.

-- 
Greg Copeland <greg@copelandconsulting.net>
Copeland Computer Consulting

Re: Incremental backup

Kevin Brown <kevin@sysexperts.com> — 2003-02-14T15:32:05Z

Bruce Momjian wrote:
> 
> OK, once we have PITR, will anyone want incremental backups?

None of my database references (Date's "Introduction to Database
Systems" and Garcia-Molina's "Database Systems - The Complete Book",
in particular) seem to talk about PITR at all.  At least, there's no
index entry for it.  And a google search for "point in time recovery"
yields mostly marketing fluff.

Is there a good reference for this that someone can point me to?  I'm
interested in exactly how it'll work, especially in terms of how logs
are stored versus the main data store, effects on performance, etc.

Thanks, and sorry for the newbie question.  :-(

-- 
Kevin Brown					      kevin@sysexperts.com

Re: Incremental backup

Chris Browne <cbbrowne@acm.org> — 2003-02-14T17:32:37Z

A long time ago, in a galaxy far, far away, kevin@sysexperts.com (Kevin Brown) wrote:
> Bruce Momjian wrote:
>> 
>> OK, once we have PITR, will anyone want incremental backups?
>
> None of my database references (Date's "Introduction to Database
> Systems" and Garcia-Molina's "Database Systems - The Complete Book",
> in particular) seem to talk about PITR at all.  At least, there's no
> index entry for it.  And a google search for "point in time recovery"
> yields mostly marketing fluff.

Well, from an "academic DBMS" standpoint, it isn't terribly
interesting, since it involves assumptions of messy imperfection that
academics prefer to avoid.  

And that's not intended to insult the academics; it is often
reasonable to leave that "out of scope" much as an academic OS
researcher might prefer to try to avoid putting attention on things
like binary linkers, text file editors, and SCM systems like CVS,
which, while terribly important from a practical standpoint, don't
make for interesting OS research.

> Is there a good reference for this that someone can point me to?
> I'm interested in exactly how it'll work, especially in terms of how
> logs are stored versus the main data store, effects on performance,
> etc.
>
> Thanks, and sorry for the newbie question.  :-(

Unfortunately, the best sources I can think of are in the "O-Word"
literature, and the /practical/ answers require digging into really
messy bits of the documentation.

What it amounts to is that anyone that isn't a "near-O*****-guru" would be
strongly advised not to engage in PITR activity.  

It doesn't surprise me overly that the documentation is poor: those
that can't figure it out despite the challenges almost surely
shouldn't be using the functionality...

What PITR generally consists of is the notion that you want to recover
to the state at a particular moment in time.

In O*****-nomenclature, this means that you recover as at some earlier
moment for which you have a good backup, and then re-apply changes,
which in their terms, are kept in "archive logs," which are somewhat
analagous to WAL files.
-- 
If this was helpful, <http://svcs.affero.net/rm.php?r=cbbrowne> rate me
http://www3.sympatico.ca/cbbrowne/x.html
"We blew it -- too big, too slow..." - Bill Gates talking about NT, as
noted by Steven McGeady of Intel during a meeting with Gates

Re: Incremental backup

Kevin Brown <kevin@sysexperts.com> — 2003-02-14T18:35:31Z

Christopher Browne wrote:
> What PITR generally consists of is the notion that you want to recover
> to the state at a particular moment in time.
> 
> In O*****-nomenclature, this means that you recover as at some earlier
> moment for which you have a good backup, and then re-apply changes,
> which in their terms, are kept in "archive logs," which are somewhat
> analagous to WAL files.

Yeah, that's pretty much what I figured.

Oracle has something they call "rollback segments" which I assume are
separate bits of data that have enough information to reverse changes
that were made to the database during a transaction, and I figured
PITR would (or could) apply particular saved rollback segments to the
current state in order to "roll back" a table, tablespace, or database
to the state it was in at a particular point in time.

As it is, it sounds like PITR is a bit less refined than I expected.

So the relevant question is: how is *our* PITR going to work?  In
particular, how is it going to interact with our WAL files and the
table store?  If I'm not mistaken, right now (well, as of 7.2 anyway)
we round robin through a fixed set of WAL files.  For PITR, I assume
we'd need an archivelog function that would copy the WAL files as
they're checkpointed to some other location (with destination names
that reflect their order in time), just for starters.

It'd be *awfully* nice if you could issue a command to roll a table
(or, perhaps, a tablespace, if you've got a bunch of foreign keys and
such) back to a particular point in time, from the command line, with
no significant advance preparation (so long as the required files are
still around, and if they're not then abort the operation with the
appropriate error message).  But it doesn't sound like that's what
we're talking about when we talk about PITR...

I wouldn't expect the O***** docs to be particularly revealing about
how the database manages PITR at the file level, but if it does, would
you happen to know where so I can look at it?  What I've seen so far
is very basic and not very revealing at all...

-- 
Kevin Brown					      kevin@sysexperts.com

Re: Incremental backup

Curt Sampson <cjs@cynic.net> — 2003-02-15T07:46:53Z

On Fri, 14 Feb 2003, Bruce Momjian wrote:

> OK, once we have PITR, will anyone want incremental backups?

Well, I'm not entirely clear on how PITR will work, so I may be off-base
here, but it seems to me that offering incremental backups that back
up only changed pages might not be all that big a win, given how
postgres writes its pages. On DBMSs that don't use MVCC, if you change a
particular item in a row ten times, one page is changed. If you do it in
postgres, you could well be changing ten pages, as the system writes the
two copies of the entire row wherever it can find space. So in databases
where a lot of rows are changed, where an incremental backup would
normally be a win because it would be much smaller than the logs over a
given period, it isn't going to be with postgres.

But you know, if we could get rid of redundant changes in the logs we're
using for backup, that could save a lot of space in a situation like
the one I described above. If a particular row and column is changed
fifty times over the course of a month, it's going to be recorded fifty
times in the log. But there's really no need for all fifty of those,
if you don't mind not being able to restore to any time before the
current time. You can reduce the size of the logs you need to store
for backup by throwing away the first forty-nine of those changes, and
keeping only the most recent version. There shouldn't be any worries
about referential integrity, because when you do a restore, you start
with a full backup that is ok, and once you've successfully applied
all the transactions in the log, you know it will be ok again, so any
intermediate states during the restore where integrity is not maintained
are not a problem.

cjs
-- 
Curt Sampson  <cjs@cynic.net>   +81 90 7737 2974   http://www.netbsd.org
    Don't you know, in this new Dark Age, we're all light.  --XTC

Re: Incremental backup

Tom Lane <tgl@sss.pgh.pa.us> — 2003-02-15T08:05:34Z

Curt Sampson <cjs@cynic.net> writes:
> ... But there's really no need for all fifty of those,
> if you don't mind not being able to restore to any time before the
> current time.

Which, of course, is exactly the point of PITR designs.

When you know that your assistant trainee DBA deleted most of your
database with a mistyped command last Tuesday evening around 8pm,
it is cold comfort to know that your database has faithfully preserved
his committed changes.  You want to get back to where you were Tuesday
afternoon, or preferably Tuesday evening 7:59pm.  This is what PITR
setups can do for you.

If you don't feel you need PITR capability, fine ... but don't tell
the people who want it that they have no need for it.

			regards, tom lane

Re: Incremental backup

Curt Sampson <cjs@cynic.net> — 2003-02-15T08:23:49Z

On Fri, 14 Feb 2003, Kevin Brown wrote:

> Oracle has something they call "rollback segments" which I assume are
> separate bits of data that have enough information to reverse changes
> that were made to the database during a transaction, and I figured
> PITR would (or could) apply particular saved rollback segments to the
> current state in order to "roll back" a table, tablespace, or database
> to the state it was in at a particular point in time.

You're right about what rollback segments are; they hold the information
about previous versions of a row, so that if a transaction is rolled
back, the previous data can be restored. (Postgres doesn't need this,
since it doesn't update the rows in place: the old copy of the row stays
as and where it is, and a new copy is made with the new data. [The old
copy can later be removed, after no transactions reference it any more,
with a VACUUM.])

Oracle9i has a new feature called a "flashback query," which uses the
information in the rollback segements to let you query the database
in a previous state. (I.e., "select such and such from this table as
of two hours ago.") Postgres could do this using the older copies of
rows as well, though the performance often wouldn't be pretty, since
your indexes become useless, I believe. (Don't they point to only the
latest copy of a row?) Still, it would be cool and life-saving in some
situations.

But yeah, PITR takes a snapshot and goes in a forward direction, not a
backwards one. This is just what Oracle does, too, using the redo logs.

> For PITR, I assume we'd need an archivelog function that would
> copy the WAL files as they're checkpointed to some other location
> (with destination names that reflect their order in time), just for
> starters.

Well, I'm not sure that you'd really need to have any special archiving
facilities: you just need to let the old files sit there, and keep
creating new ones. You do lose a bit of performance in that you can't
recycle log segments, but you could always just fork off a (well niced)
process to create a new, empty log segment at the time you start in on
the last pre-created one, so that you will have another pre-created one
ready when you finish the current one.

BTW, why exactly do we pre-create log segments, anyway? I see this comment
in backend/access/transam/xlog.c:

    /*
     * Zero-fill the file.  We have to do this the hard way to ensure that
     * all the file space has really been allocated --- on platforms that
     * allow "holes" in files, just seeking to the end doesn't allocate
     * intermediate space.  This way, we know that we have all the space
     * and (after the fsync below) that all the indirect blocks are down
     * on disk.  Therefore, fdatasync(2) or O_DSYNC will be sufficient to
     * sync future writes to the log file.
     */

This seems to imply to me that fdatasync will, when synchronizing the
data blocks of a file, not necessarially synchronize the indirect
blocks, which seems a little...odd. It's not like there's much point
in writing the data to the disk if you can't get to it. I'd understood
fdatasync as just a way to avoid updaing the inode's last changed date.
Are there OSes that implement fdatasync in a way that you could still
lose data?

> It'd be *awfully* nice if you could issue a command to roll a table
> (or, perhaps, a tablespace, if you've got a bunch of foreign keys and
> such) back to a particular point in time, from the command line, with
> no significant advance preparation (so long as the required files are
> still around, and if they're not then abort the operation with the
> appropriate error message).  But it doesn't sound like that's what
> we're talking about when we talk about PITR...

I don't think most people are thinking of that when they think of PITR;
I think they're thinking of applying changes from a log to a previous
version of a database.

And you can't do such a rollback at all, except on an entire database,
because of the potential integrity violations. The best you could do
would be to generate SQL for the changes you'd need to get back to the
previous point, and see if you can execute these changes.

cjs
-- 
Curt Sampson  <cjs@cynic.net>   +81 90 7737 2974   http://www.netbsd.org
    Don't you know, in this new Dark Age, we're all light.  --XTC

Re: Incremental backup

Curt Sampson <cjs@cynic.net> — 2003-02-15T08:26:03Z

On Sat, 15 Feb 2003, Tom Lane wrote:

> Curt Sampson <cjs@cynic.net> writes:
> > ... But there's really no need for all fifty of those,
> > if you don't mind not being able to restore to any time before the
> > current time.
>
> Which, of course, is exactly the point of PITR designs.
>
> When you know that your assistant trainee DBA deleted most of your
> database with a mistyped command last Tuesday evening around 8pm,
> it is cold comfort to know that your database has faithfully preserved
> his committed changes.  You want to get back to where you were Tuesday
> afternoon, or preferably Tuesday evening 7:59pm.  This is what PITR
> setups can do for you.
>
> If you don't feel you need PITR capability, fine ... but don't tell
> the people who want it that they have no need for it.

Hey, I never said you *have* to do this compression!

I envisioned it as an option. I'd like, for example, to be able to aim a
program that the last eight weeks worth of log files and say, "compress
the first seven weeks of this, but leave the last week fully intact."
Then I can save some space (quite a lot, if my updates have certain
characteristics), and yet still get back to Tuesday evening at 7:59 p.m.

cjs
-- 
Curt Sampson  <cjs@cynic.net>   +81 90 7737 2974   http://www.netbsd.org
    Don't you know, in this new Dark Age, we're all light.  --XTC

Re: Incremental backup

Tom Lane <tgl@sss.pgh.pa.us> — 2003-02-15T15:30:15Z

Curt Sampson <cjs@cynic.net> writes:
> Oracle9i has a new feature called a "flashback query," which uses the
> information in the rollback segements to let you query the database
> in a previous state. (I.e., "select such and such from this table as
> of two hours ago.") Postgres could do this using the older copies of
> rows as well,

Yeah, good ol' time-travel.  This was built into Postgres in Berkeley
days, and later ripped out for performance reasons.

> though the performance often wouldn't be pretty, since
> your indexes become useless, I believe. (Don't they point to only the
> latest copy of a row?)

No.  If they did, they'd not work under MVCC.

> BTW, why exactly do we pre-create log segments, anyway?

Partly because the logic is designed to work in the PITR case, but
mostly because we don't want to suffer an out-of-disk-space condition
while we are in the midst of using a log segment.

> This seems to imply to me that fdatasync will, when synchronizing the
> data blocks of a file, not necessarially synchronize the indirect
> blocks, which seems a little...odd.

The comment is being paranoid about whether fdatasync is correctly
implemented everywhere.  Whether you consider this worry justified or
not is academic, since the possible out-of-disk-space failure mode is
reason enough to do it anyway.

			regards, tom lane

Re: Incremental backup

Kevin Brown <kevin@sysexperts.com> — 2003-02-15T22:30:29Z

Curt Sampson wrote:
> Oracle9i has a new feature called a "flashback query," which uses the
> information in the rollback segements to let you query the database
> in a previous state. (I.e., "select such and such from this table as
> of two hours ago.") Postgres could do this using the older copies of
> rows as well, though the performance often wouldn't be pretty, since
> your indexes become useless, I believe. (Don't they point to only the
> latest copy of a row?) Still, it would be cool and life-saving in some
> situations.

That sounds like a really nice feature.  We don't do rollback
segments, but I suppose we could (perhaps at the expense of
performance).

> > For PITR, I assume we'd need an archivelog function that would
> > copy the WAL files as they're checkpointed to some other location
> > (with destination names that reflect their order in time), just for
> > starters.
> 
> Well, I'm not sure that you'd really need to have any special archiving
> facilities: you just need to let the old files sit there, and keep
> creating new ones. You do lose a bit of performance in that you can't
> recycle log segments, but you could always just fork off a (well niced)
> process to create a new, empty log segment at the time you start in on
> the last pre-created one, so that you will have another pre-created one
> ready when you finish the current one.

It's probably just as easy to fork off a well-niced process to copy
the log segments elsewhere.

If you're a shop trying to eke out the last bit of performance from
PostgreSQL, then you're probably going to put the transaction logs on
the fastest bit of disk you've got -- a solid-state disk if you can.
That means the amount of space you've got on the filesystem holding
the logs isn't necessarily all that high.  For PITR you almost
certainly want to put the old log files somewhere else for
safekeeping: since you're not using them anymore and you probably want
to keep a lot of them, the old log files need to go somewhere that has
tons of space and is highly reliable.  For that area, performance is
the least of your concerns, as long as it's fast enough that you can
keep up with the database as it writes transaction logs.

And chances are you'll want a background process that compresses the
old log files to minimize the amount of space they eat.

> It's not like there's much point in writing the data to the disk if
> you can't get to it. I'd understood fdatasync as just a way to avoid
> updaing the inode's last changed date.  Are there OSes that
> implement fdatasync in a way that you could still lose data?

I wouldn't think so.  Otherwise what would be the point of having it?

> And you can't do such a rollback at all, except on an entire database,
> because of the potential integrity violations. The best you could do
> would be to generate SQL for the changes you'd need to get back to the
> previous point, and see if you can execute these changes.

Well, you could do the rollback for all tables involved in the
referential integrity tree and leave it at that, but yeah it's
probably simpler to rollback the whole database.

It would still be a useful feature, but I don't know that it's worth
the upfront performance cost of having to save off rollback segments.
If you did have such a feature you'd want to be able to control
whether or not it's enabled, so that you take the performance hit only
if you need the feature.

-- 
Kevin Brown					      kevin@sysexperts.com