Introduction After a PostgreSQL major version upgrade, installed extensions may be at versions incompatible with the new server version. This causes errors when querying extension-provided functions, creating extension-dependent objects, or even starting the database if critical extensions fail to load.

Symptoms - `ERROR: could not access file "$libdir/postgis-3": No such file or directory` - `ERROR: extension postgis has no installation script nor update path for version 3.4.2` - `SELECT * FROM pg_extension` shows extensions but functions return errors - Database fails to start with extension-related errors in logs - `pg_upgrade --check` reports extension compatibility issues

Common Causes - Extension shared library files not installed for the new PostgreSQL version - `pg_upgrade` does not automatically upgrade extension versions - Extension not available or not yet ported for the new PostgreSQL version - Multiple extension versions installed, with the wrong one being loaded - Custom or third-party extensions not updated for the new server version

Step-by-Step Fix 1. **List all installed extensions and their versions": ```sql SELECT e.extname, e.extversion, n.nspname AS schema_name, r.rolname AS owner FROM pg_extension e JOIN pg_namespace n ON n.oid = e.extnamespace JOIN pg_roles r ON r.oid = e.extowner ORDER BY e.extname; ```

  1. 1.**Check available extension versions":
  2. 2.```sql
  3. 3.-- List all available versions for an extension
  4. 4.SELECT * FROM pg_available_extension_versions
  5. 5.WHERE name = 'postgis'
  6. 6.ORDER BY version;
  7. 7.`
  8. 8.**Upgrade extensions to the latest compatible version":
  9. 9.```sql
  10. 10.-- Upgrade each extension
  11. 11.ALTER EXTENSION postgis UPDATE;
  12. 12.ALTER EXTENSION pg_stat_statements UPDATE;
  13. 13.ALTER EXTENSION pgcrypto UPDATE;
  14. 14.ALTER EXTENSION btree_gin UPDATE;

-- Check for extensions that cannot be upgraded SELECT extname, extversion FROM pg_extension WHERE extname NOT IN (SELECT name FROM pg_available_extensions); ```

  1. 1.**Install missing extension packages for the new PostgreSQL version":
  2. 2.```bash
  3. 3.# Ubuntu/Debian
  4. 4.sudo apt install postgresql-16-postgis-3 postgresql-16-pgstatstatements

# RHEL/CentOS sudo yum install postgresql16-postgis3 postgresql16-contrib

# Restart PostgreSQL after installing sudo systemctl restart postgresql ```

  1. 1.**Handle extensions not available for the new version":
  2. 2.```sql
  3. 3.-- If an extension is not available, you may need to:
  4. 4.-- 1. Dump the extension data
  5. 5.-- 2. Drop the extension
  6. 6.-- 3. Upgrade PostgreSQL
  7. 7.-- 4. Recreate the extension

-- Before upgrade: pg_dump -d mydb --section=data --table='public.spatial_ref_sys' > spatial_data.sql

-- After upgrade, if postgis is available: CREATE EXTENSION postgis; psql -d mydb -f spatial_data.sql ```

Prevention - Run `pg_upgrade --check` before performing the actual upgrade - Test the upgrade process on a staging database with all extensions - Document all installed extensions and their versions before upgrading - Check extension compatibility with the target PostgreSQL version beforehand - Use `pg_dumpall --globals-only` before upgrading to preserve extension configurations - Subscribe to extension release notifications for the extensions you depend on - Consider using PostgreSQL's `shared_preload_libraries` for extensions that need it