PostgreSQL: When wal_level to logical

刺骨的言语ヽ痛彻心扉 2023-10-18 21:37 33阅读 0赞

## PostgreSQL: When wal\_level to logical ##

By [Mouhamadou Diaw][]January 27, 2019[Database Administration & Monitoring][Database Administration _ Monitoring], [Postgres][][No Comments][]

[ ][Link 1][ ][Link 1][ ][Link 1]

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

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 3 4 5 6 7 8 9 10 11 12 13 </td> 
 <td> <code>postgres=# show max_replication_slots ;</code> <code>&nbsp;</code><code>max_replication_slots</code> <code>-----------------------</code> <code>&nbsp;</code><code>10</code> <code>(1 row)</code> &nbsp; <code>postgres=# show wal_level ;</code> <code>&nbsp;</code><code>wal_level</code> <code>-----------</code> <code>&nbsp;</code><code>logical</code> <code>(1 row)</code> &nbsp; <code>postgres=#</code> </td> 
 </tr> 
 </tbody> 
</table>

First let’s create a slot. For this we will use the function pg\_create\_logical\_replication\_slot()

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 3 4 5 6 7 </td> 
 <td> <code>postgres=# </code><code>SELECT</code> <code>* </code><code>FROM</code> <code>pg_create_logical_replication_slot(</code><code>'my_slot'</code><code>, </code><code>'test_decoding'</code><code>);</code> <code>&nbsp;</code><code>slot_name |&nbsp;&nbsp;&nbsp; lsn</code> <code>-----------+-----------</code> <code>&nbsp;</code><code>my_slot&nbsp;&nbsp; | 0/702B658</code> <code>(1 row)</code> &nbsp; <code>postgres=#</code> </td> 
 </tr> 
 </tbody> 
</table>

To inspect the changes at WAL level we can use the function pg\_logical\_slot\_get\_changes(). So let’s call this function

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 3 4 5 6 </td> 
 <td> <code>postgres=# </code><code>SELECT</code> <code>* </code><code>FROM</code> <code>pg_logical_slot_get_changes(</code><code>'my_slot'</code><code>, </code><code>NULL</code><code>, </code><code>NULL</code><code>);</code> <code>&nbsp;</code><code>lsn | xid | data</code> <code>-----+-----+------</code> <code>(0 </code><code>rows</code><code>)</code> &nbsp; <code>postgres=#</code> </td> 
 </tr> 
 </tbody> 
</table>

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()

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 </td> 
 <td> <code>postgres=# </code><code>begin</code><code>;</code> <code>BEGIN</code> <code>postgres=# </code><code>insert</code> <code>into</code> <code>mytab </code><code>values</code> <code>(1,</code><code>'t1'</code><code>);</code> <code>INSERT</code> <code>0 1</code> <code>postgres=# </code><code>insert</code> <code>into</code> <code>mytab </code><code>values</code> <code>(2,</code><code>'t2'</code><code>);</code> <code>INSERT</code> <code>0 1</code> <code>postgres=# </code><code>commit</code><code>;</code> <code>COMMIT</code> &nbsp; <code>postgres=# </code><code>SELECT</code> <code>* </code><code>FROM</code> <code>pg_logical_slot_get_changes(</code><code>'my_slot'</code><code>, </code><code>NULL</code><code>, </code><code>NULL</code><code>);</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>lsn&nbsp;&nbsp;&nbsp; | xid |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; data</code> <code>-----------+-----+------------------------------------------------------------------------</code> <code>&nbsp;</code><code>0/703F538 | 582 | </code><code>BEGIN</code> <code>582</code> <code>&nbsp;</code><code>0/703F538 | 582 | </code><code>table</code> <code>public</code><code>.mytab: </code><code>INSERT</code><code>: id[</code><code>integer</code><code>]:1 </code><code>name</code><code>[</code><code>character</code> <code>varying</code><code>]:</code><code>'t1'</code> <code>&nbsp;</code><code>0/703F5B0 | 582 | </code><code>table</code> <code>public</code><code>.mytab: </code><code>INSERT</code><code>: id[</code><code>integer</code><code>]:2 </code><code>name</code><code>[</code><code>character</code> <code>varying</code><code>]:</code><code>'t2'</code> <code>&nbsp;</code><code>0/703F620 | 582 | </code><code>COMMIT</code> <code>582</code> <code>(4 </code><code>rows</code><code>)</code> &nbsp; <code>postgres=#</code> </td> 
 </tr> 
 </tbody> 
</table>

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

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.

<table> 
 <tbody> 
 <tr> 
 <td> 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 </td> 
 <td> <code>postgres=# </code><code>begin</code><code>;</code> <code>BEGIN</code> <code>postgres=# </code><code>insert</code> <code>into</code> <code>mytab </code><code>values</code> <code>(3,</code><code>'t3'</code><code>);</code> <code>INSERT</code> <code>0 1</code> <code>postgres=# </code><code>insert</code> <code>into</code> <code>mytab </code><code>values</code> <code>(4,</code><code>'t4'</code><code>);</code> <code>INSERT</code> <code>0 1</code> <code>postgres=# </code><code>commit</code><code>;</code> <code>COMMIT</code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <code>postgres=# </code><code>delete</code> <code>from</code> <code>mytab </code><code>where</code> <code>id=1;</code> <code>DELETE</code> <code>1</code> &nbsp; <code>postgres=# </code><code>SELECT</code> <code>* </code><code>FROM</code> <code>pg_logical_slot_peek_changes(</code><code>'my_slot'</code><code>, </code><code>NULL</code><code>, </code><code>NULL</code><code>);</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>lsn&nbsp;&nbsp;&nbsp; | xid |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; data</code> <code>-----------+-----+------------------------------------------------------------------------</code> <code>&nbsp;</code><code>0/703F738 | 583 | </code><code>BEGIN</code> <code>583</code> <code>&nbsp;</code><code>0/703F738 | 583 | </code><code>table</code> <code>public</code><code>.mytab: </code><code>INSERT</code><code>: id[</code><code>integer</code><code>]:3 </code><code>name</code><code>[</code><code>character</code> <code>varying</code><code>]:</code><code>'t3'</code> <code>&nbsp;</code><code>0/703F838 | 583 | </code><code>table</code> <code>public</code><code>.mytab: </code><code>INSERT</code><code>: id[</code><code>integer</code><code>]:4 </code><code>name</code><code>[</code><code>character</code> <code>varying</code><code>]:</code><code>'t4'</code> <code>&nbsp;</code><code>0/703F8A8 | 583 | </code><code>COMMIT</code> <code>583</code> <code>&nbsp;</code><code>0/703F8E0 | 584 | </code><code>BEGIN</code> <code>584</code> <code>&nbsp;</code><code>0/703F8E0 | 584 | </code><code>table</code> <code>public</code><code>.mytab: </code><code>DELETE</code><code>: (</code><code>no</code><code>-tuple-data)</code> <code>&nbsp;</code><code>0/703F948 | 584 | </code><code>COMMIT</code> <code>584</code> <code>(7 </code><code>rows</code><code>)</code> &nbsp; <code>postgres=# </code><code>SELECT</code> <code>* </code><code>FROM</code> <code>pg_logical_slot_peek_changes(</code><code>'my_slot'</code><code>, </code><code>NULL</code><code>, </code><code>NULL</code><code>);</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>lsn&nbsp;&nbsp;&nbsp; | xid |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; data</code> <code>-----------+-----+------------------------------------------------------------------------</code> <code>&nbsp;</code><code>0/703F738 | 583 | </code><code>BEGIN</code> <code>583</code> <code>&nbsp;</code><code>0/703F738 | 583 | </code><code>table</code> <code>public</code><code>.mytab: </code><code>INSERT</code><code>: id[</code><code>integer</code><code>]:3 </code><code>name</code><code>[</code><code>character</code> <code>varying</code><code>]:</code><code>'t3'</code> <code>&nbsp;</code><code>0/703F838 | 583 | </code><code>table</code> <code>public</code><code>.mytab: </code><code>INSERT</code><code>: id[</code><code>integer</code><code>]:4 </code><code>name</code><code>[</code><code>character</code> <code>varying</code><code>]:</code><code>'t4'</code> <code>&nbsp;</code><code>0/703F8A8 | 583 | </code><code>COMMIT</code> <code>583</code> <code>&nbsp;</code><code>0/703F8E0 | 584 | </code><code>BEGIN</code> <code>584</code> <code>&nbsp;</code><code>0/703F8E0 | 584 | </code><code>table</code> <code>public</code><code>.mytab: </code><code>DELETE</code><code>: (</code><code>no</code><code>-tuple-data)</code> <code>&nbsp;</code><code>0/703F948 | 584 | </code><code>COMMIT</code> <code>584</code> <code>(7 </code><code>rows</code><code>)</code> &nbsp; <code>postgres=#</code> </td> 
 </tr> 
 </tbody> 
</table>

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

<table> 
 <tbody> 
 <tr> 
 <td> 1 </td> 
 <td> <code>[postgres@dbi-pg-essentials_3 PG1]$ pg_recvlogical -d postgres </code><code>--slot=myslot_2&nbsp; --create-slot</code> </td> 
 </tr> 
 </tbody> 
</table>

And let’s start the streaming in a first terminal

<table> 
 <tbody> 
 <tr> 
 <td> 1 </td> 
 <td> <code>[postgres@dbi-pg-essentials_3 PG1]$ pg_recvlogical -d postgres </code><code>--slot=myslot_2&nbsp; --start -f -</code> </td> 
 </tr> 
 </tbody> 
</table>

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

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 3 </td> 
 <td> <code>postgres=# </code><code>insert</code> <code>into</code> <code>mytab </code><code>values</code> <code>(9,</code><code>'t9'</code><code>);</code> <code>INSERT</code> <code>0 1</code> <code>postgres=#</code> </td> 
 </tr> 
 </tbody> 
</table>

We will see following in the first terminal

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 3 4 </td> 
 <td> <code>[postgres@dbi-pg-essentials_3 PG1]$ pg_recvlogical -d postgres </code><code>--slot=myslot_2&nbsp; --start -f -&nbsp;&nbsp; </code> <code>BEGIN</code> <code>587</code> <code>table</code> <code>public</code><code>.mytab: </code><code>INSERT</code><code>: id[</code><code>integer</code><code>]:9 </code><code>name</code><code>[</code><code>character</code> <code>varying</code><code>]:</code><code>'t9'</code> <code>COMMIT</code> <code>587</code> </td> 
 </tr> 
 </tbody> 
</table>

#### 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.

[Mouhamadou Diaw]: https://blog.dbi-services.com/author/mouhamadou-diaw/
[Database Administration _ Monitoring]: https://blog.dbi-services.com/category/database-administration-monitoring/
[Postgres]: https://blog.dbi-services.com/category/postgres/
[No Comments]: https://blog.dbi-services.com/postgresql-when-wal_level-to-logical/#respond
[Link 1]: https://blog.dbi-services.com/postgresql-when-wal_level-to-logical/#