How to Upgrade PostgreSQL Extensions: A Simple Guide (original) (raw)
This post was originally written in January 2023 and was updated in February 2025.
PostgreSQL is a powerful and flexible open source database that allows users to enhance functionality by installing extensions. Keeping these extensions up-to-date is essential for accessing new features, bug fixes, and security patches. This guide covers the essential process of upgrading PostgreSQL extensions, using pg_stat_monitor as a practical example.
Prerequisite: Installing an extension
Before you can upgrade an extension, it obviously needs to be installed first. This is typically done using the CREATE EXTENSION command. You’ll need the necessary privileges in your database.
For example, to install the default version specified in the extension’s control file:
| CREATE EXTENSION pg_stat_monitor; |
|---|
If you need a specific older version initially:
| CREATE EXTENSION pg_stat_monitor VERSION '2.0'; |
|---|
Step 1: Check the current extension version
Before upgrading a PostgreSQL extension, it’s crucial to know which version is currently installed. You can easily check this using the pg_extension system catalog view:
| SELECT extversion FROM pg_extension WHERE extname = 'pg_stat_monitor'; |
|---|
This query will return the version string (e.g., ‘1.0’, ‘2.0’) of the pg_stat_monitor extension currently active in your database.
Step 2: Performing the extension upgrade
Once you know the current version and have the files for the new version installed on your database server (this is a critical prerequisite – the ALTER EXTENSION command relies on the new SQL script files being available), you can perform the upgrade.
The command for upgrading PostgreSQL extensions is ALTER EXTENSION with the UPDATE TO option. Specify the target version you want to upgrade to:
| ALTER EXTENSION pg_stat_monitor UPDATE TO '2.0'; |
|---|
If you omit the version number, PostgreSQL attempts to upgrade to the latest version available (the default_version listed in the .control file of the newly installed package):
| ALTER EXTENSION pg_stat_monitor UPDATE; |
|---|
How PostgreSQL extension upgrades work (Behind the scenes)
Understanding the mechanism helps troubleshoot potential extension upgrade issues. A PostgreSQL extension typically includes:
- Shared Library Files (.so): Compiled code providing the core functionality. (These must be updated/installed first).
- SQL Script Files: Files defining the database objects (functions, tables, views, types) the extension uses. Crucially, these include upgrade scripts named like extension–old_version–new_version.sql (e.g., pg_stat_monitor–1.0–2.0.sql).
- Control File (.control): A metadata file telling PostgreSQL about the extension (name, default version, dependencies, scripts).
When you run ALTER EXTENSION pg_stat_monitor UPDATE TO ‘2.0’, PostgreSQL looks for the appropriate upgrade script (e.g., pg_stat_monitor–1.0–2.0.sql if upgrading from 1.0). It then executes this SQL script to modify the existing database objects, add new ones, or remove old ones, effectively transitioning the extension’s database structures to the new version.
The control file guides this process and defines key properties. Here’s a simplified example for pg_stat_monitor:
| # Example pg_stat_monitor.control contentcomment = 'Real-time monitoring and analysis of database activity' default_version = '2.0' module_pathname = '$libdir/pg_stat_monitor' # Path to shared libraryrelocatable = true# Potentially requires = 'pg_stat_statements' (example dependency) |
|---|
This file tells PostgreSQL the default version to install/upgrade to if unspecified and where to find the shared library.
Conclusion
Upgrading PostgreSQL extensions is a straightforward process using the ALTER EXTENSION … UPDATE TO command, provided the new extension files are already installed on the server. This allows you to keep your database extensions current, benefiting from the latest features and improvements. Understanding the role of the SQL upgrade scripts and the control file helps demystify the process. By following these steps, you can manage your PostgreSQL extensions effectively.
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.