PG的常见属性配置方法
数据库复制、备份相关
表的复制标识
单表操作
通过ALTER语句单独更改一张表的复制标识。
ALTER TABLE [tablename] REPLICA IDENTITY FULL;
批量表操作
通过代码块的方式,对某个schema中的所有表一起更新其复制标识。
SELECT
tablename,
CASE relreplident
WHEN 'd' THEN 'default'
WHEN 'n' THEN 'nothing'
WHEN 'f' THEN 'full'
WHEN 'i' THEN 'index'
END AS replica_identity
FROM
pg_tables
JOIN
pg_class ON pg_tables.tablename = pg_class.relname
LEFT JOIN
pg_index ON pg_class.oid = pg_index.indrelid AND indisprimary
WHERE
schemaname = 'public'; -- 替换为您实际的模式名称,默认为public
DO $$
DECLARE
row record;
BEGIN
FOR row IN SELECT tablename FROM pg_tables JOIN pg_class ON pg_tables.tablename = pg_class.relname LEFT JOIN pg_index ON pg_class.oid = pg_index.indrelid AND indisprimary WHERE schemaname = 'public' LOOP
EXECUTE format('ALTER TABLE %I REPLICA IDENTITY FULL;', row.tablename);
RAISE NOTICE 'Table % has been altered to have FULL replica identity.', row.tablename;
END LOOP;
END $$;