如何创建服务器postgresql才是有用的

Question

我正在为一个测试而学习，因为我从来不需要使用"CREATE SERVER“命令，我不知道你的功能是什么。

我在PostgreSQL官方页面上看到了，但我不清楚。我希望看到一个真实的案例，在这个案例中，我可以使用"CREATE SERVER“(外部服务器)和"CREATE USER MAPPING FOR”。

不管怎样，谢谢你。

Answer 1

您可以为postgresql外表环境使用postgres_fdw，也可以使用其他表fdw，如mysql_fdw、oracle fdw。例如，在postgres_fdw中。--远程数据库

CREATE TYPE user_enum AS ENUM ('foo', 'bar', 'buz');
CREATE SCHEMA digoal;
-- TABLE : 
CREATE TABLE digoal.test1 (
    c1 int NOT NULL,
    c2 int NOT NULL,
    c3 text,
    c4 timestamptz,
    c5 timestamp,
    c6 varchar(10),
    c7 char(10),
    c8 user_enum,
    CONSTRAINT t1_pkey PRIMARY KEY (c1)
);
CREATE TABLE digoal.test2 (
    c1 int NOT NULL,
    c2 text,
    CONSTRAINT t2_pkey PRIMARY KEY (c1)
);
-- TEST DATA : 
INSERT INTO digoal.test1
    SELECT id,
           id % 10,
           to_char(id, 'FM00000'),
           '1970-01-01'::timestamptz + ((id % 100) || ' days')::interval,
           '1970-01-01'::timestamp + ((id % 100) || ' days')::interval,
           id % 10,
           id % 10,
           'foo'::user_enum
    FROM generate_series(1, 1000) id;
INSERT INTO digoal.test2
    SELECT id,
           'AAA' || to_char(id, 'FM000')
    FROM generate_series(1, 100) id;

本地数据库：

digoal=# CREATE EXTENSION postgres_fdw;
digoal=# CREATE SERVER s1 FOREIGN DATA WRAPPER postgres_fdw;
CREATE SERVER
digoal=# select * from pg_foreign_server ;
 srvname | srvowner | srvfdw | srvtype | srvversion | srvacl | srvoptions 
---------+----------+--------+---------+------------+--------+------------
 s1      |       10 |  16425 |         |            |        | 
(1 row)
digoal=# alter server s1 options ( add hostaddr '172.16.3.150', add port '9201', add dbname 'digoal');
ALTER SERVER
digoal=# grant usage on foreign server s1 to digoal;
GRANT
digoal=# select * from pg_foreign_server ;
 srvname | srvowner | srvfdw | srvtype | srvversion |                 srvacl                  |            srvoptions             
---------+----------+--------+---------+------------+-----------------------------------------+-----------------------------------
 s1      |       10 |  16425 |         |            | {postgres=U/postgres,digoal=U/postgres} | {hostaddr=172.16.3.150,port=9201,dbname=digoal}
(1 row)

digoal=# CREATE USER MAPPING FOR digoal server s1 options (user 'digoal', password 'digoal');
CREATE USER MAPPING
digoal=# \c digoal digoal
digoal=> CREATE TYPE user_enum AS ENUM ('foo', 'bar', 'buz');
CREATE TYPE
digoal=> CREATE FOREIGN TABLE ft2 (c2 text, c1 int not null) SERVER s1 options(schema_name 'digoal', table_name 'test2');
CREATE FOREIGN TABLE
digoal=> select * from ft2 limit 1;
   c2   | c1 
--------+----
 AAA001 |  1
(1 row)