配置

注：
（1）zcbus-8.1-16-20230908.tar.gz版本支持
（2）发布节点添加时，选择使用POSTGRESQL类型，后台程序会自动适配KingbaseES V8 (R3)类型

（3）KingbaseES V8 (R3)库创建对象时名称默认大写

前提条件：源库配置

• 查询默认认证方式，下面修改sys_hba.conf时，需要和库默认认证方式保持一致

  show password_encryption;

  

• 如果远程读日志，需要设置读日志的权限，修改sys_hba.conf

  # IPv4 local connections:
  host    all             all        0.0.0.0/0               md5
  # Allow replication connections from localhost, by a user with the
  # replication privilege.
  host    replication     all        0.0.0.0/0               md5

• 修改sys_hba.conf执行sys_ctl命令生效

sys_ctl reload -D  指定sys_hba.conf所在目录
示例：sys_ctl reload -D /opt/Kingbase/ES/V8/data/data/

配置说明

用户创建：

create user zcbus with passwod '******';
grant connect on database *** to zcbus;
grant select on all tables in schema public to zcbus;
grant usage on schema *** to zcbus;

创建schema：

create schema "zcbus" AUTHORIZATION zcbus;

创建ddl_event_tbl表，并开启表REPLICA IDENTITY参数：

create table "zcbus"."ddl_event_tbl" (
"event" text,
"tag" text,
"objid" oid,
"object_type" text,
"schema_name" text,
"object_identity" text,
"query" text,
"op_time" timestamp default now()
);
alter table "zcbus"."ddl_event_tbl" REPLICA IDENTITY FULL;

创建zcbus_func_ddl_command：

create or replace function "zcbus".zcbus_func_ddl_command() returns event_trigger as $$  
declare  
  v1 text;
  r record;
begin
  select query into v1 from sys_stat_activity where pid=sys_backend_pid();
  -- RAISE NOTICE 'ddl event:%, command:%', tg_event, tg_tag; 
  if TG_EVENT='ddl_command_end' then  
    if TG_TAG != 'DROP TABLE' then
      SELECT * into r FROM sys_event_trigger_ddl_commands() limit 1;
      if r.classid > 0 then
        insert into "zcbus"."ddl_event_tbl"("event", "tag", "objid", "object_type", "schema_name", "object_identity", "query")  
          values(TG_EVENT, TG_TAG, r.objid, r.object_type, r.schema_name, r.object_identity, v1);
      end if;
    end if;
  end if;
  if TG_EVENT='sql_drop' then
    if TG_TAG != 'ALTER TABLE' then
      SELECT * into r FROM sys_event_trigger_dropped_objects() limit 1;
      -- RAISE NOTICE 'ddl event:%,%,%,%,%,%,%', TG_EVENT, TG_TAG, r.objid,r.object_type, r.schema_name, r.object_identity, v1;
      insert into "zcbus"."ddl_event_tbl"("event", "tag", "objid", "object_type", "schema_name", "object_identity", "query")  
        values(TG_EVENT, TG_TAG, r.objid,r.object_type, r.schema_name, r.object_identity, v1);
    end if;
  end if;
end;  
$$ language plsql strict;


创建zcbus_et_ddl_command和zcbus_et_ddl_drop：
```sql
CREATE EVENT TRIGGER zcbus_et_ddl_command on ddl_command_end EXECUTE PROCEDURE "zcbus".zcbus_func_ddl_command();
CREATE EVENT TRIGGER zcbus_et_ddl_drop on sql_drop EXECUTE PROCEDURE "zcbus".zcbus_func_ddl_command();

赋权：

grant select,trigger on table "zcbus"."ddl_event_tbl"  to zcbus;
grant execute ON FUNCTION "zcbus".zcbus_func_ddl_command to zcbus;
alter user zcbus REPLICATION;

查看用户的表权限

select * from information_schema.table_privileges where grantee='ZCBUS';

查看usage权限表

select * from information_schema.usage_privileges where grantee='ZCBUS';

查看存储过程函数相关权限

select * from information_schema.routine_privileges where grantee='ZCBUS';

同步表开启表REPLICA IDENTITY参数

开启REPLICA IDENTITY

\c database_name;
ALTER TABLE schema_name.table_name REPLICA IDENTITY FULL;
grant select on all tables in schema schema_name to zcbus;
grant usage on schema schema_name to zcbus;

生成修改表sql语句参考

SELECT 'ALTER TABLE '||nspname||'.'||relname||' REPLICA IDENTITY FULL;' FROM sys_class c,sys_namespace n where relnamespace = n.oid and c.relkind='r' and nspname='PUBLIC';

注：表所属用户或者超级用户才有开启REPLICA IDENTITY权限，否则报错：must be owner of table

查询REPLICA IDENTITY状态

SELECT c.relreplident FROM sys_class c,sys_namespace n where relnamespace = n.oid and c.relkind='r' and nspname='schema_name' and relname='table_name';

状态说明：

• d = DEFAULT - 更新和删除将包含primary key列的现前值
• n = NOTHING - 更新和删除将不包含任何先前值
• f = FULL - 更新和删除将包含所有列的先前值
• i = INDEX index name -?更新和删除事件将包含名为index name的索引定义中包含的列的先前值。

**** 回退方式：

drop owned by zcbus cascade;
drop user zcbus;