源端要开通目标的相关访问权限目标端：1.建立远程表的视图create view v_bill_tbl_version_update_control_info as select * from dblink('hostaddr=10.10.10.8 port=4321 dbname=postgres user=postgres password=postgres', 'select id,appid,ratio,status,create_time,char_package_name,version from tbl_version_update_control_info') as t(id integer,appid character(20),ratio integer,status character(1),create_time timestamp without time zone,char_package_name character varying(50),version character varying(8)); 2.建立和远程表一样的判断表以及实体表create table tbl_version_update_control_info ( id integer not null, appid character(20) not null, ratio integer default 0 not null, status character(1) default 0 not null, create_time timestamp without time zone default now(), char_package_name character varying(50), version character varying(8));create table work_table_tbl_version_update_control_info ( id integer not null, appid character(20) not null, ratio integer default 0 not null, status character(1) default 0 not null, create_time timestamp without time zone default now(), char_package_name character varying(50), version character varying(8));3.建立同步函数create or replace function sync_tbl_version_update_control_info() returns integer language plpgsqlas $function$declarev_src_count int; --存放源数据统计数据v_dst_count int; --存放目标端数据统计数据v_equal_count int; --源端和目标端相同的数据v_run int8; --统计运行改函数的进行数，如果大于1，说明存在，改函数在运行beginv_src_count := 0;v_dst_count := 0;v_equal_count := 0;select count(*) into v_run from pg_stat_activity where query ~ 'sync_tbl_version_update_control_info';if v_run>1 then raise notice 'another process is running, this will exit soon.'; return 1;end if;if (pg_is_in_recovery()) then raise notice 'pg_is_in_recovery is true.'; return 1;end if;truncate table only work_table_tbl_version_update_control_info;insert into work_table_tbl_version_update_control_info (id,appid,ratio,status,create_time,char_package_name,version) select id,appid,ratio,status,create_time,char_package_name,version from v_bill_tbl_version_update_control_info;select count(*) into v_src_count from work_table_tbl_version_update_control_info;select count(*) into v_dst_count from tbl_version_update_control_info;raise notice 'v_src_count:%, v_dst_count:%',v_src_count,v_dst_count;if ( v_src_count = v_dst_count and v_src_count 0 ) then select count(*) into v_equal_count from work_table_tbl_version_update_control_info t1,tbl_version_update_control_info t2 where t1.id=t2.id and t1.appid = t2.appid and t1.ratio = t2.ratio and t1.status = t2.status and t1.create_time = t2.create_time and t1.char_package_name = t2.char_package_name and t1.version = t2.version; raise notice 'v_src_count:%, v_dst_count:%, v_equal_count:%',v_src_count,v_dst_count,v_equal_count; if ( v_equal_count v_src_count ) then truncate table only tbl_version_update_control_info; insert into tbl_version_update_control_info (id,appid,ratio,status,create_time,char_package_name,version) select id,appid,ratio,status,create_time,char_package_name,version from work_table_tbl_version_update_control_info; end if;elsif ( v_src_count v_dst_count and v_src_count 0 ) then truncate table only tbl_version_update_control_info; insert into tbl_version_update_control_info (id,appid,ratio,status,create_time,char_package_name,version) select id,appid,ratio,status,create_time,char_package_name,version from work_table_tbl_version_update_control_info;elsif v_src_count = 0 then raise notice 'error: src no data.'; return 1;end if; return 0;end;$function$4.执行函数进行同步并确认同步select sync_tbl_version_update_control_info();select count(*) from tbl_version_update_control_info;5.系统定时任务添加：15 2 * * * /home/postgres/sync_data.sh >>/tmp/sync.log 2>&1cat /home/postgres/sync_data.shecho -e start sync tbl_version_update_control_info;date +%f\ %tpsql -h 127.0.0.1 hank hank -c select * from sync_tbl_version_update_control_info();date +%f\ %techo -e end sync tbl_version_update_control_info;