PostgreSQL: When wal_level to logical

PostgreSQL: When wal_level to logical

By Mouhamadou DiawJanuary 27, 2019Database Administration & MonitoringPostgresNo 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

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

1

2

3

4

5

6

7

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

1

2

3

4

5

6

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

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

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?

1

2

3

4

5

6

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.

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

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

1

[postgres@dbi-pg-essentials_3 PG1]$ pg_recvlogical -d postgres --slot=myslot_2  --create-slot

And let’s start the streaming in a first terminal

1

[postgres@dbi-pg-essentials_3 PG1]$ pg_recvlogical -d postgres --slot=myslot_2  --start -f -

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

1

2

3

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

INSERT 0 1

postgres=#

We will see following in the first terminal

1

2

3

4

[postgres@dbi-pg-essentials_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.

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值