Postgresql Remote Synchronization (non-real-time synchronization, small data volume)

Source: Internet
Author: User

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)

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.