I periodically see people being advised to put their tablspaces on RAM disks or tempfs volumes. This is very bad advice. Do not put a PostgreSQL TABLESPACE on a RAM disk or tempfs.
Why you shouldn’t put a tablespace on a ramdisk
Unlike MySQL and some other databases, PostgreSQL tablespaces are not completely independent of the rest of the database system. You can’t just throw a tablespace away and have the rest of the database system keep on working.
Most importantly, the write-ahead log (WAL) that provides PostgreSQL’s crash safety is stored in pg_xlog, and is shared across all tablespaces. PostgreSQL expects to be able to replay this log in order and without errors after a crash or shutdown. Until the log has replayed, the database is assumed to be in an unsafe state and connections will be refused with:
FATAL: the database system is starting up
If the write-ahead log contains changes to tables/indexes/etc in a tablespace that no longer exists, PostgreSQL cannot replay the WAL. It will get stuck, and will never start up successfully, or will abort startup. You will be unable to access any of your tables on any database in that PostgreSQL instance. PostgreSQL will fail to start with an error like:
LOG: could not open tablespace directory "pg_tblspc/16389/PG_9.4_201405111": No such file or directory
LOG: database system was not properly shut down; automatic recovery in progress
LOG: redo starts at 0/89ECF00
FATAL: could not create directory "pg_tblspc/16389/PG_9.4_201405111": No such file or directory
CONTEXT: xlog redo insert: rel 16389/13060/16390; tid 8283/206
LOG: startup process (PID 28209) exited with exit code 1
LOG: aborting startup due to startup process failure
PostgreSQL can’t just throw the changes in the transaction logs away, because it doesn’t know you meant to remove the tablespace. What if that’s your tablespace for vitally important accounting data on a separate encrypted hard drive, and the admin hasn’t entered the password required to mount the drive yet? Or, more likely, what if you moved it and forgot to update the tablespace symlink? You don’t want PostgreSQL saying “oh, I guess you didn’t want that data after all”.
So. Don’t put a tablespace on a ramdisk.
The same problem affects removable storage. It’s fine to put a tablespace on removable storage, but you can’t remove the storage until you first drop the tablespace.
How this could improve
There are a few improvements we could make to PostgreSQL in this area. I’ve already helped make one of them: 9.4 now has a warning about this admin error in the documentation.
One useful, albeit platform specific option, would be to try to detect temporary storage and warn the user. It would be ultimately pretty futile, though – what about removable storage? What if the user creates the tablespace on durable storage then moves the symlink? What about a SAN volume on non-durable storage? etc. It’d be at best an attempt to catch obvious admin mistakes. It might still be useful, but it’s not clear that it’s worth adding the complexity required to implement it.
Instead, we need two things:
- An option that can be used to recover from this mistake. Akin to zero_damaged_pages, the (currently nonexistent) discard_missing_tablespaces option would throw away all WAL writes destined to tablespaces that do not currently exist. It would have to mark the tablespace as broken in the system catalogs so all attempts to access it failed. The admin could restart PostgreSQL with this option enabled to recover from an error like this, or could even run with it always enabled if they regularly used tablespaces on temporary storage
- A TEMPORARY TABLESPACE feature that marks a tablespace as disposable. If PostgreSQL sees WAL writes to a tablespace marked TEMPORARY and it doesn’t exist, it would discard the writes and drop the tablespace.
Recovery from lost tablespaces
I’ll write about options for recovering databases where you’ve discarded/lost an in-use tablespace in a follow-up post, along with some suggestions on what you can do safely as an alternative. In the mean time, here’s a Stack Overflow post I wrote a while ago about how to improve PostgreSQL’s performance for unit/integration testing without relying on ramdisks.
If you’re seeing errors like:
ERROR: could not open file "pg_tblspc/16384/PG_9.4_201405111/13060/16385": No such file or directory
from a psql session, you might just be able to DROP TABLESPACE and DROP TABLE. Recovery is only a serious problem when the database cannot start because of WAL records that cannot be replayed.
At least on PostgreSQL 9.4, you can also just create an empty directory where PostgreSQL expects to find the tablespace. It will assume that you’ve lost the tablespace contents and will proceed with recovery, discarding changes that apply to that tablespace.