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图:

digoal’s 大量PostgreSQL文章入口