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?
Best Answer
Yes, it is possible to work with multiple databases at the same time but you're looking in the wrong place. psycopg2 is just a library that simplifies accessing and manipulating data coming out of PostgreSQL but it doesn't go far beyond what you can do with psql. What you're looking to do you can solve on the database level by using Foreign Data Wrappers.
This does become more complicated in your schema definition but brings remote tables from host
some.other.server
databaseremote_db
to appear as though they live onlocalhost
in databaselocal_db
. A simple example on how to hook up the wrappers:Now locally you can just run
and the query will get executed against the remote host. Needless to say, this requires connectivity between the two servers.
Similarly, if you really have to, you can create a "remote" server against the localhost and point to a different local database for cross database queries. Feels dirty but it's possible.
As @a_horse_with_no_name mentioned though, this is not very efficient. If you find yourself doing this too frequently, you're not getting the most optimal performance and you'd better have very good reasons for keeping your databases separate at that point.