Copying a tablespace from one postgresql instance to another

Question

I'm looking for a way to quickly "clone" a database from 1 postgresql server to another.

Assuming...

• I have a postgresql server running on HostA, serving 2 databases
• I have 2 devices mounted on HostA, each device stores the data for one of the database (i.e. 1 database => 1 tablespace => 1 device )
• I am able to obtain a "safe" point in time snapshot through some careful method
• I am able to consistently and safely produce a clone of any of the 2 devices used (I could even assume both database only ever receive reads)
• The hosts involved are CentOS 5.4

Is it possible to host a second postgresql server on HostB, mount one of the cloned devices and get the corresponding database to "pop" into existence? (Kind of like when you copy the MyISAM table files in MySQL).

If this is at all possible, what is the mechanism (i.e. what DDL should I look into or pg commands)?

It's important for me to be able to move individual databases in isolation of each other. But if this isn't possible, would a similar approach work at a server level (trying to clone and respawn a server by copying the datadir over to a host with the same postgresql install) ?

Answer 1

Not easily, because there are a number of files that are shared between databases which means each database in the same install is dependent on this.

You can do it at the server level, or at the cluster level, but not at individual database level. Just be sure to copy/clone over the whole data directory and all external tablespaces. As long as you can make the clone atomically (either on the same filesystem or with a system that can do atomic clones across filesystems), you don't even need to stop the database on hostA to do it.