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.

Culdesac

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.

Culdesac

Sorry for asking here, but i follow step by step your advices and i get this error when i perform a select ERROR: could not connect to server "omnes_db" DETAIL: could not connect to server: Connection refused Is the server running on host "localhost" (::1) and accepting TCP/IP connections on port 5431? could not connect to server: Connection refused Is the server running on host "localhost" (127.0.0.1) and accepting TCP/IP connections on port 5431? the pg_hba.cong and postgresql.conf files are setup properly for allowing remove secured connections.REally don't know what to do – Anticipative

Update: The above answer works perfectly for my case https://mcmap.net/q/1252528/-how-to-connect-to-localhost-with-postgres_fdw. For accessing another database located within hthe same instance of postgres, you can ommit the host and port for the server and also the password for the user mapping. Probably you must allow access to the foreign table by granting at least "select" permissions. – Anticipative

Answer 2

You may connect using Unix Domain Socket instead of TCP connection to gain simpler configuration and better performance (Linux/Unix only, not supported in Windows).

CREATE SERVER app_db
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (dbname 'test');

Also, you may omit the password if peer authentication is enabled (default).

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

Note: peer authentication can only be used for postgres user as the FDW connection is created by the server backend which runs as the system user postgres.

Also, for security reasons, postgresq_fdw allows peer authentication only for clients with SUPERUSER privilege. To allow restricted users to use the FDW, you have to use password authentication as described in this answer

Audient

Answer 3

I had the exact same problem as user Victor who commented up there (can't comment in line as I currently lack the minimum rep points)

My case was using a Docker for Desktop on a Windows machine but with containers running a Linux kernel.

This https://mcmap.net/q/1252528/-how-to-connect-to-localhost-with-postgres_fdw worked like a charm :) much appreciated

Jumper