PostgreSQL "could not load library" — Why Did Extensions Disappear Post-Upgrade?
Diagnosing missing or incompatible extension libraries post-pg_upgrade: analyzing loadable_libraries.txt, shared_preload_libraries, and installing major-version packages.
Operational Verification & Reliability Metadata
The error string is a symptom, not the root cause. Do not restart the server blindly. Run the diagnostic queries below to pinpoint the actual failure mode.
▸Symptom
ERROR: could not load library "/usr/lib/postgresql/17/lib/pg_stat_statements.so":
/usr/lib/postgresql/17/lib/pg_stat_statements.so: undefined symbol: pg_stat_statements_internal
ERROR: could not access file "$libdir/postgis-3": No such file or directory
FATAL: could not load library "/usr/lib/postgresql/17/lib/timescaledb-2.14.so"This error occurs almost exclusively following a major version upgrade (pg_upgrade), manifesting in three distinct failure modes:
- 1`could not access file "$libdir/..."` + No such file or directory: The shared library (.so / .dll) is physically missing from the new PostgreSQL version's directory. The corresponding package was never installed for the target major version.
- 2`undefined symbol`: The binary exists but was compiled against incompatible PostgreSQL internal ABI headers. Typically happens when mixing official PGDG packages with native OS distribution packages.
- 3`FATAL: could not load library`: The missing module is registered under
shared_preload_librariesinpostgresql.conf; the postmaster fails to boot entirely.
$libdir token dynamically evaluates to the target version's pkglibdir (e.g. /usr/lib/postgresql/17/lib/). If you upgraded from 16 to 17, the postgis-3.so file living in PostgreSQL 16's directory is entirely invisible to 17. The user data was migrated seamlessly, but the compiled C library code is missing in the new engine directory.▸pg_upgrade Explicitly Warns About This
During upgrade executions, administrators often overlook the pre-flight notice:
Checking for presence of required libraries fatal
Your installation references loadable libraries that are missing from the
new installation. You can add these libraries to the new installation,
or remove the functions using them from the old installation.
A list of problem libraries is in the file:
loadable_libraries.txtpg_upgrade creates a plain-text file named `loadable_libraries.txt` in its working directory. It itemizes the exact missing shared libraries.
pg_upgrade --check days before the actual maintenance window. It performs non-destructive verification of schema catalog compatibility, page sizes, and extension libraries without copying data.▸Diagnostic Queries to Confirm Root Cause
1. Inspect Installed Extensions and Library Pointers
SELECT extname, extversion FROM pg_extension ORDER BY extname;Query distinct compiled shared object references:
SELECT DISTINCT probin
FROM pg_proc
WHERE probin IS NOT NULL
ORDER BY probin;probin reveals the target library string (e.g. $libdir/pg_stat_statements).
2. Validate Preloaded Libraries
SHOW shared_preload_libraries;
SHOW dynamic_library_path;3. Verify Filesystem Existence
Query the active library directory from the shell:
ls -la "$(pg_config --pkglibdir)"▸Step-by-Step Resolution
1. Inspect loadable_libraries.txt
Extract the exact list of missing module dependencies.
2. Install Packages for Target Major Version
Ensure package names strictly match the new PostgreSQL major release:
# Debian / Ubuntu (PostgreSQL 17 example)
apt-get update
apt-get install -y postgresql-17-postgis-3 postgresql-17-pg-stat-statements
# RHEL / Rocky Linux
dnf install -y postgis35_173. Normalize shared_preload_libraries
Remove hardcoded version-specific file names from postgresql.conf in favor of symbolic package names:
shared_preload_libraries = 'pg_stat_statements,timescaledb'4. Update In-Database Extension Metadata
ALTER EXTENSION postgis UPDATE;▸Related Error Guides & Hub Links
- •PostgreSQL Database System is Starting Up (H-50) — Post-upgrade recovery and startup checks
- •PostgreSQL Hub — Upgrade runbooks and extension management
Other Database Error Triage Playbooks
Other battle-tested diagnostic guides for high-severity database errors.
PostgreSQL Connection Exhaustion & too many clients Triage
Root causes of FATAL: sorry, too many clients already and remaining connection slots errors, read-only pg_stat_activity diagnosis with privilege boundaries, and two-tier pooling mitigation.
PostgreSQL password authentication failed: Root Cause and Fix
Four root causes of psql password authentication failed: incorrect password, md5/SCRAM mismatch, pg_hba.conf rule ordering, and port/cluster confusion. Step-by-step triage.
PostgreSQL connection refused: 8-Step Definitive Triage
Difference between connection refused, timeout, and no pg_hba entry; triage for listen_addresses, port conflicts, firewalls, and Docker container mappings.