Logical Decoding Output Plug-in Installation for PostgreSQL

Logical Decoding Plug-ins

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.

As of PostgreSQL 9.4, 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 the context of logical replication, a slot represents a stream of changes that can be replayed to a client in the order they were made on the origin server. Each slot streams a sequence of changes from a single database. The output plug-ins transform the data from the write-ahead log’s internal representation into the format the consumer of a replication slot desires. Plug-ins are written in C, compiled, and installed on the machine which runs the PostgreSQL server, and they use a number of PostgreSQL specific APIs, as described by the PostgreSQL documentation.

Debezium’s PostgreSQL connector works with one of Debezium’s supported logical decoding plug-ins,

to encode the changes in either Protobuf format or Logical replication format.

For simplicity, Debezium also provides a container image based on a vanilla PostgreSQL server image on top of which it compiles and installs the plug-ins.

The Debezium logical decoding plug-ins have only been installed and tested on Linux machines. For Windows and other platforms it may require different installation steps

Differences between Plug-ins

All up-to-date differences are tracked in a test suite Java class.

More information about the logical decoding and output plug-ins can be found at:

Installation

At the current installation example, the decoderbufs output plug-in for logical decoding is used. The decoderbufs output plug-in produces a Protobuf message per database change. Each message contains new/old tuples for an updated table row.. The plug-in compilation and installation is performed by executing the related commands extracted from the Debezium Dockerfile.

Before executing the commands, make sure that the user has the privileges to write the decoderbufs library at the PostgreSQL _lib_ directory (at the test environment, the directory is: /usr/lib64/pgsql/). Also note that the installation process requires the PostgreSQL utility pg_config. Verify that the PATH environment variable is set so as the utility can be found. If not, update the PATH environment variable appropriately. For example at the test environment:

decoderbufs installation commands

  1. $ git clone https://github.com/debezium/postgres-decoderbufs -b v{debezium-version} --single-branch \
  2. && cd postgres-decoderbufs \
  3. && make && make install \
  4. && cd .. \
  5. && rm -rf postgres-decoderbufs

decoderbufs installation output

  1. Cloning into 'postgres-decoderbufs'...
  2. remote: Enumerating objects: 288, done.
  3. remote: Counting objects: 100% (4/4), done.
  4. remote: Compressing objects: 100% (4/4), done.
  5. remote: Total 288 (delta 0), reused 1 (delta 0), pack-reused 284
  6. Receiving objects: 100% (288/288), 91.62 KiB | 3.66 MiB/s, done.
  7. Resolving deltas: 100% (131/131), done.
  8. Note: switching to 'c9b00aa8c093fa77e08b256bb09d33069a30db86'.
  10. You are in 'detached HEAD' state. You can look around, make experimental
  11. changes and commit them, and you can discard any commits you make in this
  12. state without impacting any branches by switching back to a branch.
  14. If you want to create a new branch to retain commits you create, you may
  15. do so (now or later) by using -c with the switch command. Example:
  17.   git switch -c <new-branch-name>
  19. Or undo this operation with:
  21.   git switch -
  23. Turn off this advice by setting config variable advice.detachedHead to false
  25. gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wno-format-truncation -Wno-stringop-truncation -O2 -g -pipe -Wall -Werror=format-security -Wp,-D_FORTIFY_SOURCE=2 -Wp,-D_GLIBCXX_ASSERTIONS -fexceptions -fstack-protector-strong -grecord-gcc-switches -specs=/usr/lib/rpm/redhat/redhat-hardened-cc1 -specs=/usr/lib/rpm/redhat/redhat-annobin-cc1 -m64 -mtune=generic -fasynchronous-unwind-tables -fstack-clash-protection -fcf-protection -fPIC -std=c11  -I/usr/local/include  -I. -I./ -I/usr/include/pgsql/server -I/usr/include/pgsql/internal  -D_GNU_SOURCE -I/usr/include/libxml2   -c -o src/decoderbufs.o src/decoderbufs.c
  26. gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wno-format-truncation -Wno-stringop-truncation -O2 -g -pipe -Wall -Werror=format-security -Wp,-D_FORTIFY_SOURCE=2 -Wp,-D_GLIBCXX_ASSERTIONS -fexceptions -fstack-protector-strong -grecord-gcc-switches -specs=/usr/lib/rpm/redhat/redhat-hardened-cc1 -specs=/usr/lib/rpm/redhat/redhat-annobin-cc1 -m64 -mtune=generic -fasynchronous-unwind-tables -fstack-clash-protection -fcf-protection -fPIC -std=c11  -I/usr/local/include  -I. -I./ -I/usr/include/pgsql/server -I/usr/include/pgsql/internal  -D_GNU_SOURCE -I/usr/include/libxml2   -c -o src/proto/pg_logicaldec.pb-c.o src/proto/pg_logicaldec.pb-c.c
  27. gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wno-format-truncation -Wno-stringop-truncation -O2 -g -pipe -Wall -Werror=format-security -Wp,-D_FORTIFY_SOURCE=2 -Wp,-D_GLIBCXX_ASSERTIONS -fexceptions -fstack-protector-strong -grecord-gcc-switches -specs=/usr/lib/rpm/redhat/redhat-hardened-cc1 -specs=/usr/lib/rpm/redhat/redhat-annobin-cc1 -m64 -mtune=generic -fasynchronous-unwind-tables -fstack-clash-protection -fcf-protection -fPIC -shared -o decoderbufs.so src/decoderbufs.o src/proto/pg_logicaldec.pb-c.o -L/usr/lib64  -Wl,-z,relro  -Wl,-z,now -specs=/usr/lib/rpm/redhat/redhat-hardened-ld  -Wl,--as-needed  -lprotobuf-c
  28. /usr/bin/mkdir -p '/usr/lib64/pgsql'
  29. /usr/bin/mkdir -p '/usr/share/pgsql/extension'
  30. /usr/bin/install -c -m 755  decoderbufs.so '/usr/lib64/pgsql/decoderbufs.so'
  31. /usr/bin/install -c -m 644 .//decoderbufs.control '/usr/share/pgsql/extension/'

Installation on Fedora 30+

Debezium provides RPM package for Fedora operating system too. The package is updated always after a final Debezium release is done. To use the RPM in question just issue the standard Fedora installation command:

  1. $ sudo dnf -y install postgres-decoderbufs

The rest of the configuration is same as described below.

PostgreSQL Server Configuration

Once the decoderbufs plug-in has been installed, the database server should be configured.

Setting up libraries, WAL and replication parameters

Add the following lines at the end of the postgresql.conf PostgreSQL configuration file in order to include the plug-in at the shared libraries and to adjust some WAL and streaming replication settings. The configuration is extracted from postgresql.conf.sample. You may need to modify it, if for example you have additionally installed shared_preload_libraries.

postgresql.conf , configuration file parameters settings

  1. ############ REPLICATION ##############
  2. # MODULES
  3. shared_preload_libraries = 'decoderbufs'   (1)
  5. # REPLICATION
  6. wal_level = logical                     (2)
  7. max_wal_senders = 4                     (3)
  8. max_replication_slots = 4               (4)
1tells the server that it should load at startup the decoderbufs (the name of the plug-in is set in decoderbufs Makefile)
2tells the server that it should use logical decoding with the write-ahead log
3tells the server that it should use a maximum of 4 separate processes for processing WAL changes
4tells the server that it should allow a maximum of 4 replication slots to be created for streaming WAL changes

Debezium uses PostgreSQL’s logical decoding, which uses replication slots. Replication slots are guaranteed to retain all WAL required for Debezium even during Debezium outages. It is important for this reason to closely monitor replication slots to avoid too much disk consumption and other conditions that can happen such as catalog bloat if a Debezium slot stays unused for too long. For more information please see the official Postgres docs on this subject.

We strongly recommend reading and understanding the official documentation regarding the mechanics and configuration of the PostgreSQL write-ahead log.

Setting up replication permissions

Replication can only be performed by a database user that has appropriate permissions and only for a configured number of hosts. In order to give a user replication permissions, define a PostgreSQL role that has at least the REPLICATION and LOGIN permissions. For example:

  1. CREATE ROLE name REPLICATION LOGIN;

Superusers have by default both of the above roles.

Add the following lines at the end of the pg_hba.conf PostgreSQL configuration file, so as to configure the client authentication for the database replication. The PostgreSQL server should allow replication to take place between the server machine and the host on which the Debezium PostgreSQL connector is running.

Note that the authentication refers to the database superuser postgres. You may change this accordingly, if some other user with REPLICATION and LOGIN permissions has been created.

pg_hba.conf , configuration file parameters settings

  1. ############ REPLICATION ##############
  2. local   replication     postgres                          trust        (1)
  3. host    replication     postgres  127.0.0.1/32            trust        (2)
  4. host    replication     postgres  ::1/128                 trust        (3)
1tells the server to allow replication for postgres locally (i.e. on the server machine)
2tells the server to allow postgres on localhost to receive replication changes using IPV4
3tells the server to allow postgres on localhost to receive replication changes using IPV6

See the PostgreSQL documentation for more information on network masks.