current community

your communities

more stack exchange communities

Stack Exchange
tour help
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.
up vote 0 down vote favorite

I have two different Postgres databases on two different servers (one is actually local). Similar to this question, I would like to work with both databases at the same time. However, I can't figure out a way how to do that using psycopg2.

So I was thinking I probably need two different cursors:

conn_local = psycopg2.connect(dbname='local_db', host='localhost')
conn_remote = psycopg2.connect(dbname='remote_db', host='some.other.server')

curs_local = conn_local.cursor()
curs_remote = conn_remote.cursor()

But how can I address those databases? For instance, when I try to join data from both tables:

curs_local.execute("""
    CREATE TABLE local_db.public.newtable AS
    SELECT remote_db.public.remotetable.rcolumn AS col_from_remote, 
    local_db.public.localtable.lcolumn AS col_from_local
    FROM remote_db.public.remotetable, local_db.public.localtable""")

There will be an error in the style of psycopg2.NotSupportedError: cross-database references are not implemented: "local_db.public.new_table". The ATTACH TABLE command (as described in the solution here) does apparently not exist in Postgres / psycopg2.

Is it possible to work with multiple databases at a time? How? Or will I have to copy (export / import) the data from remote_db to local_db first?

share|improve this question
    
Please clarify your intent and what constraints you need to overcome to resolve your problem. That might help you get a helpful response. –  RLF May 15 at 13:29
    
@RLF Voilà - is it a little clearer now? –  n1000 May 15 at 14:17
2  
I don't know Python, but in Postgres it's in general not possible to do cross database queries. If you need that on a regular basis (e.g. joining tables) you are better off putting everything in one database, but separate things using schemas. A possible workaround is to use a foreign data wrapper in one DB to expose the tables in the other DB - but that will be much slower than putting everything into a single DB. –  a_horse_with_no_name May 15 at 14:41
    
@a_horse_with_no_name OK :( I think unfortunately your comment is the answer to my question... You want to add it? –  n1000 May 15 at 14:44

Your Answer

 
discard

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

Browse other questions tagged or ask your own question.