Database migrations in prod

Some pointers before you start for the migration journey

1. Take a point in time backup of the production db
2. Plan the migration and inform your userbase that site would be down for maintenance
3. Plan the migration in a non peak hour time period
   4.Identify the actors involved in the migration like the db version source / target db

Pointers during the migration :

1. Put the application in maintenance mode
2. Once you have secured the db backup start a automated or manual back base on the tools you have selected
3. Before terminating the db instance make sure you rehearse a recovery to how things existed

Pointers post migration:

1. Test the application as much possible for defects
2. Keep looking at the application logs for few days after migration
3. Retain the db backup copy for at least a month or as long as you feel safe.

Probably the easiest to let a framework handle it if you can.

Maybe some more info on your Stack would be helpful 😁

I would always recommend a GUI interface for interacting with DB - just makes it very quick and easy to run ad hoc queries. The best one for PostgreSQL I believe is PGAdmin, though there are a couple others. You want to make sure it's quite highly secured obviously, as this is a direct porthole into your database.

Re migrations, the typical development process is to write migration files that execute any changes. Most frameworks then will have a migration process built into them.

Common PHP frameworks like Laravel for example let you create versioned migration files, the migration files basically just include a bunch of SQL statements. Then you run a migration command to the framework, and it executes any migration files to bring the database up to particular version.

This is best practice, but you can also just make migrations manually when you are doing a release (have a bunch of SQL commands recorded that you manually run at time of upgrade before/after doing the code pull). This is normally fine if you're a solo developer and know everything that's going on, but starts to break quickly as soon as you add developers. Last thing you want is poor tracking of changes into production database.

I use Rails and Rails has a built-in way of handling database migrations.

I do them as part of the application update workflow. In my case, as part of a git-receive hook. Ideally, you design the migrations in a way you won't need a downtime. Otherwise you'll need to stop your servers to perform the migration. Migration itself is "rails db:migrate" since I am running Ruby on Rails.

If you want to learn more, I am writing Deployment from Scratch. It includes a scripted demonstration of a standalone PostgreSQL cluster that set ups a strong password access and TLS, among other things.

@onebox's answer is great. I'd add that you should use a tool to manage migrations like dbmate (https://github.com/amacneil/dbmate).

Some frameworks/libraries will have a migration manager built in like Prisma, Rails, etc.

Some migrations will impact uptime, but for the most part, I've been able to handle small to medium migrations during peak traffic hours without too much impact.