Is it possible to do a tablespace recovery in PostgreSQL? The recoveries I have been testing have been full database. However, if it is possible to do just a tablespace recovery, I need to find out how to do it and test/document it.
You didn't mention which version of PostgreSQL you are using, but a quick Google search found online documentation that indicates it is possible and provides details on the methodology. The site has manuals for versions from 7.4 to 8.3, hopefully one will work.
We are using 8.3.6 currently. I have created a hotbackup script using the pg_hotbackup perl script. However, I looked at the documentation again and found the following paragraph.
"As with the plain file-system-backup technique, this method can only support restoration of an entire database cluster, not a subset. Also, it requires a lot of archival storage: the base backup might be bulky, and a busy system will generate many megabytes of WAL traffic that have to be archived. Still, it is the preferred backup technique in many situations where high reliability is needed. "
It looks like it does not allow me to do just a tablespace recovery.
I believe the main issue is that it is entirely possible to have a table in any one of a number of individual tablespaces, indexes possibly in others, etc. The problem is thus ensuring that one backed up tablespace is of the exact same 'vintage' as the rest of the tablespaces. (all related database objects would have to be of the same vintage in order to be consistent.)
"Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
"I have my standards. They may be low, but I have them!" - Bette Middler
"It's a book about a Spanish guy named Manual. You should read it." - Dilbert