PostgreSQL Archived in the Cloud
建立一个集中调度的PostgreSQL归档系统。
特点:
1. 生产库存储指纹信息。
2. 调度库存储
指纹信息，以及生产库的其他补充信息。
归档服务器（存储）信息。
生产库与归档服务器的映射信息，包括优先级信息等等。
归档日志信息。
3. DNS存储环境IP与域名信息。
4. Nagios 负责实时监控归档状态信息。
方案概貌 :
数据结构:
digoal=> \d arch_db_info
Table "digoal.arch_db_info"
Column | Type | Modifiers
---------------+-----------------------------+-----------
id | uuid | not null
idc | text |
platform | text |
software | text |
version | text |
vip | inet |
port | integer |
create_time | timestamp without time zone |
modify_time | timestamp without time zone |
active_status | boolean |
Indexes:
"arch_db_info_pkey" PRIMARY KEY, btree (id)
"uk_db_info_1" UNIQUE, btree (idc, vip, port)
Referenced by:
TABLE "arch_path_map" CONSTRAINT "fk_path_1" FOREIGN KEY (db_id) REFERENCES arch_db_info(id)
digoal=> \d arch_path_info
Table "digoal.arch_path_info"
Column | Type | Modifiers
---------------+-----------------------------+-----------
id | integer | not null
ssh_ip | inet |
ssh_port | integer |
ssh_user | text |
root_path | text |
create_time | timestamp without time zone |
modify_time | timestamp without time zone |
active_status | boolean |
Indexes:
"arch_path_info_pkey" PRIMARY KEY, btree (id)
"uk_path_info_1" UNIQUE, btree (ssh_ip, ssh_port, root_path)
Referenced by:
TABLE "arch_path_map" CONSTRAINT "fk_path_2" FOREIGN KEY (path_id) REFERENCES arch_path_info(id)
digoal=> \d arch_path_map
Table "digoal.arch_path_map"
Column | Type | Modifiers
---------------+-----------------------------+-----------
id | integer | not null
db_id | uuid |
path_id | integer |
priority | integer |
active_status | boolean |
create_time | timestamp without time zone |
modify_time | timestamp without time zone |
Indexes:
"arch_path_map_pkey" PRIMARY KEY, btree (id)
"uk_path_map_1" UNIQUE, btree (db_id, path_id)
Foreign-key constraints:
"fk_path_1" FOREIGN KEY (db_id) REFERENCES arch_db_info(id)
"fk_path_2" FOREIGN KEY (path_id) REFERENCES arch_path_info(id)
digoal=> \d arch_remote_log
Table "digoal.arch_remote_log"
Column | Type | Modifiers
----------------+-----------------------------+-----------
db_id | uuid |
ssh_ip | inet |
ssh_port | integer |
ssh_user | text |
full_path | text |
wal_size_bytes | bigint |
create_time | timestamp without time zone |
status | boolean |
digoal=> \d arch_local_log
Table "digoal.arch_local_log"
Column | Type | Modifiers
----------------+-----------------------------+-----------
db_id | uuid |
full_path | text |
wal_size_bytes | bigint |
create_time | timestamp without time zone |
status | boolean |
其他功能围绕集中调度开发。
ER图: