How to use dblink_connect and dblink_connect_u

Question

I have two databases in Postgres and want to access the data from one database to other database tables. I tried the following:

dblink_connect('myconn','hostaddr=10.10.30.53 dbname=postgres user=postgres password=postgres')

and

dblink_connect_u('myconn','hostaddr=10.10.30.53 dbname=postgres user=postgres password=postgres')

This results in a syntax error:

ERROR: syntax error at or near "dblink_connect" LINE 1: dblink_connect('myconn','hostaddr=10.10.30.53 dbname=postgre... ^
********** Error ********** ERROR: syntax error at or near "dblink_connect" SQL state:

Do I need to change anything in pg.hba.conf file?

Can anybody give me the steps with an example?

Answer 1

A syntax error indicates that the statement itself causes problems. Changing pg_hba.conf will not solve this. Your statement looks ok - except for the missing SELECT (or possibly PERFORM in a plpgsql function):

SELECT dblink_connect('myconn'
       ,'hostaddr=10.10.30.53 dbname=postgres user=postgres password=postgres')

It's a plain SQL SELECT statement.
If that's not it, are you sure you have installed the additional module dblink and your search_path includes the schema you installed it to?