Hologres:Subscribe to Hologres binlogs

Be aware of the following limitations when you subscribe to Hologres binlogs:

• Only Hologres V0.9 or later supports binlog subscription. If your instance is an earlier version, join the online support DingTalk group. For details, see How do I get more online support?.

• In Hologres V0.9 and V0.10, you cannot enable binlogs for existing tables by altering their properties. You must recreate the table. Starting from V1.1, you can enable binlogs on demand.

• Before Hologres V1.3.14 and V1.1.82, only a Superuser could consume the binlog. Using an account with fewer privileges would result in a permission denied for table hg_replication_slot_properties error. Starting from V1.3.14 and V1.1.82, an account only needs SELECT permission on the table to consume the binlog with Flink. To consume the binlog with JDBC, the account must be in the Replication Role.

• Hologres supports table-level binlogs for both row-oriented tables and column-oriented tables. The following table lists the supported consumption methods.

  Flink category	Row-oriented table	Column-oriented table	Row-column hybrid table (Supported from V1.1)
  Blink	Supported	Supported	Supported
  Realtime Compute for Apache Flink	Supported	Supported	Supported
  Apache Flink	Not supported	Not supported	Not supported
  JDBC	Supported in V1.1 and later	Supported in V1.1 and later	Supported in V1.1 and later

• Blink does not support the TIMESTAMP data type when consuming Hologres binlogs. Use the TIMESTAMPTZ type when you create tables in Hologres. Other special types such as SMALLINT are also not supported.

• You cannot consume binlogs from the parent table of a partitioned table. Use a child table or a regular (non-partitioned) table. Starting from Hologres V1.3.24, you can modify the binlog TTL of a child table on demand. If you do not explicitly specify a binlog TTL for a child table, it inherits the TTL from its parent table. Note that the binlog TTL is not a precise timer. The system does not guarantee that the binlog is deleted immediately after it expires. The system deletes the binlog sometime after it expires.

• For write-heavy workloads, enabling binlogs on a column-oriented table theoretically incurs more overhead than on a row-oriented table. Therefore, use a row-oriented table when you enable binlogs. If the table is also used for OLAP queries, use the row-column hybrid storage format. For more information, see Table storage formats.

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

A binlog record contains system fields and user table columns. The following table describes the fields.

Field name	Type	Description
hg_binlog_lsn	BIGINT	A system field of the binlog that represents the log sequence number (LSN). The value is monotonically increasing but not guaranteed to be continuous within a shard. It is not guaranteed to be unique or ordered across shards. Note For more information about how binlog data is distributed across shards, see Distribution key.
hg_binlog_event_type	BIGINT	A system field of the binlog that indicates the type of the change event. Valid values for hg_binlog_event_type are: 5 (INSERT): The binlog record is for a new row insertion. 2 (DELETE): The binlog record is for an existing row deletion. 3 (BEFORE_UPDATE): The binlog record is for the state of an existing row before an update. 7 (AFTER_UPDATE): The binlog record is for the state of an existing row after an update.
hg_binlog_timestamp_us	BIGINT	A system field of the binlog that represents the system timestamp in microseconds (us).
user_table_column_1	User-defined	A user table column.
...	...	...
user_table_column_n	User-defined	A user table column.

By default, the binlog feature is disabled in Hologres. You can enable it by setting the binlog.level and binlog.ttl table properties. The following examples show how to enable binlogs. For more information about table creation parameters, see CREATE TABLE.

• Syntax for 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 of the binlog, in seconds.
  );

• Syntax supported in 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 a row-oriented table named 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 of the binlog, in seconds.
  commit;

The following table describes the parameters.

Parameter	Description
binlog_level or binlog.level	Specifies whether to enable binlogs. Valid values: replica: enabled. none: disabled.
binlog_ttl or binlog.ttl	The TTL for the binlog, in seconds. Default value: 2592000 (30 days).

Starting from Hologres V1.1, you can enable or disable binlogs. You can also configure the TTL to meet different retention requirements. You can enable binlogs for an existing table without recreating it.

Hologres binlog data is strongly typed. To query the binlogs of a specific table, you can combine the built-in binlog system fields with the original table columns. Hologres also provides functions to query the earliest or latest binlog, or to query binlog information by a known LSN or timestamp.

Querying by built-in fields

Use the following statement to query the binlog by combining the built-in binlog fields with the original table fields.

SELECT hg_binlog_lsn,hg_binlog_event_type,hg_binlog_timestamp_us,* FROM test_message_src;

The following code provides a sample result.

postgres=# select hg_binlog_lsn, hg_binlog_event_type, hg_binlog_timestamp_us, * from test_message_src ;
 hg_binlog_lsn | hg_binlog_event_type | hg_binlog_timestamp_us | id |  title  |       body
---------------+----------------------+------------------------+----+---------+-------------------
          1081 |                    5 |       1626856018921653 |  1 | title 1 | body 1
          1092 |                    3 |       1626856054284918 |  1 | title 1 | body 1
          1093 |                    7 |       1626856054284918 |  1 | title 1 | body after update
          1095 |                    2 |       1626856059747536 |  1 | title 1 | body after update
(4 rows)

Querying the earliest or latest binlog

Use the hg_get_binlog_cursor function to query the earliest or latest binlog on a specific shard. The following code provides the syntax.

-- OLDEST: Query the earliest binlog on this shard.
SELECT * FROM hg_get_binlog_cursor('<table_name>','OLDEST',<shard_id>);
-- LATEST: Query 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 code provides a sample result.

test=# select * from hg_get_binlog_cursor('test_message_src','OLDEST',0);
 hg_shard_id | hg_binlog_lsn | hg_binlog_timestamp_us
-------------+---------------+------------------------
           0 |           152 |       1716204893791755
(1 row)

Querying timestamp by 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 record whose 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 returns the current time. The following code provides the syntax.

SELECT * FROM hg_get_binlog_cursor_by_lsn('<table_name>',<lsn>,<shard_id>);--The LSN value must be of the BIGINT type.

The following code provides an example.

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

The following code provides a sample result.

test=# select * from hg_get_binlog_cursor_by_lsn('test_message_src',152,0);
 hg_shard_id | hg_binlog_lsn | hg_binlog_timestamp_us
-------------+---------------+------------------------
           0 |           152 |      1716204893791755
(1 row)

Querying LSN by timestamp

Use the hg_get_binlog_cursor_by_timestamp function to query the LSN of a binlog. This function returns information about the first binlog record with a timestamp greater than or equal to the specified time. If the specified time is later than the latest binlog entry, the hg_binlog_timestamp_us field in the result returns the current time, and the hg_binlog_lsn field returns the LSN that will be assigned to the next inserted row. The following code provides the syntax.

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

The following code provides an 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 code provides a sample result.

test=# 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);
 hg_shard_id | hg_binlog_lsn | hg_binlog_timestamp_us |          to_timestamp
-------------+---------------+------------------------+-------------------------------
           0 |           152 |    1716204893791755 | 2024-05-20 19:34:53.791755+08
(1 row)

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

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

• To consume binlogs with JDBC, see Consume Hologres binlogs by using JDBC.

You can use the following SQL statement to see which tables have binlogs enabled.

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

The following is a sample result. The query returns four columns: table_namespace, table_name, property_key, and property_value. These columns provide information about all tables that have binlogs enabled.