活学活用 PostgreSQL 逻辑复制实现 I U D 历史记录

有些数据库是有历史表的功能的，也就是你操作的数据的历史会记录到另一个表中,包含更新的和删除的记录，以防止某些意外的情况找回历史的数据，或知道在什么时候表中的记录变化。

PostgreSQL 本身没有这样的功能的，但这里有一个想法可以来通过逻辑复制的方法来模拟这样的功能。当然通过trigger 也是可以完成这样的功能，但是考虑到可能会有遇上性能上的问题。

这里我们在test 数据库上建立log_save的表，我们的需求是通过逻辑复制的功能，将log_save 的插入的记录，update 的记录 都进行一个保留（update 只能保存最后一次修改的记录），并且通过某些SQL 的方式也能追踪到这个表到底删除了那些记录。

大致的思路，我们建立三张复制的表在不同的数据库中（因为复制的表名必须一致,三个数据库分别是 test_insert  test_update  test_delete），第一张仅仅记录 log_save 表的insert 记录，包含一个时间戳，同时另外两张表一个记录 insert,update 的记录，最后一张记录 insert  delete的操作。

create publication log_save_insert for table public.log_save with (publish = 'insert');

create publication log_save_update for table public.log_save with (publish = 'insert,update');

create publication log_save_delete for table public.log_save with (publish = 'insert,delete');

创建三个数据库 test_insert  test_update test_delete

先添加3张历史表，分别建立在不同的数据库中，每个库一张

CREATE TABLE public.log_save

test-# (

test(#     id serial,

test(#     log_type character varying(10),

test(#     log_content text,

test(#     insert_date timestamp without time zone,

test(#     PRIMARY KEY (id)

test(# )

test-# WITH (

test(#     OIDS = FALSE,

test(#     FILLFACTOR = 80,

test(#     autovacuum_enabled = TRUE

test(# );

alter table log_save add column date_time timestamp default now();

数据库中创建逻辑复制槽 (可以仅仅创建一个复制槽即可，这里为了让下面的操作更清晰，生成了三个复制槽)

创建逻辑复制槽

 SELECT * FROM pg_create_logical_replication_slot('log_save_insert', 'pgoutput');

 SELECT * FROM pg_create_logical_replication_slot('log_save_delete', 'pgoutput');

 SELECT * FROM pg_create_logical_replication_slot('log_save_update', 'pgoutput');

创建订阅，在三个不同的库中每个库对应不同的publication 创建一个订阅

 CREATE SUBSCRIPTION log_save_insert CONNECTION 'host=192.168.198.123 dbname=test user=repl password=123456 port=5432' PUBLICATION log_save_insert with (create_slot = false,slot_name = log_save_insert);

CREATE SUBSCRIPTION log_save_update CONNECTION 'host=192.168.198.123 dbname=test user=repl password=123456 port=5432' PUBLICATION log_save_update with (create_slot = false,slot_name = log_save_update);

CREATE SUBSCRIPTION log_save_delete CONNECTION 'host=192.168.198.123 dbname=test user=repl password=123456 port=5432' PUBLICATION log_save_delete with (create_slot = false,slot_name = log_save_delete);

下面我们可以做一个实验

我们在 test 数据库中的表 log_save 插入数据，然后更改数据，删除数据

下面是初始的数据

现在我们删除 ID = 19 的数据

我们在查看test_delete 的数据库 的log_save 表 我看可以看到，的确数据少了

我们在修改ID = 17 列 log_content 的数据，可以很清晰的看到

update log_save set log_content = '09899', insert_date = now() where id = 17;

以上已经验证了，三个库中的 insert  update  delete 操作是针对三种操作，通过这些表我们可以做什么

1  检索，检索今天log_save 插入多少数据库

2  今天log_save 表到底更新了多少行数据，那些行被更新过 （这里默认在update 里面的insert_date 为null的是没有更新过的数据，当然你也可以设置成其他的方式，例如插入的时候带有insert_date ，而update 操作不更新insert_date ，这样 update的表中的insert_date  和insert 表的 insert_date 就会不同）

这里采用上面说的insert_date 为空的说明没有更新

3  最后我们是要获得今天到底删除了多少记录

select count(id) from log_save where id not in (

select id from (

select id from dblink ('host=192.168.198.123 dbname=test_delete user=repl password=123456','select id from log_save') 

as b(id int) ) as b)

 ;

以上的查询比较粗糙，不过基本上大致的基础工作都做完了。

通过上面的例子，我们可以对一个数据库的所有的表都可以进行类似历史方面的记录统计，和数据的历史记录，通过一些查询可以找到误删除的数据，或者误更新的数据。当然上面说过其实通过 trigger 的方式也是可以完成这样的工作的，但是在性能方面的影响不言而喻。如果是对一个库的所有的表都进行这样的设置，trigger 的方式显然也是不合适的。并且在分析数据的时候还会对基表产生性能影响。

其实如果对数据库的某些特殊的功能熟悉后，通过这些功能可以演化出很多通过程序很难完成的功能，例如是否可以通过表继承的方式来每天生成一个一个表的一天的记录。