Database Reference
In-Depth Information
How to do it...
How do you decide to what point to recover? The point where we stop recovery is known as
the "recovery target". The most straightforward way is to do this based upon a timestamp.
In the
recovery.conf
, you can add (or uncomment) a line that says the following:
recovery_target_time = '2010-06-01 16:59:14.27452+01'
or similar. Note that you need to be careful to specify the time zone of the target, so that it
matches the time zone of
the
server
that
wrote
the
log
. That might differ from the time zone of
the current server, so check.
After that, you can check progress during a recovery by running queries in Hot Standby mode.
How it works...
Recovery works by applying individual WAL records. These correspond to individual block
changes, so there are many WAL records to each transaction. The final part of any successful
transaction is a commit WAL record, though there are abort records as well. Each transaction
completion record has a timestamp on it that allows us to decide whether to stop at that point
or not.
You can also define a recovery target using a transaction id (xid), though finding out which xid
to use is somewhat difficult, and you may need to refer to external records if they exist.
The recovery target is specified in the
recovery.conf
and cannot change while the server
is running. If you want to change the recovery target, you can shutdown the server, edit the
recovery.conf
, and then restart the server. Be careful though, if you change the recovery
target and recovery is already passed the point, it can lead to errors. If you define a recovery_
target_timestamp that has already passed, then recovery will stop almost immediately, though
this will be later than the correct stopping point. If you define a recovery_target_xid that has
already passed, then recovery will just continue to the end of logs. Restarting recovery from
the beginning using a fresh restore of the base backup is always safe.
Once a server completes recovery, it will assign a new "timeline". Once a server is fully
available, we can write new changes to the database. Those changes might differ from
changes we made in a previous "future history" of the database. So we differentiate between
alternate futures using different timelines. If we need to go back and run recovery again, we
can create a new server history using the original or subsequent timelines. The best way to
think about this is that it is exactly like a
Sci-Fi
novel—you can't change the past but you can
return to an earlier time and take a different action instead. But you'll need to be careful not
to confuse yourself.
