用R的池包重新连接到PostgreSQL数据库

Question

我有一个用R 水管工构建的API，它使用RPostgreSQL和池子连接到PostgreSQL数据库(尽管如果我使用的是一个闪亮的应用程序，这也适用)：

# create the connection pool
pool <- dbPool(
  drv = PostgreSQL(),
  host = Sys.getenv("DB_HOST"),
  port = 5432,
  dbname = "db",
  user = Sys.getenv("DB_USER"),
  password = Sys.getenv("DB_PASSWORD")
)

# start the API
pr <- plumb("plumber.R")

# on stop, close the pool
pr$registerHooks(
  list("exit" = function() { poolClose(pool) })
)

我想每天导入新的数据。最简单的方法是创建一个新的数据库并将其推广到生产中：

CREATE DATABASE db_new;
-- create the tables
-- bulk-insert the data
SELECT pg_terminate_backend (pid) FROM pg_stat_activity WHERE datname = 'db';
DROP DATABASE db;
ALTER DATABASE db_new RENAME TO db;

这是快速和最小化的停机时间。问题是，pool随后丢失的是到数据库的连接，并且不会自动尝试重新连接：

> tbl(pool, "users")
Error in postgresqlExecStatement(conn, statement, ...) : 
  RS-DBI driver: (could not Retrieve the result : FATAL:  terminating connection due to administrator command
server closed the connection unexpectedly
    This probably means the server terminated abnormally
    before or while processing the request.
)

即使我没有每天更换数据库，DB服务器偶尔也会重新启动，这也会导致我的应用程序崩溃。重新连接似乎不是池、RPostgreSQL或DBI的特性。有人知道解决这个问题的方法吗？

Answer 1

我使用带有以下函数的普通DBI (没有池)始终为DBI调用提供活动连接(例如DBI::dbExistsTable(rdsConnect()，"mytable"))。

#' Connect returns a database connection.
#' Retrieves the connection parameters from configuration.
#' 
#' FIXME: dbIsValid is not implemented
#' https://github.com/tomoakin/RPostgreSQL/issues/76
#' workaround implemented with isPostgresqlIdCurrent()
#' @return rds allocated connection
rdsConnect <- function() {
  if (!((exists("rds") && (isPostgresqlIdCurrent(rds))))) {
    source('./config.R', local = TRUE)
    print("New PostgreSQL connection")
    rds <<- DBI::dbConnect(RPostgreSQL::PostgreSQL(),
      dbname = rds_params("rds_database"),
      host = rds_params("rds_host"),
      user = rds_params("rds_user"),
      password = rds_params("rds_password")
    )
  } else print("Valid PostgreSQL connection")
  return(rds)
}