Postgresql 远程同步(非实时同步，小数据量)

源端要开通目标的相关访问权限目标端：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,

源端要开通目标的相关访问权限 目标端： 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 plpgsql AS $function$ declare v_src_count int;   --存放源数据统计数据 v_dst_count int;  --存放目标端数据统计数据 v_equal_count int;  --源端和目标端相同的数据 v_run int8;      --统计运行改函数的进行数，如果大于1，说明存在，改函数在运行 begin v_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_coun t;   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>&1 cat /home/postgres/sync_data.sh
echo -e "start sync tbl_version_update_control_info;" date +%F\ %T psql -h 127.0.0.1 hank hank -c "select * from sync_tbl_version_update_control_info()"; date +%F\ %T echo -e "end sync tbl_version_update_control_info;"