Postgresql ssequences not updated correctly upon dump import #1199
Labels
No Label
dependencies
duplicate
help wanted
invalid
kind/bug
kind/feature
needs reproduction
question
security
wontfix
No Milestone
No Assignees
2 Participants
Notifications
Due Date
No due date set.
Dependencies
No dependencies set.
Reference: vikunja/vikunja#1199
Loading…
Reference in New Issue
No description provided.
Delete Branch "%!s(<nil>)"
Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?
I, more or less accidentally, setup my instance to use the default SQLite DB, but I'd like to use Postgres.
Is there an easy way to migrate everything over?
https://vikunja.io/docs/config-options/#type
You could try the
vikunja dump
andvikunj restore
commands. Basically:vikunja restore
on the dump created earlierAnother option would be pgloader.
I dumped SQLite and restored to postgres, now there is this error:
Seems fine as pending notifications don't really matter, but I don't know if everything else was restored correctly now.
Apparently it did not restore some things such as teams.
When I tried to restore it without notifications, I got the following error:
Managed to work around the issue by trimming
notifications.json
to[]
, rezipping and reimporting :)Now new users can't sign up via OIDC, the reimport probably screwed up the provider info:
IIRC the
namespaces_pkey
constraint is the one from the namespace id. Can you check if the sequence in postgres is correct?The provider info is only stored in config.
It would be good if we could fix this in time for our meeting tomorrow, even just with a temporary workaround, but I don't see where the problem is coming from.
The issue seems to be creating a new namespace for a new user, which seems to use an id which is already in use - maybe the counter wasn't set appropriately with the import?
Can you enable database logging to see if postgres generates the wrong id or Vikunja?
Set
log.database
tostdout
andlog.databaselevel
todebug
.Are you able to create a new namespace with an existing user?
No, I can't create new namespaces at all actually.
I'll set the debug parameters and report back.
Here is the output:
This seems similar: https://stackoverflow.com/questions/24149065/auto-increment-issues-postgresql
Fixed it with the following query derived from the aforementioned SO post:
Found the correct sequence to use in:
Whooo glad you figured it out! 🎉
Do new users now work?
Yes, new namespaces, new users, all fine :)
But nevertheless this should be fixed in the importer ;)
I wonder why this didn't work though since all the importer really does is doing
INSERT
statements which should in theory use the sequence (wait, is the sequence actually incremented when records with an id are inserted?). I'll take another look.Pushed a few changes to the restore functionality, most importantly
54348c5891
. Now we're restoring the sequences when importing into a postgres database.596d2bf676
and6e15d46a93
should fix the other problems you encountered.Going to close this now as everything should be resolved, please ping if you have other problems.
I now upgraded to unstable and reimported to apply the fixes, but whenever I login I find everything empty...
This is really bad because we wanted to use Vikunja productively today :/
I tried an older backup and the import worked, but I still cannot create new lists:
As said, latest unstable.
Just noticed I am not quite at latest, but includes your commits anyways:
I spent far too much time, three hours now, resetting and reimporting different dumps.
I now finally ended up being able to copy in a directory backup of postgres, running
vikunja migrate
and thus having a working version with the latest data on unstable.The original problem is still not fixed, of course...
You mean nothing was imported?
That's something that should never happen. Can you send the content of the
migrations.json
file from inside the.zip
?So you're unable to create new lists? But namespaces work?
Sorry to hear that... maybe it'd be easier to use pgloader to migrate the sqlite db to postgres?
During the last import, was there a warning message like
Could not reset id sequence for lists
in the logs?I am getting 500's pretty randomly for some add operations (including adding task relations, lists and tasks), but they always work after a few repetitions.
I also struggled to obtain proper logs, unfortunately...
migration.json:
Looks duplicated, weird...
It does indeed. I noticed that as well during my fix but that should work fine now. Does it make a difference if you remove the duplicates?
I finally have a separate test instance running now where I will test this :)
If I repackage a newly exported zip, it goes panic now - but the non-repackaged zip works, even though they are identical in content:
Oha! If I just update
database/migration.json
in the zip, removing all migrations before the secondSCHEMA_INIT
, and then import that, it is successfull!But the error 500's are staying, because the sequence restoration fails for each table:
Actually, it worked fine for
task_relations
:But adding the first two task relations still produces error 500's.
My hunch: you need to increment the length by one ;)
actually even then the length is a bad heuristic, this was in the table referenced above before I made any additions:
You need to actually determine the highest id used to far.
There's probably a really fancy SQL statement which updates and resets all sequences in postgres and dies a better job than my quick and dirty solution... I'll take another look.
This looks promising: https://wiki.postgresql.org/wiki/Fixing_Sequences
Does it work if you apply the fix from there?
Yes, fantastic!
So the generic, codified query should be:
Changed in
0f555b7ec7
- please test again with the next unstable build.Seems to be fine now, though I did not test with the old exports.
Will report if an issue pops up again.
I wasted hours this week because the fix as available in 0.20.4 did not work properly.
Finally found this issue again and the exact same solution above (https://wiki.postgresql.org/wiki/Fixing_Sequences) fixed it again...
Migrate to different Database typeto Postgresql ssequences not updated correctly upon dump import