【数据库表的快照】如何优雅的做postgresql快照表

目录

一.快照表是什么

二.怎么去实现

1.无软删除主表

1.1 建主表

1.2 建快照表

1.3 创建test表的trigger

2.存在软删除主表

2.1 建主表

2.2 建快照表

2.3 创建test表的trigger

三、意义

---

一.快照表是什么

        首先，先解释一下快照的意思，快照：顾名思义，最初的意思就是在某一时间段快速拍一张照片做记录，那拍照片的意义是什么？意义是为了让我们以后看到这个的时候，有迹可循。那么快照表的意思其实就很简单了，就是为了来记录我们之前做的一些操作。快速的记录下我们这一瞬间的操作，可以让我们在发生问题的时候可以更好的溯源。

二.怎么去实现

目前能想到的方式是两种：

1.通过Spring AOP的方式去实现，在增删改的dao层方法上做切点，当被调用时，将信息存入指定的snapshot表中（这种方式的弊端就是，如果有人在数据库中直接操作数据了，那么操作的记录不会被保存到快照表中，所以我们今天先主要讲一下第二种实现）。

2.通过数据库自带的存储过程和触发器来实现。废话不多说。贴代码

1.无软删除主表

1.1 建主表

create table test(
    id bigserial primary key,
    name text,
    created_by text,
    created_at timestamp default now(),
    updated_by text,
    updated_at timestamp default  now()
)

1.2 建快照表

create table test_snapshot(
    test_snapshot_id bigserial primary key,
    id bigint,
    name text,
    created_by text,
    created_at timestamp default now(),
    event_type text check (event_type in ('create', 'update', 'delete'))
)

1.3 创建test表的trigger

当是删除操作的时候，一定要用OLD，因为数据一旦被删除之后，用NEW.属性都是为null

DROP TRIGGER IF EXISTS test_snapshot ON test;
 
 
CREATE
OR REPLACE FUNCTION process_test_snapshot()
    RETURNS TRIGGER AS
$$
BEGIN
    IF
(TG_OP = 'DELETE') THEN
        INSERT INTO test_snapshot (id, name, created_by, created_at, event_type)
        VALUES( OLD.id, OLD.name, OLD.updated_by, OLD.updated_at, 'delete');
    ELSIF
(TG_OP = 'UPDATE') THEN
        INSERT INTO test_snapshot (id, name, created_by, created_at, event_type)
        VALUES( NEW.id, NEW.name, NEW.updated_by, NEW.updated_at, 'update');
    ELSIF
(TG_OP = 'INSERT') THEN
        INSERT INTO test_snapshot (id, name, created_by, created_at, event_type)
        VALUES( NEW.id, NEW.name, NEW.created_by, NEW.created_at, 'create');
END IF;
RETURN NULL;
END;
$$
LANGUAGE plpgsql;
 
CREATE TRIGGER test_snapshot
    AFTER INSERT OR
UPDATE OR
DELETE
ON test
    FOR EACH ROW EXECUTE FUNCTION process_test_snapshot();

2.存在软删除主表

上述的是不存在软删除的表，那么我下面会讲一下存在软删除的表该怎么去建trigger。

2.1 建主表

create table test(
    id bigserial primary key,
    name text,
    created_by text,
    created_at timestamp default now(),
    updated_by text,
    updated_at timestamp default  now()，
    deleted_by text,
    deleted_at timestamp default now()
)

2.2 建快照表

create table test_snapshot(
    test_snapshot_id bigserial primary key,
    id bigint,
    name text,
    created_by text,
    created_at timestamp default now(),
    event_type text check (event_type in ('create', 'update', 'delete'))
)

2.3 创建test表的trigger

DROP TRIGGER IF EXISTS test_snapshot ON test;
 
 
CREATE
OR REPLACE FUNCTION process_test_snapshot()
    RETURNS TRIGGER AS
$$
BEGIN
    IF
(TG_OP = 'UPDATE' AND NEW.deleted_by IS NOT NULL) THEN
        INSERT INTO test_snapshot (id, name, created_by, created_at, event_type)
        VALUES( NEW.id, NEW.name, NEW.deleted_by, NEW.deleted_at, 'delete');
    ELSIF
(TG_OP = 'UPDATE') THEN
        INSERT INTO test_snapshot (id, name, created_by, created_at, event_type)
        VALUES( NEW.id, NEW.name, NEW.updated_by, NEW.updated_at, 'update');
    ELSIF
(TG_OP = 'INSERT') THEN
        INSERT INTO test_snapshot (id, name, created_by, created_at, event_type)
        VALUES( NEW.id, NEW.name, NEW.created_by, NEW.created_at, 'create');
END IF;
RETURN NULL;
END;
$$
LANGUAGE plpgsql;
 
CREATE TRIGGER test_snapshot
    AFTER INSERT OR
UPDATE OR
DELETE
ON test
    FOR EACH ROW EXECUTE FUNCTION process_test_snapshot();
 

ok,到这里我们的trigger就已经创建好了。快去试试效果吧（温馨提示：批量插入的时候也是会一一添加到快照表里面去）

三、意义

我相信肯定会有朋友有疑问，为什么我们需要这么一张表，说当我们在创建一张快照表的时候，其实我们不是会经常的去查看它，使用的频次也不会那么高！！

why ? ? ? ?

确实，我们在创建一张快照表的时候，我们非常少的去看它，去使用它。但是它存在意义有几点：1.当出现问题的时候，我们可以快速的去溯源，去定位问题，比如，当一条数据被误删除了，而我们如果没有快照表的话，我们还要去日志里面去找。但是我们如果存在快照表的话，我们就可以很快的去找到被删除的数据，并且知道是谁删除的，在什么时候删除的。可以让我们快速的做一个复盘。

2.当我们不小心修改一条数据的时候，我们可以快速的在快照表中找到修改之前，这条数据应该是什么样子的，可以很快的帮助我们去复原我们想要还原的数据！！