Re: [HACKERS] Incremental Backup Script

2006-01-09 Thread Gregor Zeitlinger
 As per docs, if the databases are rarely updated it could take a long
 time for the WAL segment to roll over. 
Yes, therefore I want to copy the current WAL (as I said earlier).
When restoring, I also want to make sure that I restore exactely to the point 
when I copied the current WA segment.

Hence I consider to do it as follows:
1) take the t = current time
2) copy the current WAL
3) when restoring, set  recovery_target_time  = t
 
Maybe there is even a way to ask Postgres of its last commited x = xid.
In that case, we could set recovery_target_xid = x
Is that possible?
 
Regards,
 
Gregor

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly


Re: [HACKERS] Incremental Backup Script

2006-01-04 Thread Gregor Zeitlinger
-Original Message-
From: Zach Bagnall [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, January 04, 2006 4:42 AM
To: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Incremental Backup Script


Gregor: can you explain how to identify the current file? I had 
implemented a backup and restore script for PITR but stumbled at this 
point. The page above does not specify how this is to be done.
I have found a way from 
http://archives.postgresql.org/pgsql-admin/2005-10/msg00059.php. I have not 
tried it, but it seems straightforward.

I appreciate the addition of PITR - it's better than nothing (nothing 
being full dumps) in some respects. Ideally, we need to be able to dump 
deltas for a single database. 
Yes, it is not a replacement for an incremental backup, especially due to the 
fact that WALs may be orders of magnitude larger than a delta.

In practice, restoration using the PITR 
method is awkward.
Yes, what I am planning to do:
1) drop the corrupted database
2) restore the base backup
3) replay all incremental backups (in the sense of my original mail)

Gregor Zeitlinger
LUCAS Product Development

Torex Retail Solutions GmbH

Schwedenstr. 9, D-13359 Berlin 
Tel. +49 (0) 30 49901-243
Fax +49 (0) 30 49901-139
 
Mailto:[EMAIL PROTECTED]
http://www.torexretail.de

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq


Re: [HACKERS] Incremental Backup Script

2006-01-03 Thread Zach Bagnall

On 12/26/05 11:04, Qingqing Zhou wrote:

Gregor Zeitlinger [EMAIL PROTECTED] wrote

Also, I was wondering whether it is always safe to copy the current WAL 
file, i.e. may the current WAL file be invalid in any circumstance?




If you mean current WAL file is the xlog segment in use, then it is 
dangerous. We only backup the xlog segments that have been fully used up.


As per docs, if the databases are rarely updated it could take a long 
time for the WAL segment to roll over. We need to backup the current 
segment to guarantee we have the latest trasactions archived at time of 
failure.


http://www.postgresql.org/docs/8.1/interactive/backup-online.html
If you are concerned about being able to recover right up to the 
current instant, you may want to take additional steps to ensure that 
the current, partially-filled WAL segment is also copied someplace. This 
is particularly important if your server generates only little WAL 
traffic (or has slack periods where it does so), since it could take a 
long time before a WAL segment file is completely filled and ready to 
archive. One possible way to handle this is to set up a cron job that 
periodically (once a minute, perhaps) identifies the current WAL segment 
file and saves it someplace safe.


Gregor: can you explain how to identify the current file? I had 
implemented a backup and restore script for PITR but stumbled at this 
point. The page above does not specify how this is to be done.


I appreciate the addition of PITR - it's better than nothing (nothing 
being full dumps) in some respects. Ideally, we need to be able to dump 
deltas for a single database. In practice, restoration using the PITR 
method is awkward. I guess you would tarball the current data files, do 
a full restore, do a full dump of the database you are interested in, 
ditch the restored data files and replace them with the ones you 
tarballed, then do a database load from the full dump. The only way to 
avoid having the other databases on the server offline is to restore to 
a second postgresql instance. Not complaining, just saying :-)





Regards,
Qingqing 


Zach.

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq


Re: [HACKERS] Incremental Backup Script

2006-01-03 Thread Rick Gigger

I would certainly like some instructions on this as well.

On Jan 3, 2006, at 8:41 PM, Zach Bagnall wrote:


On 12/26/05 11:04, Qingqing Zhou wrote:

Gregor Zeitlinger [EMAIL PROTECTED] wrote
Also, I was wondering whether it is always safe to copy the  
current WAL file, i.e. may the current WAL file be invalid in any  
circumstance?


If you mean current WAL file is the xlog segment in use, then it  
is dangerous. We only backup the xlog segments that have been  
fully used up.


As per docs, if the databases are rarely updated it could take a  
long time for the WAL segment to roll over. We need to backup the  
current segment to guarantee we have the latest trasactions  
archived at time of failure.


http://www.postgresql.org/docs/8.1/interactive/backup-online.html
If you are concerned about being able to recover right up to the  
current instant, you may want to take additional steps to ensure  
that the current, partially-filled WAL segment is also copied  
someplace. This is particularly important if your server generates  
only little WAL traffic (or has slack periods where it does so),  
since it could take a long time before a WAL segment file is  
completely filled and ready to archive. One possible way to handle  
this is to set up a cron job that periodically (once a minute,  
perhaps) identifies the current WAL segment file and saves it  
someplace safe.


Gregor: can you explain how to identify the current file? I had  
implemented a backup and restore script for PITR but stumbled at  
this point. The page above does not specify how this is to be done.


I appreciate the addition of PITR - it's better than nothing  
(nothing being full dumps) in some respects. Ideally, we need to be  
able to dump deltas for a single database. In practice, restoration  
using the PITR method is awkward. I guess you would tarball the  
current data files, do a full restore, do a full dump of the  
database you are interested in, ditch the restored data files and  
replace them with the ones you tarballed, then do a database load  
from the full dump. The only way to avoid having the other  
databases on the server offline is to restore to a second  
postgresql instance. Not complaining, just saying :-)





Regards,
Qingqing


Zach.

---(end of  
broadcast)---

TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq




---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly


Re: [HACKERS] Incremental Backup Script

2005-12-26 Thread Simon Riggs
On Sun, 2005-12-25 at 14:02 +0100, Gregor Zeitlinger wrote:
 as far as I have understood, the WAL backup that you control via
 archive_command is the PostgreSQL equivalent to what other databases
 let you do with an incremental backup

No it is not an incremental backup of changed data blocks, it is a
transactional log archival. So, other parts of your thinking are
slightly off - but not by much. The only way to do a partial recovery is
to follow the PITR notes.

Best Regards, Simon Riggs




---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match


Re: [HACKERS] Incremental Backup Script

2005-12-25 Thread Qingqing Zhou

Gregor Zeitlinger [EMAIL PROTECTED] wrote

 Also, I was wondering whether it is always safe to copy the current WAL 
 file, i.e. may the current WAL file be invalid in any circumstance?


If you mean current WAL file is the xlog segment in use, then it is 
dangerous. We only backup the xlog segments that have been fully used up.

Regards,
Qingqing 



---(end of broadcast)---
TIP 6: explain analyze is your friend


Re: [HACKERS] Incremental backup

2003-02-15 Thread Curt Sampson
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  [EMAIL PROTECTED]   +81 90 7737 2974   http://www.netbsd.org
Don't you know, in this new Dark Age, we're all light.  --XTC

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])



Re: [HACKERS] Incremental backup

2003-02-15 Thread Tom Lane
Curt Sampson [EMAIL PROTECTED] 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

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])



Re: [HACKERS] Incremental backup

2003-02-15 Thread Marc Munro
Kevin,
Hi.  I was looking into PITR for PostgreSQL myself about a year back but
life intervened.  I am an Oracle DBA so may be able to help you with an
understanding of how Oracle does this.

You 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.
 
 As it is, it sounds like PITR is a bit less refined than I expected.

Actually Oracle uses its redo logs (like PostgreSQL' WAL) and
archivelogs (copies of old redo logs) rather than rollback segments.

The way it works, more or less, is that you start from a hot backup, and
roll-forward using archivelogs and redo logs to the point in time to
which you wish to recover.  The whole point of this is not so much that
we can restore our database to some point in the past, as that we can
recover to just before some disaster struck, from a starting point of a
previous hot backup.  In fact, the whole PITR thing for Oracle seems to
be simply an enabling technology for hot backups.  This seems to me to
be one of the killer enterprise features that PostgreSQL currently
lacks.

 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.

I believe that three things are needed:
1) filesystem-based hot backups.  It may be possible to simply copy the
database files even though they would be open.  Oracle places each
tablespace into a hot-backup mode prior to taking the copy. In this
mode, updates to the files are queued-up to be applied once the
tablespace is taken out of hot-backup mode.  This all seems quite tricky
and will slow the database down.

2) Auto-archiving of WAL files.  Just as you suggest.

3) A recovery controller that can figure out the state of the restored
database, and manage the reapplication of archived and current WAL
files.
 
 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...

Nice but lots of work for questionable benefit.  If your application
needs to keep track of history there are better ways to do it.  If not,
then PITR should be seen as an exceptional circumstance and so need not
be so easy to manage.
 
 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...

The best Oracle documentation on this is available through Oracle
technet (technet.oracle.com) for which you will have to need to
register.  Look for documentation-Oracle 9i Documentation-list of
books-Backup and Recovery Concepts.

-- 
Marc[EMAIL PROTECTED]

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html



Re: [HACKERS] Incremental backup

2003-02-15 Thread Kevin Brown
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   [EMAIL PROTECTED]

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html



Re: [HACKERS] Incremental backup

2003-02-15 Thread Tom Lane
Curt Sampson [EMAIL PROTECTED] 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

---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly



Re: [HACKERS] Incremental backup

2003-02-15 Thread Curt Sampson
On Sat, 15 Feb 2003, Tom Lane wrote:

 Curt Sampson [EMAIL PROTECTED] 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  [EMAIL PROTECTED]   +81 90 7737 2974   http://www.netbsd.org
Don't you know, in this new Dark Age, we're all light.  --XTC

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html



Re: [HACKERS] Incremental backup

2003-02-14 Thread Kevin Brown
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   [EMAIL PROTECTED]

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster



Re: [HACKERS] Incremental backup

2003-02-14 Thread Christopher Browne
A long time ago, in a galaxy far, far away, [EMAIL PROTECTED] (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

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])



Re: [HACKERS] Incremental backup

2003-02-14 Thread Kevin Brown
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   [EMAIL PROTECTED]

---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly



Re: [HACKERS] Incremental backup

2003-02-14 Thread Martin Marques
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  |[EMAIL PROTECTED]
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



Re: [HACKERS] Incremental backup

2003-02-14 Thread Manfred Koizar
On Thu, 13 Feb 2003 19:24:13 -0500, Patrick Macdonald
[EMAIL PROTECTED] 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

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]



Re: [HACKERS] Incremental backup

2003-02-14 Thread Bruce Momjian

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  |[EMAIL PROTECTED]
 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
  [EMAIL PROTECTED]   |  (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 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])



Re: [HACKERS] Incremental backup

2003-02-14 Thread Martin Marques
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  |[EMAIL PROTECTED]
Programador, Administrador, DBA |   Centro de Telematica
   Universidad Nacional
del Litoral
-


---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly



Re: [HACKERS] Incremental backup

2003-02-14 Thread Greg Copeland
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 [EMAIL PROTECTED]
Copeland Computer Consulting


---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly



Re: [HACKERS] Incremental backup

2003-02-14 Thread Curt Sampson
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  [EMAIL PROTECTED]   +81 90 7737 2974   http://www.netbsd.org
Don't you know, in this new Dark Age, we're all light.  --XTC

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])



Re: [HACKERS] Incremental backup

2003-02-13 Thread Bruce Momjian
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
  [EMAIL PROTECTED]   |  (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 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])



Re: [HACKERS] Incremental backup

2003-02-13 Thread Rod Taylor
 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 [EMAIL PROTECTED]

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



signature.asc
Description: This is a digitally signed message part


Re: [HACKERS] Incremental backup

2003-02-13 Thread Patrick Macdonald
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

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster



Re: [HACKERS] Incremental backup

2003-02-13 Thread Patrick Macdonald
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  |[EMAIL PROTECTED]
  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
   [EMAIL PROTECTED]   |  (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

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]



Re: [HACKERS] Incremental backup

2003-02-13 Thread Bruce Momjian
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
  [EMAIL PROTECTED]   |  (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 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]



Re: [HACKERS] Incremental backup

2003-02-13 Thread Patrick Macdonald
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

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html



Re: [HACKERS] Incremental backup

2003-02-12 Thread Bruce Momjian

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  |[EMAIL PROTECTED]
 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
  [EMAIL PROTECTED]   |  (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: [HACKERS] Incremental backup

2003-02-12 Thread Christopher Kings-Lynne
 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


---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly



Re: [HACKERS] Incremental backup

2003-02-12 Thread Bruce Momjian
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
  [EMAIL PROTECTED]   |  (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 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly