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:
- a naming scheme embedded into the objects, e.g.
my_cool_function_v2vs.my_cool_function_v3
...or:
- 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_functionwill not collide withmy_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.
Hi all I was wondering how can I handle my Postgresql database version control. I tried database projects with sql server, but I don't what are the alternatives postgresql. Thanks
database - How can I perform version control of Procedures, Views, and Functions in Postgres sql - Stack Overflow
Versioning data in Postgres? Testing a Git like approach
Why is git so cumbersome . - Stack Overflow
Postgresql version control
Videos
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:
- a naming scheme embedded into the objects, e.g.
my_cool_function_v2vs.my_cool_function_v3
...or:
- 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_functionwill not collide withmy_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.
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