Setup Destination Database
1. Create a dedicated database user in the destination PostgreSQL database for Helyx data pipeline activities.
CREATE role helyx with login password <password>;2. Grant Required Privileges
Grant the following privileges to the helyx user :
Alter role helyx login; ALTER role helyx WITH REPLICATION; GRANT CREATE ON SCHEMA public TO helyx; ALTER SCHEMA public OWNER TO helyx; ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO helyx; GRANT CREATE ON SCHEMA <schemaname> TO helyx; GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA <schemaname> TO helyx; GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO helyx; GRANT USAGE ON SCHEMA <schemaname> TO helyx; CREATE SCHEMA IF NOT EXISTS helyx AUTHORIZATION helyx; ALTER DEFAULT PRIVILEGES IN SCHEMA <schemaname> GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO helyx; CREATE SCHEMA helyx AUTHORIZATION helyx;CREATE TABLE helyx.debezium_signal (id text PRIMARY KEY,type VARCHAR(30) NOT NULL,data text);Recommended PostgreSQL Configuration for Helyx Replication
To ensure stable and high-performance logical replication from Oracle to PostgreSQL using Helyx, the following PostgreSQL parameters should be configured appropriately :
1. wal_level = logical
This parameter defines the level of information written to the Write-Ahead Log (WAL). Setting it to logical enables logical decoding.
2. max_wal_size = 8GB
Specifies the maximum size that WAL files can grow to before PostgreSQL triggers a checkpoint.
3. min_wal_size = 2GB
Defines the minimum amount of WAL files PostgreSQL keeps available.
4. checkpoint_timeout = 15min
The maximum time interval between automatic checkpoints.
5. checkpoint_completion_target = 0.9
Determines how aggressively PostgreSQL performs checkpoint operations (fraction of time before next checkpoint).
6. shared_buffers = 5GB
This parameter defines how much memory PostgreSQL dedicates to caching table and index data in shared memory.
7. wal_buffers = 64MB
This parameter specifies the amount of memory reserved for buffering Write-Ahead Log (WAL) data before it is flushed to disk.
8. effective_io_concurrency = 200
This parameter tells PostgreSQL how many concurrent disk I/O operations it can expect the storage subsystem to handle efficiently, especially for asynchronous read operations.
9. maintenance_io_concurrency = 200
This parameter controls the number of concurrent I/O operations PostgreSQL can use for maintenance activities such as vacuuming, index creation, and cleanup tasks.
In replication environments, efficient maintenance supports overall database health and prevents performance degradation over time.
10. synchronous_commit = off
This parameter controls whether PostgreSQL waits for WAL records to be flushed to durable storage before confirming a transaction commit.
However, this setting involves a trade-off : in the event of a sudden crash, a small amount of recently committed data may be lost. It should therefore be used only when this risk is acceptable in the target environment.
These parameters further improve memory usage, WAL handling, disk I/O efficiency, background maintenance performance, and transaction throughput. When configured correctly, helps PostgreSQL remain stable and responsive while processing continuous replication and change data capture workloads.
How to Create tables to destination PostgreSQL database
Creating tables in the destination PostgreSQL database requires careful attention and precision. To ensure seamless replication, you must accurately map the data types from the source Oracle database to their equivalent PostgreSQL data types. Any mismatch in data types can cause the data pipeline to fail, so it is essential to use only PostgreSQL-supported data types
Additionally, the structure of the destination tables must mirror the source Oracle tables: the number of columns should be identical, and the column names must match exactly. For compatibility, all column names in PostgreSQL should be written in lowercase, as PostgreSQL defaults to lowercase notation for identifiers.
If there are discrepancies in column names or the destination tables do not contain all columns present in the source tables, the data pipeline will fail. Such issues will interrupt real-time Change Data Capture (CDC) processes, halting replication until the inconsistencies are corrected. Carefully review and validate the schema to prevent disruptions in the replication workflow.
To Automatically create destination tables run the below command :
<path_to_serverconfig> -createdesttables -pub <path_to_publication.config> -sub <path_to_subscription.config> -tables <path_to_tablelist.config>For example, if the table scott.table1 in Oracle database contains three columns, you must create a corresponding table in PostgreSQL with the exact same three columns and identical column names. This level of precision is required to ensure the successful operation of both data migration and ongoing replication processes
However, you may choose to create the tables in PostgreSQL manually, and can only setup and configure both data migration and real-time Change Data Capture (CDC), without relying on automated table creation.