【PostageSQL】【初试】基于数据库 WAL 日志的数据同步

1  前言

嘿嘿，今天看见一篇 PGSql 的基于数据库层面的日志同步，简单也跟着试了一下，暂时还没细细探索哈。

2  前置准备

（1）修改postgresql数据库配置，通过复制流技术监听增量事件，基于内置插件pgoutput、test_decoding实现解析wal日志

修改postgresql.conf文件，重启服务
wal_level=logical

（2）我的数据库表

复制代码
CREATE TABLE "public"."my_test" (
  "id" int8 NOT NULL DEFAULT nextval('my_test_id_ser'::regclass),
  "name" varchar(64) COLLATE "pg_catalog"."default",
  "password" varchar(64) COLLATE "pg_catalog"."default",
  "flag" bool,
  "number" int4,
  "weight" numeric(20,4),
  "create_time" timestamp(6),
  "remark" varchar(64) COLLATE "pg_catalog"."default",
  CONSTRAINT "my_test_pkey" PRIMARY KEY ("id")
)
;

ALTER TABLE "public"."my_test" 
  OWNER TO "postgres";
复制代码

（3）POM依赖

<dependency>
     <groupId>org.postgresql</groupId>
     <artifactId>postgresql</artifactId>
     <version>42.2.2</version>
 </dependency>

3  代码体验

我的 demo 工程哈，平时没事自己搞个 maven 的 demo 工程，方便自己做一些测试，玩一些稀奇的东西哈，代码我都贴出来哈，你们也可以试试哈：

（1）main 类

复制代码
  1 package com.virtuous.demo.laboratory.algorithm.xian;
  2 
  3 import cn.hutool.core.lang.Assert;
  4 import lombok.extern.slf4j.Slf4j;
  5 import org.postgresql.PGConnection;
  6 import org.postgresql.PGProperty;
  7 import org.postgresql.replication.LogSequenceNumber;
  8 import org.postgresql.replication.PGReplicationStream;
  9 import org.postgresql.replication.fluent.logical.ChainedLogicalStreamBuilder;
 10 
 11 import java.sql.*;
 12 import java.util.HashMap;
 13 import java.util.Map;
 14 import java.util.Properties;
 15 import java.util.concurrent.TimeUnit;
 16 
 17 /**
 18  * @author: xjx
 19  * @description
 20  */
 21 @Slf4j
 22 public class SlotCopy {
 23 
 24     public static void main(String[] args) throws SQLException {
 25         SlotCopy del = new SlotCopy();
 26         del.run();
 27     }
 28 
 29     private static String url = "jdbc:postgresql://localhost:5432/test";
 30     private static String username = "postgres";
 31     private static String password = "sql626..";
 32     private static String driverClassName = "org.postgresql.Driver";
 33 
 34     private static final String GET_ROLE = "SELECT r.rolcanlogin AS login, r.rolreplication AS replication, CAST(array_position(ARRAY(SELECT b.rolname FROM pg_catalog.pg_auth_members m JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid) WHERE m.member = r.oid), 'rds_superuser') AS BOOL) IS TRUE AS superuser, CAST(array_position(ARRAY(SELECT b.rolname FROM pg_catalog.pg_auth_members m JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid) WHERE m.member = r.oid), 'rdsadmin') AS BOOL) IS TRUE AS admin, CAST(array_position(ARRAY(SELECT b.rolname FROM pg_catalog.pg_auth_members m JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid) WHERE m.member = r.oid), 'rdsrepladmin') AS BOOL) IS TRUE AS rep_admin FROM pg_roles r WHERE r.rolname = current_user";
 35     private static final String GET_DATABASE = "SELECT current_database()";
 36     private static final String GET_WAL_LEVEL = "SHOW WAL_LEVEL";
 37     private static final String DEFAULT_WAL_LEVEL = "logical";
 38     private static final String PLUGIN_NAME = "pluginName";
 39     private static final String LSN_POSITION = "position";
 40     private static final String DROP_SLOT_ON_CLOSE = "dropSlotOnClose";
 41 
 42     private static Connection connection;
 43     private static PGReplicationStream stream;
 44     private boolean dropSlotOnClose;
 45 
 46     private static String database;
 47     private static String schema = "test";
 48     private static String metaId = "isa";
 49     private static LogSequenceNumber startLsn;
 50     protected static Map<String, String> snapshot = new HashMap<>();
 51 
 52     private static volatile boolean connected;
 53 
 54     private Connection JdbcConnection3() {
 55         try {
 56             // 1. 加载Driver类，Driver类对象将自动被注册到DriverManager类中 "org.postgresql.Driver"
 57             Class.forName(driverClassName);
 58             // 2. 连接数据库，返回连接对象
 59             Connection conn = DriverManager.getConnection(url, username, password);
 60             return conn;
 61         } catch (Exception e) {
 62             log.error("----连接数据库异常，error:{}", e);
 63         }
 64         return null;
 65     }
 66 
 67     //jdbc执行查询操作
 68     private Object query(String sql, Connection connection,String columnLabel,String type) throws SQLException {
 69         try (PreparedStatement statement = connection.prepareStatement(sql)) {
 70             try (ResultSet resultSet = statement.executeQuery()) {
 71                 while (resultSet.next()) {
 72                     if("Long".equals(type)){
 73                         return resultSet.getLong(columnLabel);
 74                     }else{
 75                         return resultSet.getString(columnLabel);
 76                     }
 77                 }
 78             }
 79         }
 80         return null;
 81     }
 82 
 83     //校验当前用户权限
 84     private boolean isHasAuth(String sql, Connection connection) throws SQLException {
 85         try (PreparedStatement statement = connection.prepareStatement(sql)) {
 86             try (ResultSet resultSet = statement.executeQuery()) {
 87                 while (resultSet.next()) {
 88                     boolean login = resultSet.getBoolean("login");
 89                     boolean replication = resultSet.getBoolean("replication");
 90                     boolean superuser = resultSet.getBoolean("superuser");
 91                     boolean admin = resultSet.getBoolean("admin");
 92                     boolean repAdmin = resultSet.getBoolean("rep_admin");
 93                     return login && (replication || superuser || admin || repAdmin);
 94                 }
 95             }
 96         }
 97         return false;
 98     }
 99 
100     public String getSlotName() {
101         return String.format("dbs_slot_%s_%s_%s", schema, username, metaId);
102     }
103 
104     //创建复制消息槽位
105     private void createReplicationSlot(PGConnection pgConnection) throws SQLException {
106         String slotName = getSlotName();
107         String plugin = "test_decoding";//messageDecoder.getOutputPlugin(); 插件：TEST_DECODING test_decoding  插件：PG_OUTPUT pgoutput
108 
109         String GET_SLOT = "select count(1) from pg_replication_slots where database = '"+database+"' and slot_name = '"+slotName+"' and plugin = '"+plugin+"'";
110         long slot = (Long) query(GET_SLOT,connection,"count","Long");
111 
112         boolean existSlot = slot > 0;
113         if (!existSlot) {
114             pgConnection.getReplicationAPI()
115                     .createReplicationSlot()
116                     .logical()
117                     .withSlotName(slotName)
118                     .withOutputPlugin(plugin)
119                     .make();
120             // wait for create replication slot to have finished
121             try {
122                 TimeUnit.MILLISECONDS.sleep(300);
123             } catch (InterruptedException e) {
124                 log.info(e.getMessage());
125             }
126         }
127 
128         if (!snapshot.containsKey(LSN_POSITION)) {
129             String GET_RESTART_LSN = "select restart_lsn from pg_replication_slots where database = '"+database+"' and slot_name = '"+slotName+"' and plugin = '"+plugin+"'";
130             LogSequenceNumber lsn = LogSequenceNumber.valueOf((String) query(GET_RESTART_LSN,connection,"restart_lsn","String"));
131             if (null == lsn || lsn.asLong() == 0) {
132                 log.error("No maximum LSN recorded in the database");
133             }
134             snapshot.put(LSN_POSITION, lsn.asString());
135         }
136 
137         startLsn = LogSequenceNumber.valueOf(snapshot.get(LSN_POSITION));
138     }
139 
140     //创建复制消息stream流
141     private void createReplicationStream(PGConnection pgConnection) throws SQLException {
142         ChainedLogicalStreamBuilder streamBuilder = pgConnection
143                 .getReplicationAPI()
144                 .replicationStream()
145                 .logical()
146                 .withSlotName(getSlotName())
147                 .withStartPosition(startLsn)
148                 .withStatusInterval(10, TimeUnit.SECONDS)
149                 .withSlotOption("include-xids", true)
150                 .withSlotOption("skip-empty-xacts", true);
151 
152         //messageDecoder.withSlotOption(streamBuilder);
153         stream = streamBuilder.start();
154     }
155 
156     private void connect() throws SQLException {
157         Properties props = new Properties();
158         PGProperty.USER.set(props, username);
159         PGProperty.PASSWORD.set(props, password);
160         // Postgres 9.4发布逻辑复制功能
161         PGProperty.ASSUME_MIN_SERVER_VERSION.set(props, "9.4");
162         PGProperty.REPLICATION.set(props, "database");
163         PGProperty.PREFER_QUERY_MODE.set(props, "simple");
164 
165         //建立消息订阅连接
166         connection = DriverManager.getConnection(url, props);
167         Assert.notNull(connection, "Unable to get connection.");
168 
169         PGConnection pgConnection = connection.unwrap(PGConnection.class);
170 
171         //创建复制消息槽位
172         createReplicationSlot(pgConnection);
173 
174         //创建复制消息stream流
175         createReplicationStream(pgConnection);
176         System.out.println("==========1.server connect success!!!");
177     }
178 
179     public void run() throws SQLException {
180         //pg数据库初始化连接
181         connection = JdbcConnection3();
182         //获取WAL日志级别
183         String walLevel = (String) query(GET_WAL_LEVEL, connection,"wal_level","String");
184         if (!DEFAULT_WAL_LEVEL.equals(walLevel)) {
185             log.error(String.format("Postgres server wal_level property must be \"%s\" but is: %s", DEFAULT_WAL_LEVEL, walLevel));
186         }
187         //校验当前登录用户的权限
188         boolean hasAuth = isHasAuth(GET_ROLE,connection);
189         if(!hasAuth){
190             log.error(String.format("Postgres roles LOGIN and REPLICATION are not assigned to user: %s",username));
191         }
192 
193         //获取当前pg连接对接的数据库
194         database = (String) query(GET_DATABASE,connection,"current_database","String");
195 
196         //订阅pg数据库变更消息连接
197         connect();
198         connected = true;
199 
200         //接收的消息处理类
201         MessageHandle messageHandle = new MessageHandle(stream, connection, snapshot, LSN_POSITION);
202 
203         //消息接收线程
204         Worker worker = new Worker(stream, startLsn, messageHandle);
205         Thread workerThread = new Thread(worker);
206         workerThread.setName(new StringBuilder("wal-parser-").append(url).append("_").append(worker.hashCode()).toString());
207         workerThread.setDaemon(false);
208         workerThread.start();
209 
210         System.out.println("==========2.server start success!!!");
211 
212     }
213 
214 }
View Code
复制代码

（2）ColumnValue 相关类

复制代码
package com.virtuous.demo.laboratory.algorithm.xian;

import java.math.BigDecimal;
import java.sql.Date;
import java.sql.Time;
import java.sql.Timestamp;

/**
 * @author AE86
 * @version 1.0.0
 * @date 2022/4/22 22:39
 */
public interface ColumnValue {

    boolean isNull();

    String asString();

    byte[] asByteArray();

    Short asShort();

    Integer asInteger();

    Long asLong();

    Float asFloat();

    Double asDouble();

    Boolean asBoolean();

    BigDecimal asBigDecimal();

    Date asDate();

    Timestamp asTimestamp();

    Time asTime();
}
View Code
复制代码
复制代码
package com.virtuous.demo.laboratory.algorithm.xian;

public abstract class AbstractColumnValue<T> implements ColumnValue {

    protected Object value;

    protected T getValue() {
        return (T) value;
    }

    public void setValue(T value) {
        this.value = value;
    }

    @Override
    public boolean isNull() {
        return value == null;
    }

}
View Code
复制代码
复制代码
package com.virtuous.demo.laboratory.algorithm.xian;

import org.postgresql.PGStatement;
import org.postgresql.geometric.*;
import org.postgresql.util.PGInterval;
import org.postgresql.util.PGmoney;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.math.BigDecimal;
import java.sql.Date;
import java.sql.SQLException;
import java.sql.Time;
import java.sql.Timestamp;
import java.time.*;
import java.util.concurrent.TimeUnit;

public final class PgColumnValue extends AbstractColumnValue<String> {

    private final Logger logger = LoggerFactory.getLogger(getClass());

    public PgColumnValue(String value) {
        setValue(value);
    }

    public PgColumnValue() {
    }

    @Override
    public String asString() {
        return getValue();
    }

    @Override
    public byte[] asByteArray() {
        return StringUtil.hexStringToByteArray(getValue().substring(2));
    }

    @Override
    public Short asShort() {
        return Short.valueOf(getValue());
    }

    @Override
    public Integer asInteger() {
        return Integer.valueOf(getValue());
    }

    @Override
    public Long asLong() {
        return Long.valueOf(getValue());
    }

    @Override
    public Float asFloat() {
        return Float.valueOf(getValue());
    }

    @Override
    public Double asDouble() {
        return Double.valueOf(getValue());
    }

    @Override
    public Boolean asBoolean() {
        return "t".equalsIgnoreCase(getValue());
    }

    @Override
    public BigDecimal asBigDecimal() {
        return new BigDecimal(getValue());
    }

    @Override
    public Date asDate() {
        return DateFormatUtil.stringToDate(asString());
    }

    @Override
    public Timestamp asTimestamp() {
        if ("infinity".equals(asString())) {
            return Timestamp.from(toInstantFromMicros(PGStatement.DATE_POSITIVE_INFINITY));
        } else if ("-infinity".equals(asString())) {
            return Timestamp.from(toInstantFromMicros(PGStatement.DATE_NEGATIVE_INFINITY));
        } else if ("null".equals(asString()) || StringUtil.isBlank(asString())) {
            return null;
        }
        return DateFormatUtil.timeWithoutTimeZoneToTimestamp(asString());
    }

    @Override
    public Time asTime() {
        return Time.valueOf(getValue());
    }

    public LocalTime asLocalTime() {
        return DateFormatUtil.stringToLocalTime(asString());
    }

    public OffsetTime asOffsetTimeUtc() {
        return DateFormatUtil.timeWithTimeZone(asString());
    }

    public OffsetDateTime asOffsetDateTimeAtUtc() {
        if ("infinity".equals(asString())) {
            return OffsetDateTime.ofInstant(toInstantFromMillis(PGStatement.DATE_POSITIVE_INFINITY), ZoneOffset.UTC);
        } else if ("-infinity".equals(asString())) {
            return OffsetDateTime.ofInstant(toInstantFromMillis(PGStatement.DATE_NEGATIVE_INFINITY), ZoneOffset.UTC);
        }
        return DateFormatUtil.timestampWithTimeZoneToOffsetDateTime(asString());
    }

    public PGbox asBox() {
        try {
            return new PGbox(asString());
        } catch (final SQLException e) {
            logger.error("Failed to parse point {}, {}", asString(), e);
            throw new ListenerException(e);
        }
    }

    public PGcircle asCircle() {
        try {
            return new PGcircle(asString());
        } catch (final SQLException e) {
            logger.error("Failed to parse circle {}, {}", asString(), e);
            throw new ListenerException(e);
        }
    }

    public Object asInterval() {
        try {
            return new PGInterval(asString());
        } catch (final SQLException e) {
            logger.error("Failed to parse point {}, {}", asString(), e);
            throw new ListenerException(e);
        }
    }

    public PGline asLine() {
        try {
            return new PGline(asString());
        } catch (final SQLException e) {
            logger.error("Failed to parse point {}, {}", asString(), e);
            throw new ListenerException(e);
        }
    }

    public PGlseg asLseg() {
        try {
            return new PGlseg(asString());
        } catch (final SQLException e) {
            logger.error("Failed to parse point {}, {}", asString(), e);
            throw new ListenerException(e);
        }
    }

    public PGmoney asMoney() {
        try {
            final String value = asString();
            if (value != null && value.startsWith("-")) {
                final String negativeMoney = "(" + value.substring(1) + ")";
                return new PGmoney(negativeMoney);
            }
            return new PGmoney(asString());
        } catch (final SQLException e) {
            logger.error("Failed to parse money {}, {}", asString(), e);
            throw new ListenerException(e);
        }
    }

    public PGpath asPath() {
        try {
            return new PGpath(asString());
        } catch (final SQLException e) {
            logger.error("Failed to parse point {}, {}", asString(), e);
            throw new ListenerException(e);
        }
    }

    public PGpoint asPoint() {
        try {
            return new PGpoint(asString());
        } catch (final SQLException e) {
            logger.error("Failed to parse point {}, {}", asString(), e);
            throw new ListenerException(e);
        }
    }

    public PGpolygon asPolygon() {
        try {
            return new PGpolygon(asString());
        } catch (final SQLException e) {
            logger.error("Failed to parse point {}, {}", asString(), e);
            throw new ListenerException(e);
        }
    }

    private Instant toInstantFromMicros(long microsSinceEpoch) {
        return Instant.ofEpochSecond(
                TimeUnit.MICROSECONDS.toSeconds(microsSinceEpoch),
                TimeUnit.MICROSECONDS.toNanos(microsSinceEpoch % TimeUnit.SECONDS.toMicros(1)));
    }

    private Instant toInstantFromMillis(long millisecondSinceEpoch) {
        return Instant.ofEpochSecond(
                TimeUnit.MILLISECONDS.toSeconds(millisecondSinceEpoch),
                TimeUnit.MILLISECONDS.toNanos(millisecondSinceEpoch % TimeUnit.SECONDS.toMillis(1)));
    }

}
View Code
复制代码

（3）ChangedEvent 相关类

复制代码
/**
 * DBSyncer Copyright 2019-2024 All Rights Reserved.
 */
package com.virtuous.demo.laboratory.algorithm.xian;

import java.util.Map;

/**
 * 变更事件
 *
 * @version 1.0.0
 * @Author AE86
 * @Date 2023-08-20 20:00
 */
public interface ChangedEvent {

    /**
     * 获取变更表名称
     *
     * @return
     */
    String getSourceTableName();

    /**
     * 获取变更事件
     *
     * @return
     */
    String getEvent();

    /**
     * 获取变更行数据
     *
     * @return
     */
    Map<String, Object> getChangedRow();

    /**
     * 获取增量偏移量
     *
     * @return
     */
    ChangedOffset getChangedOffset();

}
View Code
复制代码
复制代码
/**
 * DBSyncer Copyright 2019-2024 All Rights Reserved.
 */
package com.virtuous.demo.laboratory.algorithm.xian;

import java.util.Map;

/**
 * 通用变更事件
 *
 * @version 1.0.0
 * @Author AE86
 * @Date 2023-08-20 20:00
 */
public class CommonChangedEvent implements ChangedEvent {

    /**
     * 变更表名称
     */
    private String sourceTableName;
    /**
     * 变更事件
     */
    private String event;
    /**
     * 变更行数据
     */
    private Map<String, Object> changedRow;
    /**
     * 增量偏移量
     */
    private ChangedOffset changedOffset = new ChangedOffset();

    @Override
    public String getSourceTableName() {
        return sourceTableName;
    }

    public void setSourceTableName(String sourceTableName) {
        this.sourceTableName = sourceTableName;
    }

    @Override
    public String getEvent() {
        return event;
    }

    public void setEvent(String event) {
        this.event = event;
    }

    @Override
    public Map<String, Object> getChangedRow() {
        return changedRow;
    }

    public void setChangedRow(Map<String, Object> changedRow) {
        this.changedRow = changedRow;
    }

    @Override
    public ChangedOffset getChangedOffset() {
        return changedOffset;
    }

    public void setNextFileName(String nextFileName) {
        changedOffset.setNextFileName(nextFileName);
    }

    public void setPosition(Object position) {
        changedOffset.setPosition(position);
    }
}
View Code
复制代码
复制代码
/**
 * DBSyncer Copyright 2019-2024 All Rights Reserved.
 */
package com.virtuous.demo.laboratory.algorithm.xian;

import java.util.List;

/**
 * 监听行变更事件
 *
 * @version 1.0.0
 * @Author AE86
 * @Date 2020-06-15 20:00
 */
public final class RowChangedEvent extends CommonChangedEvent {
    private List<Object> dataList;

    public RowChangedEvent(String sourceTableName, String event, List<Object> data) {
        this(sourceTableName, event, data, null, null);
    }

    public RowChangedEvent(String sourceTableName, String event, List<Object> data, String nextFileName, Object position) {
        setSourceTableName(sourceTableName);
        setEvent(event);
        setNextFileName(nextFileName);
        setPosition(position);
        this.dataList = data;
    }

    public List<Object> getDataList() {
        return dataList;
    }

}
View Code
复制代码
复制代码
package com.virtuous.demo.laboratory.algorithm.xian;

/**
 * 增量偏移量
 *
 * @version 1.0.0
 * @Author AE86
 * @Date 2023-08-23 20:00
 */
public final class ChangedOffset {
    /**
     * 驱动ID
     */
    private String metaId;
    /**
     * 增量文件名称
     */
    private String nextFileName;
    /**
     * 增量偏移量
     */
    private Object position;
    /**
     * 是否触发刷新增量点事件
     */
    private boolean refreshOffset;

    public String getMetaId() {
        return metaId;
    }

    public void setMetaId(String metaId) {
        this.metaId = metaId;
    }

    public String getNextFileName() {
        return nextFileName;
    }

    public void setNextFileName(String nextFileName) {
        this.nextFileName = nextFileName;
    }

    public Object getPosition() {
        return position;
    }

    public void setPosition(Object position) {
        this.position = position;
    }

    public boolean isRefreshOffset() {
        return refreshOffset;
    }

    public void setRefreshOffset(boolean refreshOffset) {
        this.refreshOffset = refreshOffset;
    }
}
View Code
复制代码

（4）其他相关类

复制代码
package com.virtuous.demo.laboratory.algorithm.xian;

/**
 * @author AE86
 * @version 1.0.0
 * @date 2019/9/19 23:56
 */
public class ConnectorConstant {

    /**
     * 新增
     */
    public static final String OPERTION_INSERT = "INSERT";

    /**
     * 更新
     */
    public static final String OPERTION_UPDATE = "UPDATE";

    /**
     * 删除
     */
    public static final String OPERTION_DELETE = "DELETE";

    /**
     * 表结构更改
     */
    public static final String OPERTION_ALTER = "ALTER";

    /**
     * 查询
     */
    public static final String OPERTION_QUERY = "QUERY";

    /**
     * 查询游标
     */
    public static final String OPERTION_QUERY_CURSOR = "QUERY_CURSOR";

    /**
     * 查询过滤条件
     */
    public static final String OPERTION_QUERY_FILTER = "QUERY_FILTER";

    /**
     * 查询总数
     */
    public static final String OPERTION_QUERY_COUNT = "QUERY_COUNT";

    /**
     * 查询数据行是否存在
     */
    public static final String OPERTION_QUERY_EXIST = "QUERY_COUNT_EXIST";

}
View Code
复制代码
复制代码
package com.virtuous.demo.laboratory.algorithm.xian;

import java.sql.Date;
import java.sql.Timestamp;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.time.*;
import java.time.format.*;
import java.time.temporal.ChronoField;
import java.time.temporal.TemporalAccessor;

public abstract class DateFormatUtil {

    /**
     * yyyy-MM-dd HH:mm:ss
     */
    public static final DateTimeFormatter CHINESE_STANDARD_TIME_FORMATTER = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
    /**
     * yyyy-MM-dd'T'HH:mm:ss.SSSz
     */
    public static final DateFormat GMT_FORMATTER = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss.SSSz");
    /**
     * yyyy-MM-dd
     */
    public static final DateTimeFormatter DATE_FORMATTER = DateTimeFormatter.ofPattern("yyyy-MM-dd");
    /**
     * HH:mm:ss
     */
    public static final DateTimeFormatter TIME_FORMATTER = DateTimeFormatter.ofPattern("HH:mm:ss");
    /**
     * 默认时区
     */
    private static final ZoneId zoneId = ZoneId.systemDefault();

    private static final DateTimeFormatter TIME_TZ_FORMAT = new DateTimeFormatterBuilder()
            .append(DateTimeFormatter.ISO_LOCAL_TIME)
            .appendOffset("+HH:mm", "")
            .toFormatter();

    private static final DateTimeFormatter NON_ISO_LOCAL_DATE = new DateTimeFormatterBuilder()
            .appendValue(ChronoField.YEAR_OF_ERA, 4, 10, SignStyle.NEVER)
            .appendLiteral('-')
            .appendValue(ChronoField.MONTH_OF_YEAR, 2)
            .appendLiteral('-')
            .appendValue(ChronoField.DAY_OF_MONTH, 2)
            .toFormatter();

    private static final DateTimeFormatter TS_FORMAT = new DateTimeFormatterBuilder()
            .append(NON_ISO_LOCAL_DATE)
            .appendLiteral(' ')
            .append(DateTimeFormatter.ISO_LOCAL_TIME)
            .optionalStart()
            .appendLiteral(" ")
            .appendText(ChronoField.ERA, TextStyle.SHORT)
            .optionalEnd()
            .toFormatter();
    private static final DateTimeFormatter TS_TZ_FORMAT = new DateTimeFormatterBuilder()
            .append(NON_ISO_LOCAL_DATE)
            .appendLiteral(' ')
            .append(DateTimeFormatter.ISO_LOCAL_TIME)
            .appendOffset("+HH:mm", "")
            .optionalStart()
            .appendLiteral(" ")
            .appendText(ChronoField.ERA, TextStyle.SHORT)
            .optionalEnd()
            .toFormatter();
    private static final DateTimeFormatter TS_TZ_WITH_SECONDS_FORMAT = new DateTimeFormatterBuilder()
            .append(NON_ISO_LOCAL_DATE)
            .appendLiteral(' ')
            .append(DateTimeFormatter.ISO_LOCAL_TIME)
            .appendOffset("+HH:MM:SS", "")
            .optionalStart()
            .appendLiteral(" ")
            .appendText(ChronoField.ERA, TextStyle.SHORT)
            .optionalEnd()
            .toFormatter();

    public static String getCurrentTime() {
        return LocalDateTime.now().format(TIME_FORMATTER);
    }

    public static String dateToString(Date date) {
        return date.toLocalDate().format(DATE_FORMATTER);
    }

    public static String dateToString(java.util.Date date) {
        return date.toInstant().atZone(zoneId).toLocalDateTime().format(CHINESE_STANDARD_TIME_FORMATTER);
    }

    public static Date stringToDate(String s) {
        return Date.valueOf(LocalDate.parse(s, DATE_FORMATTER));
    }

    public static String timestampToString(Timestamp timestamp) {
        return timestamp.toLocalDateTime().format(CHINESE_STANDARD_TIME_FORMATTER);
    }

    public static LocalTime stringToLocalTime(String s) {
        return LocalTime.parse(s, CHINESE_STANDARD_TIME_FORMATTER);
    }

    public static Timestamp stringToTimestamp(String s) {
        try {
            // 2020-7-12 00:00:00
            if (s.length() < 19) {
                return Timestamp.valueOf(LocalDateTime.from(CHINESE_STANDARD_TIME_FORMATTER.parse(format(s))));
            }

            // 2020-07-12 00:00:00
            if (s.length() == 19) {
                return Timestamp.valueOf(LocalDateTime.from(CHINESE_STANDARD_TIME_FORMATTER.parse(s)));
            }

            // 2020-07-12 00:00:00.0
            if (s.length() == 21) {
                s = s.substring(0, s.lastIndexOf("."));
                return Timestamp.valueOf(LocalDateTime.from(CHINESE_STANDARD_TIME_FORMATTER.parse(s)));
            }

            // 2022-07-21T05:35:34.000+0800
            if (s.length() == 28) {
                return stringToTimestamp(s, GMT_FORMATTER);
            }

            // 2022-07-21T05:35:34.000+08:00
            if (s.length() == 29) {
                s = s.replaceAll(":[^:]*$", "00");
                return stringToTimestamp(s, GMT_FORMATTER);
            }

            throw new CommonException(String.format("Can not parse val[%s] to Timestamp", s));
        } catch (ParseException e) {
            throw new CommonException(e);
        }
    }

    public static Timestamp stringToTimestamp(String s, DateFormat formatter) throws ParseException {
        return new Timestamp(formatter.parse(s).getTime());
    }

    public static Timestamp timeWithoutTimeZoneToTimestamp(String s) {
        return Timestamp.valueOf(LocalDateTime.from(DateFormatUtil.TS_FORMAT.parse(s)).atZone(ZoneOffset.UTC).toLocalDateTime());
    }

    public static OffsetTime timeWithTimeZone(String s) {
        return OffsetTime.parse(s, TIME_TZ_FORMAT).withOffsetSameInstant(ZoneOffset.UTC);
    }

    public static OffsetDateTime timestampWithTimeZoneToOffsetDateTime(String s) {
        TemporalAccessor parsedTimestamp;
        try {
            parsedTimestamp = TS_TZ_FORMAT.parse(s);
        } catch (DateTimeParseException e) {
            parsedTimestamp = TS_TZ_WITH_SECONDS_FORMAT.parse(s);
        }
        return OffsetDateTime.from(parsedTimestamp).withOffsetSameInstant(ZoneOffset.UTC);
    }

    private static String format(String s) {
        StringBuilder buf = new StringBuilder();
        Lexer lexer = new Lexer(s);
        char comma = '-';
        // 年
        nextToken(lexer, buf, comma);
        // 月
        nextToken(lexer, buf, comma);
        // 日
        comma = ' ';
        nextToken(lexer, buf, comma);
        // 时
        comma = ':';
        nextToken(lexer, buf, comma);
        // 分
        nextToken(lexer, buf, comma);
        // 秒
        nextToken(lexer, buf, comma, false);
        return buf.toString();
    }

    private static void nextToken(Lexer lexer, StringBuilder buf, char comma) {
        nextToken(lexer, buf, comma, true);
    }

    private static void nextToken(Lexer lexer, StringBuilder buf, char comma, boolean appendComma) {
        buf.append(fillZero(lexer.nextToken(comma)));
        if (appendComma) {
            buf.append(comma);
        }
    }

    private static String fillZero(String s) {
        if (s.length() < 2) {
            return String.format("%02d", Integer.parseInt(s));
        }
        return s;
    }

}
View Code
复制代码
复制代码
package com.virtuous.demo.laboratory.algorithm.xian;

/**
 * @author AE86
 * @version 1.0.0
 * @date 2022/4/24 18:22
 */
public final class Lexer {
    private final char[] array;
    private final int length;
    private int pos = 0;
    private String token;

    public Lexer(String input) {
        this.array = input.toCharArray();
        this.length = this.array.length;
    }

    public String token() {
        return token;
    }

    public String nextToken(char comma) {
        if (pos < length) {
            StringBuilder out = new StringBuilder(16);
            while (pos < length && array[pos] != comma) {
                out.append(array[pos]);
                pos++;
            }
            pos++;
            return token = out.toString();
        }
        return token = null;
    }

    public String nextTokenToQuote() {
        if (pos < length) {
            int commaCount = 1;
            StringBuilder out = new StringBuilder(16);
            while (!((pos == length - 1 || (array[pos + 1] == ' ' && commaCount % 2 == 1)) && array[pos] == '\'')) {
                if (array[pos] == '\'') {
                    commaCount++;
                }
                out.append(array[pos]);
                pos++;
            }
            pos++;
            return token = out.toString();
        }
        return token = null;
    }

    public void skip(int skip) {
        this.pos += skip;
    }

    public char current() {
        return array[pos];
    }

    public boolean hasNext() {
        return pos < length;
    }
}
View Code
复制代码
复制代码
package com.virtuous.demo.laboratory.algorithm.xian;

/**
 * @author AE86
 * @version 1.0.0
 * @date 2019/9/28 22:39
 */
public class CommonException extends RuntimeException {

    private static final long serialVersionUID = 1L;

    public CommonException(String message) {
        super(message);
    }

    public CommonException(String message, Throwable cause) {
        super(message, cause);
    }

    public CommonException(Throwable cause) {
        super(cause);
    }

    protected CommonException(String message, Throwable cause, boolean enableSuppression, boolean writableStackTrace) {
        super(message, cause, enableSuppression, writableStackTrace);
    }
}
View Code
复制代码
复制代码
package com.virtuous.demo.laboratory.algorithm.xian;

/**
 * @author AE86
 * @version 1.0.0
 * @date 2019/9/28 22:39
 */
public class ListenerException extends RuntimeException {

    private static final long serialVersionUID = 1L;

    public ListenerException(String message) {
        super(message);
    }

    public ListenerException(String message, Throwable cause) {
        super(message, cause);
    }

    public ListenerException(Throwable cause) {
        super(cause);
    }

    protected ListenerException(String message, Throwable cause, boolean enableSuppression, boolean writableStackTrace) {
        super(message, cause, enableSuppression, writableStackTrace);
    }
}
View Code
复制代码
复制代码
package com.virtuous.demo.laboratory.algorithm.xian;

import lombok.Data;
import lombok.extern.log4j.Log4j2;
import org.postgresql.replication.LogSequenceNumber;
import org.postgresql.replication.PGReplicationStream;
import org.postgresql.util.PGmoney;

import java.nio.ByteBuffer;
import java.sql.Connection;
import java.time.Instant;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;


/**
 * 接收通知消息处理类
 */
@Log4j2
@Data
public class MessageHandle {

    private PGReplicationStream stream;
    private Connection connection;
    private Map<String, String> snapshot;
    private String LSN_POSITION;

    private PgColumnValue value = new PgColumnValue();

    public MessageHandle(PGReplicationStream stream, Connection connection, Map<String, String> snapshot, String LSN_POSITION) {
        this.stream = stream;
        this.connection = connection;
        this.snapshot = snapshot;
        this.LSN_POSITION = LSN_POSITION;
    }


    /*private void recover() {
        try {
            long s = Instant.now().toEpochMilli();
            DatabaseUtil.close(stream);
            DatabaseUtil.close(connection);
            stream = null;
            connection = null;

            while (connected) {
                try {
                    connect();
                    break;
                } catch (Exception e) {
                    log.error("Recover streaming occurred error");
                    DatabaseUtil.close(stream);
                    DatabaseUtil.close(connection);
                    sleepInMills(3000L);
                }
            }
            long e = Instant.now().toEpochMilli();
            log.info("Recover logical replication success, slot:{}, plugin:{}, cost:{}seconds", getSlotName(), ("test_decoding"), (e - s) / 1000);
        } finally {
            //connectLock.unlock();
        }
    }*/

    protected  void sendChangedEvent(RowChangedEvent event){
        changedEvent(event);
    }

    public void changedEvent(RowChangedEvent event) {
        if (null != event) {
            switch (event.getEvent()) {
                case ConnectorConstant.OPERTION_UPDATE:
                    // 是否支持监听修改事件
                    //processEvent(!listenerConfig.isBanUpdate(), event);
                    break;
                case ConnectorConstant.OPERTION_INSERT:
                    // 是否支持监听新增事件
                    //processEvent(!listenerConfig.isBanInsert(), event);
                    break;
                case ConnectorConstant.OPERTION_DELETE:
                    // 是否支持监听删除事件
                    //processEvent(!listenerConfig.isBanDelete(), event);
                    break;
                default:
                    break;
            }
        }
    }

    public String processMessage(ByteBuffer buffer) {
        if (!buffer.hasArray()) {
            throw new IllegalStateException("Invalid buffer received from PG server during streaming replication");
        }
        MessageTypeEnum type = MessageTypeEnum.getType((char) buffer.get());
        if (MessageTypeEnum.TABLE == type) {
            int offset = buffer.arrayOffset();
            byte[] source = buffer.array();
            return new String(source, offset, (source.length - offset));
        }
        return null;
    }

    /*public static RowChangedEvent processMessage(ByteBuffer buffer) {
        if (!buffer.hasArray()) {
            throw new IllegalStateException("Invalid buffer received from PG server during streaming replication");
        }
        MessageTypeEnum type = MessageTypeEnum.getType((char) buffer.get());
        if (MessageTypeEnum.TABLE == type) {
            int offset = buffer.arrayOffset();
            byte[] source = buffer.array();
            return parseMessage(new String(source, offset, (source.length - offset)));
        }
        return null;
    }*/

    private RowChangedEvent parseMessage(String message) {
        Lexer lexer = new Lexer(message);

        // table
        lexer.nextToken(' ');
        // schemaName
        lexer.nextToken('.');
        // tableName
        lexer.skip(1);
        String table = lexer.nextToken('"');
        lexer.skip(2);
        // eventType
        String eventType = lexer.nextToken(':');
        lexer.skip(1);

        List<Object> data = new ArrayList<>();
        while (lexer.hasNext()) {
            String name = parseName(lexer);
            if ("(no-tuple-data)".equals(name)) {
                // 删除时,无主键,不能同步
                return null;
            }
            String type = parseType(lexer);
            lexer.skip(1);
            String value = parseValue(lexer);
            data.add(resolveValue(type, value));
        }

        RowChangedEvent event = null;
        switch (eventType) {
            case ConnectorConstant.OPERTION_UPDATE:
            case ConnectorConstant.OPERTION_INSERT:
            case ConnectorConstant.OPERTION_DELETE:
                event = new RowChangedEvent(table, eventType, data);
                break;

            default:
                log.info("Type {} not implemented", eventType);
        }
        return event;
    }

    protected Object resolveValue(String typeName, String columnValue) {
        value.setValue(columnValue);

        if (value.isNull()) {
            // nulls are null
            return null;
        }

        switch (typeName) {
            // include all types from https://www.postgresql.org/docs/current/static/datatype.html#DATATYPE-TABLE
            case "boolean":
            case "bool":
                return value.asBoolean();

            case "integer":
            case "int":
            case "int4":
            case "smallint":
            case "int2":
            case "smallserial":
            case "serial":
            case "serial2":
            case "serial4":
                return value.asInteger();

            case "bigint":
            case "bigserial":
            case "int8":
            case "oid":
                return value.asLong();

            case "real":
            case "float4":
                return value.asFloat();

            case "double precision":
            case "float8":
                return value.asDouble();

            case "numeric":
            case "decimal":
                return value.asBigDecimal();

            case "character":
            case "char":
            case "character varying":
            case "varchar":
            case "bpchar":
            case "text":
            case "hstore":
                return value.asString();

            case "date":
                return value.asDate();

            case "timestamp with time zone":
            case "timestamptz":
                return value.asOffsetDateTimeAtUtc();

            case "timestamp":
            case "timestamp without time zone":
                return value.asTimestamp();

            case "time":
                return value.asTime();

            case "time without time zone":
                return value.asLocalTime();

            case "time with time zone":
            case "timetz":
                return value.asOffsetTimeUtc();

            case "bytea":
                return value.asByteArray();

            // these are all PG-specific types and we use the JDBC representations
            // note that, with the exception of point, no converters for these types are implemented yet,
            // i.e. those values won't actually be propagated to the outbound message until that's the case
            case "box":
                return value.asBox();
            case "circle":
                return value.asCircle();
            case "interval":
                return value.asInterval();
            case "line":
                return value.asLine();
            case "lseg":
                return value.asLseg();
            case "money":
                final Object v = value.asMoney();
                return (v instanceof PGmoney) ? ((PGmoney) v).val : v;
            case "path":
                return value.asPath();
            case "point":
                return value.asPoint();
            case "polygon":
                return value.asPolygon();

            // PostGIS types are HexEWKB strings
            // ValueConverter turns them into the correct types
            case "geometry":
            case "geography":
            case "citext":
            case "bit":
            case "bit varying":
            case "varbit":
            case "json":
            case "jsonb":
            case "xml":
            case "uuid":
            case "tsrange":
            case "tstzrange":
            case "daterange":
            case "inet":
            case "cidr":
            case "macaddr":
            case "macaddr8":
            case "int4range":
            case "numrange":
            case "int8range":
                return value.asString();

            // catch-all for other known/builtin PG types
            case "pg_lsn":
            case "tsquery":
            case "tsvector":
            case "txid_snapshot":
                // catch-all for unknown (extension module/custom) types
            default:
                return null;
        }

    }

    private String parseName(Lexer lexer) {
        if (lexer.current() == ' ') {
            lexer.skip(1);
        }
        lexer.nextToken('[');
        return lexer.token();
    }

    private String parseType(Lexer lexer) {
        lexer.nextToken(']');
        return lexer.token();
    }

    private String parseValue(Lexer lexer) {
        if (lexer.current() == '\'') {
            lexer.skip(1);
            lexer.nextTokenToQuote();
            return lexer.token();
        }
        lexer.nextToken(' ');
        return lexer.token();
    }

    public void flushLsn(LogSequenceNumber lsn) {
        if (null != lsn && lsn.asLong() > 0) {
            snapshot.put(LSN_POSITION, lsn.asString());
        }
    }

    public boolean skipMessage(ByteBuffer buffer, LogSequenceNumber startLsn, LogSequenceNumber lastReceiveLsn) {
        if (null == lastReceiveLsn || lastReceiveLsn.asLong() == 0 || startLsn.equals(lastReceiveLsn)) {
            return true;
        }

        int position = buffer.position();
        try {
            MessageTypeEnum type = MessageTypeEnum.getType((char) buffer.get());
            switch (type) {
                case BEGIN:
                case COMMIT:
                case RELATION:
                case TRUNCATE:
                case TYPE:
                case ORIGIN:
                case NONE:
                    return true;
                default:
                    // TABLE|INSERT|UPDATE|DELETE
                    return false;
            }
        } finally {
            buffer.position(position);
        }
    }

}
View Code
复制代码
复制代码
package com.virtuous.demo.laboratory.algorithm.xian;

public enum MessageTypeEnum {
    BEGIN,
    COMMIT,
    TABLE,
    INSERT,
    UPDATE,
    DELETE,
    RELATION,
    TRUNCATE,
    TYPE,
    ORIGIN,
    NONE;

    public static MessageTypeEnum getType(char type) {
        switch (type) {
            case 'B':
                return BEGIN;
            case 'C':
                return COMMIT;
            case 't':
                return TABLE;
            case 'I':
                return INSERT;
            case 'U':
                return UPDATE;
            case 'D':
                return DELETE;
            case 'R':
                return RELATION;
            case 'Y':
                return TYPE;
            case 'O':
                return ORIGIN;
            case 'T':
                return TRUNCATE;
            default:
                return NONE;
        }
    }
}
View Code
复制代码
复制代码
package com.virtuous.demo.laboratory.algorithm.xian;

import org.apache.commons.lang3.StringUtils;

public abstract class StringUtil {

    public static final String EMPTY = "";

    public static final String SYMBOL = "-";

    public static final String COLON = ":";

    public static final String SPACE = " ";

    public static final String SINGLE_QUOTATION = "'";

    public static final String FORWARD_SLASH = "/";

    public static boolean equals(CharSequence cs1, CharSequence cs2) {
        return StringUtils.equals(cs1, cs2);
    }

    public static boolean equalsIgnoreCase(CharSequence cs1, CharSequence cs2) {
        return StringUtils.equalsIgnoreCase(cs1, cs2);
    }

    public static boolean isBlank(CharSequence cs) {
        return StringUtils.isBlank(cs);
    }

    public static boolean isNotBlank(CharSequence cs) {
        return StringUtils.isNotBlank(cs);
    }

    public static String[] split(String str, String separatorChars) {
        return StringUtils.split(str, separatorChars);
    }

    public static boolean contains(CharSequence seq, CharSequence searchSeq) {
        return StringUtils.contains(seq, searchSeq);
    }

    public static boolean endsWith(final CharSequence str, final CharSequence suffix) {
        return StringUtils.endsWith(str, suffix);
    }

    public static String trim(String text) {
        return StringUtils.trim(text);
    }

    public static String replace(String text, String searchString, String replacement) {
        return StringUtils.replace(text, searchString, replacement);
    }

    public static String join(Iterable<?> iterable, String separator) {
        return StringUtils.join(iterable, separator);
    }

    public static String join(Object[] array, String separator) {
        return StringUtils.join(array, separator);
    }

    public static String substring(String str, int start) {
        return StringUtils.substring(str, start);
    }

    public static String substring(String str, int start, int end) {
        return StringUtils.substring(str, start, end);
    }

    public static int indexOf(CharSequence seq, CharSequence searchChar) {
        return StringUtils.indexOf(seq, searchChar);
    }

    public static int lastIndexOf(CharSequence seq, CharSequence searchChar) {
        return StringUtils.lastIndexOf(seq, searchChar);
    }

    public static boolean startsWith(CharSequence str, CharSequence prefix) {
        return StringUtils.startsWith(str, prefix);
    }

    public static String toString(Object obj) {
        return obj == null ? "" : String.valueOf(obj);
    }

    /**
     * 首字母转小写
     *
     * @param s
     * @return
     */
    public static String toLowerCaseFirstOne(String s) {
        if (StringUtils.isBlank(s) || Character.isLowerCase(s.charAt(0))) {
            return s;
        }
        return new StringBuilder().append(Character.toLowerCase(s.charAt(0))).append(s.substring(1)).toString();
    }

    /**
     * Restores a byte array that is encoded as a hex string.
     */
    public static byte[] hexStringToByteArray(String hexString) {
        if (hexString == null) {
            return null;
        }

        int length = hexString.length();
        byte[] bytes = new byte[length / 2];
        for (int i = 0; i < length; i += 2) {
            bytes[i / 2] = (byte) ((Character.digit(hexString.charAt(i), 16) << 4)
                    + Character.digit(hexString.charAt(i + 1), 16));
        }
        return bytes;
    }

}
View Code
复制代码
复制代码
package com.virtuous.demo.laboratory.algorithm.xian;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.extern.log4j.Log4j2;
import org.postgresql.replication.LogSequenceNumber;
import org.postgresql.replication.PGReplicationStream;

import java.nio.ByteBuffer;
import java.util.concurrent.TimeUnit;

@Log4j2
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Worker implements Runnable{

    private PGReplicationStream stream;

    private LogSequenceNumber startLsn;

    private MessageHandle messageHandle;

    @Override
    public void run() {
        while (true) {
            try {
                // non blocking receive message
                ByteBuffer msg = stream.readPending();

                if (msg == null) {
                    sleepInMills(10L);
                    continue;
                }

                LogSequenceNumber lsn = stream.getLastReceiveLSN();
                if (messageHandle.skipMessage(msg, startLsn, lsn)) {
                    continue;
                }

                messageHandle.flushLsn(lsn);
                // process decoder
                //sendChangedEvent(processMessage(msg));
                System.out.println(messageHandle.processMessage(msg));
                // feedback
                stream.setAppliedLSN(lsn);
                stream.setFlushedLSN(lsn);
                stream.forceUpdateStatus();
            } catch (Exception e) {
                log.error(e.getMessage());
                //recover();
            }
        }
    }

    private void sleepInMills(long timeout) {
        try {
            TimeUnit.MILLISECONDS.sleep(timeout);
        } catch (InterruptedException e) {
            log.info(e.getMessage());
        }
    }

}
View Code
复制代码

4  小结效果图

挺神奇，有点像我之前弄的订阅复制的功能，比如我们的数据中心就是订阅各个业务数据库表，然后同步到数据中心做一些关联查询报表的东西。这里只是简单拿代码试试哈，还有很多细节没扣哈。

感谢：https://blog.csdn.net/weixin_37598243/article/details/128056610

感谢：https://gitee.com/ghi/dbsyncer

posted @   酷酷-  阅读(247)  评论(0)  编辑  收藏  举报
相关博文：
· 【DBSyncer】用于数据同步的工具尝试
· 【PostageSQL】关于表的元数据信息比如行数、表空间大小、表索引访问次数、最近分析时间等信息收集
· PostgreSQL的WAL日志详解
· 【PG体系架构】Postgresql 体系架构学习总结.md 转载 https://www.modb.pro/db/100597?utm_source=index_ai
· PostgreSQL从小白到高手教程 - 第44讲：pg流复制部署
阅读排行：
· 分享4款.NET开源、免费、实用的商城系统
· 全程不用写代码，我用AI程序员写了一个飞机大战
· Obsidian + DeepSeek：免费 AI 助力你的知识管理，让你的笔记飞起来！
· MongoDB 8.0这个新功能碉堡了，比商业数据库还牛
· 白话解读 Dapr 1.15：你的「微服务管家」又秀新绝活了
点击右上角即可分享
微信分享提示