This is a client/server web application designed (primarily for mobile phones) to report incorrectly parked bicycles/scooters by simply using the user's location and camera to find the location, scan the QR code, and take a picture of the bicycle/scooter.
MIT License
0
stars
1
forks
source link
Implement change management strategy for database. #3
Started with creating a backup from the production database.
Manually remove the database settings commands and GRANT statements as they are outside the scope of change management.
Generate the baseline migration script naming it V1__baseline.sql.
Create clean and fresh databases in development and staging.
Create separate conf files for dev, staging and production with its credentials, hostname, dbname etc. Also specify the path to the migrations file here (this can be same for all conf files).
Run flyway info to check the conf files and review the status of migration.
Run the flyway migrate with specific conf files on dev and staging dbs to bring them upto speed with the production db, like so: flyway -configFiles=C:\temp\dbapps\slimebike\flyway\conf\flyway_slimebike_local.conf migrate. This will run the migration script V1__...
Run flyway baseline with the production conf file to tell flyway to start from here.
Create a diff of the "production-like" new database on the development server with the most cutting-edge version of the development DB that has changes not present in the production server using dbForge Data Compare (https://www.devart.com/dbforge/postgresql/datacompare/). The diff can be specific to the tables of interest and can include data as well as schema.
Create a migration script V2__description.sql using sql generated from the diff after review and manual edits if needed.
Run flyway migrate on the dev, staging and production dbs, and testing the respective setup at each step.
From now on, create a migration script Vxx..sql and run it on dev, staging and production in order.
The ultimate goal, is that someone should be able to run flyway clean and flyway migrate to setup a new development machine from scratch if needed and start contributing.
[x] Test out liquibase.
[x] Test flyway for change management.
[x] Test dbForge for diff calculation.
[x] version control sql.
[ ] Document database.
Most relevant method is to comment while creating the migration script in place.
[x] Evaluate best practices for change management.
Implemented using flyway: https://flywaydb.org/.
Started with creating a backup from the production database.
Manually remove the database settings commands and GRANT statements as they are outside the scope of change management.
Generate the baseline migration script naming it
V1__baseline.sql
.Create clean and fresh databases in development and staging.
Create separate
conf
files for dev, staging and production with its credentials, hostname, dbname etc. Also specify the path to the migrations file here (this can be same for all conf files).Run
flyway info
to check theconf
files and review the status of migration.Run the
flyway migrate
with specific conf files on dev and staging dbs to bring them upto speed with the production db, like so:flyway -configFiles=C:\temp\dbapps\slimebike\flyway\conf\flyway_slimebike_local.conf migrate
. This will run the migration scriptV1__..
.Run
flyway baseline
with the production conf file to tell flyway to start from here.Create a diff of the "production-like" new database on the development server with the most cutting-edge version of the development DB that has changes not present in the production server using
dbForge Data Compare
(https://www.devart.com/dbforge/postgresql/datacompare/). The diff can be specific to the tables of interest and can include data as well as schema.Create a migration script V2__description.sql using sql generated from the diff after review and manual edits if needed.
Run
flyway migrate
on the dev, staging and production dbs, and testing the respective setup at each step.From now on, create a migration script
Vxx..sql
and run it on dev, staging and production in order.The ultimate goal, is that someone should be able to run
flyway clean
andflyway migrate
to setup a new development machine from scratch if needed and start contributing.[x] Test out liquibase.
[x] Test flyway for change management.
[x] Test dbForge for diff calculation.
[x] version control sql.
[ ] Document database.
Most relevant method is to comment while creating the migration script in place.
The comments can stay in database, and be associated with the specific database object, namely, tables, columns, etc. Read more here: https://www.compose.com/articles/postgresql-tips-documenting-the-database/