Connecting Rstudio to PostgresSQL

Question

I am trying to connect to a external database (PostgresSQL) from Rstudio by using DBI and RPostgres. I defined all parameters:

con <- DBI::dbConnect(RPostgres::Postgres(),
                      dbname = 'name', 
                      host = 'http://bi-warehouse.cngdka9w0zww.us-east-1.rds.amazonaws.com/',
                      port = 5432,
                      user = 'user',
                      password = 'passwd')

but still getting the error:

    Error in connection_create(names(opts), as.vector(opts)) : 
  could not translate host name "http://bi-warehouse.cngdka9w0zww.us-east-1.rds.amazonaws.com/" to address: Name or service not known

http is not the database protocol ;) you have to remove that wrong information ;) — episch, Feb 20 '19 at 11:25
So, the host argument is only: bi-warehouse.cngdka9w0zww.us-east-1.rds.amazonaws.com/ — Gabriel Teotonio, Feb 20 '19 at 11:48
Now I got the following: `Error in connection_create(names(opts), as.vector(opts)) : could not connect to server: No route to host Is the server running on host "bi-warehouse.cngdka9w0zww.us-east-1.rds.amazonaws.com" (10.0.1.103) and accepting TCP/IP connections on port 5432?` — Gabriel Teotonio, Feb 20 '19 at 11:50
in no trading slash, that is not a route of a website :) its only the domain of that server (host) where you have installed your postgreSQL, do you have checked, that you postgreSQL can be connect from external systems, default is only localhost connection allowed. — episch, Feb 20 '19 at 13:25

score 0 · Answer 1 · edited Feb 24 '22 at 02:09

Try package RPostgreSQL

Example:-

library(RPostgreSQL)
library(dplyr)
library(dbplyr)
psql <- DBI::dbDriver("PostgreSQL")
con <- DBI::dbConnect(psql, 
                      dbname = "machine_db",
                      host = "192.168.13.213", 
                      port = 5432,
                      user = "user", 
                      password = 'user123')

machine_data <- dbGetQuery(con, "SELECT * from machine_db")

score 0 · Answer 2 · edited Feb 24 '22 at 02:21

Using RPostgres Package

For More Information- https://github.com/r-dbi/RPostgres

library(DBI)
library(RPostgres)

conn <- dbConnect(RPostgres::Postgres(),
                 host = "xyz-db-postgresql.postgres.database.aws.com",
                 port = 5432,
                 dbname = "xyz",
                 user = "xyz",
                 password = "password"
)

#==== Top 100 Records Of CommodityTable ====
 qr<- paste0('SELECT * from "Cxtable " LIMIT 100')
 Cxtable <- dbGetQuery(conn, qr)
 Cxtable

Connecting Rstudio to PostgresSQL

2 Answers2