快活林资源网

PostgreSQL数据库中跨库访问解决方案
数据库 2024/11/15 佚名
3 1
快活林资源网 Design By www.csstdc.com

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

快活林资源网 Design By www.csstdc.com
广告合作：本站广告合作请联系QQ：858582 申请时备注：广告合作（否则不回）
免责声明：本站资源来自互联网收集,仅供用于学习和交流,请遵循相关法律法规,本站一切资源不代表本站立场,如有侵权、后门、不妥请联系本站删除！
快活林资源网 Design By www.csstdc.com
www.csstdc.com 快活林资源网
120,135影音资源
344,641技术资源
22,817软件资源
435,032站长资源

更新日志

友情链接

杰晶网络 DDR爱好者之家 桃源资源网 杰网资源 富贵资源网 南强小屋 铁雪资源网 幽灵资源网 万梅资源网 狼山资源网 白云岛资源网 昆仑资源网 相思资源网 明霞山资源网 内蒙古资源网 黑松山资源网 茶园资源网 饿虎岗资源网 大旗谷资源网 常春岛资源网 岱庙资源网 兴国资源网 快活林资源网 蝙蝠岛资源网 帝王谷资源网 白云城资源网 伏龙阁资源网 清风细雨楼 天枫庄资源网 圆月山庄资源网 无争山庄资源网 神水资源网 移花宫资源网 神剑山庄资源网 无为清净楼资源网 金钱帮资源网 丐帮资源网 华山资源网 极乐门资源网 小李飞刀资源网 凤求凰客栈 风云阁资源网 金狮镖局 鸳鸯亭资源网 千金楼资源网 更多链接
快活林资源网 Design By www.csstdc.com