How to share Database connection pool between shiny apps under ShinyProxy

[chienyuchuan]

In my practice, many shiny apps needs to access Postgresql DB which installed on host. I use pool package to get connection pool of Postgresql, and set it to global variable shared for other apps. But every new user trigger app, it would get new connection of Postgresql. I'm afraid Postgresql would crash down when too much concurrent users use shiny apps. Is any solution for this secenario?

pool package introduction as bellowing url. https://shiny.rstudio.com/articles/pool-basics.html

One expert advise me to use pqbouncer, but I don't know how to implement in ShinyProxy, any advice or solution?

Thanks a lot.

[chienyuchuan]

If Shinyproxy would support database connection pool (ex. postgresql,mysql etc) in shiny apps, it will much more convenient for developers. Is it possible?

[shrektan]

I don’t think there’s native way of doing this. But workarounds are many. One possible easy workaround is to deploy an API using plumber.

[chienyuchuan]

thanks to @shrektan , I'll try it latter.

[chienyuchuan]

I install pgbouncer, and set shiny connect to pgbouncer service, it solve the problem. Thanks a lot

[LEDfan]

Hi @chienyuchuan happy to hear you found a solution!
Therefore I'm closing this issue now.
Don't hesitate to open a new issue if you encounter some problem with ShinyProxy!

[kuzmenkov111]

I install pgbouncer, and set shiny connect to pgbouncer service, it solve the problem. Thanks a lot

@chienyuchuan Hi! May you tell me what R package do you use for connection with pgbouncer ?

And my you please share an example of a string connection?

[chienyuchuan]

I install pgbouncer, and set shiny connect to pgbouncer service, it solve the problem. Thanks a lot

@chienyuchuan Hi! May you tell me what R package do you use for connection with pgbouncer ?

And my you please share an example of a string connection?

Dear @kuzmenkov111 , I still use shiny pool package, but connect to pgbouncer.
After you edit /etc/pgbouncer/pgbouncer.ini, inputing [databases] section, you can write R program connecting to dbname and port of [databases] section with pool package.

Here's some reference site for you, hope you can learn from it...

PgBouncer + Ubuntu 18.04
https://www.willandskill.se/en/how-to-setup-pgbouncer-on-ubuntu/

PostgreSQL Connection Pooling with PgBouncer
https://pgdash.io/blog/pgbouncer-connection-pool.html

Using the pool package (basics)
https://shiny.rstudio.com/articles/pool-basics.html

[shrektan]

Just add my two cents.

I actually deployed somewhat similar services but using the Plumber API, as I commented above.

It's a little bit more complicated and you have to write a wrapper function to use it. But it allows to connect multiple different databases and not limited to PostgreSQL.

The way I'm doing it:

1. Server: Set up a docker-compose service that runs a Plumber API. The Plumber API receives SQL and gives an R data.frame. All the data are serialized as R objects (instead of JSON) in order to handle corner-cases easier. In addition, serializings R objects allows you return the R error or messages if any.
2. Client: Write a wrapper function to use httr to use the deployed Plumber API. This wrapper function will use HTTP method to get the SQL querying data from the API or error messages if any. You can distribute the wrapper function via a simple R script or an internal R package.

I personally prefer this method as I almost always use a wrapper to get SQL data (because I need to connect to multiple different databases and correctly handling the encoding / timezone / date-datatype is not that easy).

I hope this is helpful for anyone faces a simliar cases like me.