Create and update a heartbeat table

This topic describes how to create and update a heartbeat table when you migrate data from a MySQL database, or PostgreSQL database.

Background

When you create a MySQL, or PostgreSQL database, if you select Allow OMS to automatically write heartbeat data into this instance during incremental synchronization. This resolves the problem of high latency when no business data is written in the source database. and the entered database user has the privileges to create and write tables, OMS Community Edition will create a heartbeat table in the corresponding database and update it every 5 seconds.

If you do not want the heartbeat table to be automatically created and updated by OMS Community Edition, you can create a database table and configure a crontab task to periodically perform the UPDATE operation on the source database.

Prerequisites

The source database account is granted the privileges to create databases or schemas and the privileges to read and write heartbeat tables.

MySQL database

• Grant a database user the privilege to create the drc.heartbeat table:

  GRANT CREATE ON drc.heartbeat TO '<user_name>';
  

• Grant a database user the privilege to write the drc.heartbeat table:

  GRANT INSERT, UPDATE, DELETE ON drc.heartbeat TO '<user_name>';
  

PostgreSQL database

• Grant a database user the privilege to create the drc.heartbeat table:

  GRANT CREATE ON SCHEMA public TO '<user_name>';
  

• Grant a database user the privilege to write the drc.heartbeat table:

  GRANT INSERT, UPDATE, DELETE ON oms_postgres_heartbeat TO '<user_name>';
  

Create and update a heartbeat table in a MySQL database

• Create a heartbeat table

  CREATE DATABASE IF NOT EXISTS drc;
  CREATE TABLE IF NOT EXISTS drc.heartbeat (`id` smallint(6) NOT NULL DEFAULT 1,`ts` int(11) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB;
  

• Insert data

  INSERT INTO drc.heartbeat VALUES (1,0) ON DUPLICATE KEY UPDATE ts=0;
  

• Update data

  UPDATE drc.heartbeat SET ts=%d WHERE id=1;
  

Create and update a heartbeat table in a PostgreSQL database

• Create a heartbeat table

  CREATE TABLE IF NOT EXISTS public.oms_postgres_heartbeat (id int NOT NULL DEFAULT 1, ts int DEFAULT NULL, PRIMARY KEY (id));
  

• Insert data

  INSERT INTO public.oms_postgres_heartbeat VALUES (1,0) ON CONFLICT (id) DO UPDATE SET ts=0;
  

• Update data

  UPDATE public.oms_postgres_heartbeat SET ts=%d WHERE id=1;