PGCloud beta-阿里云开发者社区

最近在写一个PGCloud的方案, 目的是降低运维人员的工作负担, 提供高可用, 节约成本, 提供精细化的报告和监控平台.

设计思路如图 :

PGCloud beta - 德哥@Digoal - The Heart,The World.

E-R图 :

分为几类角色 :

如图 :

1. 连接池(也可以是端口代理)

使用连接池可以灵活的根据当前的数据库状态判断是否需要切换连接。

使用连接池或端口代理的原因是可以在激活standby之前有一个缓冲，使得主库和备库可以达到完全一致。

2. 数据库(主,备)(当然也可以是主,主)

利用流复制来提供不依赖存储的高可用，这一点VMWARE的VFDD无法做到。

3. 仲裁

仲裁负责监控，更新当前集群的状态以及promote备库等。

4. 配置库

类似异步消息组件，在仲裁和连接池之间共享消息。

TO DO,

1. 现在只实现了自动failover, 接下来需要实现自动failback.

2. 实现平台化管理.

3. 实现根据负载情况动态切换主备.

4. promote的选择更加智能, 不光看priority.

5. 报表考虑用hadoop来做分析.

大部分逻辑全部使用plpgsql函数来实现.

具体实现的代码如下 :

-- 生产主库环境准备

新建 promote 超级用户

create role promote superuser nocreatedb nocreaterole noinherit login encrypted password 'md5c43b2a66ef200d4bf5072465a161fea';

新建 promote 库, check表

create database promote with owner postgres template template0 encoding 'UTF8';
\c promote promote
create table check_record (count int8,check_time timestamp(0));
insert into check_record (count,check_time) values (0,now());

pg_hba.conf 允许仲裁服务器连接

-- 仲裁数据库配置

-- pgcloud全局配置表序列

create sequence seq_pgcloud increment by 1 minvalue 1 no maxvalue start with  1 no cycle;

-- pgcloud全局日志序表列

create sequence seq_pgcloud_log increment by 1 minvalue 1 no maxvalue start with  1 no cycle;

-- 检测IP字段输入是否合法

create or replace function check_ip_format(i_ip text) returns boolean as $BODY$
declare
v_ip inet;
begin
v_ip := i_ip::inet;
return true;
exception
when others then
return false;
end;
$BODY$ language plpgsql;

create table soft_bit
(
id int primary key,
value text not null,
comment text,
create_time timestamp(0) not null,
modify_time timestamp(0) not null,
unique (value)
);

insert into soft_bit (id,value,create_time,modify_time) 
  values (nextval('seq_pgcloud'::regclass), 'x64', now(), now());
insert into soft_bit (id,value,create_time,modify_time) 
  values (nextval('seq_pgcloud'::regclass), 'i386', now(), now());

create table soft_name
(
id int primary key,
value text not null,
comment text,
create_time timestamp(0) not null, 
modify_time timestamp(0) not null,
unique (value)
);

insert into soft_name (id,value,create_time,modify_time) 
  values (nextval('seq_pgcloud'::regclass), 'RHEL', now(), now());
insert into soft_name (id,value,create_time,modify_time) 
  values (nextval('seq_pgcloud'::regclass), 'CentOS', now(), now());
insert into soft_name (id,value,create_time,modify_time) 
  values (nextval('seq_pgcloud'::regclass), 'PostgreSQL', now(), now());
insert into soft_name (id,value,create_time,modify_time) 
  values (nextval('seq_pgcloud'::regclass), 'pgbouncer', now(), now());

create table soft_ver
(
id int primary key,
value text not null,
comment text,
create_time timestamp(0) not null, 
modify_time timestamp(0) not null,
unique (value)
);

insert into soft_ver (id,value,create_time,modify_time) 
  values (nextval('seq_pgcloud'::regclass), '9.0.6', now(), now());
insert into soft_ver (id,value,create_time,modify_time) 
  values (nextval('seq_pgcloud'::regclass), '9.1.1', now(), now());
insert into soft_ver (id,value,create_time,modify_time) 
  values (nextval('seq_pgcloud'::regclass), '9.1.2', now(), now());
insert into soft_ver (id,value,create_time,modify_time) 
  values (nextval('seq_pgcloud'::regclass), '1.4.2', now(), now());
insert into soft_ver (id,value,create_time,modify_time) 
  values (nextval('seq_pgcloud'::regclass), '5.6', now(), now());

create table soft_role
(
id int primary key,
value text not null,
comment text,
create_time timestamp(0) not null, 
modify_time timestamp(0) not null,
unique (value)
);

insert into soft_role (id,value,create_time,modify_time) 
  values (nextval('seq_pgcloud'::regclass), 'primary', now(), now());
insert into soft_role (id,value,create_time,modify_time) 
  values (nextval('seq_pgcloud'::regclass), 'warm-standby', now(), now());
insert into soft_role (id,value,create_time,modify_time) 
  values (nextval('seq_pgcloud'::regclass), 'hot-standby', now(), now());

create table pool_ha_name
(
id int primary key,
value text not null,
comment text,
create_time timestamp(0) not null, 
modify_time timestamp(0) not null,
unique (value)
);

insert into pool_ha_name (id,value,create_time,modify_time) 
  values (nextval('seq_pgcloud'::regclass), '德哥的pgbouncer测试集群1', now(), now());

create table idc
(
id int primary key,
value text not null,
comment text,
create_time timestamp(0) not null, 
modify_time timestamp(0) not null,
unique (value)
);


insert into idc (id,value,create_time,modify_time) 
  values (nextval('seq_pgcloud'::regclass), '德哥的机房', now(), now());

create table server_sort
(
id int primary key,
value text not null,
comment text,
create_time timestamp(0) not null, 
modify_time timestamp(0) not null,
unique (value)
);

insert into server_sort (id,value,create_time,modify_time) 
  values (nextval('seq_pgcloud'::regclass), 'database', now(), now());
insert into server_sort (id,value,create_time,modify_time) 
  values (nextval('seq_pgcloud'::regclass), 'pool', now(), now());

create table user_sort
(
id int primary key,
value text not null,
comment text,
create_time timestamp(0) not null, 
modify_time timestamp(0) not null,
unique (value)
);

insert into user_sort (id,value,create_time,modify_time) 
  values (nextval('seq_pgcloud'::regclass), 'pgbouncer', now(), now());
insert into user_sort (id,value,create_time,modify_time) 
  values (nextval('seq_pgcloud'::regclass), 'promote', now(), now());
insert into user_sort (id,value,create_time,modify_time) 
  values (nextval('seq_pgcloud'::regclass), 'monitor', now(), now());

-- 服务器信息表

create table server
(
id int primary key,
virtual boolean not null, -- 是否虚拟IP
idc text not null references idc (value),
server_sort text not null references server_sort (value),
soft_name text not null references soft_name (value),
soft_ver text not null references soft_ver (value),
soft_bit text not null references soft_bit (value),
ip text not null check (check_ip_format(ip) is true),
cpu_cores int not null,
mem_gb int not null,
comment text,
create_time timestamp(0) not null,
modify_time timestamp(0) not null,
unique (idc,ip)
);

insert into server (id, virtual, idc, server_sort, soft_name, soft_ver, soft_bit, ip, cpu_cores, mem_gb, create_time, modify_time)
  values (nextval('seq_pgcloud'::regclass), false, '德哥的机房', 'database', 'RHEL', '5.6', 'x64', '172.16.3.33', 8, 8, now(), now());
insert into server (id, virtual, idc, server_sort, soft_name, soft_ver, soft_bit, ip, cpu_cores, mem_gb, create_time, modify_time)
  values (nextval('seq_pgcloud'::regclass), false, '德哥的机房', 'database', 'RHEL', '5.6', 'x64', '172.16.3.39', 8, 8, now(), now());
insert into server (id, virtual, idc, server_sort, soft_name, soft_ver, soft_bit, ip, cpu_cores, mem_gb, create_time, modify_time)
  values (nextval('seq_pgcloud'::regclass), false, '德哥的机房', 'pool', 'RHEL', '5.6', 'x64', '172.16.3.40', 8, 8, now(), now());
insert into server (id, virtual, idc, server_sort, soft_name, soft_ver, soft_bit, ip, cpu_cores, mem_gb, create_time, modify_time)
  values (nextval('seq_pgcloud'::regclass), false, '德哥的机房', 'pool', 'RHEL', '5.6', 'x64', '172.16.3.150', 8, 8, now(), now());
insert into server (id, virtual, idc, server_sort, soft_name, soft_ver, soft_bit, ip, cpu_cores, mem_gb, create_time, modify_time)
  values (nextval('seq_pgcloud'::regclass), true, '德哥的机房', 'pool', 'RHEL', '5.6', 'x64', '172.16.3.151', 8, 8, now(), now());

-- 主库表

create table db_primary
(
id int primary key,
server_id int not null references server(id),
soft_name text not null references soft_name (value),
soft_ver text not null references soft_ver (value),
soft_role text not null references soft_role (value), -- 角色,primary,hot-standby,warm-standby,standby激活后角色改变为primary.
soft_port int not null,
priority int not null default 0,
active boolean default true not null, -- 是否活动,不作为standby激活的判别
health boolean default true not null, -- 是否健康,primary被检测到不健康后变为false
trigger_file text not null,
failedcheck_count int not null default 0,  -- 如连续检测到10次failed后,health=false
comment text,
create_time timestamp(0) not null,
modify_time timestamp(0) not null, -- 记录soft_role,active,health的改变时间
unique (server_id,soft_port)
);

-- 添加主库函数

create or replace function add_db_primary
(i_idc text,
i_server_ip text,
i_soft_port int,
i_soft_name text,
i_soft_ver text,
i_soft_role text,
i_active boolean,
i_health boolean,
i_trigger_file text,
i_comment text)
returns int as $BODY$
declare
v_server_sort text;
v_server_id int;
begin
v_server_sort := 'database';
select id into v_server_id from server where idc=i_idc and ip=i_server_ip and server_sort=v_server_sort;
if not found then
  raise notice 'Server: % with server sort ''%'' not found in idc: %.',i_server_ip,v_server_sort,i_idc;
  raise exception '';
end if;
insert into db_primary(id, server_id, soft_name, soft_ver, soft_role, soft_port, active, health, trigger_file, comment, create_time, modify_time)
  values(nextval('seq_pgcloud'::regclass), v_server_id, i_soft_name, i_soft_ver, i_soft_role, i_soft_port, i_active, i_health, i_trigger_file, i_comment, now(), now());
return 0;
exception
when others then
  raise notice 'Add db primary in idc: %, server: %, port: % failed, Please check your input.', i_idc, i_server_ip, i_soft_port;
  return 1;
end;
$BODY$ language plpgsql;

select * from add_db_primary('德哥的机房','172.16.3.33',1921,'PostgreSQL','9.1.1','primary',true,true,'/pgdata/digoal/1921/data01/pg_root/promote.trigger.1921','测试db1');
select * from add_db_primary('德哥的机房','172.16.3.39',1922,'PostgreSQL','9.1.1','primary',true,true,'/pgdata/digoal/1922/data01/pg_root/promote.trigger.1922','测试db2');

-- 同一个primary的standby中只允许一台角色转换为primary.

create or replace function db_standby_role_validator (i_db_primary_id int,i_soft_role text) returns boolean as $BODY$
declare
v_standby_role text;
begin
v_standby_role := 'primary';
perform 1 from db_standby where db_primary_id=i_db_primary_id and soft_role=v_standby_role limit 1;
if found then
  if ( i_soft_role = v_standby_role ) then
    return false;
  end if;
end if;
return true;
exception
when others then
return false;
end;
$BODY$ language plpgsql;

-- 同一个primary的standby中只允许一台被激活.

create or replace function db_standby_promote_validator (i_db_primary_id int,i_promote boolean) returns boolean as $BODY$
declare
begin
perform 1 from db_standby where db_primary_id=i_db_primary_id and promote=true limit 1;
if found then
  if ( i_promote = true ) then
    return false;
  end if;
end if;
return true;
exception
when others then
return false;
end;
$BODY$ language plpgsql;

create table db_standby
(
id int primary key,
db_primary_id int not null references db_primary(id),
priority int not null default 1,
server_id int not null references server(id),
soft_name text not null references soft_name (value),
soft_ver text not null references soft_ver (value),
soft_role text not null references soft_role (value) check (db_standby_role_validator(db_primary_id,soft_role) is true),
soft_port int not null,
active boolean default true not null,
health boolean default true not null,
promote boolean default false not null check (db_standby_promote_validator(db_primary_id,promote) is true),
trigger_file text not null,
failedcheck_count int not null default 0,  -- 如连续检测到10次failed后,health=false
comment text,
create_time timestamp(0) not null,
modify_time timestamp(0) not null,
unique (server_id,soft_port),
unique (db_primary_id,priority)
);

create or replace function add_db_standby
(i_db_primary_idc text,
i_db_primary_server_ip text,
i_db_primary_soft_port int,
i_priority int,
i_idc text,
i_server_ip text,
i_soft_port int,
i_soft_name text,
i_soft_ver text,
i_soft_role text,
i_active boolean,
i_health boolean,
i_trigger_file text,
i_comment text)
returns int as $BODY$
declare
v_db_primary_server_id int;
v_db_primary_id int;
v_server_sort text;
v_server_id int;
begin
v_server_sort := 'database';
raise notice '1';
if (i_db_primary_soft_port=i_soft_port and i_db_primary_idc=i_idc and i_db_primary_server_ip=i_server_ip) then
  raise notice 'Primary''s info cann''t equal to standby''s info, Please check your input.';
  raise exception '';
end if;
raise notice '1';
select id into v_db_primary_server_id from server where idc=i_db_primary_idc and ip=i_db_primary_server_ip;
if not found then
  raise notice 'Server: % not found in idc: %.',i_db_primary_server_ip,i_db_primary_idc;
  raise exception '';
end if;
raise notice '1';
select id into v_db_primary_id from db_primary where server_id=v_db_primary_server_id and soft_port=i_db_primary_soft_port;
if not found then
  raise notice 'No db primary on idc: %, server: %, port: %.',i_db_primary_idc,i_db_primary_server_ip,i_db_primary_soft_port;
  raise exception '';
end if;
raise notice '1';
select id into v_server_id from server where idc=i_idc and ip=i_server_ip and server_sort=v_server_sort;
if not found then
  raise notice 'Server: % with server sort ''%'' not found in idc: %.',i_server_ip,v_server_sort,i_idc;
  raise exception '';
end if;
raise notice '1';
insert into db_standby(id, db_primary_id, priority, server_id, soft_name, soft_ver, soft_role, soft_port, active, health, trigger_file, comment, create_time, modify_time)
  values(nextval('seq_pgcloud'::regclass), v_db_primary_id, i_priority, v_server_id, i_soft_name, i_soft_ver, i_soft_role, i_soft_port, i_active, i_health, i_trigger_file, i_comment, now(), now());
return 0;
exception
when others then
  raise notice 'Add db standby in idc: %, server: %, port: % failed, Please check your input.', i_idc, i_server_ip, i_soft_port;
  return 1;
end;
$BODY$ language plpgsql;

select * from add_db_standby('德哥的机房','172.16.3.33',1921,1,'德哥的机房','172.16.3.39',1921,'PostgreSQL','9.1.1','hot-standby',true,true,'/pgdata/digoal/1921/data01/pg_root/promote.trigger.1921','测试db1-standby');
select * from add_db_standby('德哥的机房','172.16.3.39',1922,1,'德哥的机房','172.16.3.33',1922,'PostgreSQL','9.1.1','hot-standby',true,true,'/pgdata/digoal/1922/data01/pg_root/promote.trigger.1922','测试db2-standby');

create or replace function db_primary_poolcfg_validator(i_config text,i_db_primary_id int) returns boolean as $BODY$
declare
v_ip_config text;
v_ip_server text;
v_port_config text;
v_port_db_primary text;
begin
v_ip_config := substring(lower(i_config) from ' host *= *(\d+\.\d+\.\d+\.\d+) ');
select ip into v_ip_server from server where id=(select server_id from db_primary where id=i_db_primary_id);
v_port_config := substring(lower(i_config) from ' port=(\d+)');
select soft_port into v_port_db_primary from db_primary where id=i_db_primary_id;
if ( v_ip_config = v_ip_server and v_port_config = v_port_db_primary ) then
return true;
end if;
return fasle;
exception
when others then
return false;
end;
$BODY$ language plpgsql;

create table db_primary_poolcfg
(
id int primary key,
db_primary_id int not null references db_primary(id),
soft_name text not null references soft_name (value),
soft_ver text not null references soft_ver (value),
config_db_part text not null check(db_primary_poolcfg_validator(config_db_part,db_primary_id) is true),
config_pool_part text not null,
ver int not null check(ver>0),
comment text,
create_time timestamp(0) not null,
modify_time timestamp(0) not null,
unique (db_primary_id)
);

create or replace function add_db_primary_poolcfg
(i_db_primary_idc text,
i_db_primary_server_ip text,
i_db_primary_soft_port int,
i_soft_name text,
i_soft_ver text,
i_config_db_part text,
i_config_pool_part text,
i_ver int,
i_comment text)
returns int as $BODY$
declare
v_db_primary_server_id int;
v_db_primary_id int;
begin
select id into v_db_primary_server_id from server where idc=i_db_primary_idc and ip=i_db_primary_server_ip;
if not found then
  raise notice 'Server: % not found in idc: %.',i_db_primary_server_ip,i_db_primary_idc;
  raise exception '';
end if;
select id into v_db_primary_id from db_primary where server_id=v_db_primary_server_id and soft_port=i_db_primary_soft_port;
if not found then
  raise notice 'No db primary on idc: %, server: %, port: %.',i_db_primary_idc,i_db_primary_server_ip,i_db_primary_soft_port;
  raise exception '';
end if;
insert into db_primary_poolcfg(id, db_primary_id, soft_name, soft_ver, config_db_part, config_pool_part, ver, comment, create_time, modify_time)
  values(nextval('seq_pgcloud'::regclass), v_db_primary_id, i_soft_name, i_soft_ver, i_config_db_part, i_config_pool_part, i_ver, i_comment, now(), now());
return 0;
exception
when others then
  raise notice 'Add poolcfg with db idc: %, server: %, port: % failed, Please check your input.', i_db_primary_idc, i_db_primary_server_ip, i_db_primary_soft_port;
  return 1;
end;
$BODY$ language plpgsql;

select * from add_db_primary_poolcfg('德哥的机房','172.16.3.33',1921,'pgbouncer','1.4.2','[databases]
digoal = host=172.16.3.33 dbname=digoal port=1921 pool_size=8',
'[pgbouncer]
pool_mode = transaction
listen_port = replace_by_port
unix_socket_dir = /opt/pgbouncer/etc
listen_addr = *
auth_type = md5
auth_file = /opt/pgbouncer/etc/users.txt.replace_by_port
logfile = /dev/null
pidfile = /opt/pgbouncer/etc/pgbouncer.pid.replace_by_port
max_client_conn = 10000
reserve_pool_timeout = 0
server_reset_query =
admin_users = pgbouncer_admin
stats_users = pgbouncer_guest
ignore_startup_parameters = extra_float_digits
tcp_keepalive = 1
tcp_keepidle = 30
tcp_keepcnt = 3
tcp_keepintvl = 5',1,'测试db1连接池配置');

select * from add_db_primary_poolcfg('德哥的机房','172.16.3.39',1922,'pgbouncer','1.4.2','[databases]
digoal = host=172.16.3.39 dbname=digoal port=1922 pool_size=8',
'[pgbouncer]
pool_mode = transaction
listen_port = replace_by_port
unix_socket_dir = /opt/pgbouncer/etc
listen_addr = *
auth_type = md5
auth_file = /opt/pgbouncer/etc/users.txt.replace_by_port
logfile = /dev/null
pidfile = /opt/pgbouncer/etc/pgbouncer.pid.replace_by_port
max_client_conn = 10000
reserve_pool_timeout = 0
server_reset_query =
admin_users = pgbouncer_admin
stats_users = pgbouncer_guest
ignore_startup_parameters = extra_float_digits
tcp_keepalive = 1
tcp_keepidle = 30
tcp_keepcnt = 3
tcp_keepintvl = 5',1,'测试db2连接池配置');

create or replace function db_standby_poolcfg_validator(i_config text,i_db_standby_id int) returns boolean as $BODY$
declare
v_ip_config text;
v_ip_server text;
v_port_config text;
v_port_db_standby text;
begin
v_ip_config := substring(lower(i_config) from ' host *= *(\d+\.\d+\.\d+\.\d+) ');
select ip into v_ip_server from server where id=(select server_id from db_standby where id=i_db_standby_id);
v_port_config := substring(lower(i_config) from ' port=(\d+)');
select soft_port into v_port_db_standby from db_standby where id=i_db_standby_id;
if ( v_ip_config = v_ip_server and v_port_config = v_port_db_standby ) then
return true;
end if;
return fasle;
exception
when others then
return false;
end;
$BODY$ language plpgsql;

create table db_standby_poolcfg
(
id int primary key,
db_standby_id int not null references db_standby(id),
soft_name text not null references soft_name (value),
soft_ver text not null references soft_ver (value),
config_db_part text not null check(db_standby_poolcfg_validator(config_db_part,db_standby_id) is true),
ver int not null check(ver>0),
comment text,
create_time timestamp(0) not null,
modify_time timestamp(0) not null,
unique (db_standby_id)
);

create or replace function add_db_standby_poolcfg
(i_db_standby_idc text,
i_db_standby_server_ip text,
i_db_standby_soft_port int,
i_soft_name text,
i_soft_ver text,
i_config_db_part text,
i_ver int,
i_comment text)
returns int as $BODY$
declare
v_db_standby_server_id int;
v_db_standby_id int;
begin
select id into v_db_standby_server_id from server where idc=i_db_standby_idc and ip=i_db_standby_server_ip;
if not found then
  raise notice 'Server: % not found in idc: %.',i_db_standby_server_ip,i_db_standby_idc;
  raise exception '';
end if;
select id into v_db_standby_id from db_standby where server_id=v_db_standby_server_id and soft_port=i_db_standby_soft_port;
if not found then
  raise notice 'No db standby on idc: %, server: %, port: %.',i_db_standby_idc,i_db_standby_server_ip,i_db_standby_soft_port;
  raise exception '';
end if;
insert into db_standby_poolcfg(id, db_standby_id, soft_name, soft_ver, config_db_part, ver, comment, create_time, modify_time)
  values(nextval('seq_pgcloud'::regclass), v_db_standby_id, i_soft_name, i_soft_ver, i_config_db_part, i_ver, i_comment, now(), now());
return 0;
exception
when others then
  raise notice 'Add poolcfg with db idc: %, server: %, port: % failed, Please check your input.', i_db_standby_idc, i_db_standby_server_ip, i_db_standby_soft_port;
  return 1;
end;
$BODY$ language plpgsql;

select * from add_db_standby_poolcfg('德哥的机房','172.16.3.33',1922,'pgbouncer','1.4.2','[databases]
digoal = host=172.16.3.33 dbname=digoal port=1922 pool_size=8',1,'测试db2-standby连接池配置');

select * from add_db_standby_poolcfg('德哥的机房','172.16.3.39',1921,'pgbouncer','1.4.2','[databases]
digoal = host=172.16.3.39 dbname=digoal port=1921 pool_size=8',1,'测试db1-standby连接池配置');

create or replace function check_pool_ha_ip (i_ip text) returns boolean as $BODY$
declare
v_server_sort text;
begin
v_server_sort := 'pool';
perform 1 from server where ip=i_ip and server_sort=v_server_sort;
if found then
return true;
else
return false;
end if;
exception
when others then
return false;
end;
$BODY$ language plpgsql;

create table pool_ha_cfg
(
id int primary key,
ha_name text not null references pool_ha_name (value),
idc text not null references idc (value),
ip text not null check (check_ip_format(ip) is true and check_pool_ha_ip(ip) is true),  -- 必须包含VIP
comment text,
create_time timestamp(0) not null,
modify_time timestamp(0) not null,
unique (ip)
);

insert into pool_ha_cfg (id,ha_name,idc,ip,comment,create_time,modify_time)
  values (nextval('seq_pgcloud'::regclass), '德哥的pgbouncer测试集群1', '德哥的机房', '172.16.3.40', '德哥的pgbouncer测试集群1物理主机1', now(), now());
insert into pool_ha_cfg (id,ha_name,idc,ip,comment,create_time,modify_time)
  values (nextval('seq_pgcloud'::regclass), '德哥的pgbouncer测试集群1', '德哥的机房', '172.16.3.150', '德哥的pgbouncer测试集群1物理主机2', now(), now());
insert into pool_ha_cfg (id,ha_name,idc,ip,comment,create_time,modify_time)
  values (nextval('seq_pgcloud'::regclass), '德哥的pgbouncer测试集群1', '德哥的机房', '172.16.3.151', '德哥的pgbouncer测试集群1虚拟IP1', now(), now());

-- 所有的连接池必须注册再使用

create table registered_pool
(
id int primary key,
server_id int not null references server(id),  -- 连接池所在服务器
db_primary_id int not null references db_primary(id),  -- 连接池对应的主库,主库异常自动切到备库
soft_name text not null references soft_name (value),
soft_ver text not null references soft_ver (value),
port int not null,  -- 连接池监听端口
comment text,
create_time timestamp(0) not null,
modify_time timestamp(0) not null,
unique (server_id,db_primary_id),
unique (server_id,port)
);

create or replace function add_registered_pool
(
i_db_primary_idc text,
i_db_primary_server_ip text,
i_db_primary_soft_port int,
i_idc text,
i_server_ip text,
i_port int,
i_soft_name text,
i_soft_ver text,
i_comment text)
returns int as $BODY$
declare
v_db_primary_server_id int;
v_db_primary_id int;
v_server_sort text;
v_server_id int;
begin
v_server_sort := 'pool';

-- 检查要注册的池是否在已经加入到池的HA

perform 1 from pool_ha_cfg where ip=i_server_ip;
if not found then
  raise notice 'Ip: % not found in pool_ha_cfg, please check.',i_server_ip;
  raise exception '';
end if;
select id into v_db_primary_server_id from server where idc=i_db_primary_idc and ip=i_db_primary_server_ip;
if not found then
  raise notice 'Server: % not found in idc: %.',i_db_primary_server_ip,i_db_primary_idc;
  raise exception '';
end if;
select id into v_db_primary_id from db_primary where server_id=v_db_primary_server_id and soft_port=i_db_primary_soft_port;
if not found then
  raise notice 'No db primary on idc: %, server: %, port: %.',i_db_primary_idc,i_db_primary_server_ip,i_db_primary_soft_port;
  raise exception '';
end if;

-- 连接同一主库的连接池监听端口必须一致

perform db_primary_id,count(distinct port) from (select db_primary_id,port from registered_pool union all select v_db_primary_id as db_primary_id,i_port as port) t group by db_primary_id having count(distinct port) > 1;
if found then
  raise notice '连接同一主库的连接池监听端口必须一致.';
  raise exception '';
end if;

-- 连接同一主库的连接池所在的服务器必须在同一POOL_HA

perform count(distinct ha_name) from pool_ha_cfg where 
(idc,ip) in (select i_idc as idc,i_server_ip as ip union all select idc,ip from server where id in (select server_id from registered_pool where db_primary_id=v_db_primary_id)) having count(distinct ha_name) > 1;
if found then
  raise notice '连接同一主库的连接池所在的服务器必须在同一POOL_HA.';
  raise exception '';
end if;
select id into v_server_id from server where idc=i_idc and ip=i_server_ip and server_sort=v_server_sort;
if not found then
  raise notice 'Server: % with server sort ''pool'' not found in idc: %.',i_server_ip,i_idc;
  raise exception '';
end if;
insert into registered_pool(id, server_id, db_primary_id, soft_name, soft_ver, port, comment, create_time, modify_time)
  values(nextval('seq_pgcloud'::regclass), v_server_id, v_db_primary_id, i_soft_name, i_soft_ver, i_port, i_comment, now(), now());
return 0;
exception
when others then
  raise notice 'Add registered pool to idc: %, server: % with db primary idc: %, server: %, port: % failed, Please check your input.', i_idc, i_server_ip, i_db_primary_idc, i_db_primary_server_ip, i_db_primary_soft_port;
  return 1;
end;
$BODY$ language plpgsql;

select * from add_registered_pool('德哥的机房','172.16.3.33',1921,'德哥的机房','172.16.3.40',1921,'pgbouncer','1.4.2','测试db1连接池');
select * from add_registered_pool('德哥的机房','172.16.3.39',1922,'德哥的机房','172.16.3.40',1922,'pgbouncer','1.4.2','测试db1连接池');
select * from add_registered_pool('德哥的机房','172.16.3.33',1921,'德哥的机房','172.16.3.150',1921,'pgbouncer','1.4.2','测试db2连接池');
select * from add_registered_pool('德哥的机房','172.16.3.39',1922,'德哥的机房','172.16.3.150',1922,'pgbouncer','1.4.2','测试db2连接池');

create or replace function check_password (i_user_sort text,i_password text) returns boolean as $BODY$
declare
v_user_sort text;
begin
v_user_sort := 'pgbouncer';
if ( i_user_sort = v_user_sort ) then
if ( substring(i_password,1,3) <> 'md5' or char_length(i_password)<>35 ) then
  raise notice 'Password: % must from pg_shadow.passwd',i_password;
  raise exception '';
end if;
end if;
return true;
exception
when others then
return false;
end;
$BODY$ language plpgsql;

create table db_primary_usercfg
(
id int primary key,
db_primary_id int not null references db_primary(id),
user_sort text not null references user_sort (value),
username text not null,
password text not null check(check_password(user_sort,password) is true),
create_time timestamp(0) not null,
modify_time timestamp(0) not null,
unique (db_primary_id,user_sort,username)
);

create or replace function add_db_primary_usercfg
(i_db_primary_idc text,
i_db_primary_server_ip text,
i_db_primary_soft_port int,
i_user_sort text,
i_username text,
i_password text)
returns int as $BODY$
declare
v_db_primary_server_id int;
v_db_primary_id int;
begin
select id into v_db_primary_server_id from server where idc=i_db_primary_idc and ip=i_db_primary_server_ip;
if not found then
  raise notice 'Server: % not found in idc: %.',i_db_primary_server_ip,i_db_primary_idc;
  raise exception '';
end if;
select id into v_db_primary_id from db_primary where server_id=v_db_primary_server_id and soft_port=i_db_primary_soft_port;
if not found then
  raise notice 'No db primary on idc: %, server: %, port: %.',i_db_primary_idc,i_db_primary_server_ip,i_db_primary_soft_port;
  raise exception '';
end if;
update db_primary_poolcfg set ver=ver+1 where db_primary_id=v_db_primary_id;
update db_standby_poolcfg set ver=ver+1 where db_standby_id in (select id from db_standby where db_primary_id=v_db_primary_id);
insert into db_primary_usercfg(id, db_primary_id, user_sort, username, password, create_time, modify_time)
  values(nextval('seq_pgcloud'::regclass), v_db_primary_id, i_user_sort, i_username, i_password, now(), now());
return 0;
exception
when others then
  raise notice 'Add usercfg with db primary idc: %, server: %, port: % failed, Please check your input.', i_db_primary_idc, i_db_primary_server_ip, i_db_primary_soft_port;
  return 1;
end;
$BODY$ language plpgsql;

select * from add_db_primary_usercfg('德哥的机房','172.16.3.33',1921,'pgbouncer','digoal','md5462f71c79368ccf422f8a773ef40074d');
select * from add_db_primary_usercfg('德哥的机房','172.16.3.39',1922,'pgbouncer','digoal','md5462f71c79368ccf422f8a773ef40074d');
select * from add_db_primary_usercfg('德哥的机房','172.16.3.33',1921,'promote','promote','md5c43b2a66ef200d4bf5072465a161fea');
select * from add_db_primary_usercfg('德哥的机房','172.16.3.39',1922,'promote','promote','md5c43b2a66ef200d4bf5072465a161fea');

create table department
(
id int primary key,
part_name text not null,
up_id int references department(id),
comment text,
create_time timestamp(0) not null,
modify_time timestamp(0) not null,
unique (part_name)
);

create or replace function add_department(i_part_name text, i_up_part_name text, i_comment text)
returns int as $BODY$
declare
v_up_id int;
begin
if i_up_part_name is not null then
select id into v_up_id from department where part_name=i_up_part_name;
if not found then
  raise notice 'The up_department: % of department: % not found, Please check, If the department: % no up_department, Please keep i_up_part_name''s value to null.',i_up_part_name,i_part_name,i_part_name;
  raise exception '';
end if;
end if;
insert into department(id, part_name, up_id, comment, create_time, modify_time)
  values(nextval('seq_pgcloud'::regclass), i_part_name, v_up_id, i_comment, now(), now());
return 0;
exception
when others then
  raise notice 'Add department: % failed, Please check your input.',i_part_name;
  return 1;
end;
$BODY$ language plpgsql;

select * from add_department('研发中心',null,'研发,质量,运维,测试,客服,产品运营');
select * from add_department('质量运维部','研发中心','质量,运维,测试,客服');
select * from add_department('运维部','质量运维部','服务器,存储,网络,业务,数据库运维');

create table cluster
(
id int primary key,
alias text not null,
department_id int not null references department(id),
db_primary_id int not null references db_primary(id),
comment text not null,
create_time timestamp(0) not null,
modify_time timestamp(0) not null,
unique (alias)
);

create or replace function add_cluster
(i_alias text, i_department_name text, i_db_primary_idc text, i_db_primary_ip text, i_db_primary_port int, i_comment text)
returns int as $BODY$
declare
v_department_id int;
v_db_primary_server_id int;
v_db_primary_id int;
begin
select id into v_department_id from department where part_name=i_department_name;
if not found then
  raise notice 'Department: % not found.',i_department_name;
  raise exception '';
end if;
select id into v_db_primary_server_id from server where idc=i_db_primary_idc and ip=i_db_primary_ip;
if not found then
  raise notice 'Server: % not in idc: %.',i_db_primary_ip,i_db_primary_idc;
  raise exception '';
end if;
select id into v_db_primary_id from db_primary where server_id=v_db_primary_server_id and soft_port=i_db_primary_port;
if not found then
  raise notice 'No db primary on idc: %, server: %, port: %.',i_db_primary_idc,i_db_primary_ip,i_db_primary_port;
  raise exception '';
end if;
insert into cluster(id, alias, department_id, db_primary_id, comment, create_time, modify_time)
  values(nextval('seq_pgcloud'::regclass), i_alias, v_department_id, v_db_primary_id, i_comment, now(), now());
return 0;
exception
when others then
  raise notice 'Add cluster: % failed, Please check your input.',i_alias;
  return 1;
end;
$BODY$ language plpgsql;

select * from add_cluster('digoal1','运维部','德哥的机房','172.16.3.33',1921,'pgcloud测试');
select * from add_cluster('digoal2','运维部','德哥的机房','172.16.3.39',1922,'pgcloud测试');

-- 黑名单,在黑名单中的ID不响应promote,check,failback等操作.

-- 例如需要手工维护一个数据库时,可能会用到.

create table db_primary_black
(
id int primary key,
db_primary_id int not null references db_primary(id),
comment text,
create_time timestamp(0) not null,
unique (db_primary_id)
);

create table db_standby_black
(
id int primary key,
db_standby_id int not null references db_standby(id),
comment text,
create_time timestamp(0) not null,
unique (db_standby_id)
);

-- pgcloud库添加dblink extension.

-- pgbouncer服务器上需要知道配置是否需要更新,从版本和ID来判断.

create or replace function get_poolcfg_ver
(i_idc text, -- registered_pool中对应的server_id的idc
i_ip text, -- registered_pool中对应的server_id的ip
i_port int, -- registered_pool中的port
out o_id int, 
out o_ver int)
as $BODY$
declare
v_soft_role text;
v_db_primary_id int;
v_server_id int;
begin
o_id := 999999;
o_ver := 999999;
v_soft_role := 'primary';
select id into v_server_id from server where idc=i_idc and ip=i_ip;
select db_primary_id into v_db_primary_id from registered_pool where server_id=v_server_id and port=i_port;
perform 1 from db_primary where id=v_db_primary_id and active=true and health=true and soft_role=v_soft_role;
if found then
  select id,ver into o_id,o_ver from db_primary_poolcfg where db_primary_id = v_db_primary_id;
  return;
end if;
perform 1 from db_standby where db_primary_id=v_db_primary_id and soft_role=v_soft_role and active=true and health=true limit 1;
if found then
  select id,ver into o_id,o_ver from db_standby_poolcfg where db_standby_id = (select id from db_standby where db_primary_id=v_db_primary_id and soft_role=v_soft_role and active=true and health=true order by priority limit 1);
  return;
end if;
o_id := 999999;
o_ver := 999999;
return;
exception
when others then
raise notice 'Get poolcfg ver error.';
o_id := 999999;
o_ver := 999999;
return;
end;
$BODY$ language plpgsql;

-- psql -q -A -t -h 127.0.0.1 -p 1921 -U pgcloud pgcloud -c "select 'ID'||o_id||'VER'||o_ver from get_poolcfg_ver('德哥的机房','172.16.3.40',1923)"

-- 如果结果为ID999999VER999999或者与本地保存的信息一致,则不更新配置,

create or replace function promote_standby 
(i_db_standby_id int, OUT o_result int) as $BODY$
declare
v_ip text;
v_port int;
v_dbname text;
v_user text;
v_password text;
v_trigger_file text;
v_db_primary_id int;
v_promote_sql text;
v_sql text;
v_user_sort text;
begin
o_result := 1;
v_dbname := 'promote';
v_user_sort := 'promote';
perform 1 from db_standby_black where db_standby_id = i_db_standby_id;
if found then
  raise notice 'The db standby: % in black.',i_db_standby_id;
  raise exception '';
end if;
select trigger_file,soft_port,db_primary_id into v_trigger_file,v_port,v_db_primary_id from db_standby where id=i_db_standby_id;
if not found then
  raise notice 'The db standby: % not found.',i_db_standby_id;
  raise exception '';
end if;
select username,password into v_user,v_password from db_primary_usercfg where db_primary_id=v_db_primary_id and user_sort=v_user_sort limit 1;
select ip into v_ip from server where id=(select server_id from db_standby where id=i_db_standby_id);
v_promote_sql := 'copy (select now()) to '''''||v_trigger_file||'''''';
v_sql := 'select dblink_exec(''hostaddr='||v_ip||' port='||v_port||' dbname='||v_dbname||' user='||v_user||' password='||v_password||''','''||v_promote_sql||''',true)';
raise notice 'v_sql: %, v_promote_sql: % .', v_sql, v_promote_sql;
execute v_sql;
o_result := 0;
return;
exception
when others then
  raise notice 'Promote db standby % failed.',i_db_standby_id;
  o_result := 1;
  return;
end;
$BODY$ language plpgsql;

create or replace function check_db_primary
(
i_primary_id int,
OUT o_result int
) as $BODY$
declare
v_failedcheck_count_health_max int;
v_failedcheck_count_active_max int;
v_failedcheck_count int;
v_idc text;
v_ip text;
v_port int;
v_username text;
v_password text;
v_dbname text;
v_user_sort text;
v_sql text;
v_check_sql text;
v_result text;
v_promote_result int;
v_db_standby_role text;
v_db_standby_id int;
begin
-- 返回值0正常,1不正常,2health=false,3active=false,4异常.
o_result := 1;
v_failedcheck_count_health_max := 10;
v_failedcheck_count_active_max := v_failedcheck_count_health_max + 20;
v_dbname := 'promote';
v_user_sort := 'promote';
v_check_sql := 'update check_record set count=count+1,check_time=now();';
v_db_standby_role := 'primary';
perform 1 from db_primary_black where db_primary_id = i_primary_id;
if found then
  raise notice 'The db primary: % in black.',i_primary_id;
  raise exception '';
end if;
select t1.idc,t1.ip,t2.soft_port,t3.username,t3.password into v_idc,v_ip,v_port,v_username,v_password from server t1,db_primary t2,db_primary_usercfg t3 where t1.id=t2.server_id and t2.id=t3.db_primary_id and t2.id=i_primary_id and t3.user_sort=v_user_sort limit 1;
v_sql := 'select dblink_exec(''hostaddr='||v_ip||' port='||v_port||' dbname='||v_dbname||' user='||v_username||' password='||v_password||''','''||v_check_sql||''',false)';
raise notice 'v_sql: %, v_check_sql:% .',v_sql,v_check_sql;
execute v_sql into v_result;
if ( v_result ~* 'ERROR' ) then
  select failedcheck_count into v_failedcheck_count from db_primary where id=i_primary_id;
  if ( v_failedcheck_count >= v_failedcheck_count_health_max ) then
    if ( v_failedcheck_count >= v_failedcheck_count_active_max ) then
      update db_primary set failedcheck_count=failedcheck_count+1,health=false,active=false,modify_time=now() where id=i_primary_id;
      -- 查看是否有promote的standby,没有则调用 promote.
      select id into v_db_standby_id from db_standby where db_primary_id=i_primary_id and soft_port=v_db_standby_role;
      select o_result into v_promote_result from promote_standby(v_db_standby_id);
      if ( v_promote_result = 0 ) then
        update db_standby set promote=true where id=v_db_standby_id;
 o_result := 3;
 return;
      end if;
      o_result := 3;
      return;
    else
      update db_primary set failedcheck_count=failedcheck_count+1,health=false,modify_time=now() where id=i_primary_id;
      -- 查看是否有role='primary'的standby,没有则修改role='primary'.
      perform 1 from db_standby where db_primary_id=i_primary_id and soft_role=v_db_standby_role and active=true and health=true limit 1;
      if not found then
        update db_standby set soft_role=v_db_standby_role where id = (select id from db_standby where db_primary_id=i_primary_id and active=true and health=true order by priority limit 1);
      end if;
      o_result := 2;
      return;
    end if;
  end if;
  update db_primary set failedcheck_count=failedcheck_count+1 where id=i_primary_id;
  o_result := 1;
  return;
end if;
update db_primary set failedcheck_count=0,health=true,modify_time=now() where id=i_primary_id and health=false;
o_result := 0;
return;
exception
when others then
raise notice 'Check db primary idc:% ip:% port:% error.',v_idc,v_ip,v_port;
o_result := 1;
return;
end;
$BODY$ language plpgsql;

create or replace function check_db_standby
(
i_standby_id int,
OUT o_result int
) as $BODY$
declare
v_failedcheck_count_health_max int;
v_failedcheck_count_active_max int;
v_failedcheck_count int;
v_idc text;
v_ip text;
v_port int;
v_username text;
v_password text;
v_dbname text;
v_user_sort text;
v_sql text;
v_check_sql text;
v_result text;
begin
-- 返回值0正常,1不正常,2health=false,3active=false.
o_result := 1;
v_failedcheck_count_health_max := 10;
v_failedcheck_count_active_max := v_failedcheck_count_health_max + 20;
v_dbname := 'promote';
v_user_sort := 'promote';
v_check_sql := 'select check_time::text from check_record limit 1;';
perform 1 from db_standby_black where db_standby_id = i_standby_id;
if found then
  raise notice 'The db standby: % in black.',i_standby_id;
  raise exception '';
end if;
select t1.idc,t1.ip,t2.soft_port,t3.username,t3.password into v_idc,v_ip,v_port,v_username,v_password from server t1,db_standby t2,db_primary_usercfg t3 where t1.id=t2.server_id and t2.db_primary_id=t3.db_primary_id and t2.id=i_standby_id and t3.user_sort=v_user_sort limit 1;
v_sql := 'select info from dblink(''hostaddr='||v_ip||' port='||v_port||' dbname='||v_dbname||' user='||v_username||' password='||v_password||''','''||v_check_sql||''',false) as t(info text)';
raise notice 'v_sql: %, v_check_sql:% .',v_sql,v_check_sql;
execute v_sql into v_result;
update db_standby set failedcheck_count=0,health=true,modify_time=now() where id=i_standby_id and health=false;
o_result := 0;
return;
exception
when others then
select failedcheck_count into v_failedcheck_count from db_standby where id=i_standby_id;
if ( v_failedcheck_count >= v_failedcheck_count_health_max ) then
  if ( v_failedcheck_count >= v_failedcheck_count_active_max ) then
    update db_standby set failedcheck_count=failedcheck_count+1,health=false,active=false,modify_time=now() where id=i_standby_id;
    o_result := 3;
    return;
  end if;
  update db_standby set failedcheck_count=failedcheck_count+1,health=false,modify_time=now() where id=i_standby_id;
  o_result := 2;
  return;
end if;
update db_standby set failedcheck_count=failedcheck_count+1 where id=i_standby_id;
o_result := 1;
return;
end;
$BODY$ language plpgsql;


create or replace function get_pool_cfg(i_registered_pool_idc text, i_registered_pool_ip text, i_registered_pool_port int,OUT o_result text) as $BODY$
declare
v_db_cfg text;
v_pool_cfg text;
v_server_id int;
v_db_primary_id int;
v_soft_role text;
begin
-- 最新配置,先搜索db_primary是否激活并健康,符合要求则取primary的配置,否则取standby的配置
v_soft_role := 'primary';
select id into v_server_id from server where idc=i_registered_pool_idc and ip=i_registered_pool_ip;
select db_primary_id into v_db_primary_id from registered_pool where server_id=v_server_id and port=i_registered_pool_port;
perform 1 from db_primary where id=v_db_primary_id and active=true and health=true;
if found then
  select config_db_part,config_pool_part into v_db_cfg,v_pool_cfg from db_primary_poolcfg where db_primary_id=v_db_primary_id;
  v_pool_cfg := regexp_replace(v_pool_cfg,'replace_by_port',i_registered_pool_port::text,'g');
o_result := v_db_cfg||'
'||v_pool_cfg;
  return;
end if;
perform 1 from db_standby where db_primary_id=v_db_primary_id and active=true and health=true and soft_role=v_soft_role limit 1;
if found then
  select config_pool_part into v_pool_cfg from db_primary_poolcfg where db_primary_id=v_db_primary_id;
  select config_db_part into v_db_cfg from db_standby_poolcfg where db_standby_id=(select id from db_standby where db_primary_id=db_primary_id and active=true and health=true and soft_role=v_soft_role order by priority limit 1);
  v_pool_cfg := regexp_replace(v_pool_cfg,'replace_by_port',i_registered_pool_port::text,'g');
o_result := v_db_cfg||'
'||v_pool_cfg;
  return;
end if;
o_result := 'NO';
return;
exception
when others then
o_result := 'NO';
return;
end;
$BODY$ language plpgsql;

-- 注意, 更新db_primary_poolcfg.config_db_part和db_standby_poolcfg.config_db_part 需同时更新ver

create or replace function get_pool_users(i_registered_pool_idc text, i_registered_pool_ip text, i_registered_pool_port int) 
returns setof text as $BODY$
declare
v_db_primary_id int;
v_server_id int;
v_user_sort text;
begin
v_user_sort := 'pgbouncer';
select id into v_server_id from server where idc=i_registered_pool_idc and ip=i_registered_pool_ip;
select db_primary_id into v_db_primary_id from registered_pool where server_id=v_server_id and port=i_registered_pool_port;
return query select '"'||username||'" "'||password||'"' from db_primary_usercfg where db_primary_id=v_db_primary_id and user_sort=v_user_sort order by username;
exception
when others then
return next 'NO';
return;
end;
$BODY$ language plpgsql;

-- 主备failback操作,恢复配置版本和健康状态.

create or replace function failback (i_db_primary_id int) returns int as $BODY$
declare
v_primary_role text;
v_standby_role text;
begin
v_primary_role := 'primary';
v_standby_role := 'hot-standby';
update db_primary set soft_role=v_primary_role,active=true,health=true,failedcheck_count=0 where id=i_db_primary_id;
update db_standby set soft_role=v_standby_role,active=true,health=true,failedcheck_count=0,promote=false where db_primary_id=i_db_primary_id;
return 0;
exception
when others then
return 1;
end;
$BODY$ language plpgsql;

create table monitor_server_log
(
server_id int not null references server(id),
load_1 numeric not null,
load_5 numeric not null,
load_15 numeric not null,
used_mem_gb numeric not null,
cached_mem_gb numeric not null,
cpu_user numeric not null,
cpu_system numeric not null,
cpu_iowait numeric not null,
cpu_idle numeric not null,
create_time timestamp(0) not null
);

create table pgcloud_redo_log
(
id int primary key,
redo_sql text not null,
create_time timestamp(0) not null
);

-- shell

仲裁服务器 :

检查 db_primary

检查 db_standby

pgbouncer服务器 :

检查是否需要更新配置

#!/bin/bash

IDC="德哥的机房"
IP="172.16.3.40"
PORT="1921"

DELAY=1
POOL_MONITOR_HOME=/opt/pool_monitor
POOL_MONITOR_HOME_PID=$POOL_MONITOR_HOME/run.$PORT
POOL_HOME=/opt/pgbouncer
POOL_ETC=$POOL_HOME/etc
VER_FILE=$POOL_ETC/ver.$PORT
CONFIG_FILE=$POOL_ETC/config.$PORT
USER_FILE=$POOL_ETC/users.txt.$PORT

if [ -f $POOL_MONITOR_HOME_PID ]; then
  PID=`cat $POOL_MONITOR_HOME_PID`
  ps -p $PID
  if [ $? -eq 0 ]; then
    echo "pool monitor is already running."
    exit 1
  fi
fi

echo $$ > $POOL_MONITOR_HOME_PID

while [ true ]
do
  OLD_VER=`cat $VER_FILE`
  VER=`psql -A -t -q -h 127.0.0.1 pgcloud pgcloud -c "select * from get_poolcfg_ver('$IDC','$IP',$PORT);"`
  if [ ! -f $VER_FILE ] || [ $OLD_VER != $VER ]; then
    psql -A -t -q -h 127.0.0.1 pgcloud pgcloud -c "select * from get_pool_cfg('$IDC','$IP',$PORT);" > $CONFIG_FILE
    psql -A -t -q -h 127.0.0.1 pgcloud pgcloud -c "select * from get_pool_users('$IDC','$IP',$PORT);" > $USER_FILE
    $POOL_HOME/bin/pgbouncer -R -d $CONFIG_FILE
  fi
  echo $VER > $VER_FILE
  sleep $DELAY
done

PGCloud beta

热门文章

最新文章

相关电子书