Database Reference
In-Depth Information
F
Once the restore is complete, you can then dump the tables in the tablespace by
following the recipe
Hot
logical
backup
of
all
tables
in
a
tablespace
.
F
Now, recreate the tables into the original server and database, using parallel tasks
to speed things along. This can be executed remotely without needing to transfer
dumpfile between systems like the following:
pg_restore -d mydatabase -j 2 dumpfile
PHYSICAL:
To recover a single tablespace from a physical backup, we need to:
F
Find a suitable server, or create a new virtual server.
F
Recover database server in full, as described in previous recipes on physical recovery,
including all databases and all tables. You may wish to stop at a useful point in time,
in which case you can look at the recipe on that topic later in the chapter.
F
Once the restore is complete, you can then dump the tables in the tablespace by
following the recipe
Hot
logical
backup
of
all
tables
in
a
tablespace
.
F
Now, recreate the tables into the original server and database, using parallel tasks
to speed things along. This can be executed remotely without needing to transfer
dumpfile between systems like the following:
pg_restore -d mydatabase -j 2 dumpfile
There's more...
When recovering from a custom backup file (
-F c
), you can also use the
-l
option to list out
the contents of the archive. You can then edit that file to remove, comment out, or reorder the
actions.
pg_restore
can then reuse the list file as input, using the
-L
option.
Recovery of a dropped/damaged database
Recovering a complete database is also sometimes required. It's actually a lot easier than
recovering a single table. Many users choose to place all their tables in a single database; in
that case this recipe isn't relevant.
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
):
