Add the ability to connect Portainer to an external database

[deviantony]

Requirements

Add the ability to connect Portainer to an external database to provide an HA deployment (the database technology will need to support HA deployments).

This needs to be provided as an alternative way to deploy Portainer as we want to keep the simplicity as the default option.

The database configuration must be supported via CLI (flags or configuration file to be considered).

Considerations

As we've discussed internally, the key/value model is now limiting us and we might decide to switch to another database technology as such we might need to find an alternative to the current technology powering a simple deployment (boltdb).

Interesting tools to consider/investigate:

• https://dqlite.io/
• https://github.com/rqlite/rqlite

[huapox]

How about these(BadgerDB):

• https://github.com/mosuka/cete

Cete is a distributed key value store server written in Go built on top of BadgerDB.

$ ./bin/cete start --id=node1 --raft-address=:7000 --grpc-address=:9000 --http-address=:8000 --data-directory=/tmp/cete/node1

• https://github.com/timshannon/badgerhold

BadgerHold is a simple querying and indexing layer on top of a Badger instance.

s.Find(&result, badgerhold.Where("FieldName").Eq(value).And("AnotherField").Lt(AnotherValue).Or(badgerhold.Where("FieldName").Eq(anotherValue)))

[till]

@deviantony Are you considering a RDMS at all? Most scaling problems seem solved in this space. If you want to keep closer to kv - what about etcd or consul?

[SvenDowideit]

excellent timing :) I'm very interested in hearing what our users would prefer to use - and what you think "scaling" means for you wrt Portainer.

I'm reflexively interested in managed cloud databases - so HA effectively is someone else's problem - but I'm not sure I have scale issues yet :)

[huapox]

excellent timing :) I'm very interested in hearing what our users would prefer to use

Sure, when we use PT(Portainer) just as one tool-platform with basic configs(endpoints, access, settings..) and nodes/cluster management, the boltdb is enough.

But if we use PT in a work-flow(like the stack for CI/CD), then will generate incremental data, then this we may need some db that support HA or better supports for incremental-data in the database. Once db can be seperated, then we can consider how PT to support to be deployed with two or more instances(avoid the single one break-down, and share the CI/CD's requests)

Another point is that the DB to be similar to the end user/operator, for example Postgres/Mysql, then with much more choices for backup/HA and with the convinience for data view/edit.(some times we just want to change one param that current isn't suport by PT's command or UI)

---

Ref: https://github.com/k3s-io/kine, an etcdshim that translates etcd API to sqlite, Postgres, Mysql, and dqlite

[till]

excellent timing :) I'm very interested in hearing what our users would prefer to use - and what you think "scaling" means for you wrt Portainer.

I am interested in availability than scaling.

E.g. running two instances of Portainer behind a loadbalancer so I don't have to worry about maintenance etc..

Hence selecting a well-known/established database.

[unlucio]

This would also help if you keep portainer's data dir on a NAS share, sine sqllite doesn't like staying on a network fs.

Any news, btw? I've found this issue because of that ⬆

[andrewcrook]

Postgres and Mysql support is needed for sure.
As for the default “lite” database I don't really have a preference as such.
As long as the data can be easily be backed up, be bound to a folder when running in a container , can be migrated to Postgres and Mysql and offer a migration path for encrypted data I would be happy.

[jmls]

+1 for postgres. Any plans to do this ? :)

[jansyren]

I haven't heard of badgerDb but I would like something fairly standard, such as postgres in order to keep complexity down and have the possibility to use existing databases as backend.

[archef2000]

Is there any plan in to future to add some form of HA support?