Database Reference
In-Depth Information
There's more...
pg_dump cannot be used as a base backup for a PITR. The reason is that a log replay
contains the physical changes to data blocks, not logical changes based upon Primary Keys.
If you reload a pg_dump the data will likely go back into different data blocks, so the changes
wouldn't correctly reference the data.
WAL doesn't contain enough information to reconstruct all SQL fully that produced those
changes. Later feature additions to PostgreSQL may add the required information to WAL.
See also
Planned in 9.1 is the ability to pause/resume/stop recovery, and to set recovery targets while
the server is up dynamically. This will allow you to use the Hot Standby facility to locate the
correct stopping point more easily.
You can trick Hot Standby into stopping recovery, which may help. See the recipe on managing
Hot Standby.
Recovery of a dropped/damaged table
You may drop or even damage a table in some way. Tables could be damaged for physical
reasons, such as disk corruption, or they could also be damaged by running poorly specified
UPDATEs/DELETEs, which update too many rows or overwrite critical data.
It's a common request to recover from this situation from a backup.
How to do it...
The methods differ, depending upon the type of backup you have available. If you have
multiple types of backup, you have a choice.
LOGICAL (from custom dump -F c ):
If you've taken a logical backup using pg_dump into a custom file, then you can simply extract
the table you want from the dumpfile like the following:
pg_restore -t mydroppedtable dumpfile | psql
or connect direct to the database using -d .
The preceding command tries to re-create the table and then load data into it. Note that pg_
restore -t option does not dump out any of the indexes on the table selected. That means
we need a slightly more complex procedure than it would first appear, and the procedure
needs to vary depending upon whether we are repairing a damaged table or putting back a
dropped table.
 
Search WWH ::




Custom Search