release postgresql extension

Question

I'm developing application that holds data in postgres. So i must prepare database before working with application, there must be created few tables. I'm creating this tables by running sql code but i think it's not convenient after i found this doc:

A useful extension to PostgreSQL typically includes multiple SQL objects; for example, a new data type will require new functions, new operators, and probably new index operator classes. It is helpful to collect all these objects into a single package to simplify database management

The main advantage of using an extension, rather than just running the SQL script to load a bunch of "loose" objects into your database, is that PostgreSQL will then understand that the objects of the extension go together

I believe that i must use this approach

What i don't understand is that how can i share my extension. I thought that it works like maven, you create your extension with custom types, functions, tables and than you can pack it, name it (eg my-ext-0.1), give a version and release into some kind of a repository. After that you can connect to a database, run sql 'create extension my-ext-0.1' and have everything done :)

I thought that 'create extension' command will download extension and install it without downloading this by hands. I use maven, ivy and i expected similar behaviour from postgresql.

Documentation says that you need to place your extension files under some directory and only than run 'create extension' under some database.

How do you create your extensions and share them between different servers?

Please before reading https://www.postgresql.org/docs/current/static/extend.html, check out https://www.postgresql.org/docs/current/static/plpgsql.html — Vao Tsun, May 05 '17 at 21:07

score 2 · Answer 1 · answered May 05 '17 at 21:19

Postgres extensions do not work like this. They can have access to database internals and can run any code as database OS user. Therefore installing them is typically limited only to superusers, from a specific directory and only some of them are available on managed hosting servers.

I though that you can achieve something similar with installing your supplemental functions, types and tables in a special schema which is added to a search path. Upgrade would then be as simple as:

drop schema mylib cascade; -- don't do this!!!
create schema mylib;
\i mylib.sql

But unfortunately this would also remove all dependent objects from other schemas - columns using a custom type, triggers using a custom function etc. So it's not a solution for your problem.

Creation script, separate schema, modify search path seems a pretty sane way to go. Upgrades that involve tables or data require version-specific upgrade scripts in order to preserve data. — Glenn, May 06 '17 at 03:21

score 2 · Answer 2 · answered May 05 '17 at 21:22

I'd rather create my functions, types and all in my schema, using available extensions and "standard" languages.

Postgres will not download your extension (unless you create extension that will add this functionality to postgres). But your extension should be still created "usual" way.

to check your "directory for extension", run:

t=# create extension "where should I put control file";
ERROR:  could not open extension control file "/usr/local/share/postgresql/extension/where should I put control file.control": No such file or directory

And repeating comment, before extending SQL, please check out plpgsql and existing commands.

When you get bored and make sure existing postgres functionality is too limited, install postgres-contrib package and check other extensions as best practices. And of course check out https://pgxn.org/

release postgresql extension

2 Answers2