pgcozy

Overview

Version

Build

You can build the RPM / DEB packages for pgcozy using pig build:

pig build pkg pgcozy         # build RPM / DEB packages

Install

You can install pgcozy directly. First, make sure the PGDG and PIGSTY repositories are added and enabled:

pig repo add pgsql -u          # Add repo and update cache

Install the extension using pig or apt/yum/dnf:

pig install pgcozy;          # Install for current active PG version

pig ext install -y pgcozy -v 18  # PG 18
pig ext install -y pgcozy -v 17  # PG 17
pig ext install -y pgcozy -v 16  # PG 16
pig ext install -y pgcozy -v 15  # PG 15
pig ext install -y pgcozy -v 14  # PG 14

dnf install -y pgcozy_18       # PG 18
dnf install -y pgcozy_17       # PG 17
dnf install -y pgcozy_16       # PG 16
dnf install -y pgcozy_15       # PG 15
dnf install -y pgcozy_14       # PG 14

apt install -y postgresql-18-pgcozy   # PG 18
apt install -y postgresql-17-pgcozy   # PG 17
apt install -y postgresql-16-pgcozy   # PG 16
apt install -y postgresql-15-pgcozy   # PG 15
apt install -y postgresql-14-pgcozy   # PG 14

Create Extension:

CREATE EXTENSION pgcozy CASCADE;  -- requires: pg_buffercache, pg_prewarm

Usage

pgcozy: Pre-warming shared buffers according to previous pg_buffercache snapshots for PostgreSQL.

pgcozy snapshots the current shared buffer state and can later restore (pre-warm) buffers from those snapshots. Requires pg_buffercache and pg_prewarm extensions.

Initialize

SELECT pgcozy_init();

Creates the pgcozy schema with a snapshots table and the cozy_type type.

Take a Snapshot

-- Snapshot buffer pages with usagecount >= 3 (popularity 1-5)
SELECT pgcozy_snapshot(3);

-- Snapshot all buffered pages (popularity = 0)
SELECT pgcozy_snapshot(0);

Snapshots are stored as JSONB in pgcozy.snapshots with columns: id, snapshot_date, snapshot. Each entry contains table_name, block_no, and popularity.

Warm from a Snapshot

-- Warm from a specific snapshot by ID
SELECT pgcozy_warm(1);

-- Warm from the latest snapshot
SELECT pgcozy_warm(0);

Review Snapshots

SELECT id, snapshot_date FROM pgcozy.snapshots;

Snapshots are stored in JSONB and can be reviewed, backed up, or transferred between servers.