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

数据库 2024/11/23 佚名

3 2 1

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 * from dblink('test_dblink','select * from test') as t1(id int);

-------

1111

2222

3333

4444

6666

33333

7777

远程数据库中也存在

test=# select * from test;

-------

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

-------

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

-------

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-# '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 ;

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

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 ;

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

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

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;

----

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

----

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

-----

100

200

300

(3 rows)

test=# rollback;

ROLLBACK

test=# select * from ft_test3 ;

----

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

------

1000

2000

3000

(3 rows)

test=# rollback;

NOTICE: there is no transaction in progress

ROLLBACK

PostgreSQL,跨库访问

标签：

PostgreSQL,跨库访问

免责声明：本站文章均来自网站采集或用户投稿，网站不提供任何软件下载或自行开发的软件！如有用户或公司发现本站内容信息存在侵权行为，请邮件告知！ 858582#qq.com

评论“PostgreSQL数据库中跨库访问解决方案”

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

暂无“PostgreSQL数据库中跨库访问解决方案”评论...

www.imxmx.com 杰晶网络

8,675无损音乐

1,324高清电影

213破解软件

120,141站长资源

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

PostgreSQL,跨库访问

PostgreSQL中使用dblink实现跨库查询的方法

Windows下Postgresql下载与配置方法

评论“PostgreSQL数据库中跨库访问解决方案”

RTX 5090要首发性能要翻倍！三星展示GDDR7显存

更新动态

友情链接

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

PostgreSQL,跨库访问

PostgreSQL中使用dblink实现跨库查询的方法

Windows下Postgresql下载与配置方法

评论“PostgreSQL数据库中跨库访问解决方案”

RTX 5090要首发 性能要翻倍！三星展示GDDR7显存

更新动态

友情链接

RTX 5090要首发性能要翻倍！三星展示GDDR7显存