Install logical decoding plugin
- decoderbufs plugin
# protobuf
$ wget https://github.com/protocolbuffers/protobuf/releases/download/v2.6.1/protobuf-2.6.1.tar.gz
$ tar -xzvf protobuf-2.6.1.tar.gz
$ cd protobuf-2.6.1
$ ./configure --prefix=/usr
$ make
$ make check
$ sudo make install
$ sudo ldconfig
# protobuf-c
$ wget https://github.com/protobuf-c/protobuf-c/releases/download/v1.3.1/protobuf-c-1.3.1.tar.gz
$ tar -xzvf protobuf-c-1.3.1.tar.gz
$ cd protobuf-c-1.3.1
$ sudo yum install pkgconfig
$ export PKG_CONFIG_PATH=/usr/local/lib/pkgconfig
$ ./configure --prefix=/usr
$ make
$ sudo make install
# PostGIS
$ sudo yum -y install epel-release
$ sudo yum install https://download.postgresql.org/pub/repos/yum/11/redhat/rhel-7-x86_64/pgdg-centos11-11-2.noarch.rpm
$ sudo yum install postgis25_11
# decoderbufs
$ wget https://github.com/debezium/postgres-decoderbufs/archive/v0.9.5.Final.tar.gz
$ tar -xzvf v0.9.5.Final.tar.gz
$ sudo yum install postgresql11-devel.x86_64
$ wget http://mirror.centos.org/centos/7/sclo/x86_64/rh/llvm-toolset-7/llvm-toolset-7-llvm-5.0.1-8.el7.x86_64.rpm
$ sudo rpm -i llvm-toolset-7-llvm-5.0.1-8.el7.x86_64.rpm
$ sudo yum install centos-release-scl
$ sudo yum install devtoolset-7
$ sudo yum install llvm5.0
$ yum install llvm-toolset-7
$ cd postgres-decoderbufs-0.9.5.Final/
$ export PKG_CONFIG_PATH=/usr/local/lib/pkgconfig
$ make
$ sudo make install
- wal2json plugin
$ export PKG_CONFIG_PATH=/usr/lib/pkgconfig
# OR export PKG_CONFIG_PATH=/usr/local/lib/pkgconfig
$ wget https://github.com/eulerto/wal2json/archive/wal2json_1_0.tar.gz
$ tar -xzvf wal2json_1_0.tar.gz
$ cd wal2json-wal2json_1_0/
$ USE_PGXS=1 make
$ USE_PGXS=1 make install
- Configuring the PostgreSQL Server
Add to postgresql.conf or conf.d/replication.conf
# MODULES
shared_preload_libraries = 'decoderbufs,wal2json'
# REPLICATION
wal_level = logical # minimal, archive, hot_standby, or logical (change requires restart)
max_wal_senders = 4 # max number of walsender processes (change requires restart)
wal_keep_segments = 0 # in logfile segments, 16MB each; 0 disables
#wal_sender_timeout = 60s # in milliseconds; 0 disables
max_replication_slots = 4 # max number of replication slots (change requires restart)
Setup permissions (May be create an user with md5 for flexibility?)
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:
CREATE ROLE name REPLICATION LOGIN;
Superusers have by default both of the above roles.
Finally, configure the PostgreSQL server to allow replication to take place between the server machine and the host on which the Debezium PostgreSQL connector is running:
**pg_hba.conf**
local replication <youruser> trust
host replication <youruser> 127.0.0.1/32 trust
host replication <youruser> ::1/128 trust
--> Restart Postgres