oracle 跨schema查询,pg的跨库查询

mysql和mssql的跨库查询，基本只需要dbname.schema.table_name就可以实现，而pg的跨库查询，和oracle一样，需要通过类似dblink的方式来实现。pg在9.3之前建议使用dblink，在9.3之后，建议使用postgres_fwd(foreign-data wrapper)。

我们假设有个库mydb001，里面有2个用户mydb001_rw和mydb001_r，分别是读写用户和只读用户。有另外一个库dbprd2，里面也是有2个用户dbprd2_rw和dbprd2_r。

我们需要在mydb001库中利用mydb001_rw用户，去只读的查询dbprd2库的tb_orad_mutex表。

一、需要以superuser安装extension(注，如果你需要每个database都使用，那么每个database都要装一次这个extension，或者你也可以一开始就在template1中安装，那么后续新建的database也都会包含了这个extension):

psql -U dbmgr -d mydb001

--drop extension postgres_fdw;

create extension postgres_fdw;

mydb001=> \dx

List of installed extensions

Name | Version | Schema | Description

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

plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language

postgres_fdw | 1.0 | public | foreign-data wrapper for remote PostgreSQL servers

uuid-ossp | 1.1 | public | generate universally unique identifiers (UUIDs)

(3 rows)

mydb001=>

1

2

3

4

5

6

7

8

9

10

11

12

13

14

psql-Udbmgr-dmydb001

--dropextensionpostgres_fdw;

createextensionpostgres_fdw;

mydb001=>\dx

Listofinstalledextensions

Name|Version|Schema|Description

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

plpgsql|1.0|pg_catalog|PL/pgSQLprocedurallanguage

postgres_fdw|1.0|public|foreign-datawrapperforremotePostgreSQLservers

uuid-ossp|1.1|public|generateuniversallyuniqueidentifiers(UUIDs)

(3rows)

mydb001=>

二、还是以superuser用户，创建remote server，用于连接远程数据库。

--drop server remote_db;

create server remote_db foreign data wrapper postgres_fdw options(host '127.0.0.1',port '5432',dbname 'dbprd2');

mydb001=> \des

List of foreign servers

Name | Owner | Foreign-data wrapper

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

remote_db | dbmgr | postgres_fdw

(1 row)

mydb001=>

GRANT USAGE ON FOREIGN SERVER remote_db TO mydb001_rw;

GRANT USAGE ON FOREIGN SERVER remote_db TO mydb001_r;

\q

1

2

3

4

5

6

7

8

9

10

11

12

13

--dropserverremote_db;

createserverremote_dbforeigndatawrapperpostgres_fdwoptions(host'127.0.0.1',port'5432',dbname'dbprd2');

mydb001=>\des

Listofforeignservers

Name|Owner|Foreign-datawrapper

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

remote_db|dbmgr|postgres_fdw

(1row)

mydb001=>

GRANTUSAGEONFOREIGNSERVERremote_dbTOmydb001_rw;

GRANTUSAGEONFOREIGNSERVERremote_dbTOmydb001_r;

\q

注意此时修改pg_hba.conf文件，允许连接。

# TYPE DATABASE USER ADDRESS METHOD

……

host all all 127.0.0.1/32 md5

1

2

3

# TYPE DATABASE  USER   ADDRESS     METHOD

……

hostallall127.0.0.1/32md5

三、以应用用户连接，创建user mapping：

psql -U mydb001_rw -d mydb001

--drop user mapping for mydb001_rw server remote_db;

create user mapping for mydb001_rw server remote_db options(user 'dbprd2_r',password 'WTDw2#@e');

1

2

3

psql-Umydb001_rw-dmydb001

--dropusermappingformydb001_rwserverremote_db;

createusermappingformydb001_rwserverremote_dboptions(user'dbprd2_r',password'WTDw2#@e');

四、应用用户下创建 FOREIGN TABLE：

--drop FOREIGN TABLE db_dbprd2_tb_orad_mutex;

CREATE FOREIGN TABLE

db_dbprd2_tb_orad_mutex(appid integer,appkey character varying(40),appindex character varying(40) ,status integer)

server remote_db

options (schema_name 'dbprd2_rw',table_name 'tb_orad_mutex');

1

2

3

4

5

--dropFOREIGNTABLEdb_dbprd2_tb_orad_mutex;

CREATEFOREIGNTABLE

db_dbprd2_tb_orad_mutex(appidinteger,appkeycharactervarying(40),appindexcharactervarying(40),statusinteger)

serverremote_db

options(schema_name'dbprd2_rw',table_name'tb_orad_mutex');

五、测试查询，以及尝试是否能更新(注，如果mapping user的时候，用的是读写用户，那么也是可以更新的)

-- mydb001_rw用户查询dbprd2数据库的表。

-bash-4.2$ psql -U mydb001_rw -d mydb001

psql (9.6.2)

Type "help" for help.

mydb001=> select * from db_dbprd2_tb_orad_mutex limit 2;

appid | appkey | appindex | status

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

123456 | AAAAAAAAAAAAAAAA | lm | 999

654321 | BBBBBBBBBBBBBBB | abcdefghijklm | 999

(2 rows)

--由于之前的user mapping是通过只读用户连接，所以更新操作会报错：

mydb001=> begin;

BEGIN

mydb001=> update db_dbprd2_tb_orad_mutex set appindex='zxsaqwerre' where appid='654321' and appkey='BBBBBBBBBBBBBBB';

ERROR: permission denied for relation tb_orad_mutex

CONTEXT: Remote SQL command: UPDATE dbprd2_rw.tb_orad_mutex SET appindex = 'zxsaqwerre'::character varying(40) WHERE ((appid = 654321)) AND ((appkey = 'BBBBBBBBBBBBBBB'::text))

mydb001=> rollback;

ROLLBACK

mydb001=>

mydb001=>

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

--mydb001_rw用户查询dbprd2数据库的表。

-bash-4.2$psql-Umydb001_rw-dmydb001

psql(9.6.2)

Type"help"forhelp.

mydb001=>select *fromdb_dbprd2_tb_orad_mutexlimit2;

appid|appkey|appindex|status

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

123456|AAAAAAAAAAAAAAAA|lm|999

654321|BBBBBBBBBBBBBBB|abcdefghijklm|999

(2rows)

--由于之前的usermapping是通过只读用户连接，所以更新操作会报错：

mydb001=>begin;

BEGIN

mydb001=>updatedb_dbprd2_tb_orad_mutexsetappindex='zxsaqwerre'whereappid='654321'andappkey='BBBBBBBBBBBBBBB';

ERROR:permissiondeniedforrelationtb_orad_mutex

CONTEXT:RemoteSQLcommand:UPDATEdbprd2_rw.tb_orad_mutexSETappindex='zxsaqwerre'::charactervarying(40)WHERE((appid=654321))AND((appkey='BBBBBBBBBBBBBBB'::text))

mydb001=>rollback;

ROLLBACK

mydb001=>

mydb001=>