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

This article shows you what to do if the PostgreSQL logical replication data inconsistency leads to the infinite increase of the main library wal log, the content is concise and easy to understand, it can definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

The benefits of PostgreSQL's logical replication versus physical replication are summarized as follows

1 flexible: compared with physical replication, logical replication can replicate data in a single table, but physical replication is not allowed, and most of the time for the functional requirements of ETL, physical replication is too heavy, requiring disks, networks, and other resources are much more expensive than logical replication.

Convenience: compared with physical replication, logical replication is easier to set up, can be terminated or created at any time, data synchronization and so on.

3 customization: logical replication can set replication operations, for example, only insert replication, or update, delete and other operations replication, can be done, the data is not consistent with the last side, but to achieve some purposes.

4 data migration, if the PG is upgraded in different versions, the logical replication of PG can be used as a data migration scheme to synchronize data in different versions.

Logical replication is still implemented using physical replication architecture. As can be seen from the above picture, pgoutput plugin is added to the replication slot to convert the original wal log and send it. Subscription accepts the information and fills it to the destination. In order to avoid repeated operations on the subscription, the client records the accepted LSN number to avoid repeatedly accepting the same data and operating.

In addition, it is important to note that in many cases where vacuum cannot be carried out, there are replication slots in some cases. It is possible that this replication slot is multi-purpose and has a data receiver. If a data receiver cannot accept data, the relevant tuples that needs to be retained will not be cleaned, resulting in vacuum not being recycled.

We have a copy slot below.

Then we artificially create a conflict. In the slave library of data replication, a piece of data is artificially added to the data table.

View the subscription information on the subscription side

Then we also insert data on the publication side.

Go directly to subscription to view the error log

The system has been in the state of reporting an error. Due to the data operation conflict between the master database and the slave database, the data from the master database to the slave database can not be operated. How on earth did that affect WAL log?

Let's move on.

In the main database, we delete 1900 pieces of 2000 pieces of data.

When we look at the current data in subscription, the result must be that it has been separated from the main database and will not continue any operation. the main reason is that the logical replication is in order. If any operation is jammed, the subsequent operation will not be completed.

Then the progress of the subsequent latest checkpoint location of the main library will stop, no matter what you do, or using the checkpoint command will not affect

Here, if the PG_WAL cannot checkpoint, it means that the WAL LOG of the PG cannot be archived. With more and more operations in the main library, the WA files will become larger and larger and cannot be cleaned.

Next, after deleting the self-added records from the library, take a look at the checkpoint location of the main library.

It has changed.

Of course, how to monitor whether replication logical replication is interrupted or not?

Select pid, client_addr, state, sync_state

Pg_wal_lsn_diff (sent_lsn, write_lsn) as write_lag

Pg_wal_lsn_diff (sent_lsn, flush_lsn) as flush_lag

Pg_wal_lsn_diff (sent_lsn, replay_lsn) as replay_lag

From pg_stat_replication

If you currently have a replication, query the main database, and if the replication is normal, you will find out the situation between you and the subscription. If the data is inconsistent, causing the replication to stop, there will be no data displayed in the query again. So this is also a way to judge whether logical replication is normal or not.

The stop of logical replication will cause the problem that the wal of the main library cannot be truncated, so if PG has already used logical replication, you must monitor the logical replication, otherwise, in the busy business system, the stop of logical replication will cause problems with the wal space of your main library, and eventually lead to the problem of running out of disk space in the main library.

The above content is that the PostgreSQL logical replication data inconsistency leads to the infinite growth of the main library wal log. Have you learned the knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

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.