A PostgreSQL major version upgrade can affect more than tables, indexes, and SQL queries. Extensions are another part of the database that needs to be checked before the migration.
Many production databases depend on extensions for features that PostgreSQL does not provide directly. An application might use an extension for spatial data, cryptography, statistics, UUID generation, text search, or another database capability.
The database can appear healthy after an upgrade while an extension-dependent feature is no longer available, has changed behavior, or cannot be upgraded cleanly.
That is why extension compatibility should be checked before the PostgreSQL upgrade rather than discovered during application testing after the migration.
Why PostgreSQL Extensions Matter
A PostgreSQL extension adds functionality to the database without requiring the feature to be implemented entirely inside the application.
For example, an application might have:
CREATE EXTENSION IF NOT EXISTS pgcrypto;and then use:
SELECT gen_random_uuid();The application may depend on that function without explicitly thinking about the extension behind it.
Other applications may depend on extensions for:
geographic data
full-text search
cryptographic functions
query statistics
specialized indexes
UUID generation
database administration
foreign data access
This creates an important dependency:
Application
|
v
SQL / Database Function
|
v
PostgreSQL Extension
|
v
PostgreSQL VersionIf any part of that chain changes during an upgrade, application behavior can be affected.
Start by Listing Installed Extensions
The first step is to create an inventory of the extensions currently installed.
Run:
SELECT
extname,
extversion
FROM pg_extension
ORDER BY extname;This gives you a basic list such as:
extname extversion
---------------- ----------
pgcrypto 1.3
pg_stat_statements 1.10Do not rely on memory or application documentation alone.
The database itself is the most reliable starting point for discovering what is actually installed.
Check Extensions in Every Database
PostgreSQL extensions are installed per database.
This is an important detail.
If a PostgreSQL server contains:
application_db
reporting_db
audit_dbone database may have an extension that another database does not.
Therefore, checking only the primary application database can produce an incomplete inventory.
Create an extension inventory for every database that matters to the migration.
A simple process is:
List databases
|
v
Connect to each database
|
v
Run pg_extension query
|
v
Save results
|
v
Compare with target PostgreSQL environmentCheck the PostgreSQL Version
Record the current PostgreSQL version before investigating extension compatibility.
SELECT version();You can also use:
SHOW server_version;Save the result alongside the extension inventory.
For example:
Current PostgreSQL
14.x
Extension
pgcrypto
Installed version
1.3Then document the target PostgreSQL version and the extension version that will be used after the upgrade.
This makes the compatibility review explicit instead of relying on assumptions.
Check Which Extensions Are Available
An extension being installed in production is different from an extension being available in the target PostgreSQL environment.
PostgreSQL can show extensions that are available to be installed through:
SELECT
name,
default_version,
comment
FROM pg_available_extensions
ORDER BY name;This helps answer two different questions:
Is the extension installed?
|
v
pg_extension
Is the extension available?
|
v
pg_available_extensionsBoth checks matter.
An extension might be installed on the old environment but missing from the new PostgreSQL installation.
Compare Installed and Available Versions
You can combine the two views to inspect the current extension version and the available version:
SELECT
e.extname,
e.extversion AS installed_version,
a.default_version AS available_version
FROM pg_extension e
LEFT JOIN pg_available_extensions a
ON a.name = e.extname
ORDER BY e.extname;This provides a useful first comparison.
For example:
extension installed available
pgcrypto 1.3 1.3
pg_stat_statements 1.10 1.11A difference does not automatically mean there is a problem.
It means the extension deserves further investigation.
Understand the Difference Between PostgreSQL and Extension Versions
PostgreSQL has its own server version.
Extensions have their own versions.
For example:
PostgreSQL
|
+-- Server version
|
+-- pgcrypto version
+-- PostGIS version
+-- pg_stat_statements versionUpgrading PostgreSQL does not mean that every extension automatically changes to the newest extension version.
The extension may need to be upgraded separately.
This is why the upgrade plan should contain both:
PostgreSQL upgrade
+
Extension compatibility and upgraderather than treating them as one operation.
Check Extension Dependencies
Some extensions depend on other extensions or database objects.
You can inspect installed extension relationships using PostgreSQL's system catalogs.
For a basic inventory, start with:
SELECT
extname,
extversion
FROM pg_extension
ORDER BY extname;Then inspect the extension documentation and installation requirements for every important extension.
This is particularly important for extensions that provide custom types, functions, operators, or index access methods used throughout the application.
Find Applications Using Extension Features
Knowing that an extension is installed is only the first step.
The next question is:
Does the application actually depend on it?For example, an application using pgcrypto might call:
SELECT gen_random_uuid();A spatial application may use geographic types and functions.
A monitoring system may depend on pg_stat_statements.
Search application source code, database functions, migration scripts, and SQL files for extension-specific objects.
Look for:
CREATE EXTENSION
extension-specific functions
extension-specific types
extension-specific operators
extension-specific indexes
extension-specific schemasThis creates a dependency map.
Example Dependency Map
Suppose an application uses:
pgcryptoand the application code contains:
SELECT gen_random_uuid();The dependency looks like:
Order Service
|
v
gen_random_uuid()
|
v
pgcrypto
|
v
PostgreSQLDuring an upgrade, every layer needs to remain functional.
This is much more useful than simply writing "pgcrypto is installed" in a migration checklist.
Check Extension Schemas
Some extensions install objects into specific schemas.
Inspect installed extension information:
SELECT
e.extname,
e.extversion,
n.nspname AS schema_name
FROM pg_extension e
JOIN pg_namespace n
ON n.oid = e.extnamespace
ORDER BY e.extname;Schema placement can matter when application code references objects explicitly.
For example:
SELECT extension_schema.some_function();If the schema changes or the application depends on a specific search path, behavior may change.
Check the Search Path
Inspect the current search path:
SHOW search_path;This matters when application code calls database objects without specifying their schema.
For example:
SELECT some_extension_function();may depend on the current search path.
A more explicit reference is:
SELECT extension_schema.some_extension_function();During migration testing, verify that functions and types resolve correctly.
Check Extension-Related Permissions
Extensions can create functions, types, schemas, and other database objects.
Application roles may require permission to use those objects.
Review the database roles used by the application.
For example:
SELECT
rolname,
rolsuper,
rolcreatedb,
rolcreaterole,
rolcanlogin
FROM pg_roles
ORDER BY rolname;The application should not need unnecessary administrative privileges simply because an extension is installed.
Test the extension features using the same database role that the application uses in production.
Test Extensions With the Application Role
An administrator being able to run:
SELECT some_extension_function();does not prove that the application can run it.
The application may use a restricted role.
This is why integration testing should use realistic permissions.
For example:
Administrator
|
+-- Extension works
Application role
|
+-- Extension function worksThe second test is the one that matters for application compatibility.
Check PostGIS Separately
PostGIS deserves special attention because it introduces a large amount of database functionality.
A production application may depend on:
geometry
geography
spatial indexes
coordinate transformations
distance calculations
spatial predicates
For example:
SELECT ST_Distance(
location,
ST_SetSRID(ST_Point(85.1376, 25.5941), 4326)
)
FROM locations;If the application depends on spatial behavior, test actual production queries rather than checking only whether the extension appears in pg_extension.
Also test spatial indexes and important queries after the upgrade.
Check pg_stat_statements
If your monitoring or performance tooling depends on pg_stat_statements, include it in the upgrade test.
First check whether it is installed:
SELECT
extname,
extversion
FROM pg_extension
WHERE extname = 'pg_stat_statements';Then verify that the target PostgreSQL environment supports the required version and configuration.
If your performance monitoring depends on this extension, a successful database upgrade without functioning query statistics can leave your operations team with incomplete visibility.
Check Extension Upgrade Paths
An extension may support multiple versions.
Inspect the available extension information:
SELECT
name,
default_version,
installed_version
FROM pg_available_extensions
WHERE installed_version IS NOT NULL
ORDER BY name;If an upgrade is required, test it in the non-production environment first.
For example:
ALTER EXTENSION pgcrypto UPDATE;Do not run extension upgrades directly in production simply because the command is short.
The command itself may succeed while application behavior changes afterward.
Test Extension Upgrades Separately
When possible, test the extension upgrade independently from the application upgrade.
For example:
Test 1
Current PostgreSQL
Current extension
Application
Test 2
Target PostgreSQL
Current compatible extension
Application
Test 3
Target PostgreSQL
Target extension
ApplicationThis helps identify where a problem was introduced.
If Test 2 works and Test 3 fails, the extension change becomes the likely area of investigation.
Check Database Dumps
Before migrating, test whether your backup and restore process handles extension dependencies correctly.
A schema-only dump can provide useful information:
pg_dump --schema-only production_db > schema.sqlSearch the resulting file for:
CREATE EXTENSIONFor example:
grep "CREATE EXTENSION" schema.sqlThis provides another way to identify extensions represented in the database schema.
Remember that a dump may contain references to extensions without containing the extension binaries themselves.
The target PostgreSQL environment still needs the required extension packages or components available.
Test Restore on the Target PostgreSQL Version
A useful migration test is:
Production backup
|
v
Target PostgreSQL
|
v
Restore
|
v
Extension validation
|
v
Application testsAfter the restore, run:
Join the conversation! Your thoughts help the community grow.