postgresql when it's not your job

16:17

You Cannot Recover From the Loss of a Tablespace

10 March 2013

tl;dr: Each and every tablespace is critical to the operation of your PostgreSQL database. If you lose one, you’ve lost the entire database.

This one can be short and sweet: If you use tablespaces in PostgreSQL, each and every one of them is a critical part of your database. If you lose one, your database is corrupted, probably irretrievably. Never, ever think that if you lose a tablespace, you’ve just lost the data in the tables and indexes on the tablespace; you’ve lost the whole database.

In a couple of cases, clients have had what they thought were clever uses of tablespaces. In each case, they could have lead to disaster:

  1. “We’ll keep cached and recent data on a tablespace on AWS instance storage on SSDs, and the main database on EBS. If the instance storage goes away, we’ll just recreate that data from the main database.”

  2. “We’ll keep old historical data on a SAN, and more recent data on local storage.”

In each case, there was the assumption that if the tablespace was lost, the rest of the database would be intact. This assumption is false.

Each and every tablespace is a part of your database. They are the limbs of your database. PostgreSQL is an elephant, not a lizard; it won’t regrow a limb it has to leave behind. Don’t treat any tablespace as disposable!

There are 9 comments.

kustodian at 10:41, 11 March 2013:

I don’t agree on one condition and that is when a table space consist of indices only. In that case if that table space is lost, you can recreate the indices with reindexdb. That way you can put a table space for indices on an SSD and not worry about data loss after you replace the faulty drives.

Xof at 10:50, 11 March 2013:

That’s true if you recreate the PG_ directory in the tablespace; otherwise, you get the error:

reindexdb: reindexing of database "test" failed: ERROR: could not create directory "pg_tblspc/69944/PG_9.2_201204301/61884": No such file or directory

However, that’s not a guaranteed feature of PostgreSQL, and I would never rely on it.

Magnus at 13:35, 11 March 2013:

Sure you can recover from a lost tablespace. It’s not going to be pretty of course, but there are certainly ways to recover to a point where you can at least get rid of it.

I do agree with your final comment though – don’t treat them as disposable. Unless you put temporary tables only on them!

Marti at 16:24, 11 March 2013:

Sounds like a missing feature to me — the ability to discard xlog records for certain tablespaces in recovery. Far from “irretrievably corrupted”, just a small matter of coding.

But of course your production database shouldn’t rely on code that hasn’t been written yet. :)

Xof at 10:42, 12 March 2013:

Well, sure, you can edit pg_catalog… but that’s a great and desperate cure, not something you do routinely during recovery from a hardware failure. :)

Yang at 23:38, 30 April 2013:

I was asking about this on the PG mailing list and this blog post was brought up.

For future readers’ situational awareness:

http://www.postgresql.org/message-id/19786.1367378009@sss.pgh.pa.us

Xof at 09:25, 1 May 2013:

With all due to respect to the estimable Mr Lane, I stand by my thesis, which is: Treating a tablespace as disposable is a poor operational decision. It is true that you can patch a database back together to recover. But, as a system designer, if I heard, “And then we patch the database back together” as a routine operational step, I’d probably want that one to be rethought.

Craig Ringer at 18:57, 15 June 2014:

I wrote about this in some more detail here:

http://blog.2ndquadrant.com/postgresql-no-tablespaces-on-ramdisks/

and some advice on alternatives to tablespaces on temp storage here:

http://stackoverflow.com/a/9407940/398670

Craig Ringer at 21:04, 15 June 2014:

Also, at least in 9.4, Pg will replay past a missing tablespace, discarding WAL that applies to it, if you just recreate an empty directory for the tablespace.