pg的跨库查询

mysql和mssql的跨库查询,基本只需要dbname.schema.table_name就可以实现,而pg的跨库查询,和oracle一样,需要通过类似dblink的方式来实现。pg在9.3之前建议使用dblink,在9.3之后,建议使用postgres_fwd(foreign-data wrapper)。
 我们假设有个库myppaoo,里面有2个用户myppaoo_rw和myppaoo_r,分别是读写用户和只读用户。有另外一个库mycoon,里面也是有2个用户mycoon_rw和mycoon_r。
 我们需要在myppaoo库中利用myppaoo_rw用户,去只读的查询mycoon库的tb_orad_mutex表。

一、需要以superuser安装extension:

psql -U dbmgr -d myppaoo
--drop extension postgres_fdw;
create extension postgres_fdw;

myppaoo=> \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)

myppaoo=>

二、还是以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 'mycoon');
myppaoo=> \des
List of foreign servers
Name | Owner | Foreign-data wrapper
-----------+-------+----------------------
remote_db | dbmgr | postgres_fdw
(1 row)

myppaoo=>
GRANT USAGE ON FOREIGN SERVER remote_db TO myppaoo_rw;
GRANT USAGE ON FOREIGN SERVER remote_db TO myppaoo_r;
\q

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

# TYPE DATABASE  USER   ADDRESS     METHOD
……
host all all 127.0.0.1/32 md5

三、以应用用户连接,创建user mapping:

psql -U myppaoo_rw -d myppaoo
--drop user mapping for myppaoo_rw server remote_db;
create user mapping for myppaoo_rw server remote_db options(user 'mycoon_r',password 'WTDw2#@e');

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

--drop FOREIGN TABLE  db_mycoon_tb_orad_mutex;
CREATE FOREIGN TABLE
db_mycoon_tb_orad_mutex(appid integer,appkey character varying(40),appindex character varying(40) ,status integer)
server remote_db
options (schema_name 'mycoon_rw',table_name 'tb_orad_mutex');

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

-- myppaoo_rw用户查询mycoon数据库的表。
-bash-4.2$ psql -U myppaoo_rw -d myppaoo
psql (9.6.2)
Type "help" for help.

myppaoo=> select * from db_mycoon_tb_orad_mutex limit 2;
appid | appkey | appindex | status
--------+------------------+----------------+--------
123456 | AAAAAAAAAAAAAAAA | lm | 999
654321 | BBBBBBBBBBBBBBB | abcdefghijklm | 999
(2 rows)


--由于之前的user mapping是通过只读用户连接,所以更新操作会报错:
myppaoo=> begin;
BEGIN
myppaoo=> update db_mycoon_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 mycoon_rw.tb_orad_mutex SET appindex = 'zxsaqwerre'::character varying(40) WHERE ((appid = 654321)) AND ((appkey = 'BBBBBBBBBBBBBBB'::text))
myppaoo=> rollback;
ROLLBACK
myppaoo=>
myppaoo=>
One clap, two clap, three clap, forty?

By clapping more or less, you can signal to us which stories really stand out.