Write-ahead log is a feature of PostgreSQL database used to log all changes to the database before these changes are applied. Write-ahead log will use a file to record changes to the database in a certain order. Thanks to that, in case of an accident or power outage, data will not be lost because changes to the database have been recorded in the write-ahead log file, PostgreSQL can easily restore data based on this log file.

Logical decoding is an implementation of PostgreSQL used to decode the contents of the write-ahead log file into data or SQL statements so that it can be used by third parties. And Debezium, a data change capture tool, uses this feature to support data change capture for PostgreSQL! In this tutorial, I will guide you on how to configure the PostgreSQL write-ahead log to use logical decoding so that Debezium can capture data changes in the PostgreSQL database!

First, you can use the following SQL statement to check the current value of the PostgreSQL write-ahead log:

Or you can also have another SQL statement, like this:

also ok.

My results are as follows:

There are 3 values ​​of wal_level that you can set: minimal, replica and logical:

• minimal means PostgreSQL will remove all logs except for the necessary information so that PostgreSQL can recover if there is a power outage or crash.
• replica means PostgreSQL will log enough data to support write-ahead logs for archiving and replication.
• logical means PostgreSQL will add the necessary information to support PostgreSQL’s logical decoding feature.

With the purpose of each level as above, you can also see that we need to configure wal_level with a logical value, right?

To do this, open the postgresql.conf file in the PostgreSQL installation directory, find the configuration about wal_level in this postgresql.conf file and change the default value of replica to a logical value!

Mine is as follows:

You also need to enable a logical decoding output plugin so that it can generate an output file that Debezium can use. This plugin can be:

• decoderbufs at https://github.com/debezium/postgres-decoderbufs. You need to build and install this plugin because PostgreSQL does not have it by default.
• or pgoutput: a standard output plugin for PostgreSQL. This is a built-in plugin so you don’t need to do anything else!

You can use the pgoutput plugin by editing the value of the shared_preload_libraries configuration in the postgresql.conf file as follows:

After editing and saving the changes, you need to restart the PostgreSQL server for these changes to take effect!

Run one of the two SQL statements above, you will see that wal_level has been assigned a logical value, like mine as follows:

Check the logical decoding output plugin using SQL:

You will see the following results:

So we have successfully configured data change capture using Debezium for the PostgreSQL database!