Shulou(Shulou.com)06/01 Report--

This article introduces the example analysis of PostgreSQL logical replication to delete historical records, the content is very detailed, interested friends can refer to, I hope it can be helpful to you.

Some databases have the function of a history table, that is, the history of the data you manipulate will be recorded in another table, including updated and deleted records, in order to prevent some unexpected situation from retrieving historical data. or know when the record changes in the table.

PostgreSQL itself does not have such functionality, but here is an idea to simulate such functionality through logical replication. Of course, this function can also be done through trigger, but considering that there may be performance problems.

Here we set up the log_save table on the test database, our requirement is to keep the inserted records of log_save and the records of update through the function of logical replication (update can only save the records of the last modification), and through some SQL methods we can also track which records have been deleted by this table.

In a general way of thinking, we build three replicated tables in different databases (because the names of the replicated tables must be the same, and the three databases are test_insert test_update test_delete respectively). The first one records only the insert record of the log_save table, including a timestamp, while the other two tables record the insert,update record, and the last record the operation of the 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')

Create three databases test_insert test_update test_delete

First add three history tables, each in a different database, one for each database

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 ()

Create a logical replication slot in the database (you can create just one replication slot, here three replication slots are generated to make the following operations clearer)

Create a logical copy slot

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')

Create subscriptions, one for each of the three different libraries corresponding to a different 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)

Now we can do an experiment.

We insert the data in the table log_save in the test database, then change the data and delete the data

Here is the initial data

Now let's delete the data with ID = 19

We are looking at the log_save table in test_delete 's database, and I can see that there is indeed less data.

We are modifying the data of ID = 17 column log_content, which can be seen very clearly.

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

As has been verified above, the insert update delete operations in the three libraries are for three operations. What can we do with these tables?

1 search, retrieve how many databases are inserted by log_save today

2 how many rows of data have been updated in the log_save table today, and those rows have been updated (the insert_date in the update here is null by default is the unupdated data, of course, you can also set it to other ways, such as inserting with insert_date, while the update operation does not update the insert_date, so that the insert_date in the update table and the insert_date in the insert table will be different)

The description that uses the empty insert_date mentioned above has not been updated

Finally, we are going to get how many records have been deleted today.

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)

The above query is relatively rough, but basically the basic work has been done.

Through the above example, we can do similar historical statistics on all the tables in a database, and the history of the data. Through some queries, we can find the mistakenly deleted data, or mistakenly updated data. Of course, as mentioned above, this kind of work can also be done through trigger, but the impact on performance is self-evident. If you set up all the tables in a library in this way, the trigger approach is obviously not appropriate. And it will also have a performance impact on the base table when analyzing the data.

So much for the example analysis of deleting historical records for PostgreSQL logical replication. I hope the above content can be helpful to you and learn more. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.