【PG】常见数据库、表属性设置

江无羡

于 2024-08-29 11:49:57 发布

阅读量57

点赞数

文章标签：数据库

本文链接：https://blog.csdn.net/HinstenyHisoka/article/details/141674553

版权

postgresql 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

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 $$;