权限配置

#前提条件：源库配置

#逻辑级别日志
wal_level = logical
#设置日志保留个数，设置太少了，日志量太大了日志自动删除后，容易读不到日志
wal_keep_segments = 10
#如果需要远程读日志，需要设置，至少为1，就接受1个读日志线程
max_wal_senders = 10


**以下过程中，web页面不要点击注册按键，所有配置手工完成！
**

用postgres系统账号登录pg库，执行如下(数据库命令行执行)：

postgres=# create user zcbus with password  'zcbus';
postgres=# grant connect on database db01 to zcbus;
postgres=#create schema zcbus AUTHORIZATION zcbus;

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()
);

create or replace function zcbus_func_ddl_command() returns event_trigger as $$  
declare  
  v1 text;
  r record;
begin
  select query into v1 from pg_stat_activity where pid=pg_backend_pid();
  -- RAISE NOTICE 'ddl event:%, command:%', tg_event, tg_tag; 
  if TG_EVENT='ddl_command_end' then  
    SELECT * into r FROM pg_event_trigger_ddl_commands();
    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;
  if TG_EVENT='sql_drop' then
    if TG_TAG != 'ALTER TABLE' then
      SELECT * into r FROM pg_event_trigger_dropped_objects();
      insert into zcbus.ddl_event_tbl(event, tag, object_type, schema_name, object_identity, query)  
        values(TG_EVENT, TG_TAG, r.object_type, r.schema_name, r.object_identity, v1);
    end if;
  end if;
end;  
$$ language plpgsql strict;

postgres=# CREATE EVENT TRIGGER zcbus_et_ddl_command on ddl_command_end EXECUTE PROCEDURE zcbus_func_ddl_command();  
postgres=# CREATE EVENT TRIGGER zcbus_et_ddl_drop on sql_drop EXECUTE PROCEDURE zcbus_func_ddl_command();
postgres=#grant select,trigger on table zcbus.ddl_event_tbl  to zcbus;
postgres=#grant execute ON FUNCTION zcbus_func_ddl_command to zcbus;
postgres=#alter user zcbus REPLICATION;

****切换到db01库下执行

postgres-# \c db01
db01=# grant select on all tables in schema public to zcbus;
db01=# grant execute on function pg_hba_file_rules to zcbus;

****添加新表，需要postgres账号开启表的补充日志模式(切换到同步库执行)，并且要再次执行select权限

db01=#ALTER TABLE public.tb02 REPLICA IDENTITY FULL;
db01=#grant select on all tables in schema public to zcbus;

****回退方式：

postgres-# drop owned by zcbus cascade;
postgres-# drop user zcbus;