PostgreSQL跨库访问有3种方法：Schema，dblink，postgres_fdw。 方法A：在PG上建立不同SCHEMA，将数据和存储过程分别放到不同的schema上，经过权限管理后进行访问。 方法A的示例如下： 测试1（测试postgres超级用户对不同schema下对象的访问） 查看当前数据库中的schema  postgres=# dn  List of schemas  Name | Owner -------------------+--------- dbms_job_procedure | postgres pgagent | postgres  postgres | postgres  public | postgres  (4 rows) （当前连接数据库的用户为postgres） postgres=# select user; user ---------- postgres  (1 row) 创建名为test1的schema  postgres=# create schema test1;  CREATE SCHEMA 创建模式test1下的对象，表ticket1  postgres=# create table test1.ticket1(id int);  CREATE TABLE 可以看到并没有我们之前建立的表  postgres=# d List of relations  Schema | Name | Type | Owner -------------------------+--------- public | dept | table | postgres  public | emp | table | postgres  public | jobhist | table | postgres  public | next_empno | sequence | postgres  public | salesemp | view | postgres  (5 rows) 在对象前加schema，postgres用户可以访问ticket1表 postgres=# select * from test1.ticket1; id ------------------------------------------------- (0 rows) 查看模式 搜索路径  postgres=# show search_path ;  search_path ---------------- "$user",public  (1 row) 把创建的模式test1添加到模式搜索路径 postgres=# set search_path to "$user",public,test1;  SET  postgres=# show search_path ;  search_path ------------------------ "$user", public, test1  (1 row)  为了访问方便，在搜索路径中添加schema对象之后既可以看到该模式下的表，也可以直接进行搜索，而不用添加schema前缀。（这里因为是超级用户，所以不用给postgres赋权，如果是普通用户，想要访问，需要赋权）  postgres=# d List of relations  Schema | Name | Type | Owner -------------------------+--------- public | dept | table | postgres  public | emp | table | postgres  public | jobhist | table | postgres  public | next_empno | sequence | postgres  public | salesemp | view | postgres  test1 | ticket1 | table | postgres  (6 rows)  postgres=# select * from ticket1;  id -------------------------------------------- (0 rows)  测试2：  在postgres用户下建立名为test2的schema  postgres=# create schema test2;  CREATE SCHEMA  postgres=# create table test2.ticket2(id int);  CREATE TABLE 建立两个普通用户  postgres=# create role test1 login password '123';  CREATE ROLE  postgres=# create role test2 login password '123';  CREATE ROLE 普通用户连接数据库  postgres=# c postgres test2;  Password for user test2: You are now connected to database "postgres" as user "test2".  postgres=> d  List of relations  Schema | Name | Type | Owner -------------------------+--------- public | dept | table | postgres  public | emp | table | postgres  public | jobhist | table | postgres  public | next_empno | sequence | postgres  public | salesemp | view | postgres  (5 rows) postgres=> show search_path ;  search_path ---------------- "$user",public  (1 row) postgres=> set search_path to "$user",public,test1;  SET postgres=> d  List of relations  Schema | Name | Type | Owner -------------------------+--------- public | dept | table | postgres  public | emp | table | postgres  public | jobhist | table | postgres  public | next_empno | sequence | postgres  public | salesemp | view | postgres  test1 | ticket1 | table | postgres  test2 | ticket2 | table | postgres  (11 rows)  可以看到test2用户模式下的ticket2表，但是访问时权限不足。  postgres=> select * from test2.ticket2;  ERROR: permission denied for relation ticket2  postgres=> select * from ticket2; ERROR: permission denied for relation ticket2  通过postgres超级用户赋予权限，即可访问  postgres=# grant select on all tables in schema test2 to test1;  GRANT  postgres=> select * from test2.ticket2;  id --------------------------------------------------- (0 rows) postgres=> select * from ticket2; id --------------------------------------------------- (0 rows) 方法B：通过dblink实现跨库访问 方法B测试示例如下： 环境：本地：192.168.56.88 数据库：postgres  远程：192.168.56.99 数据库：test   PostgreSQL通过dblink实现跨库访问 测试1：在同一个实例下分别建立两个数据库，通过dblink 实现跨库访问 postgres=# create database test; CREATE DATABASE postgres=# 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) postgres=# c test You are now connected to database "test" as user "postgres". test=# create table test(id int); CREATE TABLE test=# d         List of relations  Schema | Name | Type  |  Owner  --------+------+-------+----------  public | test | table | postgres (1 row) test=# create table test2(id int); CREATE TABLE test=# insert into test values ('1111'); INSERT 0 1 test=# c postgres You are now connected to database "postgres" as user "postgres". 在postgres数据库中建立dblink连接到test数据库 postgres=# create extension dblink; CREATE EXTENSION postgres=# select * from pg_extension;  extname | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition ---------+----------+--------------+----------------+------------+-----------+--------------  plpgsql |       10 |           11 | f              | 1.0        |           |  dblink  |       10 |         2200 | t              | 1.1        |           | (2 rows) postgres=# select dblink_connect('test_dblink','dbname=test host=localhost port=5432 user=postgres password=postgres');  dblink_connect ----------------  OK (1 row) postgres=# select * from dblink('test_dblink','select * from test') as t1(id int);   id ------  1111 (1 row) 通过建立dblink，在postgres数据库可以很容易的访问到test数据库中的数据。 为了访问test数据库中的数据方便，我们可以建立一个视图，操作如下，我们只需要查询视图中的内容即可。 postgres=# CREATE VIEW testdb_dblink AS  postgres-# SELECT * FROM dblink('hostaddr=127.0.0.1 port=5432 dbname=test user=postgres password=postgres', 'SELECT * From test') AS t(id int); CREATE VIEW postgres=# d                   List of relations  Schema |          Name           | Type  |  Owner  --------+-------------------------+-------+----------  public | ptest1                  | table | postgres  public | ptest2                  | table | postgres  public | remote_people_user_name | view  | postgres  public | testdb_dblink           | view  | postgres (4 rows) postgres=# select * from testdb_dblink ;   id ------  1111 (1 row) 测试2： 在两个实例下分别创建数据库，然后通过dblink实现垮库访问。 实例1： 首先需要配置下路由配置，添加一行命令-A INPUT -s 192.168.0.0/16 -j ACCEPT [root@darry etc]# vi /etc/sysconfig/iptables ... 添加-A INPUT -s 192.168.0.0/16 -j ACCEPT  即允许192.168.0.0的网段访问 .... [root@darry etc]# service iptables reload iptables: Trying to reload firewall rules:                 [  OK  ] 在IP为192.168.56.88（本地）的postgres数据库中建立extension postgres=# create extension dblink; CREATE EXTENSION postgres=# select  * from pg_extension;  extname | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition ---------+----------+--------------+----------------+------------+-----------+--------------  plpgsql |       10 |           11 | f              | 1.0        |           |  dblink  |       10 |         2200 | t              | 1.1        |           | (2 rows) 建立dblink 访问IP为192.168.56.99（远程）数据库 postgres=# select dblink_connect('test_dblink','dbname=test host=192.168.56.99 port=5432 user=postgres password=postgres');  dblink_connect ----------------  OK (1 row)   postgres=# select * from dblink('test_dblink','select * from test') as t1(id int);   id ------  1111 (1 row) 跨库事务测试 连接远程数据库 postgres=# select dblink_connect('test_dblink','dbname=test host=192.168.56.99 port=5432 user=postgres password=postgres');  dblink_connect ----------------  OK (1 row) 在远程服务器上开始一个事务 postgres=# select dblink_exec('test_dblink','begin;');  dblink_exec -------------  BEGIN (1 row) 插入一条数据 postgres=# select dblink_exec('test_dblink','insert into test values(7777);');  dblink_exec -------------  INSERT 0 1 (1 row) 经查看远程服务器上已经插入一条数据 postgres=# select * from dblink('test_dblink','select * from test') as t1(id int);   id  -------   1111   2222   3333   4444   6666  33333   7777 (11 rows) 在远程数据库中查看未发现数据，因为事务未提交 test=# select * from test;   id  -------   1111   2222   3333   4444   6666  33333 在本地数据库中提交远程连接数据库中的事务 postgres=# select dblink_exec('test_dblink','commit;');  dblink_exec -------------  COMMIT (1 row) 再次查看 postgres=# select * from dblink('test_dblink','select * from test') as t1(id int);   id  -------   1111   2222   3333   4444   6666  33333   7777 远程数据库中也存在 test=# select * from test;   id  -------   1111   2222   3333   4444   6666  33333   7777 若换成将commit替换成rollback则插入取消 postgres=# select dblink_exec('test_dblink','begin;');  dblink_exec -------------  BEGIN (1 row) postgres=# select dblink_exec('test_dblink','insert into test values(99999);');  dblink_exec -------------  INSERT 0 1 postgres=# select * from dblink('test_dblink','select * from test') as t1(id int);   id  -------   1111   2222   3333   4444   6666  33333   7777  99999 执行回滚操作 postgres=# select dblink_exec('test_dblink','rollback;');  dblink_exec -------------  ROLLBACK (1 row) 经查看回滚之后，不记录之前插入的数据 postgres=# select * from dblink('test_dblink','select * from test') as t1(id int);    id  -------   1111   2222   3333   4444   6666  33333   7777 方法C：通过postgres_fdw实现跨库访问 环境：本地：192.168.0.14，远程：192.168.0.17，PG：9.3.9两台机器的测试用户及数据库均为：test,test 1.在本地数据库中创建postgres_fdw extension. [postgres@minion1 bin]$ ./psql test test psql (9.3.9) Type "help" for help. test=# c test test You are now connected to database "test" as user "test". test=# create extension postgres_fdw ; CREATE EXTENSION 2.在远程数据库上生成测试数据 : [postgres@minion4 bin]$ ./psql test test psql (9.3.9) Type "help" for help. test=# CREATE TYPE user_enum AS ENUM ('foo', 'bar', 'buz'); CREATE TYPE test=# dT         List of data types  Schema |   Name    | Description --------+-----------+-------------  public | user_enum | (1 row)   test=# select oid from pg_type where typname='user_enum';   oid -------  16902 (1 row) test=# CREATE SCHEMA test; CREATE SCHEMA test=# CREATE TABLE test.test1 ( test(# c1 int NOT NULL, test(# c2 int NOT NULL, test(# c3 text, test(# c4 timestamptz, test(# c5 timestamp, test(# c6 varchar(10), test(# c7 char(10), test(# c8 user_enum, test(# CONSTRAINT t1_pkey PRIMARY KEY (c1) test(# ); CREATE TABLE test=# CREATE TABLE test.test2 ( test(# c1 int NOT NULL, test(# c2 text, test(# CONSTRAINT t2_pkey PRIMARY KEY (c1) test(# ); CREATE TABLE test=# INSERT INTO test.test1 test-# SELECT id, test-#        id % 10, test-#        to_char(id, 'FM00000'), test-#        '1970-01-01'::timestamptz + ((id % 100) || ' days')::interval, test-#        '1970-01-01'::timestamp + ((id % 100) || ' days')::interval, test-#        id % 10, test-#        id % 10, test-#        'foo'::user_enum test-# FROM generate_series(1, 1000) id; INSERT 0 1000 test=# INSERT INTO test.test2 test-# SELECT id, test-#        'AAA' || to_char(id, 'FM000') test-# FROM generate_series(1, 100) id; INSERT 0 100 test=# analyze test.test1; ANALYZE test=# analyze test.test2; ANALYZE 3.在本地数据库中创建server test=# CREATE SERVER s1 FOREIGN DATA WRAPPER postgres_fdw; CREATE SERVER test=# select * from pg_foreign_server ;  srvname | srvowner | srvfdw | srvtype | srvversion | srvacl | srvoptions ---------+----------+--------+---------+------------+--------+------------  s1      |    17444 |  17449 |         |            |        | (1 row) test=# alter server s1 options ( add hostaddr '192.168.0.17', add port '5432', add dbname 'test'); ALTER SERVER 4.SERVER赋权 : test=# grant usage on foreign server s1 to test; GRANT 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=543 2,dbname=test} (1 row) 5.在本地数据库中创建user mapping : test=# create user mapping for test server s1 options(user 'test',password 'test'); CREATE USER MAPPING 6.在本地数据库中创建foreign table test=# CREATE TYPE user_enum AS ENUM ('foo', 'bar', 'buz'); CREATE TYPE test=# dT         List of data types  Schema |   Name    | Description --------+-----------+-------------  public | user_enum | (1 row)   test=# select oid from pg_type where typname='user_enum';   oid -------  17453 (1 row) test=# CREATE FOREIGN TABLE ft1 ( test(#  c0 int, test(#  c1 int NOT NULL, test(#  c2 int NOT NULL, test(#  c3 text, test(#  c4 timestamptz, test(#  c5 timestamp, test(#  c6 varchar(10), test(#  c7 char(10), test(#  c8 user_enum test(#  ) SERVER s1 options(schema_name 'test', table_name 'test1'); CREATE FOREIGN TABLE test=# select * from ft1 limit 1; ERROR:  column "c0" does not exist CONTEXT:  Remote SQL command: SELECT c0, c1, c2, c3, c4, c5, c6, c7, c8 FROM test.test1 test=# alter foreign table ft1 drop column c0; ALTER FOREIGN TABLE test=# 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) test=# create foreign table ft2 (c2 text,c1 int not null) server s1 options(schema_name 'test',table_name 'test2'); CREATE FOREIGN TABLE test=# select * from ft2 limit 1;    c2   | c1 --------+----  AAA001 |  1 (1 row) test=# create foreign table ft3(c2 text,c3 int not null) server s1 options(schema_name 'test',table_name 'test2'); CREATE FOREIGN TABLE test=# select * from ft3 limit 1; ERROR:  column "c3" does not exist CONTEXT:  Remote SQL command: SELECT c2, c3 FROM test.test2 test=# alter foreign table ft3 alter column c3 options (column_name 'c1'); ALTER FOREIGN TABLE test=# select * from ft3 limit 1;    c2   | c3 --------+----  AAA001 |  1 (1 row) test=# create foreign table ft4(c2 text,c3 int options (column_name 'c1') not null) server s1 options(schema_name 'test',table_name 'test2'); CREATE FOREIGN TABLE test=# select * from ft4 limit 2;    c2   | c3 --------+----  AAA001 |  1  AAA002 |  2 (2 rows) PostgreSQL跨库访问事务测试 远程机器创建测试表 test=# create table test3(id int); CREATE TABLE test=# select * from test3;  id ---- (0 rows) 本地机器测试 创建对应的外部表 test=# create foreign table ft_test3(id int) server s1 options(schema_name 'test',table_name 'test3'); CREATE FOREIGN TABLE test=# select * from ft_test3 ;  id ---- (0 rows) 本地机器事务测试（不提交） test=# begin; BEGIN test=# insert into ft_test3 values (100); INSERT 0 1 test=# insert into ft_test3 values (200); INSERT 0 1 test=# insert into ft_test3 values (300); INSERT 0 1 test=# select * from ft_test3 ;  id -----  100  200  300 (3 rows) test=# rollback; ROLLBACK test=# select * from ft_test3 ;  id ---- (0 rows) 本地机器事务测试（提交） test=# begin; BEGIN test=# insert into ft_test3 values (1000); INSERT 0 1 test=# insert into ft_test3 values (2000); INSERT 0 1 test=# insert into ft_test3 values (3000); INSERT 0 1 test=# end; COMMIT test=# select * from ft_test3 ;   id ------  1000  2000  3000 (3 rows) test=# rollback; NOTICE:  there is no transaction in progress ROLLBACK