search

MolSSI / QCFractal

A distributed compute and database platform for quantum chemistry.
https://molssi.github.io/QCFractal/
BSD 3-Clause "New" or "Revised" License
144 stars 47 forks source link

[next branch] Failing migrations: backup/restore full instance? #730

Closed hadim closed 1 year ago

hadim commented 1 year ago

I got failing migrations:

sqlalchemy.exc.ProgrammingError: (psycopg2.errors.DuplicateColumn) column "optimization_specification_id" of relation "neb_specification" already exists
[SQL: ALTER TABLE neb_specification ADD COLUMN optimization_specification_id INTEGER]

I think it's due to this file: https://github.com/MolSSI/QCFractal/blob/next/qcfractal/qcfractal/alembic/versions/2023-06-05-1638db43303c_enable_different_optimizations_in_neb.py. But to be confirmed cuz I haven't been able to reproduce it locally.

I don't have time to debug this currently, but I was wondering if maybe it would be nice to have an endpoint that allows to back up the full instance (at least records, molecules, dataset and specification).

It seems like a good tradeoff option versus raw SQL backup (that is still sensitive to migration failures).

The tricky part is to restore a record I guess, since those are usually sent by the managers.

Since everything is easily serializable with dict(), maybe it's not that a complex task?

Please let me know what you think @bennybp.

bennybp commented 1 year ago

The raw SQL backup should be pretty stable. It uses the postgres standard tools. I wonder if you got caught in a commit with a bug that was later fixed?

You might have created the DB at 9bbb327035041b89096d41d20bec03dde32414dc, with the migration appearing later . So you had the columns already. (If so, apologies for that, that was pretty sloppy of me)

hadim commented 1 year ago

I wonder if you got caught in a commit with a bug that was later fixed?

You might have created the DB at 9bbb327035041b89096d41d20bec03dde32414dc, with the migration appearing later . So you had the columns already. (If so, apologies for that, that was pretty sloppy of me)

Yeah not sure. And no worry, I know it's still WIP, so I understand. For now, it's not an issue since we are still prototyping but I am trying to anticipate when we'll start generating actual data we want to keep.

The raw SQL backup should be pretty stable. It uses the postgres standard tools.

Yes I can always do that but it's still sensitive to db migrations right?

bennybp commented 1 year ago

I'm not sure what you mean by sensitive to db migrations. If you upgrade QCFractal after a backup, then you should have to run upgrade-db after you restore from backup. But that would be true of any backup scheme.

hadim commented 1 year ago

Yes, indeed but the migration error I got was when using upgrade-db so as said, I can always backup the raw SQL, but that would not have fixed the original db migration issue.

I don't think it's a blocker right now for me and also probably only a one-time bug. So I will close here!

bennybp commented 1 year ago

Oh yes, sorry about that. You are right this is likely just a one time thing (because you are on the bleeding edge).