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" <[EMAIL PROTECTED]> 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