xlog、wal 分析工具 wal2json

postgresql extension 同时被 3 个专栏收录
65 篇文章 1 订阅
38 篇文章 1 订阅

os: centos 7.4
db: postgresql 10.11

版本

# cat /etc/centos-release
CentOS Linux release 7.4.1708 (Core) 
# 
# 
# yum list installed |grep -i postgresql
postgresql10.x86_64                10.11-2PGDG.rhel7                   @pgdg10  
postgresql10-contrib.x86_64        10.11-2PGDG.rhel7                   @pgdg10  
postgresql10-debuginfo.x86_64      10.11-2PGDG.rhel7                   @pgdg10  
postgresql10-devel.x86_64          10.11-2PGDG.rhel7                   @pgdg10  
postgresql10-docs.x86_64           10.11-2PGDG.rhel7                   @pgdg10  
postgresql10-libs.x86_64           10.11-2PGDG.rhel7                   @pgdg10  
postgresql10-odbc.x86_64           12.00.0000-1PGDG.rhel7              @pgdg10  
postgresql10-plperl.x86_64         10.11-2PGDG.rhel7                   @pgdg10  
postgresql10-plpython.x86_64       10.11-2PGDG.rhel7                   @pgdg10  
postgresql10-pltcl.x86_64          10.11-2PGDG.rhel7                   @pgdg10  
postgresql10-server.x86_64         10.11-2PGDG.rhel7                   @pgdg10  
postgresql10-tcl.x86_64            2.4.0-1.rhel7                       @pgdg10  
postgresql10-tcl-debuginfo.x86_64  2.3.1-1.rhel7                       @pgdg10  
postgresql10-test.x86_64           10.11-2PGDG.rhel7                   @pgdg10 

# su - postgres
Last login: Wed Jan 15 18:34:12 CST 2020 on pts/0
$
$
$ psql -c "select version();"
                                                 version                                                  
----------------------------------------------------------------------------------------------------------
 PostgreSQL 10.11 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit
(1 row)

下载安装

# su - postgres
# yum install wal2json10

# repoquery -ql wal2json10.x86_64
/usr/pgsql-10/doc/extension/README-wal2json.md
/usr/pgsql-10/lib/wal2json.so


# vi /var/lib/pgsql/10/data/postgresql.conf 
shared_preload_libraries = 'wal2json'
wal_level = logical
max_wal_senders = 20 
max_replication_slots = 20


# systemctl restart postgresql-10.service

初步使用

第一个窗口

# su - postgres
$ pg_recvlogical -d pgbenchdb --slot wal2json_slot --create-slot -P wal2json
$ pg_recvlogical -d pgbenchdb --slot wal2json_slot --start -o pretty-print=1 -f -

第二个窗口

# su - postgres
$ psql pgbenchdb

pgbenchdb=# CREATE TABLE table_with_pk (a SERIAL, b VARCHAR(30), c TIMESTAMP NOT NULL, PRIMARY KEY(a, c));
CREATE TABLE table_without_pk (a SERIAL, b NUMERIC(5,2), c TEXT);

BEGIN;
INSERT INTO table_with_pk (b, c) VALUES('Backup and Restore', now());
INSERT INTO table_with_pk (b, c) VALUES('Tuning', now());
INSERT INTO table_with_pk (b, c) VALUES('Replication', now());

DELETE FROM table_with_pk WHERE a < 3;

INSERT INTO table_without_pk (b, c) VALUES(2.34, 'Tapir');
-- it is not added to stream because there isn't a pk or a replica identity
UPDATE table_without_pk SET c = 'Anta' WHERE c = 'Tapir';
COMMIT;

这时候第一个窗口的输出如下


{
	"change": [
	]
}
{
	"change": [
	]
}


WARNING:  table "table_without_pk" without primary key or replica identity is nothing
{
	"change": [
		{
			"kind": "insert",
			"schema": "public",
			"table": "table_with_pk",
			"columnnames": ["a", "b", "c"],
			"columntypes": ["integer", "character varying(30)", "timestamp without time zone"],
			"columnvalues": [1, "Backup and Restore", "2020-04-26 09:46:58.957681"]
		}
		,{
			"kind": "insert",
			"schema": "public",
			"table": "table_with_pk",
			"columnnames": ["a", "b", "c"],
			"columntypes": ["integer", "character varying(30)", "timestamp without time zone"],
			"columnvalues": [2, "Tuning", "2020-04-26 09:46:58.957681"]
		}
		,{
			"kind": "insert",
			"schema": "public",
			"table": "table_with_pk",
			"columnnames": ["a", "b", "c"],
			"columntypes": ["integer", "character varying(30)", "timestamp without time zone"],
			"columnvalues": [3, "Replication", "2020-04-26 09:46:58.957681"]
		}
		,{
			"kind": "delete",
			"schema": "public",
			"table": "table_with_pk",
			"oldkeys": {
				"keynames": ["a", "c"],
				"keytypes": ["integer", "timestamp without time zone"],
				"keyvalues": [1, "2020-04-26 09:46:58.957681"]
			}
		}
		,{
			"kind": "delete",
			"schema": "public",
			"table": "table_with_pk",
			"oldkeys": {
				"keynames": ["a", "c"],
				"keytypes": ["integer", "timestamp without time zone"],
				"keyvalues": [2, "2020-04-26 09:46:58.957681"]
			}
		}
		,{
			"kind": "insert",
			"schema": "public",
			"table": "table_without_pk",
			"columnnames": ["a", "b", "c"],
			"columntypes": ["integer", "numeric(5,2)", "text"],
			"columnvalues": [1, 2.34, "Tapir"]
		}
	]
}

第一个窗口中删除刚才创建的 slot

Ctrl+C
$ pg_recvlogical -d pgbenchdb --slot wal2json_slot --drop-slot

更详细用法参考 https://github.com/eulerto/wal2json

参考:
https://github.com/eulerto/wal2json
https://debezium.io/documentation/reference/1.1/connectors/postgresql.html

  • 0
    点赞
  • 0
    评论
  • 0
    收藏
  • 打赏
    打赏
  • 扫一扫,分享海报

参与评论 您还未登录,请先 登录 后发表或查看评论
©️2022 CSDN 皮肤主题:代码科技 设计师:Amelia_0503 返回首页

打赏作者

数据库人生

你的鼓励将是我创作的最大动力

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值