How to connect to localhost with postgres_fdw?

Question

The idea is that I have local database named northwind, and with postgres_fdw I want to connect with another database named test on localhost (remote connection simulation, for situations like when table in my database is updated, do something in other database like save to history etc..). So I opened psql console and type:

CREATE SERVER app_db 
FOREIGN DATA WRAPPER postgres_fdw 
OPTIONS (dbname 'test', host 'localhost:5432');

As i found in A Look at Foreign Data Wrappers link. Next I also follow the tutorial:

CREATE USER MAPPING for postgres
SERVER app_db 
OPTIONS (user 'postgres', password 'postgres');

(I assume that user and password should be the same as I used e.g. in Hibernate to access database, and in place current_user from tutorial is postgres in my case cause this is the only role which exists in my PgAdmin III). Then I created foreign table:

CREATE FOREIGN TABLE groups
(
  id serial NOT NULL,
  name character varying(255) NOT NULL,
  version integer DEFAULT 0
)
 SERVER app_db OPTIONS (table_name 'groups')

And that's ok I can find it in schema/foreign tables in PgAdmin III. But when I try to SELECT * FROM groups I got:

********** ERROR**********

ERROR: could not connect to server "app_db"
SQL State: 08001

Is it possible because when I CREATE SERVER app_db.. I don't use localhost server name? I can't because it has name with space (PostgreSQL 9.3) and it causes some weird problems while creating. Thank you in advance. Update: It doesn't work even if I create another server named`app_db on localhost.

Answer 1

After many attempts probably I found a proper way to connect:

CREATE SERVER app_db 
FOREIGN DATA WRAPPER postgres_fdw 
OPTIONS (dbname 'test', port '5432', host 'localhost');

Then:

CREATE USER MAPPING for postgres
SERVER app_db 
OPTIONS (user 'postgres', password 'postgres');

And then:

CREATE FOREIGN TABLE groups
(
  id serial NOT NULL,
  name character varying(255) NOT NULL,
  version integer DEFAULT 0
)
 SERVER app_db OPTIONS (schema_name 'public', table_name 'groups')

But is there a solution to check if it's really "remote" connection? Because servers are on the same localhost and I don't know if I can be sure.