安装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 事件将包含名为索引名称的索引定义中包含的列的先前值
*/