Tunneling to redshift cluster

I am new to redshift. Currently, I am able to create a redshift cluster and connect it through SQL Workbench but I am looking forward to tunnel my redshift cluster doing ssh from my MAC terminal. I did some research and able to create an ec2 instance with same VPC ID and subnet group which I am using to create my Redshift cluster with. I have already installed psql on my ec2 instance as well. I am not able to understand where I am going wrong when I use psql command to connect to redshift :

psql -h my redshift endpoint -p 5439 -d database name -U user -c " my query "

it gives me error psql: could not translate host name "my redshift endpoint" to address: Name or service not known

edited Dec 4 '18 at 8:53

DJo

1,1971533

asked Nov 19 '18 at 15:35

Saurabh Singh

Please show the command you are using to tunnel to the EC2 instance. It should involve using ssh -L to forward a remote port to a local port.

– John Rotenstein
Nov 19 '18 at 16:07

yes I am using here is the command "ssh -I "test.pem" -L 5439:redshiftendpoint:5439 ec2-user@ec2instance.compute.amazonaws.com"

– Saurabh Singh
Nov 19 '18 at 16:13

add a comment |

psql -h my redshift endpoint -p 5439 -d database name -U user -c " my query "

it gives me error psql: could not translate host name "my redshift endpoint" to address: Name or service not known

edited Dec 4 '18 at 8:53

DJo

1,1971533

asked Nov 19 '18 at 15:35

Saurabh Singh

Please show the command you are using to tunnel to the EC2 instance. It should involve using ssh -L to forward a remote port to a local port.

– John Rotenstein
Nov 19 '18 at 16:07

yes I am using here is the command "ssh -I "test.pem" -L 5439:redshiftendpoint:5439 ec2-user@ec2instance.compute.amazonaws.com"

– Saurabh Singh
Nov 19 '18 at 16:13

add a comment |

psql -h my redshift endpoint -p 5439 -d database name -U user -c " my query "

it gives me error psql: could not translate host name "my redshift endpoint" to address: Name or service not known

edited Dec 4 '18 at 8:53

DJo

1,1971533

asked Nov 19 '18 at 15:35

Saurabh Singh

psql -h my redshift endpoint -p 5439 -d database name -U user -c " my query "

it gives me error psql: could not translate host name "my redshift endpoint" to address: Name or service not known

amazon-web-services amazon-ec2 amazon-redshift ssh-tunnel tunnel

edited Dec 4 '18 at 8:53

DJo

1,1971533

asked Nov 19 '18 at 15:35

Saurabh Singh

edited Dec 4 '18 at 8:53

DJo

1,1971533

asked Nov 19 '18 at 15:35

Saurabh Singh

edited Dec 4 '18 at 8:53

DJo

1,1971533

edited Dec 4 '18 at 8:53

DJo

1,1971533

edited Dec 4 '18 at 8:53

DJo

1,1971533

asked Nov 19 '18 at 15:35

Saurabh Singh

asked Nov 19 '18 at 15:35

Saurabh Singh

asked Nov 19 '18 at 15:35

Saurabh Singh

Please show the command you are using to tunnel to the EC2 instance. It should involve using ssh -L to forward a remote port to a local port.

– John Rotenstein
Nov 19 '18 at 16:07

yes I am using here is the command "ssh -I "test.pem" -L 5439:redshiftendpoint:5439 ec2-user@ec2instance.compute.amazonaws.com"

– Saurabh Singh
Nov 19 '18 at 16:13

add a comment |

Please show the command you are using to tunnel to the EC2 instance. It should involve using ssh -L to forward a remote port to a local port.

– John Rotenstein
Nov 19 '18 at 16:07

yes I am using here is the command "ssh -I "test.pem" -L 5439:redshiftendpoint:5439 ec2-user@ec2instance.compute.amazonaws.com"

– Saurabh Singh
Nov 19 '18 at 16:13

Please show the command you are using to tunnel to the EC2 instance. It should involve using ssh -L to forward a remote port to a local port.

– John Rotenstein
Nov 19 '18 at 16:07

yes I am using here is the command "ssh -I "test.pem" -L 5439:redshiftendpoint:5439 ec2-user@ec2instance.compute.amazonaws.com"

– Saurabh Singh
Nov 19 '18 at 16:13

add a comment |

3 Answers
3

active

oldest

votes

The first step is to tunnel to the EC2 instance using ssh, with a command that forwards a local port to a remote port:

ssh -i KEYPAIR.pem -L 5439:REDSHIFT-ENDPOINT:5439 ec2-user@EC2-PUBLIC-IP

Where:

KEYPAIR.pem should be the name of the keypair used to access the EC2 instance

REDSHIFT-ENDPOINT is the DNS name of the Redshift endpoint

EC2-PUBLIC-IP is the IP address of the EC2 instance

This command says:

Create an ssh connection using the keypair

Forward any traffic sent to local port 5439 to the remote machine, then have the remote machine send that traffic to REDSHIFT-ENDPOINT:5439 (substitute your endpoint for REDSHIFT-ENDPOINT)

Then, you can connect to Redshift on localhost:5439 as if it were running on your own computer. That traffic will be sent to the remote machine, which will send it to REDSHIFT-ENDPOINT:5439.

For example, if you want to use psql to connect to Redshift, use:

psql -h localhost -p 5439 -U <username>

answered Nov 19 '18 at 16:12

John Rotenstein

71.3k781125

Do I have to change any setting in psql or ec2 instance. As I am still getting same issue with what you have suggested. Thanks.

– Saurabh Singh
Nov 19 '18 at 21:59

Please describe what happens when you perform each step, including how long it takes to respond. For example, when you run ssh, it probably will probably appear to hang. This is okay! You should then use a separate Terminal session for psql. I can't help you unless you provide more information about what is happening.

– John Rotenstein
Nov 19 '18 at 22:28

add a comment |

try psql -h localhost -p 5439 -d -U -c " my query " or psql -p 5439 -d -U -c " my query ", you can't use switch -h without an argument, which is localhost by default if you don't use it

answered Nov 19 '18 at 15:40

AlexYes

2,2122715

sorry, i did not see i forget to mention i am using my redshift endpoint after "-h"

– Saurabh Singh
Nov 19 '18 at 15:44

add a comment |

Thanks John Rotenstein on giving me insight, I was actually missing the Inbound rule associated with my security group to only allow traffic coming from redshift private IP. Both of my ec2 instance and redshift were in the same VPC so was supposed to use private instead of public IP. Also forgot to put ssh public key of redshift in the ec2 authorized_key file. Once I did that it worked.

answered Nov 23 '18 at 14:18

Saurabh Singh

add a comment |

Your Answer

StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53377974%2ftunneling-to-redshift-cluster%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

3 Answers
3

active

oldest

votes

3 Answers
3

active

oldest

votes

The first step is to tunnel to the EC2 instance using ssh, with a command that forwards a local port to a remote port:

ssh -i KEYPAIR.pem -L 5439:REDSHIFT-ENDPOINT:5439 ec2-user@EC2-PUBLIC-IP

Where:

KEYPAIR.pem should be the name of the keypair used to access the EC2 instance

REDSHIFT-ENDPOINT is the DNS name of the Redshift endpoint

EC2-PUBLIC-IP is the IP address of the EC2 instance

This command says:

Create an ssh connection using the keypair

Forward any traffic sent to local port 5439 to the remote machine, then have the remote machine send that traffic to REDSHIFT-ENDPOINT:5439 (substitute your endpoint for REDSHIFT-ENDPOINT)

Then, you can connect to Redshift on localhost:5439 as if it were running on your own computer. That traffic will be sent to the remote machine, which will send it to REDSHIFT-ENDPOINT:5439.

For example, if you want to use psql to connect to Redshift, use:

psql -h localhost -p 5439 -U <username>

answered Nov 19 '18 at 16:12

John Rotenstein

71.3k781125

Do I have to change any setting in psql or ec2 instance. As I am still getting same issue with what you have suggested. Thanks.

– Saurabh Singh
Nov 19 '18 at 21:59

Please describe what happens when you perform each step, including how long it takes to respond. For example, when you run ssh, it probably will probably appear to hang. This is okay! You should then use a separate Terminal session for psql. I can't help you unless you provide more information about what is happening.

– John Rotenstein
Nov 19 '18 at 22:28

add a comment |

The first step is to tunnel to the EC2 instance using ssh, with a command that forwards a local port to a remote port:

ssh -i KEYPAIR.pem -L 5439:REDSHIFT-ENDPOINT:5439 ec2-user@EC2-PUBLIC-IP

Where:

KEYPAIR.pem should be the name of the keypair used to access the EC2 instance

REDSHIFT-ENDPOINT is the DNS name of the Redshift endpoint

EC2-PUBLIC-IP is the IP address of the EC2 instance

This command says:

Create an ssh connection using the keypair

Forward any traffic sent to local port 5439 to the remote machine, then have the remote machine send that traffic to REDSHIFT-ENDPOINT:5439 (substitute your endpoint for REDSHIFT-ENDPOINT)

Then, you can connect to Redshift on localhost:5439 as if it were running on your own computer. That traffic will be sent to the remote machine, which will send it to REDSHIFT-ENDPOINT:5439.

For example, if you want to use psql to connect to Redshift, use:

psql -h localhost -p 5439 -U <username>

answered Nov 19 '18 at 16:12

John Rotenstein

71.3k781125

Do I have to change any setting in psql or ec2 instance. As I am still getting same issue with what you have suggested. Thanks.

– Saurabh Singh
Nov 19 '18 at 21:59

Please describe what happens when you perform each step, including how long it takes to respond. For example, when you run ssh, it probably will probably appear to hang. This is okay! You should then use a separate Terminal session for psql. I can't help you unless you provide more information about what is happening.

– John Rotenstein
Nov 19 '18 at 22:28

add a comment |

The first step is to tunnel to the EC2 instance using ssh, with a command that forwards a local port to a remote port:

ssh -i KEYPAIR.pem -L 5439:REDSHIFT-ENDPOINT:5439 ec2-user@EC2-PUBLIC-IP

Where:

KEYPAIR.pem should be the name of the keypair used to access the EC2 instance

REDSHIFT-ENDPOINT is the DNS name of the Redshift endpoint

EC2-PUBLIC-IP is the IP address of the EC2 instance

This command says:

Create an ssh connection using the keypair

Forward any traffic sent to local port 5439 to the remote machine, then have the remote machine send that traffic to REDSHIFT-ENDPOINT:5439 (substitute your endpoint for REDSHIFT-ENDPOINT)

Then, you can connect to Redshift on localhost:5439 as if it were running on your own computer. That traffic will be sent to the remote machine, which will send it to REDSHIFT-ENDPOINT:5439.

For example, if you want to use psql to connect to Redshift, use:

psql -h localhost -p 5439 -U <username>

answered Nov 19 '18 at 16:12

John Rotenstein

71.3k781125

The first step is to tunnel to the EC2 instance using ssh, with a command that forwards a local port to a remote port:

ssh -i KEYPAIR.pem -L 5439:REDSHIFT-ENDPOINT:5439 ec2-user@EC2-PUBLIC-IP

Where:

KEYPAIR.pem should be the name of the keypair used to access the EC2 instance

REDSHIFT-ENDPOINT is the DNS name of the Redshift endpoint

EC2-PUBLIC-IP is the IP address of the EC2 instance

This command says:

Create an ssh connection using the keypair

Forward any traffic sent to local port 5439 to the remote machine, then have the remote machine send that traffic to REDSHIFT-ENDPOINT:5439 (substitute your endpoint for REDSHIFT-ENDPOINT)

Then, you can connect to Redshift on localhost:5439 as if it were running on your own computer. That traffic will be sent to the remote machine, which will send it to REDSHIFT-ENDPOINT:5439.

For example, if you want to use psql to connect to Redshift, use:

psql -h localhost -p 5439 -U <username>

answered Nov 19 '18 at 16:12

John Rotenstein

71.3k781125

answered Nov 19 '18 at 16:12

John Rotenstein

71.3k781125

answered Nov 19 '18 at 16:12

John Rotenstein

71.3k781125

answered Nov 19 '18 at 16:12

John Rotenstein

71.3k781125

Do I have to change any setting in psql or ec2 instance. As I am still getting same issue with what you have suggested. Thanks.

– Saurabh Singh
Nov 19 '18 at 21:59

Please describe what happens when you perform each step, including how long it takes to respond. For example, when you run ssh, it probably will probably appear to hang. This is okay! You should then use a separate Terminal session for psql. I can't help you unless you provide more information about what is happening.

– John Rotenstein
Nov 19 '18 at 22:28

add a comment |

Do I have to change any setting in psql or ec2 instance. As I am still getting same issue with what you have suggested. Thanks.

– Saurabh Singh
Nov 19 '18 at 21:59

Please describe what happens when you perform each step, including how long it takes to respond. For example, when you run ssh, it probably will probably appear to hang. This is okay! You should then use a separate Terminal session for psql. I can't help you unless you provide more information about what is happening.

– John Rotenstein
Nov 19 '18 at 22:28

Do I have to change any setting in psql or ec2 instance. As I am still getting same issue with what you have suggested. Thanks.

– Saurabh Singh
Nov 19 '18 at 21:59

Please describe what happens when you perform each step, including how long it takes to respond. For example, when you run ssh, it probably will probably appear to hang. This is okay! You should then use a separate Terminal session for psql. I can't help you unless you provide more information about what is happening.

– John Rotenstein
Nov 19 '18 at 22:28

add a comment |

try psql -h localhost -p 5439 -d -U -c " my query " or psql -p 5439 -d -U -c " my query ", you can't use switch -h without an argument, which is localhost by default if you don't use it

answered Nov 19 '18 at 15:40

AlexYes

2,2122715

sorry, i did not see i forget to mention i am using my redshift endpoint after "-h"

– Saurabh Singh
Nov 19 '18 at 15:44

add a comment |

try psql -h localhost -p 5439 -d -U -c " my query " or psql -p 5439 -d -U -c " my query ", you can't use switch -h without an argument, which is localhost by default if you don't use it

answered Nov 19 '18 at 15:40

AlexYes

2,2122715

sorry, i did not see i forget to mention i am using my redshift endpoint after "-h"

– Saurabh Singh
Nov 19 '18 at 15:44

add a comment |

try psql -h localhost -p 5439 -d -U -c " my query " or psql -p 5439 -d -U -c " my query ", you can't use switch -h without an argument, which is localhost by default if you don't use it

answered Nov 19 '18 at 15:40

AlexYes

2,2122715

try psql -h localhost -p 5439 -d -U -c " my query " or psql -p 5439 -d -U -c " my query ", you can't use switch -h without an argument, which is localhost by default if you don't use it

answered Nov 19 '18 at 15:40

AlexYes

2,2122715

answered Nov 19 '18 at 15:40

AlexYes

2,2122715

answered Nov 19 '18 at 15:40

AlexYes

2,2122715

answered Nov 19 '18 at 15:40

AlexYes

2,2122715

sorry, i did not see i forget to mention i am using my redshift endpoint after "-h"

– Saurabh Singh
Nov 19 '18 at 15:44

add a comment |

sorry, i did not see i forget to mention i am using my redshift endpoint after "-h"

– Saurabh Singh
Nov 19 '18 at 15:44

sorry, i did not see i forget to mention i am using my redshift endpoint after "-h"

– Saurabh Singh
Nov 19 '18 at 15:44

add a comment |

answered Nov 23 '18 at 14:18

Saurabh Singh

add a comment |

answered Nov 23 '18 at 14:18

Saurabh Singh

add a comment |

answered Nov 23 '18 at 14:18

Saurabh Singh

answered Nov 23 '18 at 14:18

Saurabh Singh

answered Nov 23 '18 at 14:18

Saurabh Singh

answered Nov 23 '18 at 14:18

Saurabh Singh

answered Nov 23 '18 at 14:18

Saurabh Singh

add a comment |

draft saved

draft discarded

Thanks for contributing an answer to Stack Overflow!

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Agfdhyk