This blog was first authored by Ibrar Ahmed in 2023. We’ve updated it in 2025 for clarity and relevance, reflecting current practices while honoring their original perspective.

PostgreSQL’s flexibility gets a lot of credit. Performance too. But what really sets it apart, especially for teams with growing systems or changing requirements, is extensibility. With tools like pg_stat_statements, PostGIS, pg_stat_monitor, and dozens more, you can shape PostgreSQL to fit how your application actually works.

But those extensions don’t update themselves. And if they’re out of date, you could be missing performance boosts, important bug fixes, or support for your current PostgreSQL version. Skip a step, and things might break; sometimes with noise, sometimes quietly in the background.

This guide shows you how to upgrade PostgreSQL extensions the right way. You’ll learn how to check your current version, install new files, and run a safe, clean upgrade. We’ll use pg_stat_monitor as an example, but the steps apply to most extensions you’ll run into. 

Start here: Is the extension already installed?

You can’t upgrade what isn’t installed. If you haven’t added the extension to your database yet, you’ll need to do that first. Run:

 

Or, if you need to install a specific version:

Step 1: Check your current extension version

Before you upgrade anything, find out what version you’re currently running. You can do this with a simple query:

This will return the version string (like ‘1.0’, ‘2.0’, etc.) for the active extension in your database. You’ll need this to match the correct upgrade path.

Discover practical ways to turn PostgreSQL into a more powerful, production-ready database with the right extensions and tools. Read the PostgreSQL Extension Handbook today.

Step 2: Make sure the new extension files are installed

This step often trips people up. Before you run the upgrade command, the new extension files must already be present on your server. If they’re missing, ALTER EXTENSION will fail; it can’t upgrade without the right SQL scripts or shared libraries.

How to get the files:

Step 3: Run the upgrade

You’re ready to upgrade. Use the ALTER EXTENSION command:

Or, if you want PostgreSQL to upgrade to the default (latest) version defined in the extension’s .control file:

That’s it. Behind the scenes, PostgreSQL runs the appropriate SQL script to update the extension from your current version to the target version. Which brings us to…

What’s actually happening under the hood?

Understanding how PostgreSQL upgrades extensions helps when things go wrong. Each extension includes a few key pieces:

  • Shared library files (.so): These contain compiled code and must be updated on the server.

  • SQL upgrade scripts: These are version-specific files that apply the database changes. For example:
     pg_stat_monitor–1.0–2.0.sql

  • Control file (.control): This tells PostgreSQL the default version, dependencies, and where to find the extension files.

When you run an ALTER EXTENSION … UPDATE TO, PostgreSQL finds the matching SQL script (based on your current and target versions) and applies the changes, modifying functions, views, tables, or whatever else the extension defined.

Here’s a snippet of what a control file might look like for pg_stat_monitor:

The control file handles version tracking and helps PostgreSQL locate what it needs. 

Don’t stop at the extensions, though

Upgrading an extension is just one part of keeping PostgreSQL healthy. But if you’re responsible for the bigger picture, i.e., things like performance, scalability, and cost control, it’s worth zooming out. Because running PostgreSQL at scale isn’t just about applying updates. It’s about designing an environment that’s easier to manage, cheaper to operate, and free from vendor lock-in.

That’s where our comprehensive collection of PostgreSQL resources comes in. It’s packed with practical insights to help you move beyond day-to-day maintenance and toward smarter, more sustainable decisions, whether you’re weighing proprietary options, evaluating self-managed trade-offs, or trying to rein in growing complexity.

Enterprise PostgreSQL

FAQ: Upgrading PostgreSQL extensions

Q1: What command is used for upgrading PostgreSQL extensions?
A: The primary command is ALTER EXTENSION extension_name UPDATE TO ‘new_version’;. You can also use ALTER EXTENSION extension_name UPDATE; to upgrade to the latest available default_version.

Q2: Do I need to install the new extension package before running ALTER EXTENSION?
A: Yes, absolutely. The ALTER EXTENSION command relies on the new version’s SQL script files (e.g., ext–1.0–2.0.sql) and potentially updated shared library files being present on the database server’s filesystem. Install the OS package (e.g., via apt, yum, or compiling from source) for the new version first.

Q3: How do I check which PostgreSQL extensions are installed and their versions?
A: You can query the pg_extension catalog: SELECT extname, extversion FROM pg_extension;. To check a specific extension, use SELECT extversion FROM pg_extension WHERE extname = ‘your_extension_name’;.

Q4: Do I need to restart PostgreSQL after upgrading an extension?
A: Usually, no. Running ALTER EXTENSION applies the necessary SQL changes within the database session. A restart is typically only required if the underlying shared library (.so file) needs to be reloaded due to changes in how it interacts with the server, or if changes were made to parameters in the .control file that require a server reload/restart, or if the upgrade is part of a larger PostgreSQL major version upgrade. Always check the specific extension’s documentation.

Subscribe
Notify of
guest

1 Comment
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Edwin Uy

Is upgrading the extension a mandatory exercise? Will it cause corruption if we don’t upgrade the extension after a major version upgrade?

0
Reply