云数据库 PostgreSQL UNLOGGED 表使用说明

-- 查看表类型
SELECT 
    schemaname,
    tablename,
    CASE 
        WHEN relpersistence = 'u' THEN 'UNLOGGED'
        WHEN relpersistence = 'p' THEN 'PERMANENT' 
        ELSE 'OTHER'
    END as table_type
FROM pg_tables t
JOIN pg_class c ON c.relname = t.tablename
WHERE schemaname = 'public';

-- 将 UNLOGGED 表转换为普通表，unlogged_table_name 为表名称
ALTER TABLE unlogged_table_name SET LOGGED;

-- 锁定状态监控, mode 字段为锁类型，AccessExclusiveLock 为最严格的锁， unlogged_table_name 为表名称
SELECT 
    l.locktype,
    l.mode,
    l.granted,
    l.pid,
    a.state,
    a.query_start,
    now() - a.query_start as duration,
    CASE 
        WHEN l.granted THEN 'GRANTED'
        ELSE 'WAITING'
    END as lock_status,
    substring(a.query, 1, 80) as query_snippet
FROM pg_locks l
LEFT JOIN pg_stat_activity a ON l.pid = a.pid
WHERE l.relation IS NOT NULL 
    AND EXISTS (
        SELECT 1 FROM pg_class c 
        WHERE c.oid = l.relation 
        AND c.relname = 'unlogged_table_name'
    )
ORDER BY l.granted, a.query_start;

-- 对于大表，建议分批转换
-- 1. 创建新的 LOGGED 表
CREATE TABLE user_cache_new (LIKE user_cache);

-- 2. 分批迁移数据
INSERT INTO user_cache_new 
SELECT * FROM user_cache 
WHERE created_at >= '2024-01-01' AND created_at < '2024-01-02';

-- 3. 重命名表
BEGIN;
DROP TABLE user_cache;
ALTER TABLE user_cache_new RENAME TO user_cache;
COMMIT;