Administration
Managing Extensions
16
Copyright
© Postgres Professional, 2016–2026
Authors: Egor Rogov, Pavel Luzanov, Ilya Bashtanov, Igor Gnatyuk
Translated by: Elena Sharafutdinova
Photo: Oleg Bartunov (Phu Monastery and Bhrikuti Peak, Nepal)
Use of Course Materials
Non-commercial use of course materials (presentations, demonstrations) is
allowed without restrictions. Commercial use is possible only with the written
permission of Postgres Professional. It is prohibited to make changes to the
course materials.
Feedback
Please send your feedback, comments and suggestions to:
edu@postgrespro.ru
Disclaimer
Postgres Professional assumes no responsibility for any damages and
losses, including loss of income, caused by direct or indirect, intentional or
accidental use of course materials. Postgres Professional company
specifically disclaims any warranties on course materials. Course materials
are provided “as is,” and Postgres Professional company has no obligations
to provide maintenance, support, updates, enhancements, or modifications.
2
Topics
Extensions in PostgreSQL
Creation and Management of Extensions
Update of Extensions
Features of the pg_dump
3
Extensibility of PostgreSQL
Features
functions and programming languages
data types, operators, access methods
foreign data wrappers (FDW)
Mechanisms
modifiable system catalog
API for external handlers
loading and execution of user code
Extensibility is the the core feature of PostgreSQL. It allows to add new
functionality on the fly without changing the server code.
Thus, we can add programming languages and develop functions using
them, define new types of data and operators to work with them, create new
access methods for data types, develop foreign data wrappers to connect to
external sources.
To make this possible, the PostgreSQL system catalog stores a large
amount of information about database objects. This information is not
hardcoded in the server source code. Users can modify the contents of the
system catalog tables, thereby adding new objects and related functionality.
In addition, PostgreSQL source code has a large number of hooks and
various APIs for connecting user functions. This enables the development of
extensions such as pg_stat_statements, auto_explain, pldebugger, and
many others.
The final key mechanism is the ability to load user code into server
processes. For example, we can develop a shared library and incorporate it
during operation.
Note that the execution of poorly written user code by server processes can
lead to catastrophic consequences. Only verified code from reliable sources
should be trusted.
4
Extensions
Group of related database objects
creating all objects with a single command
individual objects cannot be deleted
pg_dump maintains relationships between objects
tools for updating to a new version
Sources
core distribution (contrib)
external extensions
ability to create your own extensions
There are situations when several database objects are logically related.
For example, several data types, functions and operators, operator classes.
Such a relationship can be made explicit using the extension mechanism.
This makes it easier to manage objects:
●
All objects are created with a single command.
●
Individual objects cannot be deleted, an extension can only be deleted
completely.
●
The relationships between objects are also maintained when creating a
backup with the pg_dump utility.
●
There are tools for managing extension versions.
PostgreSQL includes a significant number of useful extensions, some of
which we have already used.
Another source is PostgreSQL Extension Network (PGXN), an extension
network similar to CPAN for Perl: https://pgxn.org/
Extensions can also be distributed in other ways, including via package
repositories of OS distributions.
5
Creating an Extension
1.0
CREATE EXTENSION
VERSION '1.0'
name--1.0.sql
name.control
creation
script
parameters
DROP EXTENSION
The extension is installed into the database using the CREATE EXTENSION
command. This requires two files:
●
Control file “name.control” with extension parameters
●
Script for creating extension objects “name-version.sql”
The version is typically in the format “1.0,” “1.1,” etc., but this is not
necessary: it can consist of any characters (but it should not contain “--” and
begin or end with “-”).
The version is usually not specified in the CREATE EXTENSION command,
since the current version is set in the control file (the default_version
parameter) and is used by default.
Other parameters in the control file specify whether the extension's objects
can be relocated between schemas (relocatable), list dependencies on other
extensions (requires), restrict installation to superusers only (superuser), etc.
The extension is removed by the DROP EXTENSION command. A separate
script for deleting objects is not required.
7
Extension Update
1.0
1.1
ALTER EXTENSION
UPDATE TO '1.1'
1.2
ALTER EXTENSION
UPDATE TO '1.2'
CREATE EXTENSION
VERSION '1.0'
name--1.0.sql
name.control
name--1.0--1.1.sql name--1.1--1.2.sql
creation
script
update
script
parameters
name--1.2.control
additional
parameters
The extension version is updated using the ALTER EXTENSION UPDATE
command. In this case, there must be an update script “name-old-version--
new-version.sql” containing the commands necessary for updating.
We can also change the control file “name.control,” updating the current
version and, possibly, other parameters.
If needed, there can also be a separate control file bound to the version. For
example, if version 1.2 introduces a dependency on another extension, then
it is better to indicate this dependency not in the main control file, but in an
additional file for version 1.2. The parameters specified in the additional
control file have priority over those in the main file.
The example on the slide shows update scripts 1.0→1.1 and 1.1→1.2. While
a 1.0→1.2 script is possible, it is typically not required: the extension
mechanism automatically determines the update path based on the
available transitions between versions. For example, if version 1.0 is
installed, it can be updated directly to version 1.2: the 1.0→1.1 script is
automatically applied, followed by the 1.1→1.2 script.
Similar to creation, the target version is usually not specified during an
update. In this case, the extension is updated to the latest version set in the
main control file.
9
Takeaways
Extensibility is one of the most important features of
PostgreSQL
An extension is a package of related database objects
The extension mechanism includes tools for version updates
and pg_dump support
10
Practice
1. Install the uom extension and check if it appears in the list of
available extensions.
2. Create the uom extension without specifying a version.
Check the version and the scripts that were executed.
3. Add mile and yard to the reference table.
4. Change access to the reference table: the SELECT privilege
should be granted only to a specific role, not to all users.
5. Check how the pg_dump exports extension objects: table, type,
functions and operators, table contents, access privileges.
1. Extension files are located in the uom subdirectory of the student home
directory. The installation process is similar to the one used in the
demonstration.
3. When adding the data, ensure that the value for new entries in the
“predefined” column is set to false.
Conversion factors:
1 mile = 1609.344 m
1 yard = 0.9144 m
