Access permission for the source object to be enabled: 1. Create a remote table view createviewv_bill_tbl_version_update_control_infoasSELECT * FROMdblink (metadata, SELECTid, appid, ratio, status,
Access Permissions for the source object to be opened: 1. create view v_bill_tbl_version_update_control_info as SELECT * FROM dblink ('hostaddr = 10.10.8 port = 4321 dbname = s user = s password = s s', 'select id, appid, ratio, status,
Access Permissions for the source object to be opened: 1. create view v_bill_tbl_version_update_control_info as SELECT * FROM dblink ('hostaddr = 10.10.8 port = 4321 dbname = s user = s password = s s', '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 the same judgment table and entity table as the remote table.
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; -- Store source data statistics v_dst_count int; -- store destination data statistics v_1__count int; -- the same data v_run int8 on the source and target ends; -- count the number of operations of the function to be modified. If the value is greater than 1, it indicates that the function exists. The beginv_src_count: = 0; v_dst_count: = 0; v_1__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 partition (id, appid, ratio, status, create_time, char_package_name, version) select id, appid, ratio, status, create_time, char_package_name, version from nation; select count (*) into v_src_count from nation; select count (*) into v_dst_count from nation; 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_1__count from work_table_tbl_version_update_control_info t1, required t2 where t1.id = t2.id and t1.appid = between and between = between 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_1__count: % ', v_src_count, v_dst_count, v_1__count; if (v_1__count <> v_src_count) then truncate table ONLY limit; 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 begin; end if; elsif (v_src_count <> v_dst_count and v_src_count <> 0) then truncate table ONLY values; insert into values (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 for synchronization and confirm synchronization.
Select sync_tbl_version_update_control_info (); select count (*) from tbl_version_update_control_info;
5. Add a scheduled system task:
15 2 ***/home/S/sync_data.sh>/tmp/sync. log 2> & 1cat/home/S/sync_data.sh
Echo-e "start sync tbl_version_update_control_info;" date + % F \ % Tpsql-h 127.0.0.1 hank-c "select * from sync_tbl_version_update_control_info ()"; date + % F \ % Techo-e "end sync tbl_version_update_control_info ;"
