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®
erik@xxxxxxxxxx
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
|
