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.

So we are writing an app whose schema should reference data which lies currently in an external PostgreSQL instance. We are negotiating being able to put our schema within the external database, but we are evaluating different possibilities.

One option I'm pondering is basing our app on PostgreSQL and use its facilities for accessing external PostgreSQL instances.

What's the status of this?

PG's documentation contains http://www.postgresql.org/docs/9.1/static/sql-createserver.html and http://www.postgresql.org/docs/9.1/static/sql-createforeigntable.html , which allow you to reference tables in an external server. What's their status? Are queries performant (i.e. sends WHERE to the other side)? Can you reasonably join between local and foreign tables?

There's also pgsql_fdw ( http://interdbconnect.sourceforge.net/pgsql_fdw/pgsql_fdw-en.html ), which seems more featureful. Does it offer an improvement on the above? Anyone using it?

Thanks,

Alex

share|improve this question
    
did you search archives.postgresql.org ? there are several fdw users... –  filiprem Jan 25 '12 at 0:34

1 Answer 1

Foreign data wrappers are currently read-only. This will change in the future, but that's the way it is. You can also use dblink to access other databases. As I understand it a big issue will be commit handling. I would recommend against this approach unless you are trying to aggregate lots of data that is really external. It is a huge complexity cost and the benefits have to be worth it.

Edit: Note that foreign tables and foreign servers are how you use foreign data wrappers to map in foreign data to your application. You can't choose between them. It's all three or nothing.

share|improve this answer
    
And, on an other note, dblink (with which you get read/write on the external DB) is not quite performant (to the extent it seems to be useful for small data sets only, based on my simple experiments). –  dezso Oct 3 '12 at 9:08

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.