KingbaseES V8R6集群运维案例之---数据库实例initdb后配置

案例说明：
KingbaseES V8R6集群在数据库实例启动时需加载repmgr插件，并且具有集群管理的用户esrep和存储元数据的数据库esrep库；但在手工initdb新的实例后，默认的实例将不包含repmgr extension及esrep库和esrep用户，需要手工配置，完善集群管理应用。

适用版本： KingbaseES V8R6

一、默认repmgr集群管理配置

如下图所示，需要在kingbase.conf中配置repmgr extension的加载：

如下所示，集群管理需要创建esrep用户、esrep库及repmgr插件：

test=# \l esrep
                             List of databases
 Name  | Owner  | Encoding |   Collate   |    Ctype    | Access privileges
-------+--------+----------+-------------+-------------+-------------------
 esrep | system | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
(1 row)


test=# \du esrep
           List of roles
 Role name | Attributes | Member of
-----------+------------+-----------
 esrep     | Superuser  | {}

esrep=# \dx repmgr
                 List of installed extensions
  Name  | Version | Schema |           Description
--------+---------+--------+----------------------------------
 repmgr | 5.1     | repmgr | Replication manager for Kingbase

二、执行initdb初始化实例

1、initdb初始化实例[kingbase@node101 bin]$ ./initdb -U system -W -E utf8 --enable-ci -D /data/kingbase/has/data

2、查看初始化实例extension配置
如下所示，在shared_preload_libraries配置中，默认无repmgr的加载：

[kingbase@node101 bin]$ cat /data/kingbase/has/data/kingbase.conf|grep -i shared_

shared_preload_libraries = 'liboracle_parser, synonym, plsql, force_view, kdb_flashback,plugin_debugger, plsql_plugin_debugger, plsql_plprofiler, ora_commands,
kdb_ora_expr, sepapower, dblink, sys_kwr, sys_ksh, sys_spacequota, sys_stat_statements, backtrace, kdb_utils_function, auto_bmr, sys_squeeze, src_restrict'

3、查看esrep和resrep库信息

如下所示，默认创建新实例后，并不包含esrep用户和esrep库：

prod=# \l esrep
                             List of databases
 Name  | Owner  | Encoding |   Collate   |    Ctype    | Access privileges
-------+--------+----------+-------------+-------------+-------------------

(0 row)

prod=# \du esrep
           List of roles
 Role name | Attributes | Member of
-----------+------------+-----------

三、配置repmgr extension及esrep用户和库

1、加载repmgr extension

2、创建esrep库和esrep用户

test=# create database esrep;
CREATE DATABASE
test=# create user esrep with superuser password 'Kingbaseha110';
CREATE ROLE
##注意：esrep用户的密码和.encpwd文件中用户密码要匹配

test=# alter user esrep with superuser;
ALTER ROLE
test=# \du esrep
           List of roles
 Role name | Attributes | Member of
-----------+------------+-----------
 esrep     | Superuser  | {}

3、创建repmgr extension

test=# \c esrep
You are now connected to database "esrep" as user "system".
esrep=# create extension repmgr;
CREATE EXTENSION

4、查看repmgr schema下的对象

如下所示，在创建repmgr extension后，自动创建repmgr schema及集群元数据存储对象：

esrep=# \d repmgr.*
                Table "repmgr.conf"
 Column  |  Type   | Collation | Nullable | Default
---------+---------+-----------+----------+---------
 node_id | integer |           | not null |
 key     | text    |           | not null |
 value   | text    |           | not null |

                                 Table "repmgr.events"
     Column      |           Type           | Collation | Nullable |      Default
-----------------+--------------------------+-----------+----------+-------------------
 node_id         | integer                  |           | not null |
 event           | text                     |           | not null |
 successful      | boolean                  |           | not null | true
 event_timestamp | timestamp with time zone |           | not null | CURRENT_TIMESTAMP
 details         | text                     |           |          |

               Index "repmgr.idx_monitoring_history_time"
      Column       |           Type           | Key? |    Definition
-------------------+--------------------------+------+-------------------
 last_monitor_time | timestamp with time zone | yes  | last_monitor_time
 standby_node_id   | integer                  | yes  | standby_node_id
btree, for table "repmgr.monitoring_history"

                           Table "repmgr.monitoring_history"
          Column           |           Type           | Collation | Nullable | Default
---------------------------+--------------------------+-----------+----------+---------
 primary_node_id           | integer                  |           | not null |
 standby_node_id           | integer                  |           | not null |
 last_monitor_time         | timestamp with time zone |           | not null |
 last_apply_time           | timestamp with time zone |           |          |
 last_wal_primary_location | pg_lsn                   |           | not null |
 last_wal_standby_location | pg_lsn                   |           |          |
 replication_lag           | bigint                   |           | not null |
 apply_lag                 | bigint                   |           | not null |
Indexes:
    "idx_monitoring_history_time" btree (last_monitor_time, standby_node_id)

                                  Table "repmgr.nodes"
      Column      |            Type            | Collation | Nullable |     Default
------------------+----------------------------+-----------+----------+-----------------
 node_id          | integer                    |           | not null |
 upstream_node_id | integer                    |           |          |
 active           | boolean                    |           | not null | true
 node_name        | text                       |           | not null |
 type             | text                       |           | not null |
 location         | text                       |           | not null | 'default'::text
 priority         | integer                    |           | not null | 100
 conninfo         | text                       |           | not null |
 repluser         | character varying(63 char) |           | not null |
 slot_name        | text                       |           |          |
 config_file      | text                       |           | not null |
 primary_seen     | boolean                    |           |          |
 lsn              | pg_lsn                     |           |          |
Indexes:
    "nodes_pkey" PRIMARY KEY, btree (node_id)
Check constraints:
    "nodes_type_check" CHECK (type = ANY (ARRAY['primary'::text, 'standby'::text, 'witness'::text, 'bdr'::text]))
Foreign-key constraints:
    "nodes_upstream_node_id_fkey" FOREIGN KEY (upstream_node_id) REFERENCES repmgr.nodes(node_id) DEFERRABLE
Referenced by:
    TABLE "repmgr.nodes" CONSTRAINT "nodes_upstream_node_id_fkey" FOREIGN KEY (upstream_node_id) REFERENCES repmgr.nodes(node_id) DEFERRABLE

       Index "repmgr.nodes_pkey"
 Column  |  Type   | Key? | Definition
---------+---------+------+------------
 node_id | integer | yes  | node_id
primary key, btree, for table "repmgr.nodes"

                           View "repmgr.replication_status"
          Column           |           Type           | Collation | Nullable | Default
---------------------------+--------------------------+-----------+----------+---------
 primary_node_id           | integer                  |           |          |
 standby_node_id           | integer                  |           |          |
 standby_name              | text                     |           |          |
 node_type                 | text                     |           |          |
 active                    | boolean                  |           |          |
 last_monitor_time         | timestamp with time zone |           |          |
 last_wal_primary_location | pg_lsn                   |           |          |
 last_wal_standby_location | pg_lsn                   |           |          |
 replication_lag           | text                     |           |          |
 replication_time_lag      | pg_catalog.interval      |           |          |
 apply_lag                 | text                     |           |          |
 communication_time_lag    | pg_catalog.interval      |           |          |

                   View "repmgr.show_nodes"
       Column       |  Type   | Collation | Nullable | Default
--------------------+---------+-----------+----------+---------
 node_id            | integer |           |          |
 node_name          | text    |           |          |
 active             | boolean |           |          |
 upstream_node_id   | integer |           |          |
 upstream_node_name | text    |           |          |
 type               | text    |           |          |
 priority           | integer |           |          |
 conninfo           | text    |           |          |

            Table "repmgr.voting_term"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 term   | integer |           | not null |
Indexes:
    "voting_term_restrict" UNIQUE, btree ((true))
Rules:
    voting_term_delete AS
    ON DELETE TO repmgr.voting_term DO INSTEAD NOTHING

 Index "repmgr.voting_term_restrict"
 Column |  Type   | Key? | Definition
--------+---------+------+------------
 bool   | boolean | yes  | (true)
unique, btree, for table "repmgr.voting_term"

四、总结
KingbaseES V8R6集群管理需要加载repmgr插件及创建集群管理的数据库esrep和esrep用户，在集群重新初始化实例后，并不包含以上对象，需要配置后，才能用于集群管理。