How can I perform version control of Procedures, Views, and Functions in Postgres sql

Question

I want to do a versioning control of my database.

I currently control my front-end applications through git, however I am creating my database and would like to have a versioning of my tables, function and procedures, how can I accomplish this for database? That is, I will make a change in a function but I would like to save the previous one I was executing in case there is any problem I can put the previous one again.

Answer 1

Major tools for versioning of database structure including optional data migration are:

• Flyway
• Liquibase

However, the specifics of your question are beyond what existing tools offer today.

If you need to have two or more versions of some object in the database for parallel use (e.g. step-by-step migration of tables, triggers, aso.) you are better off using either:

1. a naming scheme embedded into the objects, e.g. my_cool_function_v2 vs. my_cool_function_v3

...or:

2. use a different database schema for every major version (if you adhere to the semantic version approach), e.g. CREATE FUNCTION my_schema_v2.my_cool_function will not collide with my_schema_v1.my_cool_function

In both cases, you usually have to manage referencing the newer version where wanted. For the second approach, this can be further simplified with the schema search_path, which you can modify to prefer a new schema containing new versions of the objects, e.g. with:

SET search_path TO my_schema_v2, my_schema_v1, public;

dynamically (useful for testing in the live system without affecting actual applications/users) and once you are confident the basics are set, include it into the PostgreSQL configuration (postgresql.conf) so that the new schema becomes standard for every new connection:

search_path = 'my_schema_v2, my_schema_v1, public'

After you have migrated all of the new objects and everything is working fine, you can remove the old my_schema_v1 from the search_path and also DROP ... CASCADE it to remove all the old objects at once.

However, one downside of the schema approach is that if you always create all objects (functions, triggers, tables, ...) in all schemas, you'll lose the benefits of it when combined with the search_path. Therefore, I would create different schemas for different objects, e.g. data_v1 for the data (tables, indexes, ...) and func_v1 for other things (functions, procedures, ...). That way you can evolve the structure independently of the data but at the same time, you can also start evolving the table structure and automatically benefit from fixes/improvements in functions but also test whether changes are forward-compatible.

Answer 2

Hi future readers of this Q, I am the maintainer of project #yuniql: a schema versioning and migration tool than you can run with zero dependencies. In scenario described, most tools treats every change in the database as immutable or atomic change and if you need to rollback your change, it will just be another minor or major version. This way you can fully reconstruct the database anywhere, anytime and at the same time tracks the evolution of your database schema.

In yuniql, your workspace will typically be organized like this

v0.00
 + create_schema.sql
 + create_table_customers.sql
v0.01
 + create_table_custmomers_with_contact.sql
v0.02
 + create_table_custmomers_with_picture.sql

You may refer to github for getting started and samples ;) https://github.com/rdagumampan/yuniql
https://yuniql.io/docs/get-started-postgresql/

Br, Rodel