PostgreSQL – Can Databases Be Attached/Detached on the Fly?

postgresql

Is it possible to attach/detach databases to PostgreSQL on the fly, i.e. while the server is running without having to restart the service? I can't find anything related to this in the documentation.

What I am trying to achieve is to have the user be able to select different data directories from an archive location and then have them loaded into PostgreSQL, and later removed when the user is finished.

Best Answer

No, short answer is no. This has been asked at the PostgreSQL development mailing list at 2011: Detach/attach database and the many replies in summary said "yes, this is feasible to implement but quite complicated to do so.":

You would have to do quite some surgery because of oids from shared tables. I don't think thats easily doable.

Andres Freund

The main reason this doesn't work is XID management.
It's barely possible you could make it work if you first locked all other sessions out of the DB and then froze every XID in the database, but that's a sufficiently heavyweight operation to make it of dubious value.
...

Tom Lane

I have to admit I've thought about this from time to time, and it would be pretty cool. I was initially thinking that it wouldn't be that difficult to do this on a per-database level, because if you slurp up a whole database then by definition you're also including the system catalogs, which means that you have the pg_class, pg_attribute, and pg_type entries that are necessary to interpret the table contents. If you do anything more fine-grained (per-tablespace, per-table, or whatever) then things get much more complex, but at the database level you only need to worry about interactions with other globals: tablespace and role definitions. And we could probably write code to grovel through the system catalogs for a newly "mounted" database and do search and replace on the appropriate columns, to map from the old OIDs to the new ones. It wouldn't be simple, but I think it could be done.

But Tom's point about XIDs and LSNs seems like it kind of puts a bullet through the heart of the whole idea. Now, before you can move the database (or table, or whatever) between clusters, you've got to rewrite all the data files to freeze XIDs and, I don't know, zero out LSNs, or something. And if you're going to rewrite all the data, then you've pretty much lost all the benefit of doing this in the first place. In fact, it might end up being slower than a dump and restore; even an uncompressed dump will be smaller than the on-disk footprint of the original database, and many dumps compress quite well.

Robert Haas

What about having database-level XIDs rather than cluster-level? Is that remotely feasible?

Thom Brown

Maybe. You'd need a set separate set for shared catalogs, too. It seems like a heck of a lot of work, though, especially since (IME, anyway) most people only really one run one database per cluster.

Robert Haas

Leaving aside all the internal implementation details / problems that would make this possible, the last comment has something that may be helpful to you:

Run a different cluster per database. You would probably need to implement something of an infrastructure so when a user wants to "attach" a database, it would start up the related cluster and stop it when the user wants to "detach" it.

Related Solutions

Postgresql – Multiple databases, in multiple geographic locations while preserving PostgreSQL triggers and data consistency

Streaming replication fires the triggers on the master only. The slaves never see the actual table writes, but rather re-run the recovery logs from the master. Because this is essentially a recovery situation, it means that the binary tables are guaranteed to be brought up to a consistent level. No triggers are necessary. Triggers may be possible with Slony or Bucardo but I am not 100% sure. My initial thought suggests these should allow triggers in downstream databases but you don;t want to run the same triggers on both databases!

In current releases you can't cascading replication outside of Slony. In 9.2 streaming replication will allow for cascading.

Hope this helps.

PostgreSQL – Fix ‘Database Cluster Initialization Failed’ Error

It appears that the root cause of this issue may have been that @Celeritas's computer had an incorrect value for the COMSPEC environment variable. It had a trailing semicolon, so instead of the normal:

C:\Windows\system32\cmd.exe

it was:

C:\Windows\system32\cmd.exe;

This one-character difference is enough. The above isn't a valid command prompt path, so popen() calls were failing. Unfortunately, instead of something useful like:

'"C:\Windows\system32\cmd.exe;"' is not recognized as an internal or external command, operable program or batch file

it instead reports the delightfully useful error No error:

initdb: could not execute command ""C:/Program Files/PostgreSQL/9.2/bin/postgres.exe" --boot -x1 -F ": No error

See related:

I've reported this to the installer team and written a wiki entry to describe it. See blog post.

The issue was eventually resolved by:

Editing the COMSPEC environment variable
Uninstalling PostgreSQL
Rebooting
Reinstalling PostgreSQL

though there was a lot more before that which shouldn't actually be necessary to resolve this, including doing a total manual clean uninstall of PostgreSQL by hand.

Now I just want to find the people who wrote the program that modified this environment variable.

Best Answer

Related Solutions

Postgresql – Multiple databases, in multiple geographic locations while preserving PostgreSQL triggers and data consistency

PostgreSQL – Fix ‘Database Cluster Initialization Failed’ Error

Related Question