Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
Manually updating system tables is thought to be very bad idea. With a bit of luck you can corrupt your database beyond repair. –  dezso Dec 10 '12 at 12:43
add comment

1 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 ..

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.