SOURCE side to open the target of the relevant access rights target: 1. Create a view of the remote table 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), ratio Integer, Status character (1), create_time timestamp without time zone,char_package_name character varying (+), version character Varying (8));
2. Establish the same judgment table and entity table as the remote table
CREATE TABLE tbl_version_update_control_info (ID integer NOT NULL, AppID character (a) not null, ratio integer D Efault 0 NOT NULL, status character (1) The default 0 NOT NULL, create_time timestamp without time zone DEFAULT now (), Char_package_name character varying (a), version character varying (8));
CREATE TABLE work_table_tbl_version_update_control_info (ID integer NOT NULL, AppID character () is not NULL, Rati o integer default 0 not NULL, status character (1) The default 0 NOT NULL, create_time timestamp without time zone defaul T now (), char_package_name character varying (a), version character varying (8));
3. Create a sync function create OR REPLACE function Sync_tbl_version_update_control_info () returns integer language Plpgsqlas $function $declarev_src_count int; --Storing source data statistics V_dst_count int; --Store the target data statistic data v_equal_count int; --the same data v_run int8 on the source and the target side; --Statistics run the number of functions, if greater than 1, the description exists, the function is run 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 R Aise Notice ' Another process is running, this would 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 (*) to 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_co Ntrol_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 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; in SERT into Tbl_version_update_control_info &nbsP (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;&NB Sp 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. Execute the function to synchronize and confirm the synchronization
Select Sync_tbl_version_update_control_info (); select count (*) from Tbl_version_update_control_info;
5. System Timer Task add:
2 * * */home/postgres/sync_data.sh >>/tmp/sync.log 2>&1cat/home/postgres/sync_data.sh
Echo-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 ()";d ate +%f\%techo-e "end Sync Tbl_version_update_control_info; "
Postgresql Remote Synchronization (non-real-time synchronization, small data volume)
