关于postgresql数据库路由设置（dblink/fdw）

1、创建postgres_fdw：create extension postgres_fdw


2、查看postgres_fdw：select * from pg_extension;


3、用postgres_fdw创建远程路由：create server server_remote_70 foreign data wrapper postgres_fdw options
(host '192.168.11.70',port '5432',dbname 'postgres');


4、查询路由：select * from pg_foreign_server ;


5、用postgres_fdw创建mapping：create user mapping for postgres server server_remote_70 options
(user 'postgres',password 'postgres'); 


6、查看mapp用户：select * from pg_user_mappings;


7、创建路由表：CREATE FOREIGN TABLE tbl_fdw(id int) server server_remote_70 options 
(schema_name 'public',table_name 'tb1');  


8、查看路由表：select * from tbl_fdw ;


9、修改连接：ALTER SERVER server_remote_70 OPTIONS (set host '192.168.1.162');
update pg_foreign_server set srvoptions='{host=192.168.1.162,port=15432,dbname=test}' where srvname='server_remote_70'


10、修改用户：ALTER USER MAPPING FOR postgres SERVER server_remote_70 OPTIONS (SET password 'lgg@171125');


11、删除映射表： 




1、创建dblink：create extension dblink;


2、创建一个dblink连接：select dblink_connect('myconn', 'host=192.168.1.162 port=15432 dbname=test user=postgres password=lgg@171125');


3、通过连接“myconn”查询:SELECT * FROM dblink('myconn', 'select id, name from t1') AS t1(id varchar(10), name varchar(10));


4、插入数据：select dblink_exec('myconn', 'insert into mytable values(21,''z'',''{"a0","b0","c0"}'');');


5. 执行BEGIN命令SELECT dblink_exec('myconn', 'BEGIN');


6、SELECT dblink_exec('myconn', 'update，insert，create等命令');


7、 执行事务提交：SELECT dblink_exec('myconn', 'COMMIT');


8、 解除连接：SELECT dblink_disconnect('myconn')