Distributing and managing Postgres SCHEMAs with an EXTENSION?

Question

Postgres permits distribution and management with CREATE EXTENSION. Is it more proper to write internal EXTENSIONS to manage third-party provided data, or to use scripts that DROP and CREATE SCHEMA? What are the pro's and con's to both?

Right now I tend to write,

./mySchema.psql
./mySchema/01_schema.sql
./mySchema/02_datatypes.sql
./mySchema/10_tables.sql

Etc.

And, I install all of that into SCHEMA mySchema. I'm wondering if I should change my flow and upgrade to use CREATE EXTENSION.

Answer 1

It may make sense to distribute to third party PG users as EXTENSIONS, but it doesn't make sense to bundle their data as EXTENSIONS.

• it's more work,
• it won't be backed up by pg_dump

Because it won't be backed up by pg_dump, you'll have to add to your development repository their data rather than just the initial load scripts. For the added complexity of backing things up, it doesn't seem advisable to do this.