Обсуждение: User account and settings storage in other database than SQLite

Hi

On Thu, May 3, 2018 at 4:50 AM, Peter Palka <ppalka@cmcrc.com> wrote:

Hi,

I'd like to deploy pgAdmin in cloud container environment and use PostgreSQL database as users' data storage. The reasons are:

• we have +30 users that are heavily using pgAdmin
  
• our PostgreSQL cloud database has higher stability level than our cloud container infrastructure
• it will be much easier to scale pgAdmin in the future if we will use container-independent database

SQLite is not designed to handle heavy traffic and we don't want to keep users data in the filesystem as in case of failure all data will be lost. I'm also afraid that sooner or later it will become a performance bottleneck.

I've checked web/config.py and web/setup.py (commit 59d8f6fb84e0aabc098fecf968cde3f9fcdf53b0 Wed May 2 16:51:17 2018 +0100) and I was unable to find any way to initialise pgAdmin with Postgresql as a user data storage backend.

Documentation also says nothing about database other than SQLite (Development version (GIT head Thu May 3 13:45 2018 +1000).

Is there any way to set up pgAdmin with other database than SQLite? (Postgresql preferable)

This is not currently possible; however, the usage of SQLite is quite minimal. Have you done any load testing that shows it is actually a bottleneck, or are you speculating at this point? Certainly there will be a point where it may become an issue, but I have no idea at present if that's at 30 users or 300.

--

Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Hi

On Fri, May 4, 2018 at 4:28 AM, Peter Palka <ppalka@cmcrc.com> wrote:

Hi Dave,

According to SQLite load: just speculation.

According to not supporting Postgres user data storage in Postgres client: well, in this case the container looks more like nice flavour rather than reliable solution. Every time I will kill the docker container or there will be failure the user data will be lost. It will be good to at least mount SQLite user database as an external volume (and mention it in docker hub).

There are two examples on the docker hub page, and the second describes exactly that:

====

Run a TLS secured container using a shared config/storage directory in /private/var/lib/pgadmin on the host:

docker pull dpage/pgadmin4

docker run -p 443:443 \

-v "/private/var/lib/pgadmin:/var/lib/pgadmin" \

-v "/path/to/certificate.cert:/certs/server.cert" \

-v "/path/to/certificate.key:/certs/server.key" \

-e "PGADMIN_DEFAULT_EMAIL=user@domain.com" \

-e "PGADMIN_DEFAULT_PASSWORD=SuperSecret" \

-e "PGADMIN_ENABLE_TLS=True" \

-d dpage/pgadmin4

====

They important line is the first -v option which maps the config directory to the host under docker (you could of course do something similar with a PVC under Kubernetes/OpenShift, but I don't have a recipe for that).

To make it production ready it should be able to handle connection to external database.

I always chuckle when I hear things like that; "to make it production ready you need feature X", though I grant you it's usually for much more trivial (and often questionable) features than you're requesting. That said; whilst it's been suggested that we use an external DB instead of SQLite and it's certainly possible, I can recall just a couple of requests for it since our first release, neither of which had any concrete need for it beyond essentially "we think this will be a good idea", which means it's almost certainly going to be a low priority tasks compared to many of the other things on the todo list. Please do add a feature request to the tracker though (https://redmine.postgresql.org/projects/pgadmin4/issues/new) for future consideration. If you want to work on it yourself, we'll certainly support that.

--

Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company