1

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

Laurel
  • 5,965
  • 14
  • 31
  • 57
J Zahir
  • 11
  • 1
  • 2
  • 1
    You should be supply five arguments (host, port, dbname, username and password), but you are only supplying three... – hadley Dec 26 '13 at 19:20
  • sample http://stackoverflow.com/questions/12490863/importing-files-from-postgresql-to-r – user2510479 Dec 26 '13 at 19:41

2 Answers2

3

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

Peter O.
  • 32,158
  • 14
  • 82
  • 96
Ryan Anderson
  • 91
  • 1
  • 9
0

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

Sunny Sunny
  • 3,204
  • 4
  • 25
  • 26