Re: [HACKERS] Incremental Backup Script
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
-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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
