PG逻辑复制时,对表进行update 或者delete之后,解析出来的SQL需要在where条件上加以限制,从而防止出现在订阅端重放时出现较大的误差,这个时候就需要通过之前删除的旧值来做限定。
限制的方式主要分四种
1、default:默认通过主键进行限定(非系统表的默认值),仅保留主键旧值
2、nothing:不保留任何旧值(系统表默认)
3、full:保留所有旧值
4、using index :保留唯一索引的旧值,唯一索引列需要保证非空。
一般来说逻辑复制时,有主键是最好的,这样不用去修改直接可以用。其次是唯一索引,需要添加非空约束(如果业务上允许的话),但是需要手动去指定索引名。最后是full,这种方式会使得产生的WAL更大,不推荐。
当库里表比较多,而且大多是需要手动修改复制标识的时候,可以使用脚本、存储过程、匿名块、程序的方式进行,手动操作耗费时间且容易出错。下面贴个匿名块修改的demo
do language plpgsql
$$
declare
uniq text;
rec record;
cur1 cursor for select w.oid,w.relname,x.nspname from pg_class w,pg_namespace x where w.relnamespace=x.oid and w.relkind='r' and x.nspname not in (pg_catalog','information_schema') and w.oid not in (select a.oid from pg_class a,pg_constraint b,pg_namespace c where a.oid=b.conrelid and a.relnamespace=c.oid and c.nspname not in (information_schema','pg_catalog') and a.relkind='r' and b.contype='p') order by x.nspname;
begin
open cur1;
loop
fetch cur1 into rec;
exit when not found;
execute 'select c.relname from pg_index i,pg_class c where i.indisunique=true and i.indexrelid=c.oid and i.indrelid=$1' into uniq using rec.oid;
if uniq is null then
execute format('alter table %I.%I replica identity full',rec.nspname,rec.relname);
raise notice 'alter table %.% replica identity full;',rec.nspname,rec.relname;
else
begin
execute format('alter table %I.%I replica identity using index %I',rec.nspname,rec.relname,uniq);
raise notice 'alter table %.% replica identity using index %;',rec.nspname,rec.relname,uniq;
exception
WHEN wrong_object_type THEN
raise warning 'alter table %.% replica identity using index %;',rec.nspname,rec.relname,uniq;
end;
end if;
end loop;
close cur1;
end;
$$;
正常执行的话,会将执行修改的SQL以NOTICE的方式打印出来,如果有出错,如唯一索引列无非空约束,会以WARNING的方式打印以作区别。