CDC 之 PG数据订阅
Java订阅PostgreSQL数据变更及消费位置记录实现详解
PostgreSQL提供了逻辑解码(Logical Decoding)功能来订阅数据变更，结合wal2json或pgoutput等插件可以实现类似MySQL binlog的效果。以下是完整的实现方案。
1. PostgreSQL准备工作
1.1 修改postgresql.conf配置
# 启用逻辑解码
wal_level = logical
# 设置最大WAL发送者数量
max_wal_senders = 10
# 设置保留的WAL段数量
wal_keep_size = 1024MB
1.2 创建复制槽
-- 使用wal2json插件创建复制槽
SELECT * FROM pg_create_logical_replication_slot('my_slot', 'wal2json');
-- 或者使用pgoutput插件(PostgreSQL 10+)
SELECT * FROM pg_create_logical_replication_slot('my_slot', 'pgoutput');
2. Java实现方案
方案一：使用JDBC + 逻辑解码
import org.postgresql.PGConnection;
import org.postgresql.replication.PGReplicationStream;
import java.nio.ByteBuffer;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;
import java.util.concurrent.TimeUnit;
public class PgLogicalReplicationExample {
private static final String DB_URL = "jdbc:postgresql://localhost:5432/yourdb";
private static final String DB_USER = "youruser";
private static final String DB_PASSWORD = "yourpassword";
private static final String SLOT_NAME = "my_slot";
private static final String POSITION_FILE = "pg_replication_position.txt";
public static void main(String[] args) {
try {
// 1. 建立数据库连接
Connection connection = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
PGConnection pgConnection = connection.unwrap(PGConnection.class);
// 2. 读取上次记录的位置
String lastLSN = readLastLSN();
// 3. 创建复制流
Properties props = new Properties();
if (lastLSN != null) {
props.setProperty("start_lsn", lastLSN);
}
props.setProperty("skip_empty_xacts", "true");
props.setProperty("include_xids", "true");
PGReplicationStream stream = pgConnection
.getReplicationAPI()
.replicationStream()
.logical()
.withSlotName(SLOT_NAME)
.withSlotOption("include-xids", true)
.withSlotOption("skip-empty-xacts", true)
.withStartPosition(lastLSN != null ?
org.postgresql.replication.LogSequenceNumber.valueOf(lastLSN) :
org.postgresql.replication.LogSequenceNumber.valueOf("0/0"))
.start();
// 4. 启动消费线程
new Thread(() -> {
try {
while (true) {
// 非阻塞获取数据
ByteBuffer buffer = stream.read(1, TimeUnit.SECONDS);
if (buffer == null) {
continue;
}
// 处理变更数据
int offset = buffer.arrayOffset();
byte[] source = buffer.array();
int length = source.length - offset;
String message = new String(source, offset, length);
// 解析JSON消息(wal2json格式)
System.out.println("Received change: " + message);
processChange(message);
// 更新LSN位置
org.postgresql.replication.LogSequenceNumber lsn = stream.getLastReceiveLSN();
saveLSN(lsn.toString());
// 确认已处理
stream.setAppliedLSN(lsn);
stream.setFlushedLSN(lsn);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
stream.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}).start();
} catch (Exception e) {
e.printStackTrace();
}
}
private static void processChange(String message) {
// 解析JSON消息并处理变更
// wal2json格式示例:
// {
// "change": [
// {
// "kind": "insert",
// "schema": "public",
// "table": "users",
// "columnnames": ["id", "name", "email"],
// "columntypes": ["integer", "character varying", "character varying"],
// "columnvalues": [1, "John Doe", "john@example.com"]
// }
// ]
// }
System.out.println("Processing change: " + message);
}
private static String readLastLSN() {
try (BufferedReader reader = new BufferedReader(new FileReader(POSITION_FILE))) {
return reader.readLine();
} catch (FileNotFoundException e) {
System.out.println("No position file found, starting from beginning");
return null;
} catch (IOException e) {
System.err.println("Error reading position file: " + e.getMessage());
return null;
}
}
private static void saveLSN(String lsn) {
try (BufferedWriter writer = new BufferedWriter(new FileWriter(POSITION_FILE))) {
writer.write(lsn);
System.out.println("Saved LSN position: " + lsn);
} catch (IOException e) {
System.err.println("Error saving LSN position: " + e.getMessage());
}
}
}
方案二：使用Debezium引擎(推荐)
Debezium是构建于Kafka Connect之上的分布式变更数据捕获(CDC)平台，提供了更完善的解决方案。
Maven依赖
<dependency>
<groupId>io.debezium</groupId>
<artifactId>debezium-api</artifactId>
<version>1.9.7.Final</version>
</dependency>
<dependency>
<groupId>io.debezium</groupId>
<artifactId>debezium-embedded</artifactId>
<version>1.9.7.Final</version>
</dependency>
<dependency>
<groupId>io.debezium</groupId>
<artifactId>debezium-connector-postgres</artifactId>
<version>1.9.7.Final</version>
</dependency>
实现代码
import io.debezium.engine.ChangeEvent;
import io.debezium.engine.DebeziumEngine;
import io.debezium.engine.format.Json;
import org.apache.kafka.connect.source.SourceRecord;
import java.io.File;
import java.io.IOException;
import java.nio.file.Paths;
import java.util.Properties;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
public class DebeziumPgCDCExample {
private static final String POSITION_FILE = "debezium_pg_position.txt";
public static void main(String[] args) throws IOException {
// 1. 配置Debezium引擎
Properties props = new Properties();
props.setProperty("name", "pg-connector");
props.setProperty("connector.class", "io.debezium.connector.postgresql.PostgresConnector");
props.setProperty("offset.storage", "io.debezium.storage.file.offset.FileOffsetBackingStore");
props.setProperty("offset.storage.file.filename", Paths.get(POSITION_FILE).toAbsolutePath().toString());
props.setProperty("offset.flush.interval.ms", "60000");
// PostgreSQL连接配置
props.setProperty("database.hostname", "localhost");
props.setProperty("database.port", "5432");
props.setProperty("database.user", "youruser");
props.setProperty("database.password", "yourpassword");
props.setProperty("database.dbname", "yourdb");
props.setProperty("database.server.name", "pg_server");
// 复制槽配置
props.setProperty("plugin.name", "pgoutput"); // 或 "wal2json"
props.setProperty("slot.name", "my_slot");
props.setProperty("publication.name", "my_publication");
props.setProperty("publication.autocreate.mode", "all_tables");
// 表包含/排除配置
props.setProperty("table.include.list", "public.users,public.orders");
// 2. 创建Debezium引擎
try (DebeziumEngine<ChangeEvent<String, String>> engine = DebeziumEngine.create(Json.class)
.using(props)
.notifying(record -> {
// 处理变更事件
SourceRecord sourceRecord = record.record();
System.out.println("Key = " + record.key());
System.out.println("Value = " + record.value());
// 这里可以添加业务逻辑处理
processChange(sourceRecord);
})
.build()) {
// 3. 启动引擎
ExecutorService executor = Executors.newSingleThreadExecutor();
executor.execute(engine);
// 4. 等待停止信号
Runtime.getRuntime().addShutdownHook(new Thread(() -> {
System.out.println("Shutting down engine...");
engine.close();
}));
// 保持程序运行
executor.awaitTermination(Long.MAX_VALUE, TimeUnit.SECONDS);
} catch (InterruptedException e) {
Thread.currentThread().interrupt();
}
}
private static void processChange(SourceRecord record) {
// 解析Debezium事件格式
String topic = record.topic();
String[] parts = topic.split("\\.");
String operation = record.value() != null ?
((Map<?, ?>) Json.deserialize(record.value())).get("op").toString() : null;
System.out.printf("Table: %s, Operation: %s%n", parts[2], operation);
// c = create/insert, u = update, d = delete, r = read (snapshot)
}
}
3. 消费位置记录策略
3.1 文件存储位置
如上面示例所示，可以将LSN或offset信息存储在本地文件中。
3.2 数据库存储位置
// 存储位置到数据库
private static void savePositionToDB(String slotName, String lsn) {
try (Connection conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
PreparedStatement stmt = conn.prepareStatement(
"INSERT INTO replication_positions (slot_name, lsn, update_time) " +
"VALUES (?, ?, NOW()) " +
"ON CONFLICT(slot_name) DO UPDATE SET lsn = EXCLUDED.lsn, update_time = NOW()")) {
stmt.setString(1, slotName);
stmt.setString(2, lsn);
stmt.executeUpdate();
} catch (SQLException e) {
System.err.println("Error saving position to DB: " + e.getMessage());
}
}
// 从数据库读取位置
private static String readPositionFromDB(String slotName) {
try (Connection conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
PreparedStatement stmt = conn.prepareStatement(
"SELECT lsn FROM replication_positions WHERE slot_name = ?")) {
stmt.setString(1, slotName);
ResultSet rs = stmt.executeQuery();
if (rs.next()) {
return rs.getString("lsn");
}
} catch (SQLException e) {
System.err.println("Error reading position from DB: " + e.getMessage());
}
return null;
}
3.3 使用Debezium内置的Offset存储
Debezium已经内置了offset存储机制，可以通过配置使用文件、Kafka或数据库存储：
# 使用文件存储offset
offset.storage=io.debezium.storage.file.offset.FileOffsetBackingStore
offset.storage.file.filename=path/to/offset.dat
# 或使用Kafka存储offset
offset.storage=org.apache.kafka.connect.storage.KafkaOffsetBackingStore
offset.storage.topic=connect-offsets
offset.storage.partitions=10
# 或使用PostgreSQL存储offset
offset.storage=io.debezium.storage.jdbc.offset.JdbcOffsetBackingStore
offset.storage.jdbc.url=jdbc:postgresql://localhost:5432/connect_db
offset.storage.jdbc.user=connect_user
offset.storage.jdbc.password=connect_pass
4. 最佳实践
-
错误处理与重试机制：
- 实现连接断开时的自动重连
- 记录处理失败的消息以便重试
-
性能优化：
- 批量处理消息而不是单条处理
- 调整
max.batch.size和max.queue.size参数
-
监控与告警：
- 监控消费延迟
- 设置阈值告警
-
初始化与恢复：
- 程序启动时验证复制槽是否存在
- 如果位置文件损坏，提供恢复机制
-
多线程处理：
- 使用连接池处理高并发
- 注意消息处理的顺序性要求
5. 常见问题解决
-
复制槽不存在：
- 确保已创建复制槽
- 检查用户是否有复制权限
-
WAL保留问题：
- 确保
wal_keep_size足够大 - 考虑设置
replication_timeout
- 确保
-
消息积压：
- 增加消费者线程
- 优化处理逻辑
-
版本兼容性：
- 确保PostgreSQL版本与插件版本兼容
- Debezium版本与PostgreSQL版本匹配
通过以上方案，你可以实现可靠的PostgreSQL数据变更订阅和消费位置记录，确保数据一致性和系统可靠性。
本文来自博客园，作者：蓝迷梦，转载请注明原文链接：https://www.cnblogs.com/hewei-blogs/articles/19298279
浙公网安备 33010602011771号