Set up an Amazon Redshift target with S3 bulk loads

Create the user and a schema that the user owns. The agent creates the target tables there, and an owner can create, change, truncate and drop its own tables:

CREATE USER gluesync_user PASSWORD '<a strong password>';
CREATE SCHEMA gluesync_data AUTHORIZATION gluesync_user;

Let the user create its staging tables. The bulk path creates one staging table for each entity, and it creates it without a schema name. The table lands in the first schema of the search path of the user, which is public with the default search path (in the test, a staging table named pu_…​ appeared in public). Grant CREATE on that schema:

GRANT USAGE, CREATE ON SCHEMA public TO gluesync_user;

If you load into tables that already exist, and the user does not own them, grant the rights on each of them:

GRANT SELECT, INSERT, UPDATE, DELETE ON <schema>.<table> TO gluesync_user;

Check what the user can do:

SELECT has_schema_privilege('gluesync_user', 'gluesync_data', 'CREATE') AS data_schema,
       has_schema_privilege('gluesync_user', 'public', 'CREATE') AS staging_schema;

Create the bucket, if you do not have one:

aws s3api create-bucket --bucket <bucket> --region eu-central-1 \
  --create-bucket-configuration LocationConstraint=eu-central-1

Attach this policy to the IAM user whose access key you will give to Gluesync:

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Action": "s3:ListBucket",
      "Resource": "arn:aws:s3:::<bucket>"
    },
    {
      "Effect": "Allow",
      "Action": ["s3:PutObject", "s3:GetObject"],
      "Resource": "arn:aws:s3:::<bucket>/gluesync-bulk/*"
    }
  ]
}

The agent never deletes the files it writes, so add a lifecycle rule that removes them. Without it the bucket grows with every bulk load:

aws s3api put-bucket-lifecycle-configuration --bucket <bucket> --lifecycle-configuration '{
  "Rules": [{
    "ID": "expire-gluesync-bulk",
    "Status": "Enabled",
    "Filter": {"Prefix": "gluesync-bulk/"},
    "Expiration": {"Days": 1}
  }]
}'

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

In 1. Setup source, choose the PostgreSQL CDC agent tile and fill Base settings with your source database. Select Test and continue, then Next. The PostgreSQL WAL source guide prepares the source;

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

Under Security, set Enable TLS to encrypt the connection. It is off by default. With Enable TLS on and Trust server certificate off, the connection to Redshift Serverless was accepted and live changes kept flowing;

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

In the pipeline, select Entities. The Add entities page opens with the Object browser. Expand the source schema public, select the check box of the table events, and then select the mapping icon of the table in the Selected entities list. The Field mapping dialog opens;

Under Target, choose the Target Schema. In Target Table type the table name and choose Create table "<name>". The dialog shows New table <name> will be created on confirm. and a proposed target type for every column;

The text column shows the error Missing required field, because its Length is 0. Read the next section before you set it;

Select Confirm. The Create table dialog shows the Read-only SQL statement. Select Create table <name> to run it in Redshift;

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

For uuid and inet, the proposal is varchar with Length 0. Set Length to 36 for uuid and to 45 for inet;

For jsonb and json, the proposal is varchar. To query the document with the Redshift SUPER functions, choose super as Target type and set Length to -1. In the test, c_json.a returned the value of the key a, and is_object(c_json) returned true;

For bytea and for array columns, clear the check box of the column in the Field mapping dialog, so that Gluesync does not copy it. The proposal is varbyte, and an array shows a Data type mismatch detected warning. Step 8 shows the error that you get if you keep them.

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;

Under Speed select Custom. In the Writing column, under During the copy, select Bulk load. The diagram shows Bulk and a Staging table;

Select Start snapshot.

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. Under Writing the dialog shows Standard and This target has no bulk load: live changes always use standard writes;

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

INSERT INTO events VALUES (200001, 'new', 1, 'cdc insert', 12.34, true, now(), now(), current_date);
UPDATE events SET val = -1, note = 'cdc update' WHERE id = 5;
DELETE FROM events WHERE id = 6;

After a few seconds, read the three keys and the count in Redshift:

SELECT id, kind, val, note FROM <schema>.events WHERE id IN (5, 6, 200001) ORDER BY id;
SELECT count(*), count(DISTINCT id) FROM <schema>.events;

Set AWS S3 bucket name (used by bulk operations) to a name that does not exist. The banner says The connection was saved, but agent <id> of pipeline <id> did not accept the new credentials: S3 bucket '<bucket>' not found. Please verify the bucket name. Fix: type the right name;

Set a wrong AWS Secret Access Key for S3. The banner says …​ Access denied to S3 bucket '<bucket>'. Please check your AWS credentials and bucket permissions. Fix: use the key of the IAM user of step 2, and check its policy;

Set AWS Region for S3 bucket to a region other than the one of the bucket. The banner says …​ S3 error (status 400) while accessing bucket '<bucket>': Bad Request (Service: S3, Status Code: 400, Request ID: <id>, Extended Request ID: <id>). Fix: use the region of the bucket;

Add a source row whose text column is longer than the target column, with a standard write. For note as varchar(256), a 300-character value puts the entity in the Error group, with the notification SQL Error and ERROR: Value too long for character type. The log has context: Value too long for type character varying(256). Fix: widen the column in Redshift. The entity retries on its own and no restart is needed. In the test, the row was in Redshift within 45 seconds of the ALTER:

ALTER TABLE <schema>.events ALTER COLUMN note TYPE varchar(2000);

Load the same value with Bulk load and there is no error: the COPY command runs with TRUNCATECOLUMNS, which cuts the value to the size of the column. In the test, a 300-character value arrived as 256 characters, and the row count matched. Compare the longest value on both sides after a bulk load, and widen a column before the load if the source can hold more;

Keep a bytea or an array column in the entity. Confirm and continue shows The entity could not be saved. and Unsupported type for columns <column> with type unsupported, <column> with type unsupported in entity <schema>.<table> for <schema>.<table>. Redshift reports a varbyte column as binary varying, which the agent does not know, so the column cannot be used. Fix: clear the check box of these columns in the Field mapping dialog. Mapping them to a varchar is refused with Incompatible data types;

To delete an entity, pause it first: Delete stays disabled while it runs. Deleting an entity drops its staging table, and leaves the target table and the files in S3.