8

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?

5
  • 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, 2015 at 13:29
  • @RLF Voilà - is it a little clearer now?
    – n1000
    May 15, 2015 at 14:17
  • 3
    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.
    – user1822
    May 15, 2015 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, 2015 at 14:44
  • @n1000 I added it as an answer. Jan 12, 2017 at 5:37

3 Answers 3

10

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 database remote_db to appear as though they live on localhost in database local_db. A simple example on how to hook up the wrappers:

CREATE EXTENSION postgres_fdw;
CREATE SERVER some_remote_server 
  FOREIGN DATA WRAPPER postgres_fdw 
  OPTIONS (host 'some.remote.server', port '5432', dbname 'remote_db');
CREATE USER MAPPING FOR local_user 
  SERVER some_remote_server 
  OPTIONS (user 'remote_user', password 'remote_user_password');
CREATE FOREIGN TABLE local_table_name (id int, value int) 
  SERVER some_remote_server 
  OPTIONS ( schema_name 'remote_schema_name', table_name 'remote_table_name');

Now locally you can just run

SELECT * from local_table_name

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.

4

PostgreSQL calls "schemas" what MySQL calls databases. Do not create multiple databases with the intention of them working together. That said, if you need to access an external database, the PostgreSQL FDW will help you out

1

As I just learned from this answer, an alternative approach would be dblink. E.g.:

SELECT *
FROM dblink('dbname=mydb', 'select rcol1, rcol2 from remote_db')
  AS t1(rcol1 name, rcol2 text)
1
  • 2
    NOTE (from the official documentation): The functionality provided postgres_fdw overlaps substantially with the functionality of the older dblink module. But postgres_fdw provides more transparent and standards-compliant syntax for accessing remote tables, and can give better performance in many cases.
    – rmuller
    Jan 31, 2019 at 13:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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