Set up a Greenplum target with gpfdist bulk loads

Create the user and let it connect and create objects:

CREATE ROLE gluesync LOGIN PASSWORD '<a strong password>';
GRANT CONNECT, CREATE ON DATABASE <database> TO gluesync;
CREATE SCHEMA <schema>;
GRANT USAGE, CREATE ON SCHEMA <schema> TO gluesync;

Let the user create the external tables that gpfdist reads. The statement is the same on Greenplum 6 and 7:

ALTER ROLE gluesync CREATEEXTTABLE (type='readable', protocol='gpfdist');

On Greenplum 7 only, let the user read the catalog view that lists external tables. Schema discovery uses it to hide external tables from the object browser:

GRANT SELECT ON pg_catalog.pg_exttable TO gluesync;

Check the segments that will pull the rows:

SELECT content, role, port FROM gp_segment_configuration ORDER BY content, role;

Choose the gpfdist port. The default is 8580. Open it between every segment host and Core Hub, in the host firewall and in the network firewall. The port that the segments use must be the port Core Hub listens on, so do not translate it;

Choose the name that the segments use to reach Core Hub. It must resolve to the Core Hub machine from every segment host. A container’s own host name often does not resolve from another host, so set a name or an address that does;

If Core Hub runs in Docker, publish the port and use the address of the Docker host:

services:
  core-hub:
    ports:
      - "8580:8580"

From a segment host, check that the name resolves and the port answers. Replace <core hub host>:

getent hosts <core hub host>
nc -zv <core hub host> 8580

In the Control Plane, select Create your first pipeline, or the + button of the pipeline list if you already have pipelines. The Agents setup wizard opens;

In 1. Setup source, choose the PostgreSQL CDC agent tile and fill Base settings: Host name / Address, Port, Database name, Username and Password of your source database. Select Test and continue. The source appears with a green check mark. Select Next;

In 2. Setup target(s), choose the Greenplum agent tile and fill Base settings:

In the same tab, set the three fields that matter for gpfdist:

Select Test and continue. The target appears with a green check mark. Select Next, enter a Pipeline name and select Finish.

In the pipeline list, select the Greenplum icon of the pipeline. The Edit target(s) page opens. Select Advanced settings;

Select Add property three times and add the keys Storage type of the tables the agent creates, Compression of the append-optimized tables the agent creates and Compression level (1 to 19 for ZSTD, 1 to 9 for ZLIB);

Set the values to AO_COLUMN, ZSTD and 3. The properties start as HEAP, ZSTD and 1;

Select Test and continue.

In the pipeline, select Entities. The Add entities page opens with the Object browser. Expand the source schema public and select the table events. The Field mapping dialog opens;

Under Target, choose the Target Schema: clear the current value with the x first, because the list only offers names that match what is in the field, then select your Greenplum schema. In Target Table type events and choose Create table "events". The dialog shows New table events will be created on confirm. and the proposed target types;

Select Confirm. The Create table events dialog shows the Read-only SQL statement. Read it, then select Create table events to run it on Greenplum;

Back on the Add entities page, select Confirm and continue.

In the pipeline, find the entity. On Hold, select the start options button, the one next to the play button. The Snapshot (copy-only) dialog opens;

Keep Keep and merge under Data already in the target. It overwrites rows with the same key and keeps the others;

Under Speed select Custom. In the Writing column, under During the copy select Bulk load, and under During live changes select Bulk load;

Select Start snapshot, or Save without starting and start the entity later. The settings are kept for the next start.

Select the play button of the entity. The Sync changes (CDC) dialog opens. Under Start from choose Now, because the rows are already in the target, and select Start entity. The entity becomes Active;

On the source, insert, update and delete one row each:

INSERT INTO events VALUES (400001, 'new', 1);
UPDATE events SET val = -1 WHERE id = 5;
DELETE FROM events WHERE id = 6;

After a few seconds, read the three keys on Greenplum:

SELECT id, kind, val FROM <schema>.events WHERE id IN (5, 6, 400001) ORDER BY id;
SELECT count(*) FROM (SELECT id FROM <schema>.events GROUP BY id HAVING count(*) > 1) d;

Remove the Greenplum 7 catalog grant and add entities. The schema list fails and the notification Schemas Fetch shows ERROR: permission denied for view pg_exttable [SQLState=42501]. Fix: run GRANT SELECT ON pg_catalog.pg_exttable TO gluesync;;

Remove CREATEEXTTABLE from the role (ALTER ROLE gluesync NOCREATEEXTTABLE (type='readable', protocol='gpfdist');), set Bulk load method to GPFDIST and select Test and continue on the target while an entity with bulk operations exists. The page shows Failed to save: The connection was saved, but agent <id> of pipeline <id> did not accept the new credentials: The Greenplum segments cannot all reach the gpfdist server of the agent at <host>:8580: The probe query failed: ERROR: permission denied: no privilege to create a readable gpfdist(s) external table. Fix: run the ALTER ROLE of step 1;

Set gpfdist advertised host to a name that does not resolve and select Test and continue with GPFDIST. The connection is saved, and the page shows Failed to save with the same text and ERROR: hostname cannot be resolved 'gpfdist://<name>:8580/…​'. Fix: set a name that every segment host resolves;

Do the same with AUTO. The agent accepts the connection and raises the warning notification gpfdist is not reachable, with the same text. Bulk flushes then load with COPY: the log shows COPY in the flush line. Fix the name, and select Test and continue again to run the check again.