通过 R 建立到另一台计算机的 SSH 隧道以访问 postgreSQL 表 [英] Make an SSH tunnel to another computer via R to access postgreSQL table

问题描述

作为我的一个项目的 R 工作流程的一部分，我从位于远程服务器上的 postgreSQL 表加载数据.

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

我的代码看起来像这样(匿名凭据).

My code looks like this (anonymized credentials).

我首先在终端中打开到远程服务器的 ssh 连接.

I first open an ssh connection to the remote server in terminal.

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

然后我连接到 R 中的 postgres 数据库.

I then 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 am able to download the data with no problems.

但是，我想在 R 中而不是在终端中执行第一步 - 即创建 ssh 连接.这是我的尝试，但伴随着错误.

However, I would like to do the first step - i.e., creating the ssh connection - in R, rather than in terminal. Here is my attempt to do so, with 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.

为了澄清我的问题，我想完全在 R 中执行整个工作流程(建立连接、下载 postgreSQL 数据)，而无需在终端中执行任何步骤.

To clarify my question, I would like to perform this entire workflow (establish a connection, download postgreSQL data) entirely in R without any steps in terminal.

推荐答案

根据 @r2evans 建议.

As per @r2evans suggestions.

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

您可以通过手动查找并输入 pid 来终止该进程，也可以通过终止与您的服务器名称匹配的所有 pid 来自动终止该进程.请注意，如果您使用的是不太可能在其他进程中重复的相对唯一的服务器名称，那么您只想使用后一个版本.

You can kill the process by manually finding and typing in the pid or automatically by killing all pids matching your server name. Be warned that you only want to use this latter version if you're using 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)

这篇关于通过 R 建立到另一台计算机的 SSH 隧道以访问 postgreSQL 表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！