Error in postgresqlNewConnection(drv, ...) RS-DBI driver: (could not connect postgres@local on dbname

Question

I'm new to R and I'm trying to connect to PostgreSQL using RStudio.

I've installed the RPostgreSQL and tried the following code:

> library("DBI", lib.loc="~/R/win-library/3.2")
> library("RPostgreSQL", lib.loc="~/R/win-library/3.2")
> con <- dbConnect(dbDriver("PostgreSQL"), dbname="Delta", user="postgres")
Error in postgresqlNewConnection(drv, ...) : 
  RS-DBI driver: (could not connect postgres@local on dbname "Delta"

I'm not able to connect to the database for some reason. I'm trying to solve this issue for a long time and couldn't figure out how.

Answer 1

My solution to this problem is to use RPostgres https://github.com/rstats-db/RPostgres.

Assuming you have a connection url, the following code will work:

library(DBI)
library(RPostgres)

con <- dbConnect(RPostgres::Postgres(),
  host = url$host,
  port = url$port,
  dbname = url$dbname,
  user = url$user,
  password = url$password
)

Answer 2

My solution using the odbc package

db <- DBI::dbConnect(odbc::odbc(),
                     Driver = "{PostgreSQL ODBC Driver(ANSI)}",
                     Database = "db_name",
                     UserName = "user",
                     Password = "pass",
                     Servername = "localhost",
                     Port = 5432)

Answer 3

Running into this unclear error, I found RPostgreSQL will work by adjusting the PostgreSQL password encryption from the default of libpq 10 at scram-sha-256 to md5. See this SO post: How can I solve Postgresql SCRAM authentication problem?

I arrived at this fix by using an ODBC driver connection, specifically replacing DBI + RPostgreSQL packages for DBI + odbc which raised a much clearer error:

SCRAM authentication requires libpq version 10 or above.

Changing the authentication worked for both odbc and RPostgreSQL connections in R.

Do note: the user for R-PostgreSQL connection password must be adjusted (even with the same exact one) since the encrypted authentication will be changed:

-- SUPERUSER
ALTER USER postgres WITH PASSWORD 'new or same password';

-- LOGIN USER
ALTER USER myuser WITH PASSWORD 'new or same password';

Answer 4

I had the same problem . I installed DBI after I installed RPostgreSQL and loaded the DBI library seperately. Worked for me