PostgreSQL Replication Setup

Introduction

Helyx is a lightweight, high-performance replication solution designed for real-time heterogeneous database replication. It provides a simple CLI-driven experience and abstracts the complexity of setting up connectors, schema registry, and replication pipelines.

Key highlights of Helyx:

• Heterogeneous Replication: Supports replication across multiple database systems (e.g., PostgreSQL ↔ PostgreSQL, Oracle ↔ Oracle, Oracle → PostgreSQL, Oracle → MySQL, Oracle → MongoDB, Oracle → Snowflake).
• High Throughput: Capable of handling hundreds of thousands of transactions per second across distributed data centers.
• Schema Evolution Handling: Automatically adapts to schema changes like column additions and keeps source and target databases in sync.
• Zero-Downtime Replication: Supports ad-hoc snapshots and live streaming without interrupting production workloads.
• Monitoring & Control: Built-in CLI commands allow users to check replication lag, list connectors, view tasks, and monitor end-to-end replication health.
• Deployment Friendly: Delivered as self-contained executables and RPM packages, making installation and upgrades straightforward.
• Version-Aware Replication: Helyx supports replication between different versions of the same technology stack (e.g., PostgreSQL 11 → PostgreSQL 15 or EDB 12 → EDB 14 or Oracle11g → Oracle19c). This ensures smooth migrations and upgrades without downtime.

With Helyx, enterprises can achieve enterprise-grade replication with lower operational overhead, faster setup, and proven reliability compared to traditional tools.

Helyx Installation Guide

1. Overview

Helyx is distributed as an RPM package to simplify installation and management. By default, Helyx is installed in the directory /var/lib/helyx.

This section describes the steps to install Helyx from a standard YUM/DNF repository as well as from a local repository if you download the RPM manually.

2. Prerequisites

• Operating System: Linux (RHEL, CentOS, Rocky Linux, Oracle Linux, Fedora, etc.).
• Privileges: Root or sudo access.
• Package Manager: yum or dnf depending on your OS version.
• Network Access (for online installation): Access to the configured YUM repository

Installing Helyx from a Local Repository

mkdir -p /opt/localrepo/helyx

cp helyx-1.1.0.rpm /opt/localrepo/helyx/

cd /opt/localrepo/helyx

createrepo .

[helyx-local]
name=Helyx Local Repository
baseurl=file:///opt/localrepo/helyx
enabled=1
gpgcheck=0

sudo yum install helyx -y

Post installation verifies and ensures binaries are installed under /var/lib/helyx location.

Target: Set up real-time replication between PostgreSQL/EDB source and destination with different versions as well as same version.

Directory Structure

/var/lib/helyx/replconfig
├── publication.config
├── subscription.config
├── tablelist.config
├── tabletorepl
├── serverconfig
└── configservice

Ensure these services are configured and started:

./configservice -start sync-manager

listeners = PLAINTEXT://your.host.name:9092

./configservice -start broker

kafkastore.bootstrap.servers=PLAINTEXT://<your.host.name>:9092

listeners=http://0.0.0.0:8081

./configservice -start schemaregistry

bootstrap.servers=<your.server.ip>:9092

key.converter.schema.registry.url=http://<your.server.ip>:8081

value.converter.schema.registry.url=http://<your.server.ip>:8081

./connectservice -start connect

Step 2: Configure Source Database

Creating Helyx User in Source Database

In order for Helyx to perform replication, a dedicated database user with appropriate privileges must be created. This section describes the SQL commands required to create the helyx role, schema, and associated privileges on the source database.

CREATE ROLE helyx WITH LOGIN REPLICATION PASSWORD 'password';

CREATE SCHEMA IF NOT EXISTS helyx;

GRANT CONNECT ON DATABASE edb TO helyx;
GRANT CREATE ON DATABASE edb TO helyx;

GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA helyx TO helyx;
ALTER DEFAULT PRIVILEGES IN SCHEMA helyx
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO helyx;

Change publication.config file

Edit /var/lib/helyx/replconfig/publication.config with the following keys:

"name": "",                       // Unique identifier
"tasks.max": "1",                // Parallelism level
"database.type": "edb",          // edb or postgresql
"database.hostname": "",         // Source DB IP
"database.port": "5444",         // Source DB Port
"database.password": "",         // Source DB “helyx” user password
"database.dbname": "edb",        // Database name
"bootstrap.ip": "",              // Broker Server IP
"include.schema.list": "schema1,schema2,schema3",
"schema.history.internal.kafka.bootstrap.servers": "<serverIP>:9092",
"publication.name": "",          // Logical replication publication name
"snapshot.mode": "initial",
"database.history.kafka.bootstrap.servers": "<serverIP>:9092",
"database.history.kafka.topic": "dbhistory.<Topicname>",
"topic.prefix": "",              // Prefix for Replication topics
"max.batch.size": "2048",
"max.queue.size": 50000,
"poll.interval.ms": "1000",
"producer.batch.size": "32768",
"producer.max.request.size": "10485760"

✅ Grant Source Permissions Automatically

Enter comma-separated table list at /var/lib/helyx/replconfig/tablelist.config. These are the tables you want to replicate.

Schema1.Table1,
Schema2.Table2,
Schema2.Table3

Then run:

./tabletorepl.jar -s <path_to_publication.config> -tables <path_to_tablelist.config>

For command help: ./tabletorepl.jar -help

Step 3: Configure Destination Database

Creating Helyx User in Destination Database

In order for Helyx to perform replication, a dedicated database user with appropriate privileges must be created. This section describes the SQL commands required to create the helyx role, schema, and associated privileges on the destination database.

CREATE ROLE helyx WITH LOGIN REPLICATION PASSWORD 'password';
ALTER ROLE helyx WITH REPLICATION;

CREATE SCHEMA IF NOT EXISTS helyx AUTHORIZATION helyx;

GRANT CONNECT ON DATABASE edb TO helyx;
GRANT CREATE ON DATABASE edb TO helyx;

Granting Schema Privileges for Helyx User

For schemas listed in include.schema.list of the publication config, grant:

GRANT USAGE ON SCHEMA <schema_name> TO helyx;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA <schema_name> TO helyx;
GRANT CREATE ON SCHEMA <schema_name> TO helyx;
ALTER SCHEMA <schema_name> OWNER TO helyx;
ALTER DEFAULT PRIVILEGES IN SCHEMA <schema_name> GRANT ALL ON TABLES TO helyx;
ALTER DEFAULT PRIVILEGES IN SCHEMA <schema_name> GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO helyx;

Note: username will be same at source and destination with the same password.

Change subscription.config file

Edit /var/lib/helyx/replconfig/subscription.config with:

"name": "",   // Unique identifier name
"tasks.max": "1",
"connection.url": "jdbc:postgresql://<Dest_ServerIP>:5444/edb?user=helyx&password=<password>",
"bootstrap.ip": "",  // Kafka IP
"retry.backoff.ms": "1000",
"consumer.max.poll.records": "1000",
"consumer.fetch.max.bytes": "10485760",
"batch.size": "5000",
"max.retries": "10"

Note: User name helyx will NOT be changed

Step 4: Use CLI Tool for Replication Management

Common usages of serverconfig:

Step 5: Set Up Destination Database (Manual Grants)

On destination DB, execute:

CREATE ROLE helyx WITH LOGIN PASSWORD '<password>';
ALTER ROLE helyx WITH REPLICATION;
GRANT CREATE ON DATABASE <Database Name> TO helyx;
CREATE SCHEMA IF NOT EXISTS helyx AUTHORIZATION helyx;

GRANT USAGE ON SCHEMA <schema_name> TO helyx;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA <schema_name> TO helyx;
GRANT CREATE ON SCHEMA <schema_name> TO helyx;
ALTER SCHEMA <schema_name> OWNER TO helyx;

ALTER DEFAULT PRIVILEGES IN SCHEMA <schema_name> GRANT ALL ON TABLES TO helyx;
ALTER DEFAULT PRIVILEGES IN SCHEMA <schema_name> GRANT USAGE ON SEQUENCES TO helyx;
ALTER DEFAULT PRIVILEGES IN SCHEMA <schema_name> GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO helyx;

Note: Please note that username will be same as source and destination database with same password.

Step 6: Use CLI Tool for Starting Replication between servers

📌 Command Reference

serverconfig -createpub -s publication.config -tables tablelist.config

serverconfig -createsub -s subscription.config -pub publication.config

serverconfig -addtabletopub -s publication.config -tables tablelist.config

serverconfig -adhocsnapshot -s publication.config -tables table1,table2

serverconfig -status -pub -s publication.config

serverconfig -status -sub -s subscription.config

serverconfig -remove -pub -s publication.config

serverconfig -remove -sub -s subscription.config

serverconfig -removetablefromrepl -s publication.config -tables table1,table2

serverconfig -encryptPassword -s publication.config

serverconfig -listconnectors -s publication.config

serverconfig -listtask -s publication.config

serverconfig -lagstat -s subscription.config

serverconfig -tablelist -s publication.config

Start & Monitor

serverconfig -createpub -s publication.config -tables tablelist.config

serverconfig -createsub -s subscription.config -pub publication.config

serverconfig -status -pub -s publication.config
serverconfig -status -sub -s subscription.config

Schema Evolution: Add Column to Replicated Table

If you want to add a new column to a table that is already being replicated, follow these steps carefully:

Purpose

To safely add a new column to an existing replicated table and synchronize the schema with downstream systems.

Steps

systemctl stop connect.service

ALTER TABLE <schema>.<table_name> ADD COLUMN <new_column_name> <datatype>;

systemctl start connect.service

serverconfig -adhocsnapshot -s publication.config -tables <table_name>