I have a strange problem with PostgreSQL and dblink.
I have a remote PostgreSQL installation on amazon RDS 9.5.2 and a local PostgreSQL installation on Windows 10.
I am trying to transfer data from the local installation to the remote installation using dblink. I am connected to the remote database with a query window opened from pgAdmin III.
When connecting to the default postgres database I am successful:
select dblink_connect('dbname=postgres host=localhost user=postgres password=xxx');
However when I try to connect to any other database I get an error:
select dblink_connect('dbname=gti host=localhost user=postgres password=xxx');
ERROR: could not establish connection
DETAIL: FATAL: database "gti" does not exist
********** Error **********
ERROR: could not establish connection
SQL state: 08001
Detail: FATAL: database "gti" does not exist
I have changed ownership to the postgres user for the database.
But I am at my wits end what else to try.
Edited. A screenshot from PgAdmin showing the databases both remote and local:
pgAdmin screenshot
Edited 2: I ran select * from pg_database; as requested:
Result set
Edited 3: I set up a new PostgreSQL installation on another machine on our local network. Connected to Amazon and got exactly the same error.
If I connect from PostgreSQL on this new pc to PostgreSQL on my pc in pgAdmin, open a query window and dblink_connect from there to the local instance I get no error. So it seems to be either an error when connecting to a database outside our network, or just Amazon specifically.
Edited 4: If I add another user to my Local PostgreSQL instance and grant it superuser access, I cannot connect to my local instance from the amazon query window using dblink. I can however connect to my local instance using this new user from the other PostgreSQL on the local network.