Error: migrations table is already locked

[anthr76]

Hello 👋🏼

I run my lndhub.go deployment in Kubernetes with a RollingUpdate strategy with 1 replica. This appears to have a ill effect possibly.

{"level":"fatal","time":"2022-12-26T04:32:05Z","message":"Error migrating database: migrate: migrations table is already locked (ERROR: duplicate key value violates unique constraint \"bun_migration_locks_table_name_key\" (SQLSTATE=23505))"}

Does anyone know how I can manually clean this up?

[anthr76]

I cleaned this up in a somewhat destructive manner.

DELETE FROM (migration rows)

It seems using bun as the ORM backend to lndhub it should be trivial to scale. Does anyone know what that would take so this wouldn't happen.

[kiwiidb]

We are also running lndhub.go in Kubernetes (managed k8s + managed Postgres db from Digital Ocean). Like you, we're using a single replica with the RollingUpdate strategy, but we are not experiencing this issue. I can see this being a possible issue in the hypothetical scenario where you would run multiple replicas, but you shouldn't do this anyway: we're using a GRPC stream inside LNDhub.go to update settled invoices so running multiple copies results in invoice amounts being credited more than once.

In the future we might support a different kind of update mechanism (I'm thinking about a GRPC push instead of pull), so LNDhub.go can be scaled up trivially.

Can you recreate this issue? If you delete the lndhub pod, does it get into this state again?

[anthr76]

Sadly after changing the deployment strategy to recreate I ran into this again :(

{"level":"fatal","time":"2023-01-02T14:16:45Z","message":"Error migrating database: migrate: migrations table is already locked (ERROR: duplicate key value violates unique constraint \"bun_migration_locks_table_name_key\" (SQLSTATE=23505))"}

I decided to hold off trying to clean it until there was some light shed here. Thanks for the GRPC Push/Pull info!

This migration error has a slight chance of coming when I was in the process of changing from recreate to rollingUpdate but I find it unlikely.

You can see my configuration here:

https://github.com/anthr76/infra/blob/f1f0abf16e84974c7e6b67b70fa87b0acad8fa9c/k8s/base/crypto/lndhub/helm-release.yaml

[kiwiidb]

Interesting :) What does the initContainer "ghcr.io/onedr0p/postgres-initdb" do exactly?

[anthr76]

It simply instances the database and user if it's non-existent https://github.com/onedr0p/containers/blob/main/apps/postgres-initdb/entrypoint.sh

I have just removed my stale lock:

lndhub=# DELETE FROM bun_migration_locks WHERE id = 431;
DELETE 1
lndhub=# SELECT * FROM bun_migration_locks;
 id | table_name 
----+------------
(0 rows)