您现在的位置是：网站首页> 编程资料编程资料

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

2023-05-27 563人已围观

简介 PostgreSQL数据库中跨库访问解决方案_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;

-------------------------------------------------

(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;

--------------------------------------------

(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;

---------------------------------------------------

(0 rows)

postgres=> select * from ticket2;

---------------------------------------------------

(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

-----------+----------+----------+---------+-------+-----------------------

postgres | postgres | UTF8 | C | C |

template0 | postgres | UTF8 | C | C | =c/postgres +

template1 | postgres | UTF8 | C | C | =c/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;

---------+----------+--------------+----------------+------------+-----------+--------------

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

----------------

(1 row)

postgres=# select * from dblink('test_dblink','select * from test') as t1(id int);

------

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 ;

------

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;

---------+----------+--------------+----------------+------------+-----------+--------------

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

----------------

(1 row)

postgres=# select * from dblink('test_dblink','select * from test') as t1(id int);

------

1111

(1 row)

跨库事务测试

连接远程数据库

postgres=# select dblink_connect('test_dblink','dbname=test host=192.168.56.99 port=5432 user=postgres password=postgres');

dblink_connect

----------------

(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);

-------

1111

2222

3333

4444

6666

33333

7777

(11 rows)

在远程数据库中查看未发现数据，因为事务未提交

test=# select * from test;

-------

1111

2222

3333

4444

6666

33333

在本地数据库中提交远程连接数据库中的事务

postgres=# select dblink_exec('test_dblink','commit;');

dblink_exec

-------------

COMMIT

(1 row)

再次查看

postgres=# select * f

上一篇：Windows下Postgresql下载与配置方法_PostgreSQL_

下一篇：PostgreSQL中使用dblink实现跨库查询的方法_PostgreSQL_

您现在的位置是：网站首页> 编程资料编程资料

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

相关内容

点击排行

本栏推荐

猜你喜欢