Installation

This document describes how to install pgVolvra, who needs to run the install, and how to remove pgVolvra. pgVolvra is a single SQL file, so installing pgVolvra means running that file against a database.

Prerequisites

pgVolvra requires PostgreSQL 14 or later. PostgreSQL 14 is the floor because pgVolvra uses the date_bin function, which earlier releases do not provide.

pgVolvra requires no PostgreSQL extensions. pgVolvra uses the plpgsql language, which ships enabled in every PostgreSQL installation, and built-in functions such as sha256 and jsonb_populate_record.

The durable tier has two additional requirements that the trigger tier does not; see the Companion Overview document.

Choosing an owner

Install pgVolvra as a dedicated role that is not a superuser. The volvra.capture() function is SECURITY DEFINER, so every captured write briefly runs with the rights of the role that owns the function. A superuser owner turns every insert on a covered table into superuser-owned code.

The installing role needs the CREATE privilege on the database, and the CREATEROLE privilege to create the three pgVolvra roles. Create a suitable owner as follows:

CREATE ROLE volvra_owner LOGIN CREATEROLE PASSWORD 'use-a-real-password';
GRANT CREATE ON DATABASE app TO volvra_owner;

pgVolvra reports a superuser-owned install as a critical finding in volvra.preflight().

Installing with psql

Run the install file against the target database:

psql "$DATABASE_URL" -f sql/volvra.sql

The install prints one summary line and is otherwise quiet, because the idempotent DDL it runs would otherwise emit dozens of notices that read as failure. Warnings and errors still appear.

Confirm the install:

SELECT volvra.version();

The number is the internal schema version, not a release number. There is one schema version today; the release number is 1.0.0-beta1.

Installing from a SQL client or console

The install file contains no psql meta-commands, so any client can run the file. Managed providers that offer a browser SQL console, such as Supabase and Neon, accept the file pasted into the console.

The file is wrapped in a single transaction. A failure part way through leaves no objects behind, so a failed install is safe to retry.

Installing from a migration tool

pgVolvra installs cleanly as a migration. Add sql/volvra.sql to your migration directory and let Flyway, Liquibase, Alembic, dbmate, or Rails run the file in order. The install is idempotent, so a tool that re-runs the file changes nothing.

Installing as an extension

pgVolvra is not a PostgreSQL extension. The CREATE EXTENSION command requires the script to be present on the database server filesystem, which managed providers do not allow, and which is the reason pgVolvra ships as plain SQL.

Self-hosted users who prefer CREATE EXTENSION can build optional packaging from the same SQL file. Build and install the packaging as follows:

cd extension
./build.sh
sudo make install
psql -c 'CREATE EXTENSION volvra'

The generated script comes from sql/volvra.sql, so the two cannot diverge. Nothing in pgVolvra depends on this packaging.

The packaging is pure SQL

The extension is two text files, volvra.control and volvra--<version>.sql. pgVolvra contains no C, so nothing is compiled: the Makefile uses PGXS only because PGXS knows where to copy files.

One generated script therefore serves every supported PostgreSQL version. The same file installs and runs on 14 through 19, which extension/test.sh checks on each of them, and one published checksum covers them all. A C extension would need a separate build against each major version's headers.

What differs per major version is only where the files belong, because each major version has its own share directory:

/usr/share/postgresql/14
/usr/share/postgresql/17
/usr/share/postgresql/19

A distribution package is therefore built per major version, as volvra_14, volvra_15, and so on, but each package carries the identical SQL. The multiplication is in the packaging metadata, not in the build.

Three caveats of the extension method

The extension method carries three costs that the plain SQL method does not. Each one is a reason the plain method is the supported path everywhere.

First, CREATE EXTENSION needs the two files on the database server's filesystem, which means root or an equivalent on the host that runs PostgreSQL. Managed providers give no such access, so this method is unavailable on Amazon RDS, Aurora, Google Cloud SQL, Supabase, and Neon. This is the reason pgVolvra ships as plain SQL rather than as an extension.

Second, the choice of method is made once per database and cannot be reversed. PostgreSQL removed CREATE EXTENSION ... FROM unpackaged in version 13, so a plain install cannot be adopted into an extension afterwards; the attempt reports that the command is no longer supported. Running CREATE EXTENSION volvra over an existing plain install fails as well, reporting that the schema is not a member of the extension. Going the other way means DROP EXTENSION, which drops the history with it. Moving an existing installation between the two methods therefore means exporting the history, installing the other way, and loading the history back.

Third, an extension install updates only through ALTER EXTENSION volvra UPDATE, which requires an upgrade script named for the versions it moves between. Re-running the plain script against an extension install leaves the recorded extension version stale, and a later dump and restore then emits CREATE EXTENSION at that stale version and loses the changes.

The following table describes how each method updates:

Prefer the plain method unless you are packaging pgVolvra for a distribution.

Verifying the download

pgVolvra installs as a file rather than a signed package, so verifying where the file came from is the installer's responsibility. Print the values a release publishes:

./tools/checksums.sh

Compare the SHA-256 of the file you have against the value published with the release:

sha256sum -c volvra.sql.sha256

After installing, confirm that the code running in the database matches the released code:

SELECT sha256 FROM volvra.fingerprint() WHERE scope = 'all';

The fingerprint is identical across PostgreSQL 14 through 19, so one published value covers every supported release. See the Security document for what this check does and does not prove.

Scope of an install

pgVolvra installs into one database, in a schema named volvra. The three pgVolvra roles are cluster-wide, so a second database in the same cluster reuses the existing roles.

Uninstalling pgVolvra

Remove the triggers first, then the schema and the history:

SELECT * FROM volvra.disable_all('public');
DROP SCHEMA volvra CASCADE;

Dropping the schema destroys the recorded history. Archive the history first if you need to keep it; see the Companion Overview document.

Next Steps

• The Getting Started document walks through a first undo.
• The Configuring pgVolvra document lists every setting and its default.
• The Upgrading pgVolvra document describes how pgVolvra migrates an existing install.