In PostgreSQL, why do some extensions need shared_preload_libraries and a server restart while others work right after CREATE EXTENSION?
answer
- who loads the library, and when
- shared memory is sized at startup
- postmaster context means restart
- LOAD and session_preload_libraries
basics
~20 sLibraries that reserve shared memory, start background workers or install global hooks must be loaded by the postmaster at startup through shared_preload_libraries, which takes a restart. Libraries that only provide functions load on first call, so CREATE EXTENSION is enough.
solid answer
~40 sMost C extensions (`ltree`, `pgcrypto`, `hstore`, PostGIS) only provide functions. A backend loads their `.so` the first time one is called, so `CREATE EXTENSION` is enough. Some libraries must reserve shared memory, request lightweight locks, register background workers or install hooks before any backend forks. That work can only happen in the postmaster at startup, and that is what `shared_preload_libraries` is for. Its context is `postmaster`, so a change needs a full restart: a reload only sets `pending_restart` in `pg_settings`. A misspelled or missing library stops the server from starting. Examples are `pg_stat_statements` (whose functions error with 'must be loaded via shared_preload_libraries'), pgAudit and Citus. They still need `CREATE EXTENSION` for their SQL objects. For a library needed only in some sessions, `session_preload_libraries` or `LOAD` avoids the restart.
code
sql · 13 linesSHOW shared_preload_libraries; -- pg_stat_statements
-- ALTER SYSTEM replaces the whole list: keep what is already there
ALTER SYSTEM SET shared_preload_libraries = 'citus', 'pg_stat_statements';
SELECT pg_reload_conf();
SELECT name, setting, context, pending_restart
FROM pg_settings
WHERE name = 'shared_preload_libraries';
-- setting still the old value, context = postmaster, pending_restart = t
-- after restarting the server, in each database that needs it:
CREATE EXTENSION citus;go deeper
Remember that a few extensions, such as pg_stat_statements, need an entry in shared_preload_libraries and a server restart before they work.
Explain why: shared memory, lightweight locks, background workers and global hooks can only be set up at postmaster start. Know the postmaster context and the session_preload_libraries alternative.
Show safe rollout: install packages on every node, rewrite the whole list, confirm pending_restart, restart standbys first, respect Citus's ordering, then run CREATE EXTENSION per database.
Weigh what earns a preload slot on a shared fleet: each one adds restart coupling and process start-up cost, and the list needs an owner.
## Two ways a library gets into a backend PostgreSQL runs one server process per connection, forked from the **postmaster**. A C extension's shared library (`.so`) can enter those processes in two ways: - **On demand.** The first time a session calls a C function, the backend loads the library named in that function's definition, looking it up on `dynamic_library_path` (default `$libdir`). `ltree`, `hstore`, `pgcrypto` and PostGIS work this way, so `CREATE EXTENSION` is all they need. - **Preloaded.** The postmaster loads the libraries listed in `shared_preload_libraries` at server start, before any backend exists, and every forked backend inherits them. ## What only the postmaster can do Some libraries have to initialise at postmaster start, because that is the only moment certain resources can be set up: - reserving **shared memory**, which the server sizes and creates once at startup. `pg_stat_statements` keeps its statistics table there; - requesting **lightweight locks** that protect that memory; - registering **background workers**, such as Citus's maintenance daemon; - installing **hooks** that every backend must run, such as planner, executor or utility hooks (Citus, pgAudit). A library like this checks at load time whether it is being preloaded. `pg_stat_statements`, for example, does nothing when loaded any other way, and its functions then fail with *pg_stat_statements must be loaded via "shared_preload_libraries"*. pgAudit's documentation likewise says it must be preloaded or no audit logging happens. ## The three preload settings | Setting | `pg_settings.context` | Who may set it | Takes effect | Typical use | |---|---|---|---|---| | `shared_preload_libraries` | `postmaster` | superuser, via `postgresql.conf` or `ALTER SYSTEM` | server restart | shared memory, workers, global hooks | | `session_preload_libraries` | `superuser` | superusers and roles granted `SET` on it | each new session | hook-only modules for chosen roles or databases | | `local_preload_libraries` | `user` | any user | each new session | only libraries placed in `$libdir/plugins` | All three default to an empty string and take a comma-separated list. An explicit `LOAD 'name'` loads a library into the current session only, and non-superusers may load only from `$libdir/plugins`. ## Rolling a preload change out safely 1. **Install the package on the primary and every standby.** If a listed library is missing, that server will not start. 2. **Read the current value** with `SHOW shared_preload_libraries` and write back the **whole** list. The setting is a single list value, so `ALTER SYSTEM SET` replaces it rather than appending to it. 3. **Reload and verify**: `pending_restart` in `pg_settings` should become `true`. The `setting` column keeps showing the old, running value. 4. **Restart** in a maintenance window. With replicas, restarting standbys first and then switching over keeps the outage short; switchover itself belongs to the replication topics. 5. **Create the SQL objects**: run `CREATE EXTENSION` in each database that needs the extension's views and functions. Preloading the library does not create them. ## Order matters for some libraries Libraries load in list order, and their hooks chain in that order. Citus refuses to start unless it was loaded before any other hook-installing library, so its documentation puts it first: `shared_preload_libraries = 'citus, pg_stat_statements'`. ## The cost of preloading Preloading is not free. The manual notes that the time to start each new server process might increase slightly, even if that process never uses the library, and every change needs a restart. So list only libraries most sessions need, or that cannot work otherwise. A module that only installs per-session hooks, such as `auto_explain`, can instead go in `session_preload_libraries`, set with `ALTER ROLE ... SET` or `ALTER DATABASE ... SET`. That needs no restart and affects only new sessions. Which settings these modules expose, and how to read their output, belongs to the monitoring topics; this answer is only about getting them loaded. ## Diagnosing a preload problem - **The server will not start after a change.** The log names the library it could not load. Fix the package or the list. If `ALTER SYSTEM` wrote the value, it lives in `postgresql.auto.conf`. - **The extension's functions error with 'must be loaded via shared_preload_libraries'.** Either the entry is missing or the server was never restarted. Compare `SHOW shared_preload_libraries` with `pending_restart` in `pg_settings`. - **It works on the primary but fails after a failover.** The standby's configuration or packages differ. Keep preload lists and package versions identical on every node that can become primary. - **The extension exists in one database but not another.** Preloading is server-wide, but `CREATE EXTENSION` is per database. Run it wherever the views or functions are needed.
- In PostgreSQL, what goes wrong if you run ALTER SYSTEM SET shared_preload_libraries = 'citus' on a server that already preloads pg_stat_statements?The setting is one list value, so `ALTER SYSTEM` replaces it. After the restart `pg_stat_statements` is no longer loaded: its view and functions fail with an error saying it must be loaded via `shared_preload_libraries`, and query statistics stop being collected. Read the current value with `SHOW` first and write the full list, such as `'citus, pg_stat_statements'`.
- Why can't you run LOAD 'pg_stat_statements' in a PostgreSQL session instead of restarting the server?`LOAD` maps the library into one backend after the postmaster has already sized and created shared memory. A module that needs to reserve shared memory at startup cannot initialise that late, and `pg_stat_statements` simply skips its setup, so its functions still raise the 'must be loaded via shared_preload_libraries' error. `LOAD` and `session_preload_libraries` suit hook-only modules such as `auto_explain`.
- On a PostgreSQL server running Citus, why does the order of entries in shared_preload_libraries matter?Libraries are loaded, and their hooks installed, in list order. Citus installs planner, executor and utility hooks and refuses to start if another hook-installing library was loaded before it, so `citus` goes first, as in `'citus, pg_stat_statements'`. Each database that will hold distributed tables still needs `CREATE EXTENSION citus`.
- Every C extension must be listed in shared_preload_libraries before CREATE EXTENSION works.
- pg_reload_conf() activates a new shared_preload_libraries entry without a restart.
- Once a library is preloaded, no CREATE EXTENSION is needed in any database.
- A misspelled shared_preload_libraries entry is skipped with a warning and the server starts.
- ALTER SYSTEM SET shared_preload_libraries appends the new library to the existing list.