PostgreSQL – How to Unmount, Detach, or Deactivate Database

postgresqlpostgresql-9.3

I manage a server that serves several web applications that use PostgreSQL as a data store. Each web application has a separate role and database, and each database is stored exclusively on a dedicated tablespace on a different logical file system.

What I would like to be able to do is unmount a web app's file system without affecting postgres – if I do that at the moment CHECKPOINTs fail to run, so CREATE DATABASE, VACUUM, and other commands fail.

Is there a way to "deactivate" a database so that PostgreSQL won't try to checkpoint or autovacuum it?

Best Answer

This is not possible with PostgreSQL at this time.

What you're talking about is more like a "quiesce" or "stop" than a "detach". You don't seem to want to be able to move it from one PostgreSQL instance to another.

The issues are:

  • Transaction IDs are global. Pg would have to stop all writes to the DB then VACUUM FREEZE it, which is quite expensive, to make sure there would be no txid wrap-around issues when it was re-enabled.

  • Pg needs to be able to tell if a database contains objects that depend on a global object like a user, so when you DROP USER it can reject the attempt if it'd violate dependencies.

  • ... and probably more

I don't think it's impossible, theoretically, but it's not going to be trivial to impliment. It also won't be very useful - I don't see what you gain from this over simply leaving the DB unused, or at most forcing a VACUUM FREEZE. Checkpoint activity is insignificant to nil if there's no activity in the DB, same with autovacuum.

Related Question