Postgresql 远程同步(非实时同步,小数据量)
来源:互联网 发布:印度军事 知乎 编辑:程序博客网 时间:2024/06/01 09:10
源端要开通目标的相关访问权限
目标端:
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;"
0 0
- Postgresql 远程同步(非实时同步,小数据量)
- 大数据量远程同步
- NFS 远程实时写入同步
- Oracle db_link数据同步(数据量小)
- 移动设备非实时快速同步方案
- mac fswatch+rsync 实时同步远程文件
- 大量小文件的实时同步方案
- 大量小文件的实时同步方案
- 大量小文件的实时同步方案
- 大量小文件的实时同步方案
- eclipse 远程文件实时同步,eclipse远程部署插件
- 同步,异步,阻塞,非阻塞。实时,分时,批处理,网络,分布式。
- AngularJS中ng-model如何实现非实时同步
- 使用rsync+inotify配置触发式(实时)远程同步
- 使用rsync+inotify配置触发式(实时)远程同步
- eclipse远程部署,静态文件实时同步插件
- 双向实时远程同步文件(inotify+rsync与nfs)
- 关于SVN+sersync实时同步到远程服务器的思路
- hdu4498 Function Curve
- inside the c++ object
- C++基础知识——变量存储位置
- 织梦DedeCMS自增函数[field:global name=autoindex/]
- 动态规划经典问题--TSP问题
- Postgresql 远程同步(非实时同步,小数据量)
- [一起学Hive]之四-Hive的安装配置
- c++第六次上机作业
- asp.net C#母版页和内容页事件排版加载顺序生命周期
- 彻底删除Delphi中ActiveX面板中的第三方控件
- 反省与自勉
- C++连接mysql数据库的两种方法
- Freeform SQL (FFSQL) - Tip - How to create TEMP(Temporary) tables
- handsontable一个Excel一样的外观数据网格组件