Canonical: https://molo17.com/solutions/replicate-to-greenplum/ Markdown mirror: https://molo17.com/solutions/replicate-to-greenplum/index.md Title: Replicate to Greenplum in real time with Gluesync | MOLO17 Description: Real-time replication to Greenplum 6 and 7 from Oracle, SQL Server, PostgreSQL, MySQL, and MongoDB, with gpfdist bulk loads across segments. Start a trial. [Solutions](/solutions/) Replicate to Greenplum SOLUTION · REPLICATE TO GREENPLUM # Real-time data replication to Greenplum from your operational databases **Committed changes from your systems of record land in Greenplum continuously, loaded in parallel by every primary segment instead of squeezed through the coordinator.** Gluesync by MOLO17 captures changes from Oracle, SQL Server, PostgreSQL, MySQL, IBM i, MongoDB, and other [heterogeneous sources](/integrations/?target=Greenplum#integration-finder) with a dedicated agent per database, then writes them into Greenplum Database 6 and 7. The Greenplum agent speaks the PostgreSQL wire protocol, loads through an embedded `gpfdist` server for the bulk path, and creates tables with the distribution and storage Greenplum is built around. 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 Greenplum and WarehousePG 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. - **Greenplum** Target Tested - **WarehousePG** Target Tested WHO THIS IS FOR ## Teams that need Greenplum to reflect operations now, not after the nightly load - Data warehouse teams running Greenplum who model current-state tables for reporting and need them keyed like the source, with duplicate keys resolved by a per-entity setting instead of a hand-written merge job - 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) - Greenplum 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 - Engineers replacing `gpload` scripts, Kafka Connect sinks, or scheduled ELT jobs that fill Greenplum, who want [every source](/integrations/?target=Greenplum#integration-finder) delivered by one product with snapshots and monitoring built in THE PROBLEM ## Greenplum data that arrives in nightly loads is already behind Most Greenplum clusters are fed 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 Greenplum ingestion or a Greenplum CDC pipeline usually weigh managed ELT from 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 Greenplum**. 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 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 Greenplum ### The PostgreSQL write path, with Greenplum's connection defaults Greenplum speaks the PostgreSQL wire protocol, so the Greenplum agent is the PostgreSQL target with Greenplum's connection defaults, a bulk path built for segments, and table creation that sets distribution and storage. It connects to the coordinator through the bundled PostgreSQL JDBC driver, on Greenplum Database 6.x and 7.x, and applies the change stream from any Gluesync source agent after a snapshot seeds each table. - **Same type mapping:** values are written with the PostgreSQL target's type mapping, including enum types, arrays, and `hstore`. - **TRUNCATE before snapshot:** the Core Hub option for a clean reload is honored on the target tables. - **Database connection only:** the agent needs a connection to the coordinator and calls no Greenplum management API. [Greenplum agent overview ↗](https://docs.molo17.com/gluesync/latest/agents/greenplum-intro.html) ### 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 use the one that suits them. Both run on Greenplum 6 and 7 alike. - **Batch path:** optimized JDBC batches of `INSERT` and `DELETE` statements. An upsert is an `UPDATE … FROM` followed by an `INSERT` of the keys that were not updated. - **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 The bulk load method decides how the file reaches Greenplum, and the default chooses for you. - **`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 Greenplum 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, with the distribution policy and storage it already has. - **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. - **Greenplum's own schemas stay out of the way:** `gp_toolkit` and the other schemas Greenplum 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 Greenplum 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 Greenplum 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 ↗](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, with the same syntax on Greenplum 6 and 7: `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 ## The Greenplum target agent Greenplum has one Gluesync target agent. 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 | Greenplum Database 6.x and 7.x; tested on 6.27 and 7.1, each with two primary segments | Greenplum 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 Greenplum is the destination. | SOURCES AND TOPOLOGIES ## Feed Greenplum from the systems of record you already run Any Gluesync source agent can feed Greenplum. Open the [integrations finder with Greenplum pre-selected](/integrations/?target=Greenplum#integration-finder) to see every source you can pair with it. One Core Hub runs Oracle to Greenplum, SQL Server to Greenplum, and PostgreSQL to Greenplum 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 Greenplum from the redo logs: see [Oracle CDC](/solutions/oracle-cdc/) - SQL Server to Greenplum through Change Data Capture or Change Tracking: see [SQL Server CDC](/solutions/sql-server-cdc/) - PostgreSQL and MySQL to Greenplum 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 Greenplum through the native journal APIs and Change Streams: see [IBM i CDC](/solutions/ibm-i-cdc/) and [MongoDB CDC](/solutions/mongodb-cdc/) - WarehousePG, EDB's open-source Greenplum fork, written by the same agent: see [Replicate to WarehousePG](/solutions/replicate-to-warehousepg/) FAIR, HIGH-LEVEL COMPARISON ## Where Gluesync fits among Greenplum ingestion approaches | Approach | What buyers usually get | Where Gluesync fits | | --- | --- | --- | | Fivetran, Airbyte, and other ELT tools | Managed or open-source connectors that load on a sync schedule; Greenplum 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 Greenplum; 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 Greenplum 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 | | gpload, gpfdist, and the Greenplum Streaming Server | Native loaders that bring files or Kafka topics into Greenplum 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 Greenplum | | AWS DMS, Qlik Replicate, and similar replication services | Mature replication products with broad target lists; Greenplum support and load method vary by product | Agents that run on premises or in any cloud with Docker, Docker Compose, or Kubernetes and write straight to Greenplum; see [migrating to Gluesync](/migrate-to-gluesync/) | | 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 ## Greenplum replication research and implementation detail - [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/) - [Replicate to WarehousePG](/solutions/replicate-to-warehousepg/) - [Greenplum agent overview ↗](https://docs.molo17.com/gluesync/latest/agents/greenplum-intro.html) - [Greenplum target setup guide ↗](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 ## Greenplum replication questions What does replicating to Greenplum 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 Greenplum tables continuously after a snapshot seeds each table. How does Gluesync write data into Greenplum? 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. What is gpfdist loading and why does it matter? The agent runs an embedded gpfdist server and loads each flush through a temporary readable external table, so every primary segment pulls its share of the data in parallel and the load grows with the number of segments. COPY streams the same file through the coordinator. The default chooses gpfdist when the segments can reach Core Hub and the load is large enough to benefit. Which Greenplum versions are supported? Greenplum Database 6.x and 7.x. MOLO17 tests the agent against Greenplum Database 6.27 and 7.1, each with two primary segments. Does the agent need a Greenplum 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. The same ALTER ROLE statement works on Greenplum 6 and 7. 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. Can Gluesync also write to WarehousePG? Yes. WarehousePG, EDB's open-source fork of Greenplum Database, is binary compatible with Greenplum 6.x and 7.x, and the same Greenplum agent writes to it exactly as it writes to Greenplum. The Replicate to WarehousePG page has the details. Which sources can replicate to Greenplum? 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 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 WarehousePG](/solutions/replicate-to-warehousepg/) - [Replicate to YugabyteDB](/solutions/replicate-to-yugabytedb/) ## Evaluate Gluesync with your Greenplum cluster Start a trial against your Greenplum 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/)