pgxman is a package manager like npm, but instead of Javascript packages, it is for PostgreSQL extensions. It detects and streamlines extension operations and looks after dependency management, installation and uninstallation, based on the local development environment.

In It's 2024. Why Does PostgreSQL Still Dominate? we gathered that one of PostgreSQL reasons of popularity is its agile extension mechanism:

With extensibility as an architectural core, it is possible to be creative and stop worrying so much about discipline: you can try many extensions and let the strong succeed. Done well, the “second system” is not doomed; it benefits from the confidence, pet projects, and ambitions developed during the first system.Say:

• Do you need a distributed database? get Citus.
• Do you need Incremental View Maintenance (IVM) ? get pg_ivm.
• Import a SQLite database into Postgres? there's pgsqlite for that.
• Native Asynchronous Queries Within Postgres ? pg_later.
• Add columnar tables to Postgres? Hydra
• Turn it into a specialized vector store ? pg_vector
• Bake ElasticSearch FTS capabilities into it? pg_bmp25

and so on. The list is too big to be comprehensive.

As such the next problem is how do you keep track and how do you install an extension taking into consideration the extension's versions, its build and run dependencies, the underlying operating system and the platform architecture?

The solution is pgxman. It is a package manager that automatically detects and streamlines extension operations
based on the local development environment. If you know npm then you'll be right at home;instead of

npm install [extension name]

you do :

pgxman install [extension name]

For instance to install pg_vector , you do:

pgxman install pgvector

To install multiple extensions, specific extension versions, and a PG version becomes as easy as :

pgxman install --pg 15 pgvector=0. 5. 1 pg_ivm=1. 7. 0

pgxman uses a repository of buildkits to know what extensions are available. When you use search or install, the buildkit metadata is used to obtain information about that extension. A cached copy of this repository is stored in the pgxman folder in your user config directory.

Each buildkit specifies how to build each extension, and the buildkit build system builds it for each package manager.
When a buildkit is added or updated, a build is conducted automatically (using Github Actions) and the packages are stored in pgxman’s repository.

This way, pgxman is able to handle dependency management, installation, and uninstallation through your system's package manager.

Caveat, pxgman works only on local installations of Postgres. If you are on an Cloud managed instance, it can too but the provider has to add explicit support for it.And, it doesn't also support TLEs.

As a refresher on TLEs, we had a good look at them in Trusted Language Extensions Bring PostgreSQL Procedural UDFs To The Cloud where I report that:

PostgreSQL allows user defined functions to be written in other languages besides SQL and C. These other languages are generically called procedural languages (PLs). Procedural languages aren't built into the PostgreSQL server; they are offered by loadable modules. That way you can extend your database with powerful features not found in SQL.

This of course comes with security issues when the database invokes code like file system operations, or using statements that could interact with the operating system or database server process.

For that Trusted Language Extensions (TLE) came about. They are PostgreSQL extensions that you can safely run on your DB instance. Trusted Language Extensions do not provide access to the filesystem and are designed to prevent access to unsafe resources. Its runtime environment limits the impact of any extension defect to a single database connection.

As said, pxman does not support them, however there's another package manager, DbDev by Supababase, which is dedicated to installing TLEs.

Like pxman, dbdev comes with a package registry too which comes pre-loaded with a handful of useful pglets, like the olirice-asciiplot.

To install pxman with homebrew you do:

brew install pgxman/tap/pgxman

and without:

curl -sfL https://install. pgx. sh | sh -

After that you can start enjoying the extra functionality just added to your Postgres instance!

More Information

pgxman

pgxman on Github 

Related Articles

It's 2024. Why Does PostgreSQL Still Dominate?

The DbDev Package Manager For PostgreSQL TLEs

Trusted Language Extensions Bring PostgreSQL Procedural UDFs To The Cloud 

To be informed about new articles on I Programmer, sign up for our weekly newsletter, subscribe to the RSS feed and follow us on Twitter, Facebook or Linkedin.

Comments

Make a Comment or View Existing Comments Using Disqus

or email your comment to: comments@i-programmer.info