synchronizing schemas in server

Posted on

Question :

In production server there’s a database per company, with each produced from a single template.

CREATE DATABASE company_x WITH TEMPLATE company_template;

Let’s say I have updated the schema, is there a way to apply those changes to both existing databases and the template, or somehow synchronize between schema versions?

Answer :

CREATE DATABASE .. WITH TEMPLATE just copies from the template – like the term implies. Later changes to the template database are completely unrelated to databases created before that.

You have to deal with it in some other way. Like:

  • Create a backup of the schema.
  • Prepend the generated SQL script with DROP SCHEMA ... CASCADE;
  • Create a backup of just the schema in each target DB. Compare the dump with the template dump.
    Be very sure, that it’s safe to replace the whole schema. Did you make any updates to objects in the schema? Any at all? That includes FK or other references to any object in the schema from anywhere in your DB. Or even references from outside.

  • Unless you know what you are doing, create a backup for each DB you are going to update.

  • Run the script against each affected DB after that.

Leave a Reply

Your email address will not be published. Required fields are marked *