Hologres:Set up binlog replication for Hologres

Note the following limitations when you subscribe to Hologres binlogs:

• Subscribing to Hologres binlogs is supported only in Hologres V0.9 and later. If your instance version is earlier than V0.9, join the online support DingTalk group. For more information, see How do I get more online support?.

• In Hologres V0.9 and V0.10, you cannot enable binlogs for existing tables by modifying table properties. Instead, you must recreate the tables. In Hologres V1.1 and later, you can enable binlogs as needed.

• In versions earlier than Hologres V1.3.14 and V1.1.82, only a superuser can consume binlogs. If you attempt to use an account with fewer permissions to consume binlogs, a permission error occurs: permission denied for table hg_replication_slot_properties. In Hologres V1.3.14, V1.1.82, and later, an account only needs permission to query the table if you use Flink to consume binlogs. To consume Hologres binlogs using Java Database Connectivity (JDBC), the account must have the Replication Role.

• Hologres supports the binlog feature at the table level for both row-oriented and column-oriented tables. The following table describes the support for subscribing to Hologres binlogs.

  Flink classification	Hologres row-oriented table binlog	Hologres column-oriented table binlog	Hologres row-column hybrid table binlog (supported since Hologres V1.1)
  Realtime Compute (Blink)	Supported	Supported	Supported
  Fully managed Flink	Supported	Supported	Supported
  Open source Flink	Not supported	Not supported	Not supported
  JDBC	Supported since Hologres V1.1	Supported since Hologres V1.1	Supported since Hologres V1.1

• When you use Blink to consume Hologres binlogs, the TIMESTAMP data type is not supported. You must use the TIMESTAMPTZ data type when you create tables in Hologres. Other data types, such as SMALLINT, are also not supported.

• You cannot consume binlogs from a parent table of a partitioned table. You must use a child table or a standard (non-partitioned) table. In Hologres V1.3.24 and later, you can modify the binlog TTL for a child table of a partitioned table as needed. If you do not explicitly specify a binlog TTL for a child table, it inherits the binlog TTL of the parent table. Note that the binlog TTL is not a precise value. The system does not guarantee that a binlog is deleted immediately after it expires. The binlog is deleted at some point after expiration.

• For scenarios that involve frequent updates, enabling binlogs for a column-oriented table theoretically has more overhead than for a row-oriented table. We recommend that you use a row-oriented table with binlogs enabled. If the table is also used for online analytical processing (OLAP) queries, we recommend that you use a row-column hybrid storage format. For more information, see Table storage formats.

• Only Hologres internal tables support binlogs. Foreign tables do not.

A Binlog record consists of Binlog system fields and user table fields, which are described in the table below.

In Hologres, the binlog feature is disabled by default. You can enable it by setting the binlog.level and binlog.ttl table properties. The following example shows how to enable binlogs. For more information about the parameters used to create a table, see CREATE TABLE.

• Syntax supported in V2.1 and later:

  The table property names binlog.level and binlog.ttl are updated to binlog_level and binlog_ttl.

  CREATE TABLE test_message_src (
      id int PRIMARY KEY,
      title text NOT NULL,
      body text
  )
  WITH (
      orientation = 'row',
      clustering_key = 'id',
      binlog_level = 'replica',
      binlog_ttl = '86400' -- The TTL for the binary log, in seconds.
  );

• Syntax supported by all versions:

  begin;
  create table test_message_src(
    id int primary key, 
    title text not null, 
    body text);
  call set_table_property('test_message_src', 'orientation', 'row');-- Create the row-oriented table test_message_src.
  call set_table_property('test_message_src', 'clustering_key', 'id');-- Create a clustered index on the id column.
  call set_table_property('test_message_src', 'binlog.level', 'replica');-- Set a table property to enable the binlog feature.
  call set_table_property('test_message_src', 'binlog.ttl', '86400');-- The TTL for the binary log, in seconds.
  commit;

The following table describes the parameters.

In Hologres V1.1 and later, you can enable or disable the binlog feature based on your business needs. You can also configure the TTL to meet the retention period requirements of different business scenarios. You can enable binlogs for existing tables without recreating them, which is a fast and convenient process.

The binlog data of a Hologres table uses a strong schema format. To query the binlog for a specific table, you can combine the built-in binlog fields with the original table fields. Hologres also provides functions to query the latest or earliest binlog, or to query binlog information using a known LSN and timestamp.

Directly query binlogs using built-in special fields

You can query binlogs by combining the built-in binlog fields with the original table fields. The following code provides an example.

SELECT hg_binlog_lsn,hg_binlog_event_type,hg_binlog_timestamp_us,* FROM test_message_src;

The following figure shows a sample query result.

Query the earliest or latest binlog on a specified shard of a table

You can use the hg_get_binlog_cursor function to query binlogs. The syntax is as follows.

-- OLDEST: The earliest binlog on this shard.
SELECT * FROM hg_get_binlog_cursor('<table_name>','OLDEST',<shard_id>);

-- LATEST: The latest binlog on this shard.
SELECT * FROM hg_get_binlog_cursor('<table_name>','LATEST',<shard_id>);

The following code provides an example.

SELECT * FROM hg_get_binlog_cursor('test_message_src','OLDEST',0);

The following figure shows a sample query result.

Query the timestamp of a binlog by its LSN

Use the hg_get_binlog_cursor_by_lsn function to query the timestamp of a binlog. This function returns information about the first binlog whose log sequence number (LSN) is greater than or equal to the specified LSN. If the specified LSN does not exist, the hg_binlog_timestamp_us field in the result is the current time. The syntax is as follows.

SELECT * FROM hg_get_binlog_cursor_by_lsn('<table_name>',<lsn>,<shard_id>);--LSN value (bigint type)

The following provides an example.

SELECT * FROM hg_get_binlog_cursor_by_lsn('test_message_src',152,0);

The following shows a sample query result.

Query the LSN of a binlog by its timestamp

You can use the hg_get_binlog_cursor_by_timestamp function to query the Log Sequence Number (LSN) of a binlog. This function returns information about the first binlog with a timestamp that is greater than or equal to the specified time. If the specified time is later than the timestamp of the latest binlog entry, the hg_binlog_timestamp_us field in the result contains the current time, and the hg_binlog_lsn field contains the LSN that will be assigned to the next data insertion. The syntax is as follows.

SELECT * FROM hg_get_binlog_cursor_by_timestamp('<table_name>',<timestamp>,<shard_id>);

For example:

SELECT *,to_timestamp(hg_binlog_timestamp_us/1000000.0) FROM hg_get_binlog_cursor_by_timestamp('test_message_src','2024-05-20 19:34:53.791+08',0);

The following shows a sample query result.

You can consume Hologres binlogs using Flink, Blink, and JDBC (including Holo Client). For more information, see the following topics:

• To consume binlogs in real time using Flink and Blink, see Consume Hologres binlogs in real time using Flink or Blink.

• To consume binlogs in real time using JDBC, see Consume Hologres binlogs using JDBC.

You can use the following SQL statement to view the tables for which binlogs are enabled.

SELECT
    *
FROM
    hologres.hg_table_properties
WHERE
    property_key = 'binlog.level'
    AND property_value = 'replica';

The following figure shows a sample result.