Make SSH tunnel another computer through R to access postgreSQL table

Question

As part of my R workflow for one of my projects, I am loading data from a postgreSQL table located on a remote server.

My code looks like this (anonymous credentials).

First, I opened an ssh connection with a remote server in the terminal .

ssh -p Port -L LocalPort:IP:RemotePort servername" 

Then I connect to the postgres database in R.

 # Load the RPostgreSQL package library("RPostgreSQL") # Create a connection Driver <- dbDriver("PostgreSQL") # Establish database driver Connection <- dbConnect(Driver, dbname = "DBName", host = "localhost", port = LocalPort, user = "User") # Download the data Data<-dbGetQuery(Connection,"SELECT * FROM remote_postgres_table") 

This approach works fine, and I can upload data without any problems.

However, I would like to take the first step - i.e. create an ssh connection - in R, not in the terminal. Here is my attempt to do this with an accompanying error.

 # Open the ssh connection in R system("ssh -T -p Port -L LocalPort:IP:RemotePort servername") # Load the RPostgreSQL package library("RPostgreSQL") # Create a connection Driver <- dbDriver("PostgreSQL") # Establish database driver Connection <- dbConnect(Driver, dbname = "DBName", host = "localhost", port = LocalPort, user = "User") # Download the data Data<-dbGetQuery(Connection,"SELECT * FROM remote_postgres_table") Error in postgresqlExecStatement(conn, statement, ...) : RS-DBI driver: (could not Retrieve the result : server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. 

To clarify my question, I would like to complete this entire workflow (establish a connection, load postgreSQL data) completely in R without any steps in the terminal.

Answer 1

As suggested by @ r2evans.

 ##### Starting the Connection ##### # Start the ssh connection to server "otherhost" system2("ssh", c("-L8080:localhost:80", "-N", "-T", "otherhost"), wait=FALSE) 

You can kill the process by manually finding and typing pid or automatically by killing all signatures that match your server name. Be warned that you want to use this latest version if you use a relatively unique server name that is unlikely to be duplicated in other processes.

 ##### Killing the Connection: Manually ##### # To end the connection, find the pid of the process system2("ps",c("ax | grep otherhost")) # Kill pid (x) identified by the previous grep. tools::pskill(x) ##### Killing the Connection: Automatically ##### # To end the connection, find the pid of the process GrepResults<-system2("ps",c("ax | grep otherhost"),stdout=TRUE) # Parse the pids from your grep into a numeric vector Processes<-as.numeric(sub(" .*","",GrepResults)) # Kill all pids identified in the grep tools::pskill(Processes) 

Answer 2

Alternatively, you can use plink with shell

 library(RPostgreSQL) drv <- dbDriver("PostgreSQL") cmd<- paste0( "plink ", # use key and run in background process " -i ../.ssh/id_rsa -N -batch -ssh", # port forwarding " -L 5432:127.0.0.1:5432", # location of db " [e-mail] " ) shell( cmd, wait=FALSE) # sleep a while before the the connection been established. Sys.sleep(5) conn <- dbConnect( drv, host = "127.0.0.1", port=5432, dbname="mydb", password = "pass" ) dbListTables(conn)