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:

# Download the new release
curl -LO https://github.com/getretake/pg_trickle/releases/download/v0.2.0/pg_trickle-0.2.0-pg18-linux-amd64.tar.gz
tar xzf pg_trickle-0.2.0-pg18-linux-amd64.tar.gz

# Copy files to PostgreSQL directories
sudo cp pg_trickle-0.2.0-pg18-linux-amd64/lib/* $(pg_config --pkglibdir)/
sudo cp pg_trickle-0.2.0-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 (e.g., pg_trickle--0.1.3--0.2.0.sql). PostgreSQL automatically determines the 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

No SQL changes. The extension's function, view, and event trigger interface is identical to 0.2.0. This is an infrastructure-only release.

The migration script (pg_trickle--0.2.0--0.2.1.sql) is a no-op.

What's new:

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

Supported Upgrade Paths

The following upgrade chains are supported:

PostgreSQL automatically chains migration scripts. Running ALTER EXTENSION pg_trickle UPDATE from v0.1.3 will apply both scripts in sequence.

Rollback / Downgrade

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

1. Export stream table definitions (if you want to recreate them later):

   SELECT pgt_name, original_query, schedule, refresh_mode
   FROM pgtrickle.pgt_stream_tables;
   

2. Drop the extension (destroys all stream tables):

   DROP EXTENSION pg_trickle CASCADE;
   

3. Install the old version and restart PostgreSQL.

4. Recreate the extension at the old version:

   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.