Subscribe to Hologres binlogs

Usage notes

Enable binary logging

• Overview

  By default, binary logging is disabled for Hologres tables. You can enable this feature for a Hologres table by setting the table properties binlog.level and binlog.ttl. Theoretically, if your tables are frequently updated, the cost of binary logging for column-oriented tables is greater than the cost of binary logging for row-oriented tables. Therefore, we recommend that you enable binary logging for row-oriented tables.

• Limits
  • In Hologres V0.9 and V0.10, you cannot enable binary logging by modifying the table properties of an existing table. To enable binary logging for a table, you must enable the feature when you create the table. In Hologres V1.1 and later, you can enable binary logging for all tables as needed.
  • Blink-based Realtime Compute does not support Hologres binlogs that contain data of the TIMESTAMP type. When you create a table in Hologres, specify the TIMESTAMPTZ data type for related fields. Blink-based Realtime Compute also does not support Hologres binlogs that contain data of special types such as the SMALLINT type.
  • You cannot subscribe to binlogs of a parent partitioned table. You can subscribe to only the binlogs of a non-partitioned table.
  • Theoretically, if your tables are frequently updated, the cost of binary logging for column-oriented tables is greater than the cost of binary logging for row-oriented tables. Therefore, we recommend that you enable binary logging for row-oriented tables.
• Examples

  When you create a table, you can set the table properties binlog.level and binlog.ttl to enable binary logging. For more information about the parameters that you can use to create a table, see CREATE TABLE.

  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 for the id column.
  call set_table_property('test_message_src', 'binlog.level', 'replica'); -- Set the binlog.level property to enable binary logging.
  call set_table_property('test_message_src', 'binlog.ttl', '86400'); -- Set the binlog.ttl property to specify the time to live (TTL) of binlogs. Unit: seconds.
  commit;

  Property	Description
  binlog.level	Specifies whether to enable binary logging. Valid values: replica: enables binary logging. none: disables binary logging.
  binlog.ttl	The TTL of binlogs. Unit: seconds. Default value: 2592000, which indicates 30 days.

Enable binary logging as needed

• Enable binary logging
  You can execute the following statements to enable binary logging for an existing table and specify the TTL of binlogs:

  -- Set the binlog.level property to enable binary logging.
  begin;
  call set_table_property('<table_name>', 'binlog.level', 'replica');
  commit;
  
  -- Set the binlog.ttl property to specify the TTL of binlogs. Unit: seconds.
  begin;
  call set_table_property('<table_name>', 'binlog.ttl', '2592000');
  commit;

  The table_name property specifies the name of the table for which binary logging is to be enabled.
• Disable binary logging
  You can execute the following statements to disable binary logging for a table:

  -- Set the binlog.level property to disable binary logging.
  begin; 
  call set_table_property('<table_name>', 'binlog.level', 'none'); 
  commit; 

  The table_name property specifies the name of the table for which binary logging is to be disabled.
• Change the TTL of binlogs
  You can execute the following statements to change the TTL of binlogs for a table. This way, you can set an appropriate binlog retention time based on your business requirements.

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

  The table_name property specifies the name of the table for which the TTL of binlogs is to be changed.

Binlog format

• The hg_binlog_event_type field has four valid values:
  • INSERT=5, which indicates that a row or column is inserted.
  • DELETE=2, which indicates that a row or column is deleted.
  • BEFORE_UPDATE=3, which indicates that a row or column is saved before it is updated.
  • AFTER_UPDATE=7, which indicates that a row or column is saved after it is updated.
• An UPDATE operation generates two binlog records, which indicate that the row or column is saved before and after the update. Binary logging ensures that the ordinal numbers of the two records are continuous and in the right order. The record that is generated after the update comes right after the record that is generated before the update.
• The order of user-defined fields is consistent with the order that is defined in a DDL statement.

Query log backup files

select hg_binlog_lsn,hg_binlog_event_type,hg_binlog_timestamp_us,* from test_message_src;

Consume Hologres binlogs in real time