1. 跨库访问简介 PostgreSQL 跨库访问有3种方法：Schema，dblink，postgres_fdw。

  2. 跨库访问方案 2.1 SCHEMA 方案 在 PostgreSQL 上基于不同的业务创建不同的 SCHEMA，并将业务数据和业务程序分别创建在不同的 SCHEMA 下，各个业务用户经过授权之后进行访问。

2.2 dblink 方案 在一个 PostgreSQL 实例下分别创建两个 database 数据库，并且通过 dblink 实现跨库访问。 创建 database，名称为 test \l List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges -----------+----------+----------+---------+-------+----------------------- postgres | postgres | UTF8 | C | C | template0 | postgres | UTF8 | C | C | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | C | C | =c/postgres + | | | | | postgres=CTc/postgres test | postgres | UTF8 | C | C | (4 rows)

在 test 数据库中创建测试表 create table t(id int); create table t2(id int); insert into t values ('1111');

创建 dblink 插件 登录到 PostgreSQL 实例中，在 postgres 数据库中创建 dblink 插件连接到test数据库。 create extension dblink; select * from pg_extension;

创建 dblink 连接 SELECT dblink_connect('test_dblink', 'dbname=test host=localhost port=5432 user=postgres password=postgres');

SELECT * FROM dblink('test_dblink', 'select * from t') AS t1(id int);

创建快捷访问视图 CREATE VIEW testdb_dblink AS SELECT * FROM dblink('hostaddr=127.0.0.1 port=5432 dbname=test user=postgres password=postgres', 'SELECT * From test') AS t(id int);

2.3 postgres_fdw 访问 本地：192.168.0.14，目标端：192.168.0.17，本地和目标端两台机器的测试用户及数据库均为：test,test 本地端创建 postgres_fdw 插件 -- 登录到本地端业务数据库 create extension postgres_fdw ;

本地端业务库中创建 server -- 创建 server CREATE SERVER s1 FOREIGN DATA WRAPPER postgres_fdw;

-- 查询 server SELECT * FROM pg_foreign_server ; srvname | srvowner | srvfdw | srvtype | srvversion | srvacl | srvoptions ---------+----------+--------+---------+------------+--------+------------ s1 | 17444 | 17449 | | | | (1 row)

-- 修改 server alter server s1 options ( add hostaddr '192.168.0.17', add port '5432', add dbname 'test');

本地端业务库中 SERVER 赋权 grant usage on foreign server s1 to test; SELECT * FROM pg_foreign_server ;

srvname | srvowner | srvfdw | srvtype | srvversion | srvacl | srvoptions
---------+----------+--------+---------+------------+---------------+-------------------------------- s1 | 17444 | 17449 | | | {test=U/test} | {hostaddr=192.168.0.17,port=5432,dbname=test}

在本地端业务库中创建 user mapping create user mapping for test server s1 options(user 'test',password 'test');

本地业务库中创建 foreign table CREATE TYPE user_enum AS ENUM ('foo', 'bar', 'buz'); CREATE FOREIGN TABLE ft1 ( c0 int, c1 int NOT NULL, c2 int NOT NULL, c3 text, c4 timestamptz, c5 timestamp, c6 varchar(10), c7 char(10), c8 user_enum ) SERVER s1 OPTIONS(schema_name 'test', table_name 'test1');

-- 验证 select * from ft1 limit 1; c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8 ----+----+-------+------------------------+---------------------+----+------------+----- 1 | 1 | 00001 | 1970-01-02 00:00:00+08 | 1970-01-02 00:00:00 | 1 | 1 | foo (1 row)