在 PostgreSQL 里如何处理数据的版本跟踪和回滚？

在数据库管理中，数据的版本跟踪和回滚是非常重要的功能，有助于在数据操作出现错误或需要回滚到特定状态时进行有效的处理。PostgreSQL 提供了几种方法来实现数据的版本跟踪和回滚，包括事务、保存点、版本控制扩展等。

一、事务

事务是数据库操作的基本单元，它确保一系列的操作要么全部成功执行，要么全部回滚，从而保证数据的一致性和完整性。

1. 事务的基本操作

在 PostgreSQL 中，可以使用 BEGIN 语句开始一个事务，COMMIT 语句提交事务，使事务中的更改永久生效，或者使用 ROLLBACK 语句回滚事务，撤销事务中所做的所有更改。

BEGIN;
-- 一系列的数据操作
UPDATE table_name SET column1 = value1 WHERE condition;
INSERT INTO table_name (column1, column2) VALUES (value1, value2);
-- 如果一切正常
COMMIT;
-- 如果出现错误
ROLLBACK;

2. 事务的隔离级别

PostgreSQL 支持四种事务隔离级别：READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ 和 SERIALIZABLE。不同的隔离级别对并发事务中的数据可见性和一致性有不同的影响。

READ COMMITTED 是 PostgreSQL 的默认隔离级别，在这个级别下，一个事务只能看到已经提交的数据，避免了脏读，但仍可能出现不可重复读和幻读。

REPEATABLE READ 级别可以避免不可重复读，但仍可能出现幻读。

SERIALIZABLE 级别提供了最强的隔离性，避免了脏读、不可重复读和幻读，但可能会导致更多的并发限制。

可以通过以下语句设置事务隔离级别：

SET TRANSACTION ISOLATION LEVEL { READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE };

示例：

考虑一个银行转账的场景，从账户 A 向账户 B 转账 100 元。

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 'A';
UPDATE accounts SET balance = balance + 100 WHERE account_id = 'B';
COMMIT;

如果在转账过程中，例如第二个更新操作由于某种原因失败，我们可以执行 ROLLBACK 来撤销整个转账操作，确保数据的一致性。

二、保存点

除了整个事务的回滚，PostgreSQL 还支持在事务内设置保存点（Savepoint），允许部分回滚到特定的保存点。

1. 保存点的操作

使用 SAVEPOINT 语句创建保存点，ROLLBACK TO SAVEPOINT 回滚到指定的保存点，而 RELEASE SAVEPOINT 则用于释放保存点。

BEGIN;
-- 一些操作
SAVEPOINT savepoint1;
-- 更多操作
ROLLBACK TO SAVEPOINT savepoint1;
-- 继续其他操作
COMMIT;

示例：

假设在一个复杂的业务流程中，包括订单处理、库存更新和客户信息更新。在订单处理过程中设置了保存点，如果库存更新失败，可以回滚到保存点，而不必回滚整个事务。

BEGIN;
-- 处理订单
SAVEPOINT order_processed;
-- 更新库存
UPDATE inventory SET quantity = quantity - 10 WHERE product_id = 1;
-- 如果库存更新失败
ROLLBACK TO SAVEPOINT order_processed;
-- 尝试其他库存策略或采取其他补偿操作
COMMIT;

三、使用版本控制扩展

对于更复杂和精细的数据版本跟踪需求，可以使用 PostgreSQL 的扩展，如 pg_version 。

安装和使用扩展

首先，需要安装相应的扩展。

CREATE EXTENSION pg_version;

然后，可以使用提供的函数和表来跟踪数据的版本。

示例

假设有一个 products 表，我们希望跟踪其数据的版本变化。

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50),
    price DECIMAL(10, 2)
);

-- 创建版本表
SELECT versioning_create('products');

-- 插入数据
INSERT INTO products (name, price) VALUES ('Product 1', 50.00);

-- 更新数据
UPDATE products SET price = 60.00 WHERE id = 1;

-- 查看版本历史
SELECT * FROM versions('products');

通过上述扩展，可以方便地查看数据的历史版本和变更情况。

四、审计表和触发器

另一种实现数据版本跟踪的方法是创建审计表，并使用触发器在数据更改时记录更改历史。

1. 创建审计表

审计表通常包含原始表的主键、更改的时间、执行的操作（插入、更新、删除）、更改前的数据和更改后的数据。

CREATE TABLE products_audit (
    audit_id SERIAL PRIMARY KEY,
    product_id INT,
    operation VARCHAR(10),
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    old_name VARCHAR(50),
    old_price DECIMAL(10, 2),
    new_name VARCHAR(50),
    new_price DECIMAL(10, 2)
);

2. 创建触发器

然后为原始表创建相应的触发器，在插入、更新和删除操作时将数据记录到审计表。

CREATE OR REPLACE FUNCTION products_audit_trigger()
RETURNS TRIGGER AS $$
BEGIN
    IF (TG_OP = 'INSERT') THEN
        INSERT INTO products_audit (product_id, operation, new_name, new_price)
        VALUES (NEW.id, 'INSERT', NEW.name, NEW.price);
    ELSIF (TG_OP = 'UPDATE') THEN
        INSERT INTO products_audit (product_id, operation, old_name, old_price, new_name, new_price)
        VALUES (OLD.id, 'UPDATE', OLD.name, OLD.price, NEW.name, NEW.price);
    ELSIF (TG_OP = 'DELETE') THEN
        INSERT INTO products_audit (product_id, operation, old_name, old_price)
        VALUES (OLD.id, 'DELETE', OLD.name, OLD.price);
    END IF;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER products_audit
AFTER INSERT OR UPDATE OR DELETE ON products
FOR EACH ROW
EXECUTE FUNCTION products_audit_trigger();

示例

当对 products 表进行插入、更新或删除操作时，相关的更改信息会自动记录到 products_audit 表中。

INSERT INTO products (name, price) VALUES ('Product 2', 70.00);
UPDATE products SET price = 80.00 WHERE id = 2;
DELETE FROM products WHERE id = 2;

SELECT * FROM products_audit;

通过查询审计表，可以了解数据的更改历史和轨迹。

五、使用时间戳列

可以在表中添加 created_at 和 updated_at 时间戳列来跟踪数据的创建和更新时间。

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50),
    price DECIMAL(10, 2),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

然后，通过查询这些时间戳列，可以确定数据的版本和更改时间。

SELECT * FROM products WHERE updated_at BETWEEN 'tart_time' AND 'end_time';

这种方法相对简单，但不能提供详细的更改内容，只知道数据何时被创建和更新。

六、比较和还原数据

在需要回滚数据时，可以通过比较当前数据和之前保存的版本（例如通过备份、审计表或其他记录方式），然后执行相应的更新操作来还原数据。

-- 获取之前的版本数据
SELECT * FROM products_audit WHERE audit_id = 1; 

-- 根据之前的版本数据更新当前表
UPDATE products 
SET name = 'old_name', price = 'old_price' 
WHERE id = 'product_id';

七、考虑数据备份和恢复

定期对数据库进行备份也是一种数据回滚的策略。在出现严重错误或数据损坏时，可以使用备份文件进行恢复。

可以使用 PostgreSQL 提供的工具，如 pg_dump 进行备份，pg_restore 进行恢复。

-- 备份数据库
pg_dump -U username database_name > backup_file.sql

-- 恢复数据库
psql -U username database_name < backup_file.sql

八、结论

PostgreSQL 提供了多种方法来处理数据的版本跟踪和回滚，具体的选择取决于应用程序的需求和复杂度。事务和保存点适用于简单的回滚需求；版本控制扩展提供了更强大的版本管理功能；审计表和触发器可以详细记录数据的更改历史；时间戳列则提供了基本的时间跟踪信息；而数据备份和恢复是在极端情况下保证数据可用性的重要手段。

实际应用，通常会结合使用这些方法，以构建一个可靠、灵活和高效的数据管理系统，确保数据的准确性、完整性和可追溯性。同时，在设计和实现版本跟踪和回滚机制时，需要充分考虑性能、存储空间和数据一致性等方面的因素，以达到最佳的效果。

🎉相关推荐

• 🍅关注博主🎗️ 带你畅游技术世界，不错过每一次成长机会！
• 📚领书：PostgreSQL 入门到精通.pdf
• 📙PostgreSQL 中文手册
• 📘PostgreSQL 技术专栏