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用户，在集群重新初始化实例后，并不包含以上对象，需要配置后，才能用于集群管理。
KINGBASE研究院
