Idempotent PostgreSQL schema dump that can be applied to an existing DB

Question

I'm looking for a way to replicate a PostgreSQL schema to a database which may already have some of it defined. I wonder if there's a simple way to produce DDL that is idempotent (i.e. that can be applied any number of times) from PostgreSQL. Currently I obtain DDL from my source DB using pgdump -s, and manipulate it using the ad-hoc Python code below. I wonder if there's a cleaner solution.

import re
def MakeDdlIdempotent(sql):
 "Make PGSQL DDL idempotent by changing various 'create' statements to idempotent versions"
 ddlReplacements = {
 "CREATE SCHEMA": "CREATE SCHEMA IF NOT EXISTS",
 "CREATE TABLE": "CREATE TABLE IF NOT EXISTS",
 "CREATE INDEX": "CREATE INDEX IF NOT EXISTS",
 "CREATE VIEW": "CREATE OR REPLACE VIEW",
 "CREATE FUNCTION": "CREATE OR REPLACE FUNCTION"
 }
 for orig, replacement in ddlReplacements.items():
 sql = sql.replace(orig, replacement)
 addConstraintRe = re.compile(r"ALTER TABLE [^;]+ ADD CONSTRAINT [^;]+;", flags = re.DOTALL | re.MULTILINE)
 sql = addConstraintRe.sub(
 r"""DO $$
BEGIN
 BEGIN
 \g<0>
 EXCEPTION
 WHEN invalid_table_definition OR duplicate_object OR duplicate_table THEN
 END;
END $$;""",
 sql)
 return sql

• joanolo: Check if a tool like Liquibase or FlyWay are good for you. If using Python, check if sqlalchemy-migrate can help you.
• Isac Casapu: Thanks for the suggestions! I learned from them of some useful tools. Anyway, I plan to stick with my ad-hoc solution for the time being, since it's satisfactory and doesn't entail any further integrations...

Answer 1

I wonder if there's a simple way to produce DDL that is idempotent

There is not, and it doesn't make much sense for a schema with data in it. If one column goes away and another appears, how should such a tool know whether it's an ALTERed column that's had its type changed and been renamed, or a DROP + a ADD? It could be either. The tool would have no way to deduce what the expression used to move the data over, if any, was either.

What you should generally do is use a schema management tool that can create versioned "migration" files for you, like liquibase.

• Isac Casapu: Actually my use-case is not so general, and schema changes would typically entail addition of a whole new table. Anyway, I plan to stick with my ad-hoc solution for the time being...
• Vishesh Mangla: Actually if postgres maintains a log of changes done on table, then during pg dump, those logs can be read by the tool
• Craig Ringer: @VisheshMangla You're probably thinking of logical decoding, which has existed since postgres 10, but won't help you for DDL, let alone declarative DDL to express a desired state without expressing how to get there.