Re:Postgres 11 – A First Look
by ht85 on Saturday September 22, 2018 @05:28PM
(#18048105)
Maintaining and migrating stored procedures can be a pain, especially when you're running a single tenant infrastructure, and even more if you don't migrate everyone at the same time.
I've had a lot of success by creating a small build system for the database logic, that can be as simple as running a bunch of SQL files in order.
The most important aspect is for the build to be "pure", rebuilding the whole thing every time instead of incrementally changing the schema.
A practical way to do this is to have all your stored procedures in a separate schema from your data, and starting your build process with `DROP SCHEMA procs CASCADE`.
I've had a lot of success by creating a small build system for the database logic, that can be as simple as running a bunch of SQL files in order.
The most important aspect is for the build to be "pure", rebuilding the whole thing every time instead of incrementally changing the schema.
A practical way to do this is to have all your stored procedures in a separate schema from your data, and starting your build process with `DROP SCHEMA procs CASCADE`.