Use data in a tablespace to populate another postgres database instance without actually inserting

Question

I have a postgres database (version 8.4) with many tables. Some tables are huge in size, but the data is static (common for any instance). I have stored such tables in a separate tablespace. Is there a way to use this existing data. Or can I create another database instance (may be on a separate server) and create empty bigTable and point to the existing tablespace to populate the data in the existing bigTable?

Can I use the existing data in a tablespace to be populated in another postgres database instance without actually inserting the data, as reinserting the whole data takes so much time.

I tried updating the pg_class table to point to the existing reltablespace and relfilenode. But it didn't help me fully since I was not able to change the database OID. Is there a way so that we can create a database instance on another database server with he same database OID?

• András Váczi: Manually updating system tables is thought to be very bad idea. With a bit of luck you can corrupt your database beyond repair.

Answer 1

A table belongs to one database. Period. You can share a tablespace among databases of the same DB-cluster, but you cannot share a table.

But you can use dblink for what you have in mind. This way you can query tables from a different database - even from a different DB-cluster on a different server. Simple SELECT queries on the same server are very fast. Try this search for code examples.

Or look at CREATE FOREIGN TABLE for a very similar functionality. For now, I prefer dblink, because it is mature and stable. The new SQL/MED features (Management of External Data) FOREIGN DATA WRAPPER and FOREIGN TABLE are still in the making ..