Canonical: https://molo17.com/solutions/replicate-to-warehousepg/ Markdown mirror: https://molo17.com/solutions/replicate-to-warehousepg/index.md Title: Replicate to WarehousePG in real time with Gluesync | MOLO17 Description: Real-time replication to WarehousePG from Oracle, SQL Server, PostgreSQL, MySQL, and MongoDB, with parallel gpfdist loads on every segment. Start a free trial. [Solutions](/solutions/) Replicate to WarehousePG SOLUTION · REPLICATE TO WAREHOUSEPG # Real-time data replication to WarehousePG from your operational databases **Committed changes from your systems of record land in WarehousePG continuously, loaded in parallel by every primary segment instead of squeezed through the coordinator.** WarehousePG is EDB's Apache 2 licensed, open-source fork of Greenplum Database, binary compatible with Greenplum 6.x and 7.x: the same wire protocol, catalog, segments, external tables, and `gpfdist`. Gluesync by MOLO17 serves it with its Greenplum agent, which writes to WarehousePG exactly as it writes to Greenplum. Dedicated source agents capture changes from Oracle, SQL Server, PostgreSQL, MySQL, IBM i, MongoDB, and other [heterogeneous sources](/integrations/?target=WarehousePG#integration-finder), and Core Hub, the Gluesync control plane, runs every pipeline from one web UI and REST API. [Start a Gluesync trial](/get-gluesync/) [Talk to us](/contacts/) VENDOR COMPATIBILITY ## One agent for WarehousePG and Greenplum WarehousePG is EDB's open-source fork of Greenplum Database, binary compatible with Greenplum 6.x and 7.x. The Gluesync Greenplum agent writes to WarehousePG exactly as it writes to Greenplum, so one pipeline design serves both. - **WarehousePG** Target Tested - **Greenplum** Target Tested WHO THIS IS FOR ## Teams that want the Greenplum loading path on an open-source MPP warehouse - EDB customers running WarehousePG who need current-state tables fed from operational systems all day, keyed like the source, with duplicate keys resolved by a per-entity setting instead of a hand-written merge job - Teams that moved from Greenplum to WarehousePG after Greenplum went closed source and want the same parallel loading they already know, without rebuilding their pipelines around a new tool - WarehousePG DBAs who want loads that use the cluster the way it is designed: every primary segment pulling its share of the data in parallel, tables distributed by key, and append-optimized column storage with compression - Platform leads who want one Core Hub for Oracle, SQL Server, PostgreSQL, MySQL, IBM i, and MongoDB instead of one loader per source, backed by [best-in-class enterprise support, rated 4.9/5 by customers](/support/#customer-ratings) THE PROBLEM ## A WarehousePG cluster fed by nightly loads is already behind Most WarehousePG clusters are filled by scheduled extracts: a job queries the source, writes files, and loads them on a timer. Reports then show the business as it was at the last load, each extract adds read load to a production database, and every source ends up with its own loader and failure mode. Teams looking for real-time WarehousePG ingestion usually weigh the gpload and gpfdist tooling they inherited from Greenplum, a managed ELT product such as Fivetran or Airbyte, a Debezium and Kafka stack with a JDBC sink, or scripts their own team keeps running. Gluesync addresses that with **per-agent CDC into WarehousePG**. A source agent reads each database's native change log, journal, or change stream; Core Hub routes the changes; the Greenplum agent applies them to your WarehousePG tables as they arrive, in commit order per entity. Whether the data comes from Oracle, IBM i, or MongoDB, the pipeline model, the snapshot, and the operations stay the same. HOW IT WORKS ## How Gluesync writes to WarehousePG ### The Greenplum agent, pointed at WarehousePG WarehousePG keeps Greenplum's wire protocol, catalog, segments, external tables, and `gpfdist`, so the Gluesync Greenplum agent writes to it exactly as it writes to Greenplum: you create a pipeline, enter the coordinator's connection details, and the same two write paths, the same table creation, and the same type mapping apply. It connects through the bundled PostgreSQL JDBC driver on WarehousePG and Greenplum 6.x and 7.x alike, and applies the change stream from any Gluesync source agent after a snapshot seeds each table. [Greenplum agent overview, covering WarehousePG ↗](https://docs.molo17.com/gluesync/latest/agents/greenplum-intro.html) - **Same type mapping:** values are written with the PostgreSQL target's type mapping, including enum types, arrays, and `hstore`. - **Clean reload on demand:** the Core Hub option to truncate before a snapshot is honored on the target tables. - **Database connection only:** the agent needs a connection to the coordinator and calls no management API, so a self-managed cluster and a cluster run by EDB tooling look the same to it. ### Two write paths: optimized batches and parallel bulk loads The write path is chosen per entity, so a small reference table and a billion-row fact table can each use the one that suits them. - **Batch path:** optimized JDBC batches of `INSERT` and `DELETE` statements, with each upsert applied as an update of the existing keys followed by an insert of the rest. - **Bulk path:** turned on per entity for the snapshot and for CDC. Rows are encoded as tab-delimited text in a spool file on Core Hub and loaded into a staging table in the `GLUESYNC` schema, then merged into the target table. ### Every segment loads its share with gpfdist If your team already runs `gpfdist` for file loads, this is the same mechanism, started and fed by Gluesync. - **`gpfdist`:** the agent serves the file from an embedded `gpfdist` server and loads it through a temporary readable external table. Every primary segment pulls its share of the data in parallel, so the load grows with the number of segments. - **`COPY`:** the file streams through `COPY … FROM STDIN` on the coordinator. - **Automatic choice:** the default uses `gpfdist` when the segments can reach Core Hub and the load is large enough to benefit, and `COPY` otherwise. At setup the agent checks that every primary segment can reach the server. ### Merged in one transaction, loaded straight when the key is enforced - **One transaction per flush:** the staging table is merged into the target table in the same transaction as the load. If the load fails, the transaction rolls back and the batch is retried without writing any row twice. - **Co-located merges:** the staging table is distributed by the key, so the merge joins rows that sit on the same segment as the target table's rows. - **Snapshot straight into the table:** a snapshot of a table with an enforced unique key, such as a primary key, is loaded directly into the table with no staging step, so every row is written once. ### Tables that look like WarehousePG tables When a target table does not exist, Core Hub proposes a `CREATE TABLE` from the source definition that you can edit before it runs. The agent also writes any table in the database that the connection user can write to, keeping the distribution policy and storage it already has, which makes pointing Gluesync at tables you migrated from Greenplum straightforward. - **Distribution:** tables are distributed by their primary key, and randomly when they have none. Edit the statement to distribute by another key. - **Storage:** choose `HEAP`, `AO_ROW`, or `AO_COLUMN` append-optimized storage for the tables the agent creates, with `ZSTD` or `ZLIB` compression and a configurable compression level. - **System schemas stay out of the way:** `gp_toolkit` and the other schemas the cluster keeps for its tools, plus external and foreign tables, are not listed in discovery. ### Data types The type coverage is the PostgreSQL target's, matched to the types WarehousePG has, and every column can be mapped to the alternatives Core Hub lists. - **Numbers and booleans:** `smallint`, `integer`, `bigint`, the serial types, `boolean`, `numeric` and `decimal`, `money`, `real`, and `double precision`. - **Dates and times:** `date`, `time`, `timetz`, `timestamp`, `timestamptz`, and `interval`. - **Text and documents:** `varchar`, `char`, `text`, `json`, `jsonb`, `xml`, `uuid`, enum types, `tsvector`, and `hstore` when the extension is installed. - **Binary, network, geometric, and ranges:** `bytea` and bit strings, `inet`, `cidr`, and `macaddr`, the geometric types, and the range types, plus arrays. ### What your WarehousePG admin sets up The agent does not need a superuser. It needs a role that can log in to the coordinator and create its helper schema and tables. Full statements and settings are in the [Greenplum target setup guide, which covers WarehousePG ↗](https://docs.molo17.com/gluesync/latest/agents/greenplum-target.html). 1. Create a login role for Gluesync and grant `CONNECT` and `CREATE` on the database, and `USAGE` and `CREATE` on the target schema. The `CREATE` on the database lets the agent create its `GLUESYNC` helper schema. 2. For the `gpfdist` load, add one role attribute, the same one you would use for any `gpfdist` reader: `ALTER ROLE gluesync CREATEEXTTABLE (type='readable', protocol='gpfdist');` 3. Allow the Core Hub host in the coordinator's `pg_hba.conf` and open the coordinator port, 5432 by default. 4. For the `gpfdist` load, open port 8580, the default, from every segment host to Core Hub, and set the advertised host to the name or address of the Core Hub machine that the segment hosts resolve. 5. In Core Hub, enter the coordinator's host, port, database, username, and password, and choose the storage type for the tables Gluesync creates. ### TLS, Docker, and Kubernetes - **TLS for the connection:** turn on TLS for the coordinator connection and either trust the server certificate or verify it against a trust store you provide. - **TLS for gpfdist:** serve `gpfdists` from a PKCS12 keystore, with optional client certificates presented by the segments, signed by the same CA. - **Docker:** publish the `gpfdist` port of the Core Hub container, 8580 by default, and set the advertised host to the Docker host address that the segments can reach. - **Kubernetes:** expose the `gpfdist` port of the Core Hub pod with a Service or a host port and allow it in the network policies between the segment hosts and the pod. ### Architecture around Core Hub Source agents sit close to each database, and Core Hub orchestrates them through its web UI and REST APIs. A pipeline groups a source agent with the Greenplum agent and the tables it replicates, and pipeline state lives in Core Hub. Core Hub and agents deploy with Docker, Docker Compose, or Kubernetes. See [CDC streaming](/solutions/cdc-streaming/) for the wider pattern. [Explore the general CDC streaming architecture →](/solutions/cdc-streaming/) WRITE OPTIONS ## WarehousePG is served by the Greenplum agent WarehousePG shares its engine with Greenplum, so it has no separate Gluesync agent: the Greenplum agent is the one that writes to it. Every Gluesync source agent can feed it, and each entity follows the same snapshot-then-CDC path, with batches or parallel bulk loads. | Agent | Write technique | Versions | Best for | | --- | --- | --- | --- | | [Greenplum agent ↗](https://docs.molo17.com/gluesync/latest/agents/greenplum-intro.html) | Bundled PostgreSQL JDBC driver over the PostgreSQL wire protocol; optimized batches, or a bulk path that loads through an embedded `gpfdist` server (every primary segment in parallel) or `COPY`, merged through a staging table in one transaction | WarehousePG and Greenplum Database, 6.x and 7.x compatible | WarehousePG clusters fed from Oracle, SQL Server, PostgreSQL, MySQL, IBM i, MongoDB, and other sources, where snapshots and changes should load in parallel across segments into tables distributed by key and stored append-optimized. Pick it whenever WarehousePG is the destination. | SOURCES AND TOPOLOGIES ## Feed WarehousePG from the systems of record you already run Any Gluesync source agent can feed WarehousePG. Open the [integrations finder with WarehousePG pre-selected](/integrations/?target=WarehousePG#integration-finder) to see every source you can pair with it. Running Greenplum as well? The same agent writes to it too: see [Replicate to Greenplum](/solutions/replicate-to-greenplum/). One Core Hub runs Oracle to WarehousePG, SQL Server to WarehousePG, and PostgreSQL to WarehousePG side by side, with the same snapshot, monitoring, and duplicate-key settings for each. Gluesync keeps pace with your change volume at any scale, and [MOLO17 Professional Services](/solutions/professional-services/) can help plan distribution, storage, and the write path for each table with your team. - Oracle to WarehousePG from the redo logs: see [Oracle CDC](/solutions/oracle-cdc/) - SQL Server to WarehousePG through Change Data Capture or Change Tracking: see [SQL Server CDC](/solutions/sql-server-cdc/) - PostgreSQL and MySQL to WarehousePG from the WAL and the binlog: see [PostgreSQL CDC](/solutions/postgresql-cdc/) and [MySQL CDC](/solutions/mysql-cdc/) - IBM i (AS/400) and MongoDB to WarehousePG through the native journal APIs and Change Streams: see [IBM i CDC](/solutions/ibm-i-cdc/) and [MongoDB CDC](/solutions/mongodb-cdc/) FAIR, HIGH-LEVEL COMPARISON ## Where Gluesync fits among WarehousePG ingestion approaches | Approach | What buyers usually get | Where Gluesync fits | | --- | --- | --- | | gpload, gpfdist, and the Greenplum Streaming Server | The loaders WarehousePG inherits from Greenplum, bringing files or Kafka topics in through external tables; getting changes out of each source database and merging them into current-state tables is a component you build and run | Gluesync captures from each source's native log and runs the gpfdist load itself, with the merge into the target table in the same transaction, so no loader job sits between the source and WarehousePG | | EDB tooling around WarehousePG | Cluster deployment, management, and support from EDB, which keep the warehouse running; moving data in from your operational databases is left to the loader you add | Gluesync is the ingestion layer beside it: it connects to the coordinator like any client, so it fits clusters run with EDB tooling or self-managed, and adds capture, snapshots, and monitoring from every source in Core Hub | | Fivetran, Airbyte, and other ELT tools | Managed or open-source connectors that load on a sync schedule; WarehousePG support and change capture vary by product and source connector | Native capture per engine with a commercial agent per source and MOLO17 enterprise support, writing continuously into WarehousePG; see [warehouse sync](/solutions/warehouse-sync/) | | Debezium, Kafka Connect, and a JDBC sink connector | Open-source capture into Kafka topics, loaded by a sink connector; you run Kafka, Connect, offsets, and schemas, and usually add a step that merges change events into current-state tables | Changes applied to WarehousePG tables by key, with no Kafka cluster in the path, while Kafka stays available as another target. See the [Debezium alternative](/solutions/debezium-alternative/) page | | DIY scheduled ELT and scripts | Full control; your team owns extract queries, file staging, merge logic, retries, and the load each run puts on production | Log-based capture, snapshot seeding, and Core Hub monitoring without pipeline code to maintain; read [batch ETL vs real-time data replication](/blog/batch-etl-vs-real-time-data-replication/) | RELATED CONTENT ## WarehousePG replication research and implementation detail - [Replicate to Greenplum](/solutions/replicate-to-greenplum/) - [Batch ETL vs real-time data replication: how to choose](/blog/batch-etl-vs-real-time-data-replication/) - [Log-based CDC explained](/blog/real-time-replication-log-based-cdc/) - [Warehouse sync: keep cloud warehouses synchronized with operations](/solutions/warehouse-sync/) - [Debezium alternative: managed CDC versus Kafka + Debezium](/solutions/debezium-alternative/) - [Replicate to PostgreSQL](/solutions/replicate-to-postgresql/) - [Greenplum agent overview (covers WarehousePG) ↗](https://docs.molo17.com/gluesync/latest/agents/greenplum-intro.html) - [Greenplum target setup guide (covers WarehousePG) ↗](https://docs.molo17.com/gluesync/latest/agents/greenplum-target.html) - [Bulk load: staging, COPY, and apply phases ↗](https://docs.molo17.com/gluesync/latest/core-hub/bulk-load.html) - [Write strategies: batch mode and bulk mode ↗](https://docs.molo17.com/gluesync/latest/core-hub/write-strategies.html) FAQ ## WarehousePG replication questions What does replicating to WarehousePG with Gluesync involve? A source agent captures committed changes from your database through its native change mechanism, Core Hub routes them, and the Greenplum agent applies them to WarehousePG tables continuously after a snapshot seeds each table. What is WarehousePG, and how does Gluesync support it? WarehousePG is EDB's Apache 2 licensed, open-source fork of Greenplum Database, binary compatible with Greenplum 6.x and 7.x. It keeps the same wire protocol, catalog, segments, external tables, and gpfdist, so the Gluesync Greenplum agent writes to it exactly as it writes to Greenplum. Is there a separate WarehousePG agent? No. WarehousePG is served by the Greenplum agent, with the same setup, write paths, and options. You choose WarehousePG as the target in the integrations finder, and the pipeline uses the Greenplum agent. Can I keep my gpfdist setup when moving from Greenplum to WarehousePG? Yes. gpfdist works the same way on WarehousePG, and the Greenplum agent runs an embedded gpfdist server of its own. Every primary segment pulls its share of each load in parallel, so the load grows with the number of segments. How does Gluesync write data into WarehousePG? Through the bundled PostgreSQL JDBC driver over the PostgreSQL wire protocol, with the PostgreSQL target's type mapping. Changes go in optimized batches, and a bulk path loads a spool file into a staging table, through gpfdist or COPY, and merges it into the target table in one transaction. Does the agent need a superuser? No. It needs a login role with CONNECT and CREATE on the database, USAGE and CREATE on the target schema, and, for the gpfdist load, the CREATEEXTTABLE role attribute for readable gpfdist external tables. How are tables distributed and stored? Tables the agent creates are distributed by their primary key, and randomly when they have none. You choose heap, append-optimized row, or append-optimized column storage, with ZSTD or ZLIB compression and a configurable compression level. You can edit the generated CREATE TABLE statement to distribute by another key. What network access does the gpfdist load need? Core Hub reaches the coordinator on port 5432 by default, and every segment host reaches Core Hub on the gpfdist port, 8580 by default, at the advertised host you set. Docker and Kubernetes deployments publish that port from the Core Hub container or pod. Is the traffic encrypted? Yes, both parts can be. TLS is available for the connection to the coordinator, with an option to verify the server certificate against your trust store, and for the gpfdist load as gpfdists, with optional client certificates for the segments. Which sources can replicate to WarehousePG? Any Gluesync source agent, including Oracle, SQL Server, PostgreSQL, MySQL, IBM Db2 for i, Db2 for LUW, SAP HANA, SAP ASE, and MongoDB. The integrations finder on our website lists every pairing. REPLICATE TO A TARGET ## Other targets Gluesync delivers to - [Replicate to Aerospike](/solutions/replicate-to-aerospike/) - [Replicate to DynamoDB](/solutions/replicate-to-dynamodb/) - [Replicate to Redshift](/solutions/replicate-to-redshift/) - [Replicate to Amazon S3 & S3-compatible](/solutions/replicate-to-amazon-s3/) - [Replicate to Cassandra](/solutions/replicate-to-cassandra/) - [Replicate to Kafka](/solutions/replicate-to-kafka/) - [Replicate to Cosmos DB](/solutions/replicate-to-cosmos-db/) - [Replicate to Azure Data Lake](/solutions/replicate-to-azure-data-lake/) - [Replicate to ClickHouse](/solutions/replicate-to-clickhouse/) - [Replicate to CockroachDB](/solutions/replicate-to-cockroachdb/) - [Replicate to Couchbase](/solutions/replicate-to-couchbase/) - [Replicate to Databricks](/solutions/replicate-to-databricks/) - [Replicate to Elasticsearch and OpenSearch](/solutions/replicate-to-elasticsearch/) - [Replicate to file stores](/solutions/replicate-to-file-stores/) - [Replicate to BigQuery](/solutions/replicate-to-bigquery/) - [Replicate to Google Cloud Storage](/solutions/replicate-to-google-cloud-storage/) - [Replicate to Google Pub/Sub](/solutions/replicate-to-google-pubsub/) - [Replicate to Greenplum](/solutions/replicate-to-greenplum/) - [Replicate to GridGain](/solutions/replicate-to-gridgain/) - [Replicate to Db2](/solutions/replicate-to-db2/) - [Replicate to Informix](/solutions/replicate-to-informix/) - [Replicate to MariaDB](/solutions/replicate-to-mariadb/) - [Replicate to Microsoft Fabric](/solutions/replicate-to-microsoft-fabric/) - [Replicate to SQL Server](/solutions/replicate-to-sql-server/) - [Replicate to MongoDB](/solutions/replicate-to-mongodb/) - [Replicate to MySQL](/solutions/replicate-to-mysql/) - [Replicate to Oracle](/solutions/replicate-to-oracle/) - [Replicate to PostgreSQL](/solutions/replicate-to-postgresql/) - [Replicate to RavenDB](/solutions/replicate-to-ravendb/) - [Replicate to Redis](/solutions/replicate-to-redis/) - [Replicate to Redis Streams](/solutions/replicate-to-redis-streams/) - [Replicate to SAP ASE](/solutions/replicate-to-sap-ase/) - [Replicate to SAP HANA](/solutions/replicate-to-sap-hana/) - [Replicate to ScyllaDB](/solutions/replicate-to-scylladb/) - [Replicate to SingleStore](/solutions/replicate-to-singlestore/) - [Replicate to Snowflake](/solutions/replicate-to-snowflake/) - [Replicate to Solace PubSub+](/solutions/replicate-to-solace/) - [Replicate to Supabase](/solutions/replicate-to-supabase/) - [Replicate to Vertica](/solutions/replicate-to-vertica/) - [Replicate to YugabyteDB](/solutions/replicate-to-yugabytedb/) ## Evaluate Gluesync with your WarehousePG cluster Start a trial against your WarehousePG cluster, or talk to MOLO17 about your sources, the segment layout you load into, and the distribution and storage each table should use. [Start a Gluesync trial](/get-gluesync/) [Talk to MOLO17](/contacts/)