PostgreSQL如何获取表更新前的历史版本？对应BigQuery语法替代

PostgreSQL没有像BigQuery那样直接的FOR SYSTEM_TIME AS OF语法，但可以通过以下几种方式实现类似需求，其中PostgreSQL 13+的系统版本化表是最贴近的替代方案：

一、系统版本化表（PostgreSQL 13+，推荐） #

PostgreSQL 13引入了符合SQL:2011标准的系统版本化表，支持直接查询指定时间点的表状态，语法和BigQuery非常接近：

1. 开启表的系统版本化 #

• 创建新表时启用：

CREATE TABLE users (
    id INT PRIMARY KEY,
    name TEXT,
    email TEXT
) WITH (SYSTEM VERSIONING = TRUE);

• 对现有表启用（需先添加系统时间范围列）：

ALTER TABLE users ADD COLUMN sys_period TSTZRANGE NOT NULL DEFAULT tstzrange(CURRENT_TIMESTAMP, NULL);
ALTER TABLE users SET (SYSTEM VERSIONING = TRUE);

2. 查询指定时间点的历史版本 #

对应你在BigQuery中使用的FOR SYSTEM_TIME AS OF TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 6 HOUR)，PostgreSQL的写法为：

-- 两种等价写法任选其一
SELECT * FROM users FOR SYSTEM_TIME AS OF CURRENT_TIMESTAMP - INTERVAL '6 hours';
-- 或
SELECT * FROM users AS OF SYSTEM TIME CURRENT_TIMESTAMP - INTERVAL '6 hours';

二、未开启系统版本化的兼容方案 #

如果使用的是PostgreSQL 12及以下版本，或无法开启系统版本化，可选择以下方法：

1. 时间点恢复（PITR） #

若数据库开启了WAL日志（默认开启），可以通过基础备份+WAL日志恢复到指定时间点的完整数据库快照：

# 先创建基础备份
pg_basebackup -D /path/to/backup_dir -Ft -z -P

# 配置恢复目标时间（编辑recovery.conf或postgresql.conf + recovery.signal）
echo "recovery_target_time = '$(date -d '6 hours ago' +'%Y-%m-%d %H:%M:%S')'" >> /path/to/backup_dir/recovery.conf
echo "recovery_target_action = 'promote'" >> /path/to/backup_dir/recovery.conf

# 启动恢复后的数据库（此时为只读状态，可查询历史数据）
pg_ctl -D /path/to/backup_dir start

注意：此方法会恢复整个数据库到指定时间点，适合需要完整历史状态的场景。

2. 自定义历史表+触发器 #

通过手动创建历史表和触发器，记录每次数据变更的历史版本：

• 创建历史表：

CREATE TABLE users_history (
    id INT,
    name TEXT,
    email TEXT,
    changed_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    operation TEXT NOT NULL -- 记录操作类型：INSERT/UPDATE/DELETE
);

• 创建触发器函数：

CREATE OR REPLACE FUNCTION log_user_changes()
RETURNS TRIGGER AS $$
BEGIN
    CASE TG_OP
        WHEN 'UPDATE' THEN
            INSERT INTO users_history SELECT OLD.*, CURRENT_TIMESTAMP, 'UPDATE';
        WHEN 'DELETE' THEN
            INSERT INTO users_history SELECT OLD.*, CURRENT_TIMESTAMP, 'DELETE';
        WHEN 'INSERT' THEN
            INSERT INTO users_history SELECT NEW.*, CURRENT_TIMESTAMP, 'INSERT';
    END CASE;
    RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql;

• 绑定触发器到原表：

CREATE TRIGGER users_change_trigger
BEFORE INSERT OR UPDATE OR DELETE ON users
FOR EACH ROW EXECUTE FUNCTION log_user_changes();

• 查询6小时前的某条记录版本：

SELECT * FROM users_history
WHERE id = 1 -- 替换为目标记录ID
AND changed_at <= CURRENT_TIMESTAMP - INTERVAL '6 hours'
ORDER BY changed_at DESC
LIMIT 1;

内容的提问来源于stack exchange，提问作者Harshitha S V