Postgresql: Select a table from different database and different host

Question

I know this is not new, but I have not found a solution, even though I have followed several posts in this forum.

I'm trying to show table from different database using dblink extension. Installing dblink using :

CREATE EXTENSION dblink

And run successfully. In db1 then I try querying, like:

SELECT *
    FROM dblink('dbname=db_name2 port=5432 
            host=xx.xx.x.x user=username_db2 password=password_db2',
    'SELECT * FROM tableFrom_Db2');

I always get notice like this:

ERROR: a column definition list is required for functions returning "record"

When I try replace SELECT * with a list of columns, I get:

ERROR: invalid input syntax for type boolean: "SELECT column FROM tableFrom_Db2"

To make sure that the connection is going well, I tried

SELECT dblink_connect('host=xx.xx.x.x user=username_db2 password=password_db2 dbname=db_name2');

And I get notice like :

ERROR: could not establish connection
DETAIL: FATAL: no pg_hba.conf entry for host "IP_db1", user "username_db2", database "db_name2", SSL on
FATAL: no pg_hba.conf entry for host "IP_db1", user "username_db2", database "db_name2", SSL off

By the way What's wrong?

Answer 1

The second error message means, that your Postgres on db_name2 is not configured to allow connections from IP_db1. Unrelated to this problem: with modern Postgres versions it's usually better to use a foreign table, rather than the dblink module. – a_horse_with_no_name

You need to edit your pg_hba.conf file on the far host (x.x.x.x) to allow the connection. Don't forget to reload the server config files after for it to take effect. It is probably better to debug connection issues with "psql" rather than using dblink or postgres_fdw, those are just going to confuse the issue. – jjanes