I think that I now see the error
of my ways.
When I shutdown my server, the files under the ./data/ directory still
all point to 'now' and not 'a week ago when the backups were taken'. So
the recover process insists on bringing the database to a PITR equal to
'now'.
Instead, in order to achieve my goal I would have to restore to that
backup, and rely on the contents of the archive_logs to have the
recovery process return me to the selected xid PITR.
So is there any way to 'trick' or force the server to forget what it
thinks 'now' is and instead to step back to the selected xid and make
that the new version of 'now'?
-jason
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
Tom Lane wrote:
"Jason L. Buberel" <jason@xxxxxxxxxxx> writes:
## stopped and started postgres, following syslog output:
You seem to have omitted all the interesting details about what you did
here; but "stopping and starting" postgres is certainly not intended to
cause it to discard data. There would need to have been some steps
involving restoring a previous base backup and rolling forward through
archived xlog files.
regards, tom lane
---------------------------(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
|
