How to connect to localhost with postgres_fdw?
The idea is that I have a local database named northwind
and using postgres_fdw
I want to connect to another database named test
on localhost (simulate a remote connection for situations like when a table in my database is updated, do something in another database, e.g. save history, etc.). So I opened my psql console and typed:
CREATE SERVER app_db
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (dbname 'test', host 'localhost:5432');
As I found in the link to external data wrappers . Next I also follow the guide:
CREATE USER MAPPING for postgres
SERVER app_db
OPTIONS (user 'postgres', password 'postgres');
(I assume the user and password should be the same as I used, for example, in Hibernate to access the database, and in place current_user
from the tutorial postgres
in my case the reason is the only role that exists in my PgAdmin III). Then I created an external 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 schema/foreign tables
in PgAdmin III. But when I try SELECT * FROM groups
I got:
********** ERROR**********
ERROR: could not connect to server "app_db"
SQL State: 08001
Is this possible because when I am CREATE SERVER app_db..
, I am not using the server name localhost? I can't, because it has a name with a space (PostgreSQL 9.3) and it causes some weird build problems. Thank you in advance.
Update . It doesn't work even if I create another server named `app_db on localhost.
source to share
After many attempts, I found a suitable 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 this connection is really "remote"? Since the servers are on the same localhost and I don't know if I can be sure.
source to share
You can connect using Unix Domain Socket instead of TCP connection for easier configuration and better performance (Linux / Unix only, not supported on Windows).
CREATE SERVER app_db
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (dbname 'test');
In addition, you can omit the password if peer
Authentication is enabled (default).
CREATE USER MAPPING for postgres
SERVER app_db
OPTIONS (user 'postgres');
Note: Authentication peer
can only be used for the user postgres
, since the FDW connection is created by the server that runs as the system user postgres
.
In addition, for security reasons, postgresq_fdw only allows peer
privileged client authentication SUPERUSER
. To allow restricted users to use FDW, you must use authentication password
.
source to share