layout: doc title: Install pg_local_cache on PostgreSQL 14-18 seo_title: Install pg_local_cache on PostgreSQL 14-18 description: Install the pg_local_cache PostgreSQL extension with verified Linux binaries or PGXS, then configure preload, restart, verify, and recover safely. section: Install permalink: /docs/INSTALL_EXISTING.html

last_modified_at: “2026-09-05”

Install pg_local_cache on an existing PostgreSQL server

Install the extension with a verified Linux package or build it with PostgreSQL’s PGXS toolchain. Both paths require one controlled PostgreSQL restart before CREATE EXTENSION.

Plan a maintenance window: first activation changes shared_preload_libraries. Preserve its existing entries and restart the correct cluster only after preflight succeeds.

Choose an installation path

Path Best for Restart owner
Latest verified binary Local Linux amd64 cluster pg_ctl bootstrap
Fixed verified binary Production and managed operations systemd, pg_ctl, or external operator
PGXS source build Unsupported platform or custom PostgreSQL installation Your normal operations workflow

Published binaries support PostgreSQL 14-18 on Linux amd64 with glibc or musl. The fixed-version examples below use pg_local_cache 2.0.1.

Fast binary install

For a local cluster controlled by pg_ctl:

curl -fsSL https://github.com/profundium/pg_local_cache/releases/latest/download/install-latest.sh | bash -s -- app

Replace app with the database name. This enables SQL-only mode with pg_local_cache.port = 0.

The bootstrap resolves one release tag, verifies fetch-release.sh against that release’s SHA256SUMS, selects the matching PostgreSQL and libc archive, verifies it, installs, restarts, creates the extension, and runs local_cache.health().

If curl | bash is outside your policy, inspect the script first:

curl -fsSLO https://github.com/profundium/pg_local_cache/releases/latest/download/install-latest.sh
less install-latest.sh
bash install-latest.sh app

Controlled binary install

Download a fixed release with its published helper:

curl -fsSLO https://github.com/profundium/pg_local_cache/releases/download/v2.0.1/fetch-release.sh
bash fetch-release.sh --release-tag v2.0.1 --output-directory ./pg_local_cache-package

Run preflight, then choose the restart owner explicitly:

sudo ./pg_local_cache-package/install.sh preflight --database app
sudo ./pg_local_cache-package/install.sh install \
  --database app \
  --restart-method systemd \
  --systemd-unit postgresql@16-main

Supported restart methods are systemd, pg_ctl, and none. Use none with Patroni, a Kubernetes operator, or another external controller. Restart through that controller, then verify:

sudo ./pg_local_cache-package/install.sh verify --database app

The installer prints a state directory. Keep it until verification succeeds; it contains the online backup required by recover.

Build from source

Use the same pg_config as the target PostgreSQL server. Install its server development headers, a C compiler, and GNU Make first.

git clone --branch v2.0.1 --depth 1 https://github.com/profundium/pg_local_cache.git
cd pg_local_cache
make PG_CONFIG=/usr/lib/postgresql/16/bin/pg_config
sudo make install PG_CONFIG=/usr/lib/postgresql/16/bin/pg_config

Build from a clean checkout so the binary records its Git commit. Source installation copies extension files only. Continue with preload configuration, restart, and the SQL initialization below.

Configure before restart

Minimum SQL-only configuration using the default capacity and memory budget:

shared_preload_libraries = 'pg_local_cache'
pg_local_cache.database = 'app'
pg_local_cache.role = 'local_cache_worker'
pg_local_cache.cache_entries = 16384
pg_local_cache.memory_budget_mb = 384
pg_local_cache.port = 0

Keep any existing shared_preload_libraries entries. Replace app with the actual database name here and in the SQL grants below. The memory budget is for the extension, not the whole PostgreSQL server.

Size cache_entries, relation states, clients, workers, and memory_budget_mb together. Binary installer preflight rejects inconsistent plans. Source builds require the same capacity review before restart; do not increase the entry count without reviewing the memory budget.

Initialize a source installation

After restarting, connect to the configured database as a database superuser. For a first manual installation, run:

CREATE EXTENSION IF NOT EXISTS pg_local_cache;
CREATE ROLE local_cache_worker LOGIN NOINHERIT NOSUPERUSER
    NOCREATEDB NOCREATEROLE NOREPLICATION NOBYPASSRLS;
GRANT CONNECT ON DATABASE app TO local_cache_worker;
GRANT USAGE ON SCHEMA local_cache TO local_cache_worker;
GRANT SELECT ON TABLE local_cache.mapping TO local_cache_worker;

The binary installer creates this role and its metadata grants; skip this block when it has already completed that setup. For a custom pg_local_cache.role, use that name consistently. Do not repurpose a role that owns application tables.

The role is required even with pg_local_cache.port = 0. Table attachment validates it in SQL-only mode too. It must be separate from the table owner and have the attributes and metadata grants shown above. attach_table manages its access to each mapped table. No password or network listener is needed for SQL-only operation.

Attach a table

Use an existing permanent table with a supported primary key. As a database superuser in the configured database:

SELECT local_cache.attach_table('public.items'::regclass);
SELECT local_cache.health();

Grant an existing application role only what it needs:

GRANT SELECT ON public.items TO app_user;
GRANT USAGE ON SCHEMA local_cache TO app_user;
GRANT EXECUTE ON FUNCTION local_cache.mget(regclass, anyarray) TO app_user;

Ordinary SELECT is not rewritten by the extension.

Verify cold fill and warm hit

SELECT local_cache.invalidate('public.items');
SELECT local_cache.mget('public.items'::regclass, ARRAY[1]::bigint[]);
SELECT local_cache.mget('public.items'::regclass, ARRAY[1]::bigint[]);
SELECT local_cache.stats();

Confirm local_cache.health() is ready, the mapping has converged, and SQL cache counters move as expected.

Enable optional RESP2

RESP2 adds a listener, worker processes, and one shared token. It uses the same dedicated PostgreSQL role required for table attachment:

sudo ./pg_local_cache-package/install.sh preflight \
  --database app \
  --mode resp \
  --token-file /secure/path/token

sudo ./pg_local_cache-package/install.sh install \
  --database app \
  --mode resp \
  --token-file /secure/path/token \
  --restart-method systemd \
  --systemd-unit postgresql@16-main

Keep the listener on 127.0.0.1 or behind authenticated TLS. RESP clients share the configured worker role and do not receive per-client PostgreSQL ACL context.

Recover a failed binary install

Use the state directory printed by the installer:

sudo ./pg_local_cache-package/install.sh recover \
  --state-directory /path/printed/by/install

Do not recover after a new postmaster has accepted traffic until you review the recorded state and operational impact.

Troubleshooting

  • Preload error: confirm the target cluster’s configuration and restart the correct postmaster.
  • Worker role missing or rejected: complete the SQL initialization above, including its role attributes and metadata grants, even in SQL-only mode.
  • Table rejected: use a permanent, non-partitioned, non-RLS table with a supported primary key.
  • mget permission error: grant source-table SELECT, schema USAGE, and function EXECUTE.
  • Cache bypasses: inspect isolation level, current-transaction writes, recovery state, row size, and metrics.
  • Stale mapping after DDL: run local_cache.reconcile_table('public.items'::regclass).

Next: read the technical reference for SQL contracts, consistency, memory sizing, monitoring, and RESP security.