title: Reconnect to PostgreSQL database with R's pool package
body: <p>I have an API built with R <a href="https://www.rplumber.io/" rel="noreferrer">plumber</a> that connects to a PostgreSQL database using <a href="https://db.rstudio.com/databases/postgresql/" rel="noreferrer">RPostgreSQL</a> and <a href="https://github.com/rstudio/pool/" rel="noreferrer">pool</a> (although this would also apply if I was using a Shiny app):</p>
  
  <pre class="lang-r prettyprint-override"><code># create the connection pool
  pool &lt;- 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 &lt;- plumb("plumber.R")
  
  # on stop, close the pool
  pr$registerHooks(
    list("exit" = function() { poolClose(pool) })
  )
  </code></pre>
  
  <p>I want to import new data every day. The easiest way is to create a new database and promote it to production:</p>
  
  <pre class="lang-sql prettyprint-override"><code>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;
  </code></pre>
  
  <p>This is fast and minimizes downtime. The problem is that <code>pool</code> then loses is connection to the database and doesn't automatically attempt to reconnect:</p>
  
  <pre><code>&gt; 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.
  )
  </code></pre>
  
  <p>Even if I wasn't replacing the database every day, DB servers occasionally restart and that will also cause my app to break. Reconnection doesn't seem to be a feature of pool, RPostgreSQL, nor DBI. Does anyone know a way to deal with this problem?</p>