Amazon redshift glyph screenshot RedShift agent

Target RedShift

Prerequisites

To have Gluesync working with your Amazon RedShift instance you will need:

  • Valid AWS credentials with permissions to access RedShift and S3;

  • RedShift cluster endpoint or Serverless workgroup URL;

  • Database name and credentials with read/write access to target tables;

  • S3 bucket for staging bulk load files;

  • IAM role or access keys with the following permissions:

    • RedShift: redshift:GetClusterCredentials, redshift:DescribeClusters

    • S3: s3:PutObject, s3:GetObject, s3:DeleteObject on the staging bucket

Creating an IAM policy for Gluesync

If you don’t have an IAM user with the required permissions, you can create one using the following IAM policy:

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Action": [
        "redshift:GetClusterCredentials",
        "redshift:DescribeClusters"
      ],
      "Resource": "*"
    },
    {
      "Effect": "Allow",
      "Action": [
        "s3:PutObject",
        "s3:GetObject",
        "s3:DeleteObject"
      ],
      "Resource": "arn:aws:s3:::your-staging-bucket/*"
    }
  ]
}

Creating a RedShift user with the required permissions

If you don’t have a database user with the required permissions, you can create one by running the following SQL commands in RedShift:

CREATE USER gluesync_user PASSWORD '<REDACTED>';

-- Grant schema usage and table permissions
GRANT USAGE ON SCHEMA your_schema TO gluesync_user;
GRANT CREATE ON SCHEMA your_schema TO gluesync_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA your_schema TO gluesync_user;

-- Ensure future tables get permissions automatically
ALTER DEFAULT PRIVILEGES IN SCHEMA your_schema
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO gluesync_user;

Setup via Web UI

  • Hostname / IP Address: RedShift cluster endpoint (e.g., cluster-name.region.redshift.amazonaws.com);

  • Port: RedShift port (default: 5439);

  • Database name: Name of your target database;

  • Username: Database username with read & write access to target tables;

  • Password: Password belonging to the given username;

  • AWS Access Key ID: IAM user access key for S3 operations;

  • AWS Secret Access Key: IAM user secret key for S3 operations;

  • S3 Staging Bucket: S3 bucket name for bulk load staging files;

  • S3 Region: AWS region where the S3 bucket is located;

  • Enable TLS: Recommended for secure connections (default: enabled).

Custom properties

  • Schema: (defaults to public) The target schema where tables will be created/updated;

  • Warehouse: (provisioned clusters only) The RedShift warehouse/cluster name for query execution;

  • Workgroup: (Serverless only) The RedShift Serverless workgroup name;

  • S3 Key Prefix: (optional) Prefix path for staging files in the S3 bucket;

  • Copy Options: (optional) Additional options for the RedShift COPY command (e.g., DELIMITER ',' REMOVEQUOTES);

Specific configuration

This agent supports the following specific configuration properties:

Property Default Description

s3StagingBucket

required

The S3 bucket name used for staging CSV files during bulk loads.

s3Region

required

The AWS region where the S3 bucket is located (e.g., us-east-1).

s3KeyPrefix

gluesync-staging/

Optional prefix path for staging files within the bucket.

awsAccessKeyId

required

AWS IAM access key ID for S3 operations.

awsSecretAccessKey

required

AWS IAM secret access key for S3 operations.

schema

public

The target schema in RedShift.

workgroup

(Serverless only)

RedShift Serverless workgroup name (required for Serverless, omit for provisioned clusters).

copyOptions

DELIMITER ',' REMOVEQUOTES ESCAPE

Additional COPY command options for fine-tuning bulk loads.

Setup via Rest APIs

Here following an example of calling the Core Hub’s Rest API via curl to setup the connection for this Agent.

Connect the agent

curl --location --request PUT 'http://core-hub-ip-address/pipelines/{pipelineId}/agents/{agentId}/config/credentials' \
--header 'Content-Type: application/json' \
--header 'Authorization: ••••••' \
--data '{
  "hostCredentials": {
    "connectionName": "myRedshiftAgent",
    "host": "cluster-name.region.redshift.amazonaws.com",
    "port": 5439,
    "databaseName": "my_database",
    "username": "gluesync_user",
    "password": "<REDACTED>",
    "maxConnectionsCount": 50,
    "enableTls": true
  },
  "customHostCredentials": {
    "schema": "public",
    "s3StagingBucket": "my-redshift-staging-bucket",
    "s3Region": "us-east-1",
    "s3KeyPrefix": "gluesync-staging/",
    "awsAccessKeyId": "AKIAIOSFODNN7EXAMPLE",
    "awsSecretAccessKey": "wJalrXUtnFEMI/K7MDENG/bPxRfiCYEXAMPLEKEY",
    "workgroup": "my-workgroup",
    "copyOptions": "DELIMITER ',' REMOVEQUOTES ESCAPE"
  }
}'

Bulk load workflow

The RedShift agent implements the following bulk load workflow:

  1. Data Collection: CDC changes are collected during the mirroring cycle

  2. CSV Generation: Changes are formatted as CSV files

  3. S3 Upload: CSV files are uploaded to the configured S3 staging bucket

  4. COPY Execution: RedShift’s native COPY command loads data from S3 into staging tables

  5. Merge Apply: Staging data is merged into target tables using optimized SQL operations

  6. Cleanup: Staging files are removed from S3 after successful load

For more details on bulk load patterns, see Bulk load.

Troubleshooting

Issue Solution

S3 access denied errors

Verify IAM credentials have s3:PutObject, s3:GetObject, and s3:DeleteObject permissions on the staging bucket.

RedShift connection timeout

Check security group rules allow inbound traffic from Gluesync on port 5439. Verify the cluster is running (for provisioned clusters).

COPY command failures

Review RedShift STL_LOAD_ERRORS table for detailed error messages. Common issues include malformed CSV data or insufficient permissions.

Slow bulk load performance

Ensure the S3 bucket is in the same AWS region as the RedShift cluster. Consider using a VPC endpoint for S3 to reduce latency.