Migration Conflict when the Migration File Version Numbers Jump

[xqi-aviatrix]

Hello,

 We encounter a migration conflict issue recently. Here is our setup
 We have migration files under `postgres_migrations` 
```
0001_aaa.sql
0002_bbb.sql
0003_ccc.sql
0004_ddd.sql
0005_eee.sql
0006_fff.sql
0007_ggg.sql
0008_hhh.sql
0009_jjj.sql
0021_kkk.sql
```

when goose run the migrations we get migration conflict error

...
Error: could not create backing store: could not run Up migrations: error: found 1 missing migrations before current version 21:
        version 9: postgres_migrations/0009_jjj.sql
...

Does the latest version number need to exactly one plus the previous version number in order to avoid migration conflict?
I cannot find any related document about this migration conflict.
Any insights would be appreciated.

Thank you!

[mfridman]

What this is saying is that migration 21 already got applied (it's the current version in the database), and version 9 is "new". It was not there before and is there now somehow.

It's a safety mechanism to prevent versions from being out of order.

There's an article on this that might help.

https://pressly.github.io/goose/blog/2021/out-of-order-migrations/

This almost always happens because developer PRs are merged out-of-order and they use sequential versioning.

You can either

1. Remove 9, and try to figure out how it was intriduced after version 21
2. If out-of-order is okay for you -- use -allow-missing for the goose CLI or goose.WithAllowOutofOrder if using the goose provider