postgres_fdw操作
pg自带postgres_fdw插件,无需额外安装。
1. 增
创建postgres_fdw插件
CREATE EXTENSION postgres_fdw;
创建foreign server
CREATE SERVER foreign_server
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host '192.83.123.89', port '5432', dbname 'foreign_db');
创建user mapping
CREATE USER MAPPING FOR local_user
SERVER foreign_server
OPTIONS (user 'foreign_user', password 'password');
创建foreign table
CREATE FOREIGN TABLE foreign_table (
id integer NOT NULL,
data text
)
SERVER foreign_server
OPTIONS (schema_name 'some_schema', table_name 'some_table');
2. 删
删除foreign table
DROP FOREIGN TABLE foreign_table;
删除user mapping
DROP USER MAPPING [ IF EXISTS ] FOR { user_name | USER | CURRENT_USER | PUBLIC } SERVER server_name;
删除 foreign server
DROP SERVER foreign_server;
3. 改
修改foreign server配置
ALTER SERVER foo OPTIONS (host 'foo', dbname 'foodb');
ALTER SERVER foo VERSION '8.4' OPTIONS (SET host 'baz');
修改user mapping
ALTER USER MAPPING FOR bob SERVER foo OPTIONS (user 'bob', password 'public');
修改foreign table
ALTER FOREIGN TABLE distributors ALTER COLUMN street SET NOT NULL;
ALTER FOREIGN TABLE myschema.distributors OPTIONS (ADD opt1 'value', SET opt2, 'value2', DROP opt3 'value3');
4. 查
查看 foreign server
SELECT * FROM pg_foreign_server;
查看user mapping
SELECT * FROM pg_user_mappings;