PostgreSQL数据库中跨库访问解决方案

    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