2

I need to implement schema migration mechanism for PostgreSQL. Just to remove ambiguity: with schema-migration I mean that I need upgrade my database structures to the latest version regardless of their current state on particular server instance.

For example in version one I created some tables, then in version two I renamed some columns and in version three I removed one table and created another one. I have multiple servers and on some of them I have version one on some version three etc.

My idea:

  • Generate hash for output produced by

pg_dump --schema-only

every time before I change my database schema. This will be a reliable way to identify database version in the future to which the patch should apply.

  • Contain a list of patches with the associated hashed to which they should apply.
  • When I need to upgrade my database I will run an application that will search for hash that corresponds to current database structure (by calculating hash of local database and comparing it with hash set that I have) and apply associated patch.
  • Repeat until next hash is not found.

Could you please point any weak sides of this approach?

Pavlo
  • 1,594
  • 2
  • 15
  • 30

1 Answers1

0

Have you ever heard of https://pgmodeler.io ? At the company where I work we decided to go for this since it can perform schema diff even between local and remote. We are very satisfied with it.

Otherwise if you are more for a free solution, you could develop a migration tool which can be used to apply migrations you store in a single repo. Furthermore this tool could rely on a migration table you keep in a separate schema so that your DB(s) will always know which migrations were applied or not.

The beauty of this approach is that migrations can both be about a schema change and data changes.

I hope this can give you some ideas.

Lucat
  • 2,242
  • 1
  • 30
  • 41
  • Thank You for your suggestion but we cannot use GUI solution and the approach with migration table do not give any warranties: someone can change something without reflecting this change in that table. – Pavlo Jan 25 '20 at 15:52
  • What if the only way to apply certain migrations is to use a tool which also updates that `migration` table? This may force a protocol in the team :) – Lucat Jan 25 '20 at 15:56
  • Yes, it would solve the problem but the team is pretty big, scattered across multiple countries, permissions are managed by multiple people and skill level also varies. It is hard to follow spoken rule. – Pavlo Jan 26 '20 at 06:14
  • 1
    Almost feels like an organisational issue if too many people are allowed to make changes to the DB schema. Sorry for being of not much help, try out this related link: https://stackoverflow.com/questions/175451/how-do-you-version-your-database-schema – Lucat Jan 26 '20 at 16:01