Wal2Json in PostgreSQL on Windows

Easy Setup and Usage of Wal2Json in PostgreSQL on Windows

Wal2Json:  Wal2Json is a PostgreSQL extension that provides a logical decoding output plugin for capturing changes to a PostgreSQL database and producing them in JSON format. The name "wal2json" stands for "Write-Ahead Logging to JSON."

PostgreSQL's Write-Ahead Logging (WAL) is a critical mechanism that ensures durability and crash recovery for the database. It records changes made to the database in a sequential log, which can then be used for various purposes, including replication, point-in-time recovery (PITR), and logical decoding. Logical decoding is a feature introduced in PostgreSQL 9.4 that allows applications to consume the changes captured in the WAL log in a more human-readable and application-specific format.

Wal2Json is one such logical decoding output plugin that targets JSON output. It converts the changes recorded in the WAL into JSON format, making it easy for applications to process and consume the changes in a structured manner.

Why do we Use: We use Wal2Json to capture changes made to a PostgreSQL database in real-time and convert them into a JSON format. This helps us perform the following tasks:

1. Real-time Tracking: We can instantly know when data is added, updated, or deleted in the database.

2. Real-time Integration: We can synchronize data across different systems in real-time, ensuring all systems stay up-to-date.

3. Real-time Analytics: We can analyze data changes as they happen, allowing us to generate insights and reports in real-time.

4. Replication: We can use `wal2json` along with replication techniques to create backup copies of the database in real-time.

Overall, `wal2json` is a powerful tool that enables real-time data processing and integration, making it useful for a variety of applications and scenarios.

Setup and Usage of Wal2Json:

Step 1: Install `wal2json` extension in your PostgreSQL server if not already installed.

Step 1.1: Clone Wal2Json from https://github.com/eulerto/wal2json using Visual Studio.

 Step 1.2: Edit wal2json.vcxproj file and change c:\postgres\pg103(means in the               wal2json.vcxproj wherever you find c:\postgres\pg103 replace it with C:\Program Files\ PostgreSQL\15)   to the PostgreSQL prefix directory and save. 

Step 1.3: In wal2json.c please edit and enable data->include_timestamp = true; (by default it is false) to get time stamp also in JSON data output.

Step 1.4: Now click on build then please ensure that you got build success status.

Step 1.5: Final step is to copy wal2json.dll it is present in \x64\Debug\ to the pg_config --pkglibdir directory (means copy the wal2json.dll to C:\Program Files\PostgreSQL\15\lib folder).

Step 2: Configure PostgreSQL for logical decoding by enabling the `wal_level` to `logical`.

Step 2.1: We need to set up at least two parameters at postgresql.conf: (postgresql.conf is present in C:\Program Files\PostgreSQL\15\data\ folder).

§  wal_level = logical

§  max_replication_slots = 10

§  max_wal_senders = 10

Step 2.2: First add a replication connection rule at pg_hba.conf : (pg_hba.conf is present in C:\Program Files\PostgreSQL\15\data\ folder). At the end of pg_hba.conf file the details must be like this (please check comment(#) details and values properly)  :

Step 3: Then restart the postgres database using services.msc 

Step 4: So now we are ready to try Wal2Json:

Move to C:\Program Files\PostgreSQL\15\bin folder in command prompt as administrator

Note: Here we are creating slot for the database postgres (we can specify our databases instead of postgres while creating slot)

pg_recvlogical -d postgres --slot test_slot --create-slot -P wal2json –U postgres –W

Step 5: To start wal2Json process use the following command:

pg_recvlogical -d postgres --slot test_slot --start -o pretty-print=1 -o add-msg-prefixes=wal2json -f - -U postgres

 Then it will ask password, please provide password for the above specified user

Step 6: Open pgAdmin4 and try insert/delete/update command and see the JSON data in the command prompt:

 Summary:  

`wal2json` is a powerful PostgreSQL extension that enhances logical decoding capabilities, enabling real-time data processing and integration. It stands for "Write-Ahead Logging to JSON," and its primary purpose is to capture changes made to a PostgreSQL database and produce them in JSON format. This allows applications to consume the changes in a structured manner, making it ideal for real-time data tracking, synchronization, analytics, and integration.