Should I skip some migrations on production in case of migrating a legacy database that has not been generated via migration script?

Question

I have a complex PostgreSQL database structure that is consisted with views, materialized views and foreign wrappers.

The database schema is manually updated without any sort of migration script, hence I want to introduce a database migration schemes.

One approach is to generate an initial SINGLE migration that will run only under these conditions:

1. The environment is not a production or a staging one.
2. There are no tables into the database.

This single migration will generate the existing database schema from a database dump. After that each change on the database will be placed upon a new migration script. The framework that will execute the migration scripts is the Laravel and the database layer is the PostgreSQL one.

The reason why I tried this approach is that I want to avoid database corruption on the existing db on production and staging, but to be able to reproduce the database on local development environment.

AFAIK Laravel keeps in the database the last executed migrations as well, so I want to avoid the migrations to be out of sync.

Also, approaches such as this one https://github.com/Xethron/migrations-generator fail to handle the foreign database tables, each foreign table is being generated as normal one.

Would you recommend this approach in my case?

Answer 1

You are looking at a schema compare tool. If you do a schema compare into an empty database, the diff should contain your database structure. Then, quickly commit the source files needed to create a new empty database to source control before any more partying happens directly on the live database without an option to roll back or version changes.