Can't connect to AWS Redshift using RPostgreSQ

Question

I'm not able to connect to my AWS Redshift database using RPostgreSQL.

Does anyone have an example of code that would work?

library (RPostgreSQL)
drv <- dbDriver("PostgreSQL")
conn <- dbConnect(drv, "database.us-east-1.redshift.amazonaws.com",  "jeffz", "PsWrd123")    
Error in postgresqlNewConnection(drv, ...) : 
  RS-DBI driver: (could not connect database.us-east-1.redshift.amazonaws.com@PsWrd123 on dbname "database.us-east-1.redshift.amazonaws.com"

Windows 7 postgresql-8.4-703.jdbc4 in drivers path to jre7 is set in environment

Answer 1

I had the same issue - here is an example of code that 'works' for me:

Using library (RPostgreSQL)

library (RPostgreSQL)
drv <- dbDriver("PostgreSQL")
con1 <- dbConnect(drv, host="hydrogen2.YOURHOST.us-east-1.redshift.amazonaws.com", 
                 port="5439",
                 dbname="mydb", 
                 user="master_user", 
                 password=password)
con1 # check that you have a connection (e.g. <PostgreSQLConnection:(8892,0)>  )
### Make sure AWS has the security/access permissions opened up to allow Port 5439 access from YOUR IP (or all IPs)

Using library(RODBC)

password <- read.table(file="private.txt", header=FALSE) # where I'm holding pw
password <- paste(password[1,1], sep="") #

library(RODBC)
con2 <- odbcConnect("AWS_hydrogen2_source", uid = "master_user", pwd = password) # east region
con2 # works!  if a positive integer, you are connected
odbcGetInfo(con2)

---

Full code here:

https://dreamtolearn.com/ryan/data_analytics_viz/93

https://github.com/rustyoldrake/AWS_Redshift_S3_R_Interface

* As the other person noted - if system is UNABLE TO CONNECT - ensure AWS has the security/access permissions opened up to allow Port 5439 access from YOUR IP (or all IPs) - by default they are NOT open, so if you don't open them, you will not connect

Answer 2

Make sure you allow access in RDS security groups by specifying 0.0.0.0/0 for all IPs