feat: add support for custom template databases

[munjalpatel]

trafficstars

Description

This PR introduces a new flag, --template-db, allowing users to specify the template database used when creating a temporary database. Previously, template0 was hard-coded, limiting the ability to validate migrations in scenarios requiring a different template DB.

Motivation

We frequently use various Postgres extensions that rely on different template databases for initialization, and we want to leverage migration validation against a temporary database. The prior hard-coded template0 prevented using migration validation with those extensions. By making the template configurable, we can accommodate more specialized Postgres environments and maintain robust validation flows.

Testing

• Added unit tests to validate the new --template-db flag.
• Verified downstream acceptance tests pass without regressions.
• Confirmed correct inheritance of objects from the user-specified template DB in the newly created temporary database.

[cla-assistant[bot]]

All committers have signed the CLA.

[munjalpatel]

@bplunkett-stripe Thanks for the suggestions on this PR! I’m happy to incorporate improvements.

However, in my testing, I noticed an issue: when we create a new database from a template that already has extensions preloaded, and our SQL migrations don’t include CREATE EXTENSION … statements, pg-schema-diff tries to remove those extensions.

Because of that, I’m not sure how much value this PR provides unless there’s a more straightforward way to ignore everything inherited from the template database (tables, functions, extensions, etc.). Do you have any thoughts on how we might address this?

[bplunkett-stripe]

@bplunkett-stripe Thanks for the suggestions on this PR! I’m happy to incorporate improvements.

However, in my testing, I noticed an issue: when we create a new database from a template that already has extensions preloaded, and our SQL migrations don’t include CREATE EXTENSION … statements, pg-schema-diff tries to remove those extensions.

Because of that, I’m not sure how much value this PR provides unless there’s a more straightforward way to ignore everything inherited from the template database (tables, functions, extensions, etc.). Do you have any thoughts on how we might address this?

Hmmm I'm not sure why that would be occurring. @munjalpatel, could you:

• pg_dump your template database (schema only)
• pg_dump your current database created from the template (schema only)
• Send your target ddl
• Send the command you're running and it's output