pg_squeeze

Custom DollarDeploy service that enables online table bloat removal with pg_squeeze.

DollarDeploy clones this repo to $APPDIR/services/<name> and runs its prepare.sh during the host's prepare run. The script is self-contained.

Install

1. Open the app and go to your Host.
2. Open the Services tab.
3. Click Add service and choose Custom.
4. Paste the repo URL: https://github.com/dollardeploy/service-pgsqueeze/
5. Set POSTGRES_SQUEEZE_TABLES (and any other settings below) as host or service env vars.
6. Save, then Prepare the host. DollarDeploy clones the repo and runs prepare.sh, which builds and enables pg_squeeze on the host's PostgreSQL.

Requires a PostgreSQL service already on the host — pg_squeeze attaches to the existing cluster.

What it does:

1. Detects the PostgreSQL major version already installed on the host (pg_lsclusters).

2. Installs build dependencies and compiles pg_squeeze from source for that major version, then make install.

3. Sets wal_level = logical, ensures max_replication_slots >= 1, adds pg_squeeze to shared_preload_libraries (preserving existing entries), configures the scheduler worker to autostart for the configured databases and restarts the matching cluster(s).

4. Runs CREATE EXTENSION IF NOT EXISTS pg_squeeze in the configured databases.

5. For each table named in POSTGRES_SQUEEZE_TABLES, checks it exists in the public schema and registers it:

   INSERT INTO squeeze.tables (tabschema, tabname, schedule)
   VALUES ('public', 'TABLE_NAME', ('{30}', '{23}', NULL, NULL, NULL))
   ON CONFLICT DO NOTHING;

It is idempotent: if pg_squeeze.so is already installed the build is skipped unless POSTGRES_SQUEEZE_FORCE_INSTALL=1, and table registration uses ON CONFLICT DO NOTHING so re-running is safe.

Settings (override via host/service env vars)

Env var	Default	Description
POSTGRES_SQUEEZE_TABLES	(empty)	Space/comma separated public tables to register. Missing tables are skipped.
POSTGRES_SQUEEZE_SCHEDULE	('{30}', '{23}', NULL, NULL, NULL)	schedule composite (minutes, hours, days_of_month, months, days_of_week); default 23:30.
POSTGRES_SQUEEZE_DATABASES	POSTGRES_DATABASES or postgres	Databases to create the extension in / register tables / autostart the worker.
POSTGRES_SQUEEZE_WORKER_ROLE	postgres	Role the scheduler worker runs as (must be superuser or the table owner).
POSTGRES_SQUEEZE_MAX_REPLICATION_SLOTS	10	Minimum max_replication_slots to guarantee (only raised, never lowered).
POSTGRES_SQUEEZE_REF	repo default branch	Pin a specific pg_squeeze branch/tag.
POSTGRES_SQUEEZE_FORCE_INSTALL	0	Force rebuild/reconfigure.

The schedule resembles a crontab entry. Each field is an int[] (or NULL for "any"). For example ('{0}', '{1,13}', NULL, NULL, '{1,3,5}') means at 01:00 and 13:00 on Mon/Wed/Fri.

How squeezing runs

pg_squeeze registers a background scheduler worker (via shared_preload_libraries) that autostarts for the databases listed in squeeze.worker_autostart. It periodically checks the registered tables and, when one is bloated enough and its schedule matches, rewrites it online through logical decoding — without holding a long exclusive lock. Inspect progress and history with:

SELECT * FROM squeeze.tables;        -- registered tables
SELECT * FROM squeeze.log;           -- completed squeeze runs
SELECT * FROM squeeze.errors;        -- failures

You can also squeeze a table immediately:

SELECT squeeze.squeeze_table('public', 'TABLE_NAME');

Uninstall

Remove the service from the host's Services tab. DollarDeploy runs uninstall.sh, which stops the worker, drops the extension from the databases (which unregisters its tables), removes pg_squeeze from shared_preload_libraries and the squeeze.* settings, restarts the cluster(s) and deletes the compiled extension files. wal_level and max_replication_slots are left as-is since other things may rely on them. Set POSTGRES_SQUEEZE_REMOVE_FILES=0 to keep pg_squeeze.so on disk.