(Also posted a version on Stack Overflow but didn't get any answers.)
Does anyone know of a way to maintain a stable connection to a Postgres database with existing connection management packages like pool, DBI, etc.? In my case, I need a Plumber API to connect to a Postgres database and be resilient to the database being replaced by a new version (as part of a daily data refresh), but my scenario would also apply in the case of a Shiny app connecting to a DB.
The general issue is that if you want to create a new database and promote it to production via a simple rename, you must first disconnect existing connections to the old one. None of the DB-related packages I've tried have logic that attempts to reconnect to the database when the connection is lost. The only way I know to fix it is to restart the app, which calls
I believe this same problem would also manifest in the case of a DB server restart, which with some hosting providers might occur at an unpredictable time.
Has anyone ever dealt with this problem? Is there a way to capture an error like
Error in postgresqlExecStatement globally and attempt a reconnect?