【PostgreSQL】CentOS7.9环境安装postgresql14和插件

安装postgresql14

sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
sudo yum install -y postgresql14-server
## 主库初始化数据库
/usr/pgsql-14/bin/postgresql-14-setup initdb
## 启动
sudo systemctl enable postgresql-14
sudo systemctl start postgresql-14
su postgres
psql
alter user postgres with password 'postgres';
## 修改参数文件
cp /var/lib/pgsql/14/data/pg_hba.conf /var/lib/pgsql/14/data/pg_hba.conf.bak
cp /var/lib/pgsql/14/data/postgresql.conf /var/lib/pgsql/14/data/postgresql.conf.bak
sed -i "s/                                     peer/                                     trust/g" /var/lib/pgsql/14/data/pg_hba.conf
sed -i "s#host    replication     all             127.0.0.1/32            scram-sha-256#host    replication     all             127.0.0.1/32            trust#g" /var/lib/pgsql/14/data/pg_hba.conf
sed -i "s#host    replication     all             ::1/128                 scram-sha-256#host    replication     all             ::1/128                 trust#g" /var/lib/pgsql/14/data/pg_hba.conf
echo "host    all             all             0.0.0.0/0               scram-sha-256">> /var/lib/pgsql/14/data/pg_hba.conf
sed -i "s/#listen_addresses = localhost/listen_addresses = */g" /var/lib/pgsql/14/data/postgresql.conf
sed -i 's/max_connections = 100/max_connections = 300/g' /var/lib/pgsql/14/data/postgresql.conf
sed -i 's/shared_buffers = 128MB/shared_buffers = 8GB/g' /var/lib/pgsql/14/data/postgresql.conf
# 设置环境变量
echo "export PATH=/usr/pgsql-14/bin:$PATH">/var/lib/pgsql/.pgsql_profile
echo "export PGDATA=/var/lib/pgsql/14/data">/var/lib/pgsql/.pgsql_profile
source /var/lib/pgsql/.pgsql_profile

安装插件

wal2json_14

这是一个解析postgresql日志的插件，用来对postgresql的wal日志进行采集和解析，官方介绍

Logical Decoding Output Plug-in Installation for PostgreSQL :: Debezium Documentation

Git:https://github.com/debezium/container-images/tree/main/postgres/14 

root$ sudo yum install wal2json_14

修改postgresql.conf

su - postgres
# MODULES
sed -i "s/#shared_preload_libraries = ''/shared_preload_libraries = 'wal2json'/g" $PGDATA/postgresql.conf

# REPLICATION
sed -i "s/#wal_level = replica/wal_level = logical/g" $PGDATA/postgresql.conf
sed -i "s/#max_wal_senders = 10/max_wal_senders = 4/g" $PGDATA/postgresql.conf
sed -i "s/#max_replication_slots = 10/max_replication_slots = 4/g" $PGDATA/postgresql.conf

exit
sudo systemctl restart postgresql-14

创建专用角色

su - postgres
psql
CREATE ROLE name REPLICATION LOGIN;

 创建测试数据库

su - postgres
psql
create database test;
create table test_table(id char(10) not null,code char(10),primary key(id));

 创建slot使用pg_recvlogical客户端应用程序监控实施日志采集

pg_recvlogical -d test --slot test_slot --create-slot -P wal2json
# 开启实时采集监视
pg_recvlogical -d test --slot test_slot --start -o pretty-print=1 -f -

# 测试数据库操作
psql -d test
INSERT INTO test_table (id, code) VALUES('id1', 'code1');
update test_table set code='codex' where id='id1';

# 采集日志信息反馈如下：
-bash-4.2$ pg_recvlogical -d test --slot test_slot --start -o pretty-print=1 -f -
{
	"change": [
	]
}
{
	"change": [
		{
			"kind": "insert",
			"schema": "public",
			"table": "test_table",
			"columnnames": ["id", "code"],
			"columntypes": ["character(10)", "character(10)"],
			"columnvalues": ["id1       ", "code1     "]
		}
	]
}
{
	"change": [
		{
			"kind": "update",
			"schema": "public",
			"table": "test_table",
			"columnnames": ["id", "code"],
			"columntypes": ["character(10)", "character(10)"],
			"columnvalues": ["id1       ", "codex     "],
			"oldkeys": {
				"keynames": ["id"],
				"keytypes": ["character(10)"],
				"keyvalues": ["id1       "]
			}
		}
	]
}

删除slot

pg_recvlogical -d test --slot test_slot --drop-slot

验证通过

补充知识点

可以通过REPLICA IDENTITY来对表进行表级解码信息量的设置

# 例子：
ALTER TABLE test_table REPLICA IDENTITY FULL;
/*
DEFAULT - UPDATE and DELETE 事件将仅包含表主键列的先前值
NOTHING - UPDATE and DELETE 事件将不包含关于任何表列上的上一个值的任何信息
FULL - UPDATE and DELETE 事件将包含表中所有列的先前值
INDEX index name - UPDATE and DELETE 事件将包含名为索引名称的索引定义中包含的列的先前值
*/