Can't connect to Redshift cluster

Platform
1

I have created redshift cluster with following configurations, but I am not able to connect to it using psql

Docker-compose file:
version: "3.7"
  
services:

    localstack:
        image: localstack/localstack:2.2.0
        container_name: localstack
        environment:
            - SERVICES=s3,secretsmanager,kms,iam,lambda,redshift
            - DEBUG=1
            - AWS_ACCESS_KEY_ID=test
            - AWS_SECRET_ACCESS_KEY=test
            - DOCKER_HOST=unix:///var/run/docker.sock
            - DATA_DIR=/tmp/localstack/data
            - PERSISTENCE=1
        ports:
            - "4566:4566"            # LocalStack Gateway
            - "4510-4559:4510-4559"  # external services port range
        volumes:
            - "${LOCALSTACK_VOLUME_DIR:-./volume}:/var/lib/localstack"
            - "/var/run/docker.sock:/var/run/docker.sock"

I created cluster using command:-
aws --endpoint-url http://localhost:4566 redshift create-cluster --db-name my_redshift_db --cluster-identifier my_redshift_identifier1 --node-type ds2.xlarge --master-username abcdef --master-user-password def12345678 --publicly-accessible

{
    "Cluster": {
        "ClusterIdentifier": "my_redshift_identifier1",
        "NodeType": "ds2.xlarge",
        "ClusterStatus": "creating",
        "MasterUsername": "abcdef",
        "DBName": "my_redshift_db",
        "Endpoint": {
            "Address": "my_redshift_identifier1.cg034hpkmmjt.us-east-1.redshift.amazonaws.com",
            "Port": 5439
        },
        "ClusterCreateTime": "2024-06-06T05:53:25.075000Z",
        "AutomatedSnapshotRetentionPeriod": 1,
        "ClusterSecurityGroups": [
            {
                "ClusterSecurityGroupName": "Default",
                "Status": "active"
            }
        ],
        "VpcSecurityGroups": [],
        "ClusterParameterGroups": [
            {
                "ParameterGroupName": "default.redshift-1.0",
                "ParameterApplyStatus": "in-sync"
            }
        ],
        "ClusterSubnetGroupName": "",
        "AvailabilityZone": "us-east-1a",
        "PreferredMaintenanceWindow": "Mon:03:00-Mon:03:30",
        "PendingModifiedValues": {},
        "ClusterVersion": "1.0",
        "AllowVersionUpgrade": true,
        "NumberOfNodes": 1,
        "PubliclyAccessible": true,
        "Encrypted": false,
        "Tags": [],
        "KmsKeyId": "",
        "EnhancedVpcRouting": false,
        "IamRoles": []
    }
}

I also mapped my_redshift_identifier1.cg034hpkmmjt.us-east-1.redshift.amazonaws.com with 127.0.0.1 in /etc/hosts

Still I am getting error:-
psql: error: could not translate host name "my_redshift_identifier1.cg034hpkmmjt.us-east-1.redshift.amazonaws.com" to address: Name or service not known

psql -h my_redshift_identifier1.cg034hpkmmjt.us-east-1.redshift.amazonaws.com -U abcdef -p 5439

Where I am doing it wrong?

2

Hi — The error is because you’re trying to connect to the real AWS cloud rather than the emulated Redshift cluster running on your local machine.

For a better developer experience, I would suggest to upgrade to the latest tagged version (3.4) by swapping localstack/localstack:2.2.0 with localstack/localstack:3.4.0 and run the following command:

aws --endpoint-url http://localhost:4566 redshift create-cluster \
    --db-name my_redshift_db \
    --cluster-identifier my_redshift_identifier1 \
    --node-type ds2.xlarge \
    --master-username abcdef \
    --master-user-password def12345678 \
    --publicly-accessible

In the output you will see:

{
    "Cluster": {
        "ClusterIdentifier": "my_redshift_identifier1",
        "NodeType": "ds2.xlarge",
        "ClusterStatus": "creating",
        "MasterUsername": "test",
        "DBName": "my_redshift_db",
        "Endpoint": {
            "Address": "my_redshift_identifier1.fdjawcaqluxs.us-east-1.localhost.localstack.cloud",
            "Port": 4510
        },
        "ClusterCreateTime": "2024-06-06T06:51:20.498000Z",
...

You can connect using the following command:

psql -h my_redshift_identifier1.fdjawcaqluxs.us-east-1.localhost.localstack.cloud -p 4510

I hope this helps! Let us know if you encounter further issues :slight_smile:

3

Using localstack version 3.4.0 but still facing same issue as original one

image
image1611×950 53.1 KB

4

Hi @ahyan2328,

Here is a simple script based on Harsh reply, that works as expected:

aws --endpoint-url http://localhost:4566 redshift create-cluster \
    --db-name my_redshift_db \
    --cluster-identifier my_redshift_identifier1 \
    --node-type ds2.xlarge \
    --master-username abcdef \
    --master-user-password def12345678 \
    --publicly-accessible \
    --region ap-southeast-1

sleep 5
ENDPOINT_ADDRESS=$(aws --endpoint-url http://localhost:4566 redshift describe-clusters --region ap-southeast-1 --query "Clusters[0].Endpoint.Address" --output text)
ENDPOINT_PORT=$(aws --endpoint-url http://localhost:4566 redshift describe-clusters --region ap-southeast-1 --query "Clusters[0].Endpoint.Port" --output text)

PGDATABASE=my_redshift_db PGUSER=abcdef PGPASSWORD=def12345678 psql -h $ENDPOINT_ADDRESS -p $ENDPOINT_PORT -c "CREATE TABLE my_table (id INT PRIMARY KEY, name VARCHAR(255));"

PGDATABASE=my_redshift_db PGUSER=abcdef PGPASSWORD=def12345678 psql -h $ENDPOINT_ADDRESS -p $ENDPOINT_PORT  -c "INSERT INTO my_table (id, name) VALUES (1, 'Alice');" 

PGDATABASE=my_redshift_db PGUSER=abcdef PGPASSWORD=def12345678 psql -h $ENDPOINT_ADDRESS -p $ENDPOINT_PORT  -c "SELECT * FROM my_table;"