zalando / postgres-operator

Postgres operator creates and manages PostgreSQL clusters running in Kubernetes
https://postgres-operator.readthedocs.io/
MIT License
4.37k stars 981 forks source link

Moving from users/databases config to preparedDatabases #2748

Open xvilo opened 2 months ago

xvilo commented 2 months ago

Please, answer some short questions which should help us to understand your problem / question better?

We're running Sentry Helm Chart with the Postgres operator for the Database. However, due to the complexities of Sentry, it saves all of the raw events data in the Postgres database and often find us with a full PVC.

We found out that we need to VACUUM FULL that table to reclaim space on the PVC, however, this requires an exclusive lock on that table, rendering Sentry out of service for a bit. After searching for alternatives, we came across pg_repack, which should be a way to reclaim PVC space without an exclusive lock in the table.

To start using pg_repack I believe we need to have a preparedDatabase with that extension in the list. But currently we're using the users and databases configurations keys.

The question: Can I just switch to preparedDatabases and remove the databases/users mapping without dataloss? Or do I need to keep them in there, and additionally add preparedDatabases? What is the best way forward to add this extension into the mix.

FxKu commented 2 months ago

So logging into the pod and database and run CREATE EXTENSION pg_repack; is not an option for you? The pod exec you need anyway to start the pg_repack binary. This is how we usually do it.

preparedDatabases is not a must. It's only a service from us to provide you with a good role setup and adding schemas and extensions are just nice-to-haves which you could otherwise (preferably) add via SQL migrations. How to switch from defined users and database is documented here. Our operator typically does not delete database objects when you remove them from the manifest (with the exception of replication slots).