如何使用postgres_fdw连接到本地主机? [英] How to connect to localhost with postgres_fdw?

问题描述

这个想法是，我有一个名为northwind的本地数据库，并且想通过postgres_fdw与本地主机上的另一个名为test的数据库连接(远程连接模拟，例如当数据库中的表被更新时，在其他数据库中执行某些操作，例如保存到历史记录等.).所以我打开了psql控制台并输入:

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');

(我假设用户名和密码应该与我在Hibernate中访问数据库时使用的密码相同，在本例中，教程中的current_user是postgres，因为这是我的PgAdmin中存在的唯一角色III). 然后我创建了外部表:

(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')

没关系，我可以在PgAdmin III的schema/foreign tables中找到它.但是当我尝试SELECT * FROM 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

是否可能是因为当我CREATE SERVER app_db..时不使用本地主机服务器名称吗?我不能，因为它的名称带有空格(PostgreSQL 9.3)，并且在创建时会引起一些奇怪的问题.先感谢您. 更新:即使我在本地主机上创建另一个名为`app_db的服务器，该操作也无法正常工作.

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.

推荐答案

经过多次尝试，我可能找到了一种正确的连接方法:

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');

然后:

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

然后:

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.

这篇关于如何使用postgres_fdw连接到本地主机?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！