Cheatsheet

Install

Single static binary, ~21 MB, no runtime dependencies. The package installs a hardened systemd unit that runs as a dedicated pgpipe system user.

1. Install the package

# arm64: swap amd64 → arm64
wget https://www.pghorizon.com/downloads/pgpipe/v1.1.4/pgpipe_1.1.4_amd64.deb
sudo apt install ./pgpipe_1.1.4_amd64.deb

# aarch64: swap x86_64 → aarch64
wget https://www.pghorizon.com/downloads/pgpipe/v1.1.4/pgpipe-1.1.4-1.x86_64.rpm
sudo dnf install ./pgpipe-1.1.4-1.x86_64.rpm

The package's postinst script creates the pgpipe system user, sets ownership on the config + state directories, and enables the systemd unit. You do not need to run systemctl enable by hand.

2. What got installed

3. Start it & open the setup wizard

# Start now (the package enabled the unit at install time)
sudo systemctl start pgpipe
sudo systemctl status pgpipe

# Follow structured-JSON logs from the systemd journal
sudo journalctl -u pgpipe -f

# Open http://<host>:8080 — the setup wizard walks you through
# source + destination credentials and writes /etc/pgpipe/pgpipe.yaml.

Need the first-run admin password? → Dashboard login section below.

More options? .deb / .rpm packages + Docker quickstart →

Docker quickstart

Spin up two Postgres databases plus pgpipe in one go — fastest way to see replication.

mkdir pgpipe-demo && cd pgpipe-demo

BASE="https://www.pghorizon.com/downloads/pgpipe/v1.1.4/docker"
for f in Dockerfile docker-compose.yml init-source.sql init-dest.sql; do
  curl -fsSL $BASE/$f -O
done
curl -fsSL $BASE/pgpipe.example.yaml -o pgpipe.yaml

docker compose up -d --build
docker compose logs -f pgpipe

Dashboard login

On first run pgpipe generates a random admin password, prints it once, and persists it to a 0600 file.

# Just the password (always works, even after first run)
docker compose exec pgpipe cat /var/lib/pgpipe/pgpipe-admin.password

# Or grep the full first-run banner from the logs
docker compose logs pgpipe | grep -A 6 "FIRST RUN — DASHBOARD"

# Then open the dashboard and log in as admin
open http://localhost:8080

Setting your own password? Edit pgpipe.yaml → server.auth.password. For production, prefer the PGPIPE_DASHBOARD_PASSWORD env var over a value baked into YAML.

Minimum config (pgpipe.yaml)

The smallest config that runs against your own databases.

pipeline:
  name: "prod-replica"

source:
  host: "source.example.com"
  database: "app"
  user: "pgpipe_repl"
  password: "${PGPIPE_SOURCE_PASSWORD}"
  ssl_mode: "require"
  replication:
    slot_name: "pgpipe_prod"
    publication_name: "pgpipe_prod"
  tables:
    - { schema: "public", name: "orders" }
    - { schema: "public", name: "customers" }

destination:
  host: "replica.example.com"
  database: "app_replica"
  user: "pgpipe_writer"
  password: "${PGPIPE_DEST_PASSWORD}"
  ssl_mode: "require"

state:
  backend: "boltdb"
  boltdb:
    path: "/var/lib/pgpipe/state.db"

Full reference: annotated pgpipe.example.yaml →

Source prerequisites

Run these on the source PostgreSQL before pointing pgpipe at it.

-- 1. postgresql.conf (requires restart for wal_level)
wal_level             = logical
max_replication_slots = 10
max_wal_senders       = 10

-- 2. pg_hba.conf — allow replication from pgpipe's IP
host  all  pgpipe_repl  10.0.0.0/8  scram-sha-256

-- 3. Replication role with least privilege
CREATE ROLE pgpipe_repl WITH LOGIN REPLICATION PASSWORD 'redacted';
GRANT USAGE ON SCHEMA public TO pgpipe_repl;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO pgpipe_repl;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT SELECT ON TABLES TO pgpipe_repl;

Walking through this end-to-end: PostgreSQL Logical Replication, Step by Step →

Start / stop / restart

# Foreground (development)
pgpipe start -c pgpipe.yaml

# Validate config without starting
pgpipe validate -c pgpipe.yaml

# Setup-only (creates publication, slot, schema — useful for CI dry-runs)
pgpipe setup -c pgpipe.yaml

# Under systemd (.deb / .rpm install)
sudo systemctl enable --now pgpipe
sudo systemctl status pgpipe
sudo journalctl -u pgpipe -f

# Under Docker Compose
docker compose restart pgpipe
docker compose logs -f pgpipe

Verify replication

-- On the source: slot health + lag
SELECT slot_name, active, wal_status,
       pg_size_pretty(pg_wal_lsn_diff(
         pg_current_wal_lsn(), confirmed_flush_lsn)) AS lag_bytes
FROM   pg_replication_slots;

-- On the source: tables in the publication
SELECT schemaname, tablename
FROM   pg_publication_tables
WHERE  pubname = 'pgpipe_pub';

-- Compare row counts source vs destination
SELECT count(*) FROM public.orders;

pgpipe also exposes everything as Prometheus metrics at http://<host>:8080/metrics.

Add / remove tables

Use the dashboard's table picker, or hit the REST API while pgpipe is running.

# Add a table to an active pipeline
curl -u admin:$PASSWORD -X POST http://localhost:8080/api/tables \
  -H 'Content-Type: application/json' \
  -d '{"schema":"public","name":"new_table"}'

# Remove a table
curl -u admin:$PASSWORD -X DELETE \
  'http://localhost:8080/api/tables/public/new_table'

# List configured tables
curl -u admin:$PASSWORD http://localhost:8080/api/tables | jq

Diagnose common issues

Error: opening state store: opening boltdb at pgpipe-state.db:
       open pgpipe-state.db: read-only file system

# Backup, then promote the path from relative to absolute
sudo cp /etc/pgpipe/pgpipe.yaml /etc/pgpipe/pgpipe.yaml.bak
sudo sed -i 's|pgpipe-state\.db|/var/lib/pgpipe/pgpipe-state.db|' /etc/pgpipe/pgpipe.yaml

# Clear systemd's restart-burst lockout, then start fresh
sudo systemctl reset-failed pgpipe.service
sudo systemctl start pgpipe.service

# Confirm — should see "snapshot complete" → "started WAL streaming"
sudo journalctl -u pgpipe -f

Upgrade

pgpipe is not (yet) published to a Debian repository, so sudo apt update && sudo apt install --only-upgrade pgpipe won't find an upgrade — there's no source list to pull from. Instead, download the new .deb and install it on top; apt handles the stop-replace-enable dance automatically, and your config + state are preserved because neither is in the package payload.

1. 1

   Snapshot the state DB (optional but cheap)

   Belt-and-suspenders in case you ever need to roll back. The state DB is small and the copy takes a fraction of a second.

   sudo cp /var/lib/pgpipe/pgpipe-state.db /var/lib/pgpipe/pgpipe-state.db.bak

2. 2

   Download the new .deb and install over the old one

   Substitute X.Y.Z with the version you're upgrading to (latest is v1.1.4). Apt accepts a local .deb the same way it accepts a remote one — and the package's prerm script stops the running service before the binary is swapped.

   # arm64: swap amd64 → arm64
   wget https://www.pghorizon.com/downloads/pgpipe/vX.Y.Z/pgpipe_X.Y.Z_amd64.deb
   sudo apt install ./pgpipe_X.Y.Z_amd64.deb

   RHEL / Rocky / Fedora equivalent: sudo dnf upgrade ./pgpipe-X.Y.Z-1.x86_64.rpm.

3. 3

   Start the service back

   The package's postinst calls systemctl enable but not start. If you skip this step, the pipeline stays stopped, the replication slot keeps pinning WAL on the source, and your source's pg_wal grows until you notice.

   # Optional but recommended: validate the existing config against the
   # new binary BEFORE starting — catches removed/renamed fields cleanly
   # instead of crash-looping the service.
   sudo -u pgpipe pgpipe validate -c /etc/pgpipe/pgpipe.yaml
   
   sudo systemctl start pgpipe

4. 4

   Verify

   # Binary reports the new version
   pgpipe --version
   
   # Unit is active and not in restart loop
   sudo systemctl status pgpipe
   
   # Watch the first ~30s of logs — confirms the slot reattached
   # at the saved LSN and streaming is back
   sudo journalctl -u pgpipe -f

What gets preserved across the upgrade

Rollback (if the new version misbehaves)

Re-install the previous .deb on top — apt accepts a downgrade when handed a local file. Restore the state DB snapshot from step 1 only if you suspect on-disk corruption; for a clean version bump-back, the existing state file works as-is.

sudo systemctl stop pgpipe
sudo apt install ./pgpipe_OLD-X.Y.Z_amd64.deb

# Only if you suspect the new version corrupted on-disk state:
# sudo cp /var/lib/pgpipe/pgpipe-state.db.bak /var/lib/pgpipe/pgpipe-state.db

sudo systemctl start pgpipe
pgpipe --version

Every release directory at /downloads/pgpipe/vX.Y.Z/ is kept indefinitely, so an older .deb is always reachable by URL.

Uninstall

Cleanly remove pgpipe from a Linux host. Order matters — drop the replication slot on the source before removing the package, otherwise the source's WAL keeps growing with no consumer to drain it and the disk can fill up.

1. 1

   Stop the service

   sudo systemctl stop pgpipe.service
   sudo systemctl disable pgpipe.service

   Stopping first releases the replication slot so the next step's DROP doesn't have to fight an active backend.

2. 2

   Clean up the source database

   This is the step most uninstall guides miss. Run on the source PostgreSQL as a superuser. The names below are the defaults — substitute yours if you customised slot_name, publication_name, or ddl.setup_schema in pgpipe.yaml.

   -- Terminate any backend still attached to the slot, then drop it
   SELECT pg_terminate_backend(active_pid)
   FROM   pg_replication_slots
   WHERE  slot_name = 'pgpipe_slot' AND active;
   SELECT pg_drop_replication_slot('pgpipe_slot');
   
   DROP PUBLICATION IF EXISTS pgpipe_pub;
   
   DROP EVENT TRIGGER IF EXISTS pgpipe_ddl_capture;
   DROP EVENT TRIGGER IF EXISTS pgpipe_drop_capture;
   
   -- Removes the heartbeat table + DDL log
   DROP SCHEMA IF EXISTS pgpipe CASCADE;

   pgpipe ships a pgpipe teardown subcommand that prints the equivalent SQL but does not execute it — running these statements by hand is the supported path today.

3. 3

   Remove the package

   On Debian/Ubuntu use apt purge, not apt remove. purge triggers the postrm script that also deletes /etc/pgpipe, /var/lib/pgpipe, and the pgpipe system user; remove leaves all of those behind.

   # Debian / Ubuntu
   sudo apt purge -y pgpipe
   sudo apt autoremove -y

   # RHEL / Rocky / Fedora
   sudo dnf remove -y pgpipe
   sudo rm -rf /etc/pgpipe /var/lib/pgpipe
   sudo userdel pgpipe; sudo groupdel pgpipe

   dnf remove has no built-in "purge" mode, so the config + state dirs and the system user are removed by hand.

4. 4

   Verify

   On the host — every command should produce no output:

   command -v pgpipe
   ls -d /etc/pgpipe /var/lib/pgpipe 2>/dev/null
   getent passwd pgpipe
   
   # Reload systemd so the dropped unit is forgotten
   sudo systemctl daemon-reload
   sudo systemctl reset-failed pgpipe.service 2>/dev/null || true

   On the source PostgreSQL — both queries should return zero rows:

   SELECT slot_name FROM pg_replication_slots WHERE slot_name LIKE 'pgpipe%';
   SELECT pubname   FROM pg_publication      WHERE pubname   LIKE 'pgpipe%';

Docker Compose teardown

If you ran pgpipe via the Docker quickstart, steps 1 and 3 collapse into:

# Stop containers and delete the data volumes
docker compose down -v

# Remove the working directory (Dockerfile, compose, configs)
cd .. && rm -rf pgpipe-demo

The compose teardown handles its bundled source + destination databases. If your compose-built pgpipe was pointed at an external source, step 2 above still applies — drop the slot before deleting the container.