Migrate to Operator managed database

[Timothy Hansen]

I'm in the process of moving our quay deployment from a standalone docker host to our OpenShift cluster using the quay operator.

I've gone through the process of moving from local storage to s3 storage using a minio cluster but now I'd like to see if its possible to migrate our database from the mariadb instance its on to the managed postgres database that can be deployed with the operator.

I went through a similar process a few months ago with our Ansible AWX instance that was originally on a standalone docker host as well.  During that process there was an option with the config files to tell the operator of an existing postgres database that it could use to pull the data from and import into the database container that the awx operator created.

Is there a way to do something similar with the quay operator?  I don't see anything in the documentation which is discouraging but I'm hoping I'm just missing something.  At one point I tried moving the sql data manually into the postgres container however the secret that contains the DATABASE_SECRET_KEY for quay is managed and any time I try to update the key in that secret it gets overwritten again.  Making the data in postgres useless I believe?

I suppose worse case scenario I just use an external postgres server.

[Toni Schmidbauer]

hi,

from my personal experience with the quay operator > 3.3 (aka QuayRegistry) i wouldn't recommend using the managed postgres option. the QuayRegistry operator

is deploying a fixed size pv (50G) for postgres (quay and clair). the size seems to be hardcoded in the kustomize files used for deploying the database.

i had the case that clair GC does not work because of a bug and the clair postgresql pv was full. resizing does not help because the operator wants to resize

the volume back to 50g. only solution was to wipe the clair db.

i'm currently recommending customers to use an externally managed pgsql instance for quay and clair. could be something like crunchy, could be managed by a dedicated db team.

if anyone has better insights into this, please let me know

thanks

toni

 

--
You received this message because you are subscribed to the Google Groups "quay-sig" group.
To unsubscribe from this group and stop receiving emails from it, send an email to quay-sig+u...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/quay-sig/0bdf03d8-5666-4615-a820-26b8cae3ff0en%40googlegroups.com.

--

Toni Schmidbauer

Consulting Architect

Red Hat Austria

tschmi...@redhat.com

+43 664 3565813

[Ivan Bazulic]

The bug for Clair GC has since been fixed and should not be a problem. Clair can also be marked as unmanaged so you can deploy your own clair db and set the PVC as you wish. Regarding the original question, we don't have specific instructions on how to migrate from MySQL to PostgreSQL. Probably the best way would be to dump the whole SQL, convert it from MySQL to PostgreSQL format and then upload it to the new database. Regarding the database secret key and secret key, these can be captured and saved. You need to use the keys from the config.yaml file on your current deployment. Then add those keys to the custom config bundle that the operator consumes on startup. This will make them permanent, the operator will not change them when reconciling. You can also skip version 3.4 and 3.5 and go directly to 3.6 from 3.3.

HTH!

To view this discussion on the web visit https://groups.google.com/d/msgid/quay-sig/CALXTm0bgrFe8BPbV3xYQpO-ouu9N4G-%2Bvs-R%3D9FZovq%2BBQOcrg%40mail.gmail.com.

--

IVAN BAŽULIĆ

PRINCIPAL SUPPORT ENGINEER

Red Hat EMEA

Communications House,

Cork Road,

Waterford X91 NY33

ibaz...@redhat.com    M: +385959269281     IM: ibazulic

[Timothy Hansen]

Thanks for the responses.  I hadn't thought about the PV size being an issue so I should check to see what our current DB sizes are.  We don't really have a large deployment but still worth checking.  I should've probably been clearer that I wasn't necessarily looking for instructions for moving from mysql to postgres as I'm pretty sure I know how to do that portion of it.  Was more looking for instructions on a scenario where an external postgres database already exists but an end user like myself might want to move that into the managed instance by the operator.  An example of what the AWX team did in that scenario can be found here: https://github.com/ansible/awx-operator/blob/devel/docs/migration.md

I'll do some testing and set the database secret key and secret key in the config bundle before hand so that I don't have to worry about trying to change it in the kubernetes secret after the fact like Ivan suggested.  That should allow me to manually move the database with a sql dump like you mentioned.  At this point I'm not sure if its better to try and use the managed database vs just managing my own externally but this gives me something to test for now.  I think that postgres 10 is usually deployed by the operator?  If I go the other route and make my own database servers is there a specific version of postgres I should target?  I generally prefer using percona's distribution.

[Ivan Bazulic]

Hey Timothy,

The problem would show itself even on small deployments. Clair stores audit info about update operations, this info should be garbage collected over time, keeping only n last updates (this should be 10 by default). However, we found that in older Clair versions, this gc does not work unless the number of update operations is explicitly stated in Clair's config.yaml file and we didn't set it there. This has been fixed since so the db pressure is much lower.

Regarding moving the db in a managed instance, it would require you to:

1) Deploy a new Quay instance via the operator. The custom config bundle is not important here.

2) Once all Quay components are created, downscale both the mirror and Quay main app pods to 0 and the quay-operator deployment to 0.

3) Execute a shell inside Quay's PostgreSQL Drop the current database and create a new one with the same name and same owner. Also create the pg_trgm extension inside the db.

4) Import the SQL data from your backup to the newly created database.

5) Create a custom config bundle that the operator will consume. This bundle must have a key called config.yaml and the minimum that this key needs to have is the DATABASE_SECRET_KEY and SECRET_KEY from your old deployment.

6) Add the custom config bundle to the CRD and scale the operator up. 

The operator should reconcile everything and bootstrap Quay with your old data. 

As for PostgreSQL versions, Quay works fine with anything over 9.6. I'd go for 12 or 13, as 10 is a bit old at this point.

To view this discussion on the web visit https://groups.google.com/d/msgid/quay-sig/07d925c1-2254-4156-995c-ddd9814a6b04n%40googlegroups.com.

[Timothy Hansen]

It looks like we have 5 databases in total.  One for quay and 4 for clair.  All of the databases are MBs in size but the "clair-matcher" database is 31GBs.  Would this be the data that isn't getting garbage collection due to the version I'm on or does that sound like a reasonable size for that database?

I should probably also mention that I didn't necessarily plan on moving the clair databases as I didn't think that was really necessarily?  Seemed like letting the operator create a new clair instance and having it rescan the repositories would be sufficient based on my understanding of clair's purpose?  But if the new clair instance manages to get grow to 31GBs in size I could see that eventually becoming an issue on a 50GB PV.

[Stephen Nemeth]

Does it support external database services? Just manage with crunchydata...

To view this discussion on the web visit https://groups.google.com/d/msgid/quay-sig/a7c3f187-9b4e-4531-a431-d455d409d9b2n%40googlegroups.com.

[Timothy Hansen]

Thanks again everyone,

This week I was able to get our production quay instance moved from a standalone docker host into OpenShift.  Used pgloader to migrate the database from mariadb to the managed postgres instance and we're now using object storage instead of locally mounted storage.  On top of that we have quay builders again!

[Ivan Bazulic]

Very nice, congrats! Glad to hear that the migration went well and that your Quay is functioning properly.

To view this discussion on the web visit https://groups.google.com/d/msgid/quay-sig/7017e495-ee9e-47be-a255-3942b24a7e5bn%40googlegroups.com.