Can't connect to AWS Redshift using RPostgreSQ

问题:

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

回答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

回答2:

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