RPostgreSQL won't connect anymore, but it used to

[mst...@newrelic.com]

I'm not sure why, but I'm no longer able to connect to my postgres database the RPostgreSQL packages. It's a postgres database on Amazon AWS (not redshift).

library(RPostgreSQL)
drv <- dbDriver("PostgreSQL")
con <- dbConnect(drv,
                 host='<host>',
                 port='5432',
                 dbname='<dbname>',
                 user='<username>',
                 password='<password>')

Error in postgresqlNewConnection(drv, ...) :
  RS-DBI driver: (could not connect <username>@<host> on dbname "<dbname>"
)

The weird thing is that this used to work until very recently (I'm not sure what broke it). I'm able to connect to this database via psql and other SQL editors. Here are other details that might be of use:

• I'm using a Mac (OS X 10.11.5 (El Capitan))
• R version 3.3.1
• RPostgreSQL_0.4-1 
• DBI_0.4-1

[Ian Gow]

Does installing the latest RPostgreSQL (v.0.5-2) from GitHub help?

devtools::install_github("tomoakin/RPostgreSQL", subdir="RPostgreSQL")

• I'm using a Mac (OS X 10.11.5 (El Capitan)) -

• R version 3.3.1
• RPostgreSQL_0.4-1
• DBI_0.4-1

--
You received this message because you are subscribed to the Google Groups "RPostgreSQL Development and Discussion List" group.
To unsubscribe from this group and stop receiving emails from it, send an email to rpostgresql-d...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[mst...@newrelic.com]

Hi Ian,

Thanks for the quick reply. I wasn't able to install the latest though. See the attached screenshot of what happened.

Thanks,

Maria

[Jenny Hutchison]

I am having the same problem with my connection to my postgres databases (and continue to connect just fine through other means, it most definitely is my upgrading R and its packages yesterday that broke this!). When I try to use install RPostgreSQL using devtools as described, I get a different error: 

Downloading GitHub repo tomoakin/RPostgreSQL@master

from URL https://api.github.com/repos/tomoakin/RPostgreSQL/zipball/master

Error: Does not appear to be an R package (no DESCRIPTION)

[I already have postgres running locally; it, too, is behaving perfectly]

[Ian Gow]

I had the same problem, try:

devtools::install_github("tomoakin/RPostgreSQL", subdir="RPostgreSQL")

On 11 Aug 2016, at 3:09, Jenny Hutchison wrote:

I am having the same problem with my connection to my postgres databases
(and continue to connect just fine through other means, it most definitely
is my upgrading R and its packages yesterday that broke this!). When I try
to use install RPostgreSQL using devtools as described, I get a different
error:

Downloading GitHub repo tomoakin/RPostgreSQL@master
from URL https://api.github.com/repos/tomoakin/RPostgreSQL/zipball/master
Error: Does not appear to be an R package (no DESCRIPTION)

[I already have postgres running locally; it, too, is behaving perfectly]

On Tuesday, 12 July 2016 03:14:54 UTC+10, Ian Gow wrote:

Does installing the latest RPostgreSQL (v.0.5-2) from GitHub help?

devtools::install_github("tomoakin/RPostgreSQL", subdir="RPostgreSQL")

email to rpostgresql-d...@googlegroups.com <javascript:>.

For more options, visit https://groups.google.com/d/optout.

[Ellen]

Hi Maria, 

Did you solve this? I have exactly the same problem - my code with dbConnect worked until recently. I tried to install RPostgreSQL from github and get the same error as you. It would be great to know how you fixed it!

Best wishes

Ellen

[Maria Stopak]

Hi Ellen,

Try this:

1. First, you need homebrew on your machine
1. Open your terminal (in spotlight, just open search for 'terminal')
2. Copy/Paste this line and hit enter
• /usr/bin/ruby -e "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install)"
• This may take a few minutes
2. Once it's completed, copy/paste this line in the terminal and hit enter
• brew install postgresql
  
3. Once it's done, you can exit the terminal
4. Open R (or RStudio) and run the following 2 lines
• install.packages("devtools") 

• devtools::install_github("tomoakin/RPostgreSQL", subdir="RPostgreSQL")
  

1. Then try connecting to your database. If the stars have aligned, it should work! 

If it still doesn't work, try downloading the latest version of R, that worked for me.

If that still doesn't work, I had a coworker who got a weird error when running "brew install postgres" in his terminal. Something about it wasn't linked? Not sure, but maybe this could help if you happen to have that issue: http://stackoverflow.com/questions/25075888/brew-install-postgresql-upgrade-error-could-not-link-dead-links-to-old-non

Thanks,

Maria

--
You received this message because you are subscribed to a topic in the Google Groups "RPostgreSQL Development and Discussion List" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/rpostgresql-dev/TyoIlrlxpm4/unsubscribe.
To unsubscribe from this group and all its topics, send an email to rpostgresql-dev+unsubscribe@googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

--

Maria Stopak

Marketing Analytics Manager

mst...@newrelic.com

[Ian Gow]

It works for me with MacPorts PostgreSQL, but I think I had a similar issue at some point. I think the critical thing might be in finding pg_config (this is the MacPorts location).

checking for pg_config... /opt/local/bin/pg_config

-Ian

devtools::install_github("tomoakin/RPostgreSQL", subdir="RPostgreSQL")

Downloading GitHub repo tomoakin/RPostgreSQL@master

from URL https://api.github.com/repos/tomoakin/RPostgreSQL/zipball/master

Installing RPostgreSQL

trying URL 'https://cran.rstudio.com/bin/macosx/mavericks/contrib/3.3/DBI_0.5-1.tgz'

Content type 'application/x-gzip' length 353669 bytes (345 KB)

==================================================

downloaded 345 KB

Installing DBI

'/Library/Frameworks/R.framework/Resources/bin/R' --no-site-file --no-environ --no-save --no-restore --quiet CMD INSTALL \

'/private/var/folders/qg/sptkmd_n32n5_4xb5tg7bn_wxz6v__/T/Rtmp6gycmB/devtools1684c4358f050/DBI' \

--library='/Library/Frameworks/R.framework/Versions/3.3/Resources/library' --install-tests

* installing *binary* package ‘DBI’ ...

* DONE (DBI)

'/Library/Frameworks/R.framework/Resources/bin/R' --no-site-file --no-environ --no-save --no-restore --quiet CMD INSTALL \

'/private/var/folders/qg/sptkmd_n32n5_4xb5tg7bn_wxz6v__/T/Rtmp6gycmB/devtools1684c1baac8e5/tomoakin-RPostgreSQL-cbe456d/RPostgreSQL' \

--library='/Library/Frameworks/R.framework/Versions/3.3/Resources/library' --install-tests

* installing *source* package ‘RPostgreSQL’ ...

checking for gcc... gcc

checking whether the C compiler works... yes

checking for C compiler default output file name... a.out

checking for suffix of executables...

checking whether we are cross compiling... no

checking for suffix of object files... o

checking whether we are using the GNU C compiler... yes

checking whether gcc accepts -g... yes

checking for gcc option to accept ISO C89... none needed

checking build system type... x86_64-apple-darwin16.0.0

checking host system type... x86_64-apple-darwin16.0.0

checking target system type... x86_64-apple-darwin16.0.0

checking for pg_config... /opt/local/bin/pg_config

checking for "/opt/local/include/postgresql96/libpq-fe.h"... yes

configure: creating ./config.status

config.status: creating src/Makevars

** libs

clang -I/Library/Frameworks/R.framework/Resources/include -DNDEBUG -I/opt/local/include/postgresql96 -I/usr/local/include -I/usr/local/include/freetype2 -I/opt/X11/include -fPIC -Wall -mtune=core2 -g -O2 -c RS-DBI.c -o RS-DBI.o

clang -I/Library/Frameworks/R.framework/Resources/include -DNDEBUG -I/opt/local/include/postgresql96 -I/usr/local/include -I/usr/local/include/freetype2 -I/opt/X11/include -fPIC -Wall -mtune=core2 -g -O2 -c RS-PQescape.c -o RS-PQescape.o

clang -I/Library/Frameworks/R.framework/Resources/include -DNDEBUG -I/opt/local/include/postgresql96 -I/usr/local/include -I/usr/local/include/freetype2 -I/opt/X11/include -fPIC -Wall -mtune=core2 -g -O2 -c RS-PostgreSQL.c -o RS-PostgreSQL.o

clang -I/Library/Frameworks/R.framework/Resources/include -DNDEBUG -I/opt/local/include/postgresql96 -I/usr/local/include -I/usr/local/include/freetype2 -I/opt/X11/include -fPIC -Wall -mtune=core2 -g -O2 -c RS-pgsql-copy.c -o RS-pgsql-copy.o

clang -I/Library/Frameworks/R.framework/Resources/include -DNDEBUG -I/opt/local/include/postgresql96 -I/usr/local/include -I/usr/local/include/freetype2 -I/opt/X11/include -fPIC -Wall -mtune=core2 -g -O2 -c RS-pgsql-getResult.c -o RS-pgsql-getResult.o

clang -I/Library/Frameworks/R.framework/Resources/include -DNDEBUG -I/opt/local/include/postgresql96 -I/usr/local/include -I/usr/local/include/freetype2 -I/opt/X11/include -fPIC -Wall -mtune=core2 -g -O2 -c RS-pgsql-pqexec.c -o RS-pgsql-pqexec.o

clang -I/Library/Frameworks/R.framework/Resources/include -DNDEBUG -I/opt/local/include/postgresql96 -I/usr/local/include -I/usr/local/include/freetype2 -I/opt/X11/include -fPIC -Wall -mtune=core2 -g -O2 -c RS-pgsql-pqexecparams.c -o RS-pgsql-pqexecparams.o

clang -dynamiclib -Wl,-headerpad_max_install_names -undefined dynamic_lookup -single_module -multiply_defined suppress -L/Library/Frameworks/R.framework/Resources/lib -L/usr/local/lib -o RPostgreSQL.so RS-DBI.o RS-PQescape.o RS-PostgreSQL.o RS-pgsql-copy.o RS-pgsql-getResult.o RS-pgsql-pqexec.o RS-pgsql-pqexecparams.o -L/opt/local/lib/postgresql96 -lpq -F/Library/Frameworks/R.framework/.. -framework R -Wl,-framework -Wl,CoreFoundation

installing to /Library/Frameworks/R.framework/Versions/3.3/Resources/library/RPostgreSQL/libs

** R

** inst

** tests

** preparing package for lazy loading

Creating a generic function for ‘format’ from package ‘base’ in package ‘RPostgreSQL’

Creating a generic function for ‘print’ from package ‘base’ in package ‘RPostgreSQL’

Creating a generic function for ‘summary’ from package ‘base’ in package ‘RPostgreSQL’

** help

*** installing help indices

** building package indices

** testing if installed package can be loaded

* DONE (RPostgreSQL)

[Begli]

Hi Ellen,

Just put together my notes from 4-5 years ago that would help you get going if it's still something you need.

https://medium.com/datatime/rpostgresql-on-os-x-mavericks-yosemite-and-beyond-3dedc0558eb0

Begli

[Begli]

Hi there,

Just responded to Ellen's post, but in case you miss it, here are my notes from a few years ago that should help if it's something you still need.

https://medium.com/datatime/rpostgresql-on-os-x-mavericks-yosemite-and-beyond-3dedc0558eb0

I hope this helps.

Cheers,
Begli