R help - Oct 2010 - how to connect to a remote PostgreSQL database from R on mac osx

Hello List,

I would like to connect to a postgreSQL database on a remote server and I am
wondering what is the best package to do that. I have just installed RpgSQL,
RPostgreSQL, Rdbi and RODBC.

The handiest to me is RPostgreSQL but I don't see how to connect to a remote
server with it. For sure I can connect to remote server with RODBC, but since I
am on a Mac OSX 10.6, I have quite difficulties to find ODBC drivers and compile
them.

Any advice is more than welcome.

best regards,
Simone Gabbriellini

On Wed, Oct 20, 2010 at 6:30 PM, Simone Gabbriellini
<simone.gabbriellini at gmail.com> wrote:
> Hello List,
>
> I would like to connect to a postgreSQL database on a remote server and I
am wondering what is the best package to do that. I have just installed RpgSQL,
RPostgreSQL, Rdbi and RODBC.
I have used RPostgreSQL in the past for my needs. I use to keep my
interaction with the DB backend to a minimum, trying to do as much as
possible in R. Also, RPostgreSQL seems to be active after a year of
dormancy ;)
> The handiest to me is RPostgreSQL but I don't see how to connect to a
remote server with it.
Firstly, make sure you can connect to the remote DB w/o R, for
example, using pgadmin3 or psql (see below for ssh tunneling).

Then, try along these lines:

dbname <- "myname"; dbuser <- "myname"; dbpass <-
"IWillNotTell";

dbhost <- "remote.host.edu"; dbport <- 5432;

If the remote host is withing the same local network (ie, your local
host is myhost.host.edu), then, the following should work as well:

dbhost <- "remote"

library(RPostgreSQL)
drv <- dbDriver("PostgreSQL")
con <- dbConnect(drv, host=dbhost, port=dbport, dbname=dbname,
user=dbuser, password=dbpass)

The important thing is firewall and access to the DB. I suggest, using
pgadmin3 to make sure you can connect to the server.

If the local firewall does not allow connection to the postgres server
port then you'll need to use ssh tunneling - the connection can get
pretty slow depending on how remote the remote host is. For example:

assuming the sever allows listens to only "localhost" (this is wrt to
the server), then:

ssh -L 22222:localhost:5432 myname at remote.host.edu

will forward the 22222 port on your client to the remote's 5432 port.
Again, use pgadmin3 at this point to make that you _can_ connect.

Then set

dbhost <- "localhost"; dbport <- "22222"

and use the same dbConnect call.

> Any advice is more than welcome.
See: https://code.google.com/p/rpostgresql/ and I believe Dirk may be
able to provide more help.

HTH,
-- 
Prasenjit