Upgrading pg_trickle

This guide covers upgrading pg_trickle from one version to another.

Quick Upgrade (Recommended)

-- 1. Check current version
SELECT extversion FROM pg_extension WHERE extname = 'pg_trickle';

-- 2. Replace the binary files (.so/.dylib, .control, .sql)
--    See the installation method below for your platform.

-- 3. Restart PostgreSQL (required for shared library changes)
--    sudo systemctl restart postgresql

-- 4. Run the upgrade in each database that has pg_trickle installed
ALTER EXTENSION pg_trickle UPDATE;

-- 5. Verify the upgrade
SELECT pgtrickle.version();
SELECT * FROM pgtrickle.health_check();

Step-by-Step Instructions

1. Check Current Version

SELECT extversion FROM pg_extension WHERE extname = 'pg_trickle';
-- Returns: '0.1.3'

2. Install New Binary Files

Replace the extension files in your PostgreSQL installation directory. The method depends on how you originally installed pg_trickle.

From release tarball:

# Replace <new-version> with the target release, for example 0.2.3
curl -LO https://github.com/getretake/pg_trickle/releases/download/v<new-version>/pg_trickle-<new-version>-pg18-linux-amd64.tar.gz
tar xzf pg_trickle-<new-version>-pg18-linux-amd64.tar.gz

# Copy files to PostgreSQL directories
sudo cp pg_trickle-<new-version>-pg18-linux-amd64/lib/* $(pg_config --pkglibdir)/
sudo cp pg_trickle-<new-version>-pg18-linux-amd64/extension/* $(pg_config --sharedir)/extension/

From source (cargo-pgrx):

cargo pgrx install --release

3. Restart PostgreSQL

The shared library (.so / .dylib) is loaded at server start via shared_preload_libraries. A restart is required for the new binary to take effect.

sudo systemctl restart postgresql
# or on macOS with Homebrew:
brew services restart postgresql@18

4. Run ALTER EXTENSION UPDATE

Connect to each database where pg_trickle is installed and run:

ALTER EXTENSION pg_trickle UPDATE;

This executes the upgrade migration scripts in order (for example, pg_trickle--0.5.0--0.6.0.sql → pg_trickle--0.6.0--0.7.0.sql). PostgreSQL automatically determines the full upgrade chain from your current version to the new default_version.

5. Verify the Upgrade

-- Check version
SELECT pgtrickle.version();

-- Run health check
SELECT * FROM pgtrickle.health_check();

-- Verify stream tables are intact
SELECT * FROM pgtrickle.stream_tables_info;

-- Test a refresh
SELECT pgtrickle.refresh_stream_table('your_stream_table');

Version-Specific Notes

0.1.3 → 0.2.0

New functions added: - pgtrickle.list_sources(name) — list source tables for a stream table - pgtrickle.change_buffer_sizes() — inspect CDC change buffer sizes - pgtrickle.health_check() — diagnostic health checks - pgtrickle.dependency_tree() — visualize the dependency DAG - pgtrickle.trigger_inventory() — audit CDC triggers - pgtrickle.refresh_timeline(max_rows) — refresh history - pgtrickle.diamond_groups() — diamond dependency group info - pgtrickle.version() — extension version string - pgtrickle.pgt_ivm_apply_delta(...) — internal IVM delta application - pgtrickle.pgt_ivm_handle_truncate(...) — internal TRUNCATE handler - pgtrickle._signal_launcher_rescan() — internal launcher signal

No schema changes to pgtrickle.pgt_stream_tables or pgtrickle.pgt_dependencies catalog tables.

No breaking changes. All v0.1.3 functions and views continue to work as before.

0.2.0 → 0.2.1

Three new catalog columns added to pgtrickle.pgt_stream_tables:

Column Type Default Purpose
topk_offset INT NULL Pre-provisioned for paged TopK OFFSET (activated in v0.2.2)
has_keyless_source BOOLEAN NOT NULL FALSE EC-06: keyless source flag; switches apply strategy from MERGE to counted DELETE
function_hashes TEXT NULL EC-16: stores MD5 hashes of referenced function bodies for change detection

The migration script (pg_trickle--0.2.0--0.2.1.sql) adds these columns via ALTER TABLE … ADD COLUMN IF NOT EXISTS.

No breaking changes. All v0.2.0 functions, views, and event triggers continue to work as before.

What’s also new: - Upgrade migration safety infrastructure (scripts, CI, E2E tests) - GitHub Pages book expansion (6 new documentation pages) - User-facing upgrade guide (this document)

0.2.1 → 0.2.2

No catalog table DDL changes. The topk_offset column needed for paged TopK was already added in v0.2.1.

Two SQL function updates are applied by pg_trickle--0.2.1--0.2.2.sql:

  • pgtrickle.create_stream_table(...)
    • default schedule changes from '1m' to 'calculated'
    • default refresh_mode changes from 'DIFFERENTIAL' to 'AUTO'
  • pgtrickle.alter_stream_table(...)
    • adds the optional query parameter used by ALTER QUERY support

Because PostgreSQL stores argument defaults and function signatures in pg_proc, the migration script must DROP FUNCTION and recreate both signatures during ALTER EXTENSION ... UPDATE.

Behavioral notes:

  • Existing stream tables keep their current catalog values. The migration only changes the defaults used by future create_stream_table(...) calls.
  • Existing applications can opt a table into the new defaults explicitly via pgtrickle.alter_stream_table(...) after the upgrade.
  • After installing the new binary and restarting PostgreSQL, the scheduler now warns if the shared library version and SQL-installed extension version do not match. This helps detect stale .so/.dylib files after partial upgrades.

0.2.2 → 0.2.3

One new catalog column is added to pgtrickle.pgt_stream_tables:

Column Type Default Purpose
requested_cdc_mode TEXT NULL Optional per-stream-table CDC override ('auto', 'trigger', 'wal')

The upgrade script also recreates two SQL functions:

  • pgtrickle.create_stream_table(...)
    • adds the optional cdc_mode parameter
  • pgtrickle.alter_stream_table(...)
    • adds the optional cdc_mode parameter

Monitoring view updates:

  • pgtrickle.pg_stat_stream_tables gains the cdc_modes column
  • pgtrickle.pgt_cdc_status is added for per-source CDC visibility

Because PostgreSQL stores function signatures and defaults in pg_proc, the upgrade script drops and recreates both lifecycle functions during ALTER EXTENSION ... UPDATE.

0.6.0 → 0.7.0

One new catalog column is added to pgtrickle.pgt_stream_tables:

Column Type Default Purpose
last_fixpoint_iterations INT NULL Records how many rounds the last circular-dependency fixpoint run required

Two new catalog tables are added:

Table Purpose
pgtrickle.pgt_watermarks Stores per-source watermark progress reported by external loaders
pgtrickle.pgt_watermark_groups Stores groups of sources that must stay temporally aligned before refresh

The upgrade script also updates and adds SQL functions:

  • Recreates pgtrickle.pgt_status() so the result includes scc_id
  • Adds pgtrickle.pgt_scc_status() for circular-dependency monitoring
  • Adds pgtrickle.advance_watermark(source, watermark)
  • Adds pgtrickle.create_watermark_group(name, sources[], tolerance_secs)
  • Adds pgtrickle.drop_watermark_group(name)
  • Adds pgtrickle.watermarks()
  • Adds pgtrickle.watermark_groups()
  • Adds pgtrickle.watermark_status()

Behavioral notes:

  • Circular stream table dependencies can now run to convergence when pg_trickle.allow_circular = true and every member of the cycle is safe for monotone DIFFERENTIAL refresh.
  • The scheduler can now hold back refreshes until related source tables are aligned within a configured watermark tolerance.
  • Existing non-circular stream tables continue to work as before. The new catalog objects are additive.

Supported Upgrade Paths

The following migration hops are available. PostgreSQL chains them automatically when you run ALTER EXTENSION pg_trickle UPDATE.

From To Script
0.1.3 0.2.0 pg_trickle--0.1.3--0.2.0.sql
0.2.0 0.2.1 pg_trickle--0.2.0--0.2.1.sql
0.2.1 0.2.2 pg_trickle--0.2.1--0.2.2.sql
0.2.2 0.2.3 pg_trickle--0.2.2--0.2.3.sql
0.2.3 0.3.0 pg_trickle--0.2.3--0.3.0.sql
0.3.0 0.4.0 pg_trickle--0.3.0--0.4.0.sql
0.4.0 0.5.0 pg_trickle--0.4.0--0.5.0.sql
0.5.0 0.6.0 pg_trickle--0.5.0--0.6.0.sql
0.6.0 0.7.0 pg_trickle--0.6.0--0.7.0.sql

That means any installation currently on 0.1.3 through 0.6.0 can upgrade to 0.7.0 in one step after the new binaries are installed and PostgreSQL has been restarted.

Rollback / Downgrade

PostgreSQL does not support automatic extension downgrades. To roll back:

  1. Export stream table definitions (if you want to recreate them later): bash cargo run --bin pg_trickle_dump -- --output backup.sql Or, if the binary is already installed in your PATH: bash pg_trickle_dump --output backup.sql Use --dsn '<connection string>' or standard PG* / DATABASE_URL environment variables when the default local connection parameters are not sufficient.

  2. Drop the extension (destroys all stream tables): sql DROP EXTENSION pg_trickle CASCADE;

  3. Install the old version and restart PostgreSQL.

  4. Recreate the extension at the old version: sql CREATE EXTENSION pg_trickle VERSION '0.1.3';

  5. Recreate stream tables from your backup.

Troubleshooting

“function pgtrickle.xxx does not exist” after upgrade

This means the upgrade script is missing a function. Workaround:

-- Check what version PostgreSQL thinks is installed
SELECT extversion FROM pg_extension WHERE extname = 'pg_trickle';

-- If the version looks correct but functions are missing,
-- the upgrade script may be incomplete. Try a clean reinstall:
DROP EXTENSION pg_trickle CASCADE;
CREATE EXTENSION pg_trickle CASCADE;
-- Warning: this destroys all stream tables!

Report this as a bug — upgrade scripts should never silently drop functions.

“could not access file pg_trickle” after restart

The new shared library file was not installed correctly. Verify:

ls -la $(pg_config --pkglibdir)/pg_trickle*

ALTER EXTENSION UPDATE says “already at version X”

The binary files are already the new version but the SQL catalog wasn’t upgraded. This usually means the .control file’s default_version matches your current version. Check:

cat $(pg_config --sharedir)/extension/pg_trickle.control

Multi-Database Environments

ALTER EXTENSION UPDATE must be run in each database where pg_trickle is installed. A common pattern:

for db in $(psql -t -c "SELECT datname FROM pg_database WHERE datname NOT IN ('template0', 'template1')"); do
  psql -d "$db" -c "ALTER EXTENSION pg_trickle UPDATE;" 2>/dev/null || true
done

CloudNativePG (CNPG)

For CNPG deployments, see cnpg/README.md for upgrade instructions specific to the Kubernetes operator.