Hologres:Set up binlog replication for Hologres

Binlog support matrix

Create a table with binlogs enabled

V2.1 and later (property names 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'  -- TTL in seconds (24 hours)
);

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');       -- Row-oriented storage
CALL set_table_property('test_message_src', 'clustering_key', 'id');    -- Clustered index on id
CALL set_table_property('test_message_src', 'binlog.level', 'replica'); -- Enable binlogs
CALL set_table_property('test_message_src', 'binlog.ttl', '86400');     -- TTL in seconds (24 hours)
COMMIT;

Parameters:

For full CREATE TABLE syntax, see CREATE TABLE.

Enable or disable binlogs on an existing table

These operations require V1.1 or later.

Enable binlogs:

-- Enable binlogs
BEGIN;
CALL set_table_property('<table_name>', 'binlog.level', 'replica');
COMMIT;

-- Set binlog TTL (in seconds)
BEGIN;
CALL set_table_property('<table_name>', 'binlog.ttl', '2592000');
COMMIT;

Disable binlogs:

BEGIN;
CALL set_table_property('<table_name>', 'binlog.level', 'none');
COMMIT;

Update binlog TTL:

CALL set_table_property('<table_name>', 'binlog.ttl', '8640000');  -- Unit: seconds

In V1.3.24 and later, you can set a TTL on a child table of a partitioned table. A child table with no TTL set inherits the parent table's TTL.

Replace <table_name> with the actual table name.

hg_binlog_event_type values

How UPDATE events are recorded

An UPDATE operation produces two consecutive binlog records: the BEFORE_UPDATE record comes first, immediately followed by the AFTER_UPDATE record. The binlog subscription feature guarantees these two records are consecutive.

BEFORE_UPDATE (3) and AFTER_UPDATE (7) appear only when an UPDATE is executed as a plain SQL statement. When UPDATE is performed through a Hologres connector—such as Holo Client, Flink Connector, or Data Integration—the connector translates BEFORE_UPDATE to DELETE (2) and AFTER_UPDATE to INSERT (5) to ensure eventual consistency.

Structurally, a Hologres binlog behaves like a row-oriented table where hg_binlog_lsn is the key, and hg_binlog_event_type, hg_binlog_timestamp_us, and all original table columns form the value. Because binlog fields are fixed and strongly typed, row-oriented or row-column hybrid tables deliver better read performance when binlogs are enabled.

Query binlog records directly

Combine the three system fields with the table's original columns in a SELECT statement:

SELECT hg_binlog_lsn, hg_binlog_event_type, hg_binlog_timestamp_us, *
FROM test_message_src;

The following figure shows a sample result.

Query the earliest or latest binlog on a shard

Use hg_get_binlog_cursor to retrieve the first or last binlog entry on a specific shard:

-- Earliest binlog on shard 0
SELECT * FROM hg_get_binlog_cursor('test_message_src', 'OLDEST', 0);

-- Latest binlog on shard 0
SELECT * FROM hg_get_binlog_cursor('test_message_src', 'LATEST', 0);

Syntax:

SELECT * FROM hg_get_binlog_cursor('<table_name>', 'OLDEST' | 'LATEST', <shard_id>);

The following figure shows a sample result for the OLDEST query.

Query binlog position by LSN

Use hg_get_binlog_cursor_by_lsn to find the first binlog record whose LSN is greater than or equal to the specified value. If no such record exists, hg_binlog_timestamp_us in the result returns the current time.

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

Syntax:

SELECT * FROM hg_get_binlog_cursor_by_lsn('<table_name>', <lsn>, <shard_id>);
-- <lsn>: BIGINT

The following figure shows a sample result.

Query binlog position by timestamp

Use hg_get_binlog_cursor_by_timestamp to find 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, hg_binlog_timestamp_us returns the current time and hg_binlog_lsn returns the LSN that will be assigned to the next inserted row.

If the provided timestamp is later than the current time returned by now(), the query throws a Get binlog cursor in future time exception.

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);

Syntax:

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

The following figure shows a sample result.

View tables with binlogs enabled

Run the following query to list all tables with binlogs enabled in the current database:

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

The following figure shows a sample result.

View binlog storage size

• All versions: Use pg_relation_size to get total table storage, which includes binlog storage. See View the storage size of a table.

• V2.1 and later: Use hologres.hg_relation_size to view a storage breakdown by type—data, binlogs, and other components. See View table storage details.

Suppress binlog generation for a session

To run DML statements without generating binlog records, set the following Grand Unified Configuration (GUC) parameter at the session level before executing the DML statement:

SET hg_experimental_generate_binlog = off;

This setting applies only to the current session and must be set before each DML statement for which you want to suppress binlog output.