Postgresql 跨库同步表及postgres_fdw的用法说明

PostgreSQL 9.3 add postgres_fdw extension for accessing remote tables

-- 安装 postgres_fdw 插件

CREATE EXTENSION postgres_fdw;

-- 创建远程服务

CREATE SERVER remote_server FOREIGN DATA WRAPPER postgres_fdw;

-- 查看远程服务

select * from pg_foreign_server ;

-- 修改远程服务

alter server remote_server options ( add hostaddr '11.216.10.215' ,

        add port '5432' , add dbname 'db_des' );

-- SERVER赋权

grant usage on foreign server remote_server to postgres;

-- 在本地数据库中创建user mapping

CREATE USER MAPPING FOR postgres server remote_server options ( user 'postgres' , password 'xxxxx' );

-- 同样创建枚举

CREATE TYPE db_enum AS ENUM ( 'postgres' , 'sqlserver' , 'mysql' );

-- 查看枚举类型的oid

select oid from pg_type where typname= 'db_enum' ;

-- 创建外部表developer_month_orders_data

CREATE FOREIGN TABLE developer_month_orders_data(

  id integer not null ,

  developer_user_id integer ,

  package_id integer ,

  order_month date ,

  create_datetime timestamp

) SERVER remote_server options (schema_name 'public' ,table_name 'developer_month_orders_data' );

-- 查看外部表

select * from developer_month_orders_data;

drop user mapping for postgres server server_remote ;

drop server server_remote;

drop extension postgres_fdw ;

drop foreign table test1;

create extension postgres_fdw;

create server server_remote_rudy_01 foreign data wrapper postgres_fdw options(host ‘192.168.11.44 ',port ‘5432' ,dbname ‘vsphere_info');

select * from pg_foreign_server ;**

create user mapping for postgres server server_remote_rudy_01 options( user ‘vsphere ',password ‘viadmin' );

CREATE FOREIGN TABLE v1_cost(sample_time TIMESTAMP ,datacenter_id int4,host_id int4 ,cost NUMERIC ) server server_remote_rudy_01 options (schema_name ‘ public ',table_name ‘vi_cost' );

IMPORT FOREIGN SCHEMA public FROM SERVER server_remote_rudy_01 INTO public ;

IMPORT FOREIGN SCHEMA public limit to (t1) FROM SERVER server_remote_rudy_01 INTO public ;