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）:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
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=> |
二、还是以superuser用户，创建remote server，用于连接远程数据库。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
--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 |
注意此时修改pg_hba.conf文件，允许连接。
|
1 2 3 |
# TYPE DATABASE USER ADDRESS METHOD …… host all all 127.0.0.1/32 md5 |
三、以应用用户连接，创建user mapping：
|
1 2 3 |
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'); |
四、应用用户下创建 FOREIGN TABLE：
|
1 2 3 4 5 |
--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'); |
五、测试查询，以及尝试是否能更新（注，如果mapping user的时候，用的是读写用户，那么也是可以更新的）
|
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 -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=> |