【PgSQL插件】实时同步插件cdc wal2json

1、操作系统版本

$ cat /etc/redhat-release
CentOS Linux release 7.4.1708 (Core)

PostgreSQL版本

$ psql -V
psql (PostgreSQL) 12.3

wal2json插件获取地址：https://github.com/eulerto/wal2json/releases/tag/wal2json_2_3

2、部署wal2json插件

# unzip wal2json-wal2json_2_3.zip -d /usr/local/

# cd /usr/local/

# chown -R postgres.postgres wal2json-wal2json_2_3/

# su - postgres

$ cd /usr/local/wal2json-wal2json_2_3/
$ ll
total 136
drwxr-xr-x 2 postgres postgres 4096 Jul 27 2020 expected
-rw-r--r-- 1 postgres postgres 1517 Jul 27 2020 LICENSE
-rw-r--r-- 1 postgres postgres 1096 Jul 27 2020 Makefile
-rw-r--r-- 1 postgres postgres 17409 Jul 27 2020 README.md
drwxr-xr-x 2 postgres postgres 4096 Jul 27 2020 sql
-rw-r--r-- 1 postgres postgres 91315 Jul 27 2020 wal2json.c
-rw-r--r-- 1 postgres postgres 5758 Jul 27 2020 wal2json.vcxproj

$export PATH=/usr/local/pgsql/bin/pg_config:$PATH

+ View Code?

$ make

gcc -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches -m64 -mtune=generic -DLINUX_OOM_SCORE_ADJ=0 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -fPIC -I. -I. -I/usr/include/pgsql/server -I/usr/include/pgsql/internal -D_GNU_SOURCE -I/usr/include/libxml2   -c -o wal2json.o wal2json.c

wal2json.c:21:33: fatal error: replication/logical.h: No such file or directory

 #include "replication/logical.h"

                                 ^

compilation terminated.

make: *** [wal2json.o] Error 1

　　

$ make
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -I. -I./ -I/usr/local/postgresql-12.3/include/server -I/usr/local/postgresql-12.3/include/internal -D_GNU_SOURCE -c -o wal2json.o wal2json.c
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC wal2json.o -L/usr/local/postgresql-12.3/lib -Wl,--as-needed -Wl,-rpath,'/usr/local/postgresql-12.3/lib',--enable-new-dtags -shared -o wal2json.so
$ make install
/bin/mkdir -p '/usr/local/postgresql-12.3/lib'
/bin/install -c -m 755 wal2json.so '/usr/local/postgresql-12.3/lib/'
$

 配置postgresql.conf

shared_preload_libraries = 'wal2json'
wal_level = logical
max_wal_senders = 10
max_replication_slots =  10

创建用户

create user usr_zee replication login connection limit 5 encrypted password 'usr_zee';   

修改pg_hba.conf，使该用户可以远程访问数据库

# IPv4 local connections:
host       all     all             0.0.0.0/0                 md5

重启数据库

pg_ctl restart -D /data/pgsql/data

3、测试wal2json

创建slot

pg_recvlogical -d zeedb --slot zee_slot --create-slot -P wal2json

json信息

pg_recvlogical -d zeedb --slot zee_slot --start -o pretty-print=1 -f -