Infrastructure at your Service

wal_level determines the quantity of information written to the WAL. With PostgreSQL 11 the parameter wal_level can have 3 values:
-minimal : only information needed to recover from a crash or an immediate shutdown
-replica : enough data to support WAL archiving and replication
-logical : enough information to support logical decoding.

If we want to use logical decoding, wal_level should be set to logical. Logical decoding is the process of extracting all persistent changes to a database’s tables into a coherent, easy to understand format which can be interpreted without detailed knowledge of the database’s internal state.
In PostgreSQL, logical decoding is implemented by decoding the contents of the write-ahead log, which describe changes on a storage level, into an application-specific form such as a stream of tuples or SQL statements.

In this blog we are going to see some easy examples which will allow us to better understand this concept.

Before we can use logical decoding the parameter wal_level should be set to logical. As we will create replications slots, the parameter max_replication_slots should also be at least 1.
Below our values for these parameters

postgres=# show max_replication_slots ;
 max_replication_slots
-----------------------
 10
(1 row)

postgres=# show wal_level ;
 wal_level
-----------
 logical
(1 row)

postgres=#

First let’s create a slot. For this we will use the function pg_create_logical_replication_slot()

postgres=# SELECT * FROM pg_create_logical_replication_slot('my_slot', 'test_decoding');
 slot_name |    lsn
-----------+-----------
 my_slot   | 0/702B658
(1 row)

postgres=#

To inspect the changes at WAL level we can use the function pg_logical_slot_get_changes(). So let’s call this function

postgres=# SELECT * FROM pg_logical_slot_get_changes('my_slot', NULL, NULL);
 lsn | xid | data
-----+-----+------
(0 rows)

postgres=#

This above output is expected because there is no change yet in our database
Now let’s do some insert in the database and let’s call again the function pg_logical_slot_get_changes()

postgres=# begin;
BEGIN
postgres=# insert into mytab values (1,'t1');
INSERT 0 1
postgres=# insert into mytab values (2,'t2');
INSERT 0 1
postgres=# commit;
COMMIT

postgres=# SELECT * FROM pg_logical_slot_get_changes('my_slot', NULL, NULL);
    lsn    | xid |                                  data
-----------+-----+------------------------------------------------------------------------
 0/703F538 | 582 | BEGIN 582
 0/703F538 | 582 | table public.mytab: INSERT: id[integer]:1 name[character varying]:'t1'
 0/703F5B0 | 582 | table public.mytab: INSERT: id[integer]:2 name[character varying]:'t2'
 0/703F620 | 582 | COMMIT 582
(4 rows)

postgres=#

As expected we can see changes that were made.
Now what happen if we call again the same function?

postgres=# SELECT * FROM pg_logical_slot_get_changes('my_slot', NULL, NULL);
 lsn | xid | data
-----+-----+------
(0 rows)

postgres=#

The changes are no longer reported. It’s normal because with the function pg_logical_slot_get_changes(), changes are consumed (will not be returned again). If we want the changes not to be consumed we can use the function pg_logical_slot_peek_changes(). This function behaves like the first one, except that changes are not consumed; that is, they will be returned again on future calls.

postgres=# begin;
BEGIN
postgres=# insert into mytab values (3,'t3');
INSERT 0 1
postgres=# insert into mytab values (4,'t4');
INSERT 0 1
postgres=# commit;
COMMIT                   
postgres=# delete from mytab where id=1;
DELETE 1

postgres=# SELECT * FROM pg_logical_slot_peek_changes('my_slot', NULL, NULL);
    lsn    | xid |                                  data
-----------+-----+------------------------------------------------------------------------
 0/703F738 | 583 | BEGIN 583
 0/703F738 | 583 | table public.mytab: INSERT: id[integer]:3 name[character varying]:'t3'
 0/703F838 | 583 | table public.mytab: INSERT: id[integer]:4 name[character varying]:'t4'
 0/703F8A8 | 583 | COMMIT 583
 0/703F8E0 | 584 | BEGIN 584
 0/703F8E0 | 584 | table public.mytab: DELETE: (no-tuple-data)
 0/703F948 | 584 | COMMIT 584
(7 rows)

postgres=# SELECT * FROM pg_logical_slot_peek_changes('my_slot', NULL, NULL);
    lsn    | xid |                                  data
-----------+-----+------------------------------------------------------------------------
 0/703F738 | 583 | BEGIN 583
 0/703F738 | 583 | table public.mytab: INSERT: id[integer]:3 name[character varying]:'t3'
 0/703F838 | 583 | table public.mytab: INSERT: id[integer]:4 name[character varying]:'t4'
 0/703F8A8 | 583 | COMMIT 583
 0/703F8E0 | 584 | BEGIN 584
 0/703F8E0 | 584 | table public.mytab: DELETE: (no-tuple-data)
 0/703F948 | 584 | COMMIT 584
(7 rows)

postgres=#

Logical decoding can also be managed using pg_recvlogical included in the PostgreSQL distribution.
Let’s create a slot using pg_recvlogical

[[email protected]_3 PG1]$ pg_recvlogical -d postgres --slot=myslot_2  --create-slot

And let’s start the streaming in a first terminal

[[email protected]_3 PG1]$ pg_recvlogical -d postgres --slot=myslot_2  --start -f -

If we do an insert in the database from a second terminal

postgres=# insert into mytab values (9,'t9');
INSERT 0 1
postgres=#

We will see following in the first terminal

[[email protected]_3 PG1]$ pg_recvlogical -d postgres --slot=myslot_2  --start -f -   
BEGIN 587
table public.mytab: INSERT: id[integer]:9 name[character varying]:'t9'
COMMIT 587

Conclusion

In this blog we have seen that if we want to do logical decoding, we have to set the parameter wal_level to logical. Be aware that setting wal_level to logical can increase the volume of generated WAL. If we just want replication or archiving WALs, the value replica is enough with PostgreSQL.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Mouhamadou Diaw
Mouhamadou Diaw

Consultant