Understood all around - thanks to everyone for helping me clarify (in my
head/understanding) how these pieces fit together.
One last request - can you glance over the plan below and let me know if
it sounds sane? The goal again is to be able to recover to a PITR record
in the very recent past after a mistake (accidental deletion) occurs.
This is not meant to be a substitute for a real backup strategy:
1. On a daily basis, run a hot backup more/less as described in this
Wiki article on postgresqlforums.com: http://shorl.com/naprofamynone
2. But instead of creating a .tar.gz file, simply 'rsync' the entire
$PGDATA dir to another directory stored on a local disk.
3. When an accidental deletion occurs, shutdown the database, restore
the $PGDATA dir from yesterday's rsync snapshot (after saving any
unarchived pg_xlogs), and use the normal recovery process to effectively
'roll back' the database state to what it was just prior to the accident.
Thanks again for all the helpful comments and clarifications. I am now a
more clueful person as a result ;)
-jason
Erik Jones wrote:
On Jul 2, 2007, at 11:58 PM, Jason L. Buberel wrote:
I am now learning that fact, but recall the original scenario that I
am trying to mimic:
1. Person accidentally deletes contents of important table.
2. Admin (me) wants to roll back db state to just prior to that
deletion.
3. (Me) Assumes that by creating a recovery.conf file and setting the
target to a an earlier trxn id and restarting the database would
simply do the trick.
So now I think that my scenario should look more like:
1. Person accidentally deletes contents of important table.
2. Admin (me) wants to roll db state back to just prior to that delete.
3. (Me) Performs steps, in addition to creating the recovery.conf
with the selected xid, that will cause the DB to restart at that PITR.
Now all I need to lock down are those 'additional steps needed to
force the recovery process to only recover up to the specified xid
and no further'.
Such as:
- Remove from pg_xlog all of the log files containing transactions
that come after the selected xid?
- Other?
-jason
Whoa. If what you're asking is for a database level rollback or undo
type of function, I'm pretty sure that's not doable in the way you're
asking. Once a postgres cluster (data/*) has committed or rolled back
transactions, you can not roll that particular cluster instance back,
i.e. you can not stop the database and have it start from some
previous transaction state and stop at an arbitrary point. The only
way to do this is if you have a filesystem level backup from a point
in time previous to the point to which you wish to return along with
all of the transaction logs from just before the point where the
backup was taken up to the point to which you wish to return, which
you can then bring up in recovery mode and have it play up until a
transaction id you specify.
The main point here is that PITR requires and, is run on, a base backup.
Erik Jones
Software Developer | Emma®
[EMAIL PROTECTED]
800.595.4401 or 615.292.5888
615.292.0777 (fax)
Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com
