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

2024-02-22 11:28:03 353

PostgreSQL跨库访问有3种方法：Schema，dblink，postgres_fdw。

方法A：在PG上建立不同SCHEMA，将数据和存储过程分别放到不同的schema上，经过权限管理后进行访问。

方法A的示例如下：

测试1（测试postgres超级用户对不同schema下对象的访问）

查看当前数据库中的schema

postgres=#\dn

Listofschemas

Name|Owner

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

dbms_job_procedure|postgrespgagent|postgres

postgres|postgres

public|postgres

(4rows)

（当前连接数据库的用户为postgres）

postgres=#selectuser;

user

----------

postgres

(1row)

创建名为test1的schema

postgres=#createschematest1;

CREATESCHEMA

创建模式test1下的对象，表ticket1

postgres=#createtabletest1.ticket1(idint);

CREATETABLE

可以看到并没有我们之前建立的表

postgres=#\d

Listofrelations

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

(5rows)

在对象前加schema，postgres用户可以访问ticket1表

postgres=#select*fromtest1.ticket1;

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

(0rows)

查看模式搜索路径

postgres=#showsearch_path;

search_path

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

"$user",public

(1row)

把创建的模式test1添加到模式搜索路径

postgres=#setsearch_pathto"$user",public,test1;

SET

postgres=#showsearch_path;

search_path

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

"$user",public,test1

(1row)

为了访问方便，在搜索路径中添加schema对象之后既可以看到该模式下的表，也可以直接进行搜索，而不用添加schema前缀。（这里因为是超级用户，所以不用给postgres赋权，如果是普通用户，想要访问，需要赋权）

postgres=#\d

Listofrelations

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

(6rows)

postgres=#select*fromticket1;

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

(0rows)

测试2：

在postgres用户下建立名为test2的schema

postgres=#createschematest2;

CREATESCHEMA

postgres=#createtabletest2.ticket2(idint);

CREATETABLE

建立两个普通用户

postgres=#createroletest1loginpassword'123';

CREATEROLE

postgres=#createroletest2loginpassword'123';

CREATEROLE

普通用户连接数据库

postgres=#\cpostgrestest2;

Passwordforusertest2:

Youarenowconnectedtodatabase"postgres"asuser"test2".

postgres=>\d

Listofrelations

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

(5rows)

postgres=>showsearch_path;

search_path

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

"$user",public

(1row)

postgres=>setsearch_pathto"$user",public,test1;

SET

postgres=>\d

Listofrelations

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

(11rows)

可以看到test2用户模式下的ticket2表，但是访问时权限不足。

postgres=>select*fromtest2.ticket2;

ERROR:permissiondeniedforrelationticket2

postgres=>select*fromticket2;

ERROR:permissiondeniedforrelationticket2

通过postgres超级用户赋予权限，即可访问

postgres=#grantselectonalltablesinschematest2totest1;

GRANT

postgres=>select*fromtest2.ticket2;

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

(0rows)

postgres=>select*fromticket2;

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

(0rows)

方法B：通过dblink实现跨库访问

方法B测试示例如下：

环境：本地：192.168.56.88数据库：postgres

远程：192.168.56.99数据库：test

PostgreSQL通过dblink实现跨库访问

测试1：在同一个实例下分别建立两个数据库，通过dblink实现跨库访问

postgres=#createdatabasetest;

CREATEDATABASE

postgres=#\l

Listofdatabases

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

postgres |postgres|UTF8 |C |C |

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

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

test |postgres|UTF8 |C |C |

(4rows)

postgres=#\ctest

Youarenowconnectedtodatabase"test"asuser"postgres".

test=#createtabletest(idint);

CREATETABLE

test=#\d

Listofrelations

Schema|Name|Type | Owner

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

public|test|table|postgres

(1row)

test=#createtabletest2(idint);

CREATETABLE

test=#insertintotestvalues('1111');

INSERT01

test=#\cpostgres

Youarenowconnectedtodatabase"postgres"asuser"postgres".

在postgres数据库中建立dblink连接到test数据库

postgres=#createextensiondblink;

CREATEEXTENSION

postgres=#select*frompg_extension;

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

plpgsql| 10| 11|f |1.0 | |

dblink | 10| 2200|t |1.1 | |

(2rows)

postgres=#selectdblink_connect('test_dblink','dbname=testhost=localhostport=5432user=postgrespassword=postgres');

dblink_connect

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

(1row)

postgres=#select*fromdblink('test_dblink','select*fromtest')ast1(idint);

------

1111

(1row)

通过建立dblink，在postgres数据库可以很容易的访问到test数据库中的数据。

为了访问test数据库中的数据方便，我们可以建立一个视图，操作如下，我们只需要查询视图中的内容即可。

postgres=#CREATEVIEWtestdb_dblinkAS

postgres-#SELECT*FROMdblink('hostaddr=127.0.0.1port=5432dbname=testuser=postgrespassword=postgres','SELECT*Fromtest')ASt(idint);

CREATEVIEW

postgres=#\d

Listofrelations

Schema| Name |Type | Owner

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

public|ptest1 |table|postgres

public|ptest2 |table|postgres

public|remote_people_user_name|view |postgres

public|testdb_dblink |view |postgres

(4rows)

postgres=#select*fromtestdb_dblink;

------

1111

(1row)

测试2：

在两个实例下分别创建数据库，然后通过dblink实现垮库访问。

实例1：

首先需要配置下路由配置，添加一行命令-AINPUT-s192.168.0.0/16-jACCEPT

[root@darryetc]#vi/etc/sysconfig/iptables

...

添加-AINPUT-s192.168.0.0/16-jACCEPT 即允许192.168.0.0的网段访问

....

[root@darryetc]#serviceiptablesreload

iptables:Tryingtoreloadfirewallrules: [ OK ]

在IP为192.168.56.88（本地）的postgres数据库中建立extension

postgres=#createextensiondblink;

CREATEEXTENSION

postgres=#select *frompg_extension;

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

plpgsql| 10| 11|f |1.0 | |

dblink | 10| 2200|t |1.1 | |

(2rows)

建立dblink访问IP为192.168.56.99（远程）数据库

postgres=#selectdblink_connect('test_dblink','dbname=testhost=192.168.56.99port=5432user=postgrespassword=postgres');

dblink_connect

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

(1row)

postgres=#select*fromdblink('test_dblink','select*fromtest')ast1(idint);

------

1111

(1row)

跨库事务测试

连接远程数据库

postgres=#selectdblink_connect('test_dblink','dbname=testhost=192.168.56.99port=5432user=postgrespassword=postgres');

dblink_connect

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

(1row)

在远程服务器上开始一个事务

postgres=#selectdblink_exec('test_dblink','begin;');

dblink_exec

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

BEGIN

(1row)

插入一条数据

postgres=#selectdblink_exec('test_dblink','insertintotestvalues(7777);');

dblink_exec

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

INSERT01

(1row)

经查看远程服务器上已经插入一条数据

postgres=#select*fromdblink('test_dblink','select*fromtest')ast1(idint);

-------

1111

2222

3333

4444

6666

33333

7777

(11rows)

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

test=#select*fromtest;

-------

1111

2222

3333

4444

6666

33333

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

postgres=#selectdblink_exec('test_dblink','commit;');

dblink_exec

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

COMMIT

(1row)

再次查看

postgres=#select*fromdblink('test_dblink','select*fromtest')ast1(idint);

-------

1111

2222

3333

4444

6666

33333

7777

远程数据库中也存在

test=#select*fromtest;

-------

1111

2222

3333

4444

6666

33333

7777

若换成将commit替换成rollback则插入取消

postgres=#selectdblink_exec('test_dblink','begin;');

dblink_exec

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

BEGIN

(1row)

postgres=#selectdblink_exec('test_dblink','insertintotestvalues(99999);');

dblink_exec

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

INSERT01

postgres=#select*fromdblink('test_dblink','select*fromtest')ast1(idint);

-------

1111

2222

3333

4444

6666

33333

7777

99999

执行回滚操作

postgres=#selectdblink_exec('test_dblink','rollback;');

dblink_exec

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

ROLLBACK

(1row)

经查看回滚之后，不记录之前插入的数据

postgres=#select*fromdblink('test_dblink','select*fromtest')ast1(idint);

-------

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_fdwextension.

[postgres@minion1bin]$./psqltesttest

psql(9.3.9)

Type"help"forhelp.

test=#\ctesttest

Youarenowconnectedtodatabase"test"asuser"test".

test=#createextensionpostgres_fdw;

CREATEEXTENSION

2.在远程数据库上生成测试数据:

[postgres@minion4bin]$./psqltesttest

psql(9.3.9)

Type"help"forhelp.

test=#CREATETYPEuser_enumASENUM('foo','bar','buz');

CREATETYPE

test=#\dT

Listofdatatypes

Schema| Name |Description

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

public|user_enum|

(1row)

test=#selectoidfrompg_typewheretypname='user_enum';

oid

-------

16902

(1row)

test=#CREATESCHEMAtest;

CREATESCHEMA

test=#CREATETABLEtest.test1(

test(#c1intNOTNULL,

test(#c2intNOTNULL,

test(#c3text,

test(#c4timestamptz,

test(#c5timestamp,

test(#c6varchar(10),

test(#c7char(10),

test(#c8user_enum,

test(#CONSTRAINTt1_pkeyPRIMARYKEY(c1)

test(#);

CREATETABLE

test=#CREATETABLEtest.test2(

test(#c1intNOTNULL,

test(#c2text,

test(#CONSTRAINTt2_pkeyPRIMARYKEY(c1)

test(#);

CREATETABLE

test=#INSERTINTOtest.test1

test-#SELECTid,

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-#FROMgenerate_series(1,1000)id;

INSERT01000

test=#INSERTINTOtest.test2

test-#SELECTid,

test-# 'AAA'||to_char(id,'FM000')

test-#FROMgenerate_series(1,100)id;

INSERT0100

test=#analyzetest.test1;

ANALYZE

test=#analyzetest.test2;

ANALYZE

3.在本地数据库中创建server

test=#CREATESERVERs1FOREIGNDATAWRAPPERpostgres_fdw;

CREATESERVER

test=#select*frompg_foreign_server;

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

s1 | 17444| 17449| | | |

(1row)

test=#alterservers1options(addhostaddr'192.168.0.17',addport'5432',adddbname'test');

ALTERSERVER

4.SERVER赋权:

test=#grantusageonforeignservers1totest;

GRANT

test=#select*frompg_foreign_server;

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

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

s1 | 17444| 17449| | |{test=U/test}|{hostaddr=192.168.0.17,port=543

2,dbname=test}

(1row)

5.在本地数据库中创建usermapping:

test=#createusermappingfortestservers1options(user'test',password'test');

CREATEUSERMAPPING

6.在本地数据库中创建foreigntable

test=#CREATETYPEuser_enumASENUM('foo','bar','buz');

CREATETYPE

test=#\dT

Listofdatatypes

Schema| Name |Description

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

public|user_enum|

(1row)

test=#selectoidfrompg_typewheretypname='user_enum';

oid

-------

17453

(1row)

test=#CREATEFOREIGNTABLEft1(

test(# c0int,

test(# c1intNOTNULL,

test(# c2intNOTNULL,

test(# c3text,

test(# c4timestamptz,

test(# c5timestamp,

test(# c6varchar(10),

test(# c7char(10),

test(# c8user_enum

test(# )SERVERs1options(schema_name'test',table_name'test1');

CREATEFOREIGNTABLE

test=#select*fromft1limit1;

ERROR: column"c0"doesnotexist

CONTEXT: RemoteSQLcommand:SELECTc0,c1,c2,c3,c4,c5,c6,c7,c8FROMtest.test1

test=#alterforeigntableft1dropcolumnc0;

ALTERFOREIGNTABLE

test=#select*fromft1limit1;

c1|c2| c3 | c4 | c5 |c6| c7 |c8

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

1| 1|00001|1970-01-0200:00:00+08|1970-01-0200:00:00|1 |1 |foo

(1row)

test=#createforeigntableft2(c2text,c1intnotnull)servers1options(schema_name'test',table_name'test2');

CREATEFOREIGNTABLE

test=#select*fromft2limit1;

c2 |c1

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

AAA001| 1

(1row)

test=#createforeigntableft3(c2text,c3intnotnull)servers1options(schema_name'test',table_name'test2');

CREATEFOREIGNTABLE

test=#select*fromft3limit1;

ERROR: column"c3"doesnotexist

CONTEXT: RemoteSQLcommand:SELECTc2,c3FROMtest.test2

test=#alterforeigntableft3altercolumnc3options(column_name'c1');

ALTERFOREIGNTABLE

test=#select*fromft3limit1;

c2 |c3

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

AAA001| 1

(1row)

test=#createforeigntableft4(c2text,c3intoptions(column_name'c1')notnull)servers1options(schema_name'test',table_name'test2');

CREATEFOREIGNTABLE

test=#select*fromft4limit2;

c2 |c3

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

AAA001| 1

AAA002| 2

(2rows)

PostgreSQL跨库访问事务测试

远程机器创建测试表

test=#createtabletest3(idint);

CREATETABLE

test=#select*fromtest3;

----

(0rows)

本地机器测试

创建对应的外部表

test=#createforeigntableft_test3(idint)servers1options(schema_name'test',table_name'test3');

CREATEFOREIGNTABLE

test=#select*fromft_test3;

----

(0rows)

本地机器事务测试（不提交）

test=#begin;

BEGIN

test=#insertintoft_test3values(100);

INSERT01

test=#insertintoft_test3values(200);

INSERT01

test=#insertintoft_test3values(300);

INSERT01

test=#select*fromft_test3;

-----

100

200

300

(3rows)

test=#rollback;

ROLLBACK

test=#select*fromft_test3;

----

(0rows)

本地机器事务测试（提交）

test=#begin;

BEGIN

test=#insertintoft_test3values(1000);

INSERT01

test=#insertintoft_test3values(2000);

INSERT01

test=#insertintoft_test3values(3000);

INSERT01

test=#end;

COMMIT

test=#select*fromft_test3;

------

1000

2000

3000

(3rows)

test=#rollback;

NOTICE: thereisnotransactioninprogress

ROLLBACK

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

热门推荐

随机推荐