PostgreSQL: When wal_level to logical
By Mouhamadou DiawJanuary 27, 2019Database Administration & Monitoring, PostgresNo Comments
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
1 2 3 4 5 6 7 8 9 10 11 12 13 |
|
First let’s create a slot. For this we will use the function pg_create_logical_replication_slot()
1 2 3 4 5 6 7 |
|
To inspect the changes at WAL level we can use the function pg_logical_slot_get_changes(). So let’s call this function
1 2 3 4 5 6 |
|
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()
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
|
As expected we can see changes that were made.
Now what happen if we call again the same function?
1 2 3 4 5 6 |
|
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 |
|
Logical decoding can also be managed using pg_recvlogical included in the PostgreSQL distribution.
Let’s create a slot using pg_recvlogical
1 |
|
And let’s start the streaming in a first terminal
1 |
|
If we do an insert in the database from a second terminal
1 2 3 |
|
We will see following in the first terminal
1 2 3 4 |
|
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.