0

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"

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?

|improve this question
  • Have you tried to replace SELECT * by column names? – McNets Feb 18 at 7:54
  • @McNets Yes, I get ERROR: invalid input syntax for type boolean: "SELECT column FROM tableFrom_Db2" – Ugy Astro Feb 18 at 8:05
  • All tables have the same schema? – McNets Feb 18 at 8:17
  • 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 Feb 18 at 8:51
  • yes @McNets @a_horse_with_no_name I try with this way and give the same result, like error when use dblink_connect. How to allow connection on db_name2? – Ugy Astro Feb 18 at 9:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.