search

mattes / migrate

Database migrations. CLI and Golang library.
Other
2.29k stars 326 forks source link

PostgreSQL error running migrations with more than one Schema #288

Closed graux closed 6 years ago

graux commented 7 years ago

Hi, I have a PG 9.6 database with many schemas. (not just public) Initially when I run the tool the schema_migrations table gets created in public and all the migrations are executed fine with the "up" command. Next time I run the up command it tells me: error:

Dirty database version 1502183967. Fix and force version.

I have been checking and the tool creates another schema_migrations table in a different schema. This is, I have the public.schema_migrations with DIRTY false and a different core.schema_migrations table with DIRTY true.

Any idea how to get around this? Any idea how to force the public schema or tell the tool the search_path that has to use?

Thanks!!

mattes commented 7 years ago

Did you use search_path in the connection URL ?

graux commented 7 years ago

No, that is the bug, if you do not specify it should use always public by default and not a random schema. Migrate should not be affected by the search_path or other operations performed by the migrations as happens now.

arturgspb commented 6 years ago

I have many schemas as data shard. And I would like to migrate the schemas separately. I would not want to start separate databases, since I have a lot of shard - more than 5000, so I use schema sharding and only then on databases sharding.

graux commented 6 years ago

Hi @arturgspb et al

Due to the issues I had with the schemas (my project has multiple schemas and does not have the public schema) I decided to fork and fix the issue. I have forked the newly and maintained fork golang-migrate/migrate. https://github.com/graux/migrate

My fork takes the search_path parameter from the connection url string and uses it to create and maintain the migrations table. It does not rely on the current search_path or the current_schema() which might be altered by the migrations SQL code and cause errors.

It also allows you to delete and not depend on the public schema if you don't need it. I have only used the cli version, compilation, installation and usage is the same as the mattes / golang-migrate versions.

Usage:

migrate -path ./migrations -database "postgres://[user]:[password]@[host]/[database]?sslmode=disable&search_path=[target_schema]" up

Hope this helps! Fran