Setting Up the Spool Directory¶

You need three things before the first file can land:

A folder on the Processor server.
An environment variable that tells the Processor where that folder is.
A channel in the database.

1. Create the folder¶

The Processor never creates the folder. You create it, and you decide who owns it. That way you control who can write files and who can read them.

If the folder is missing, the Processor tells you at startup with a spool_dir_unavailable warning. Events for that channel wait in the queue until the folder exists. Nothing is lost.

The simplest safe setup is a shared group. Put the Processor's user and the consumer's user in the same group. Give the folder mode 0770. The Processor writes files with mode 0640, which is the default.

sudo groupadd spool
sudo usermod -aG spool pgrelay          # the Processor's user
sudo usermod -aG spool etl              # the consumer's user
sudo install -d -m 0770 -o pgrelay -g spool /var/spool/pg_relay
sudo install -d -m 0770 -o pgrelay -g spool /var/spool/pg_relay/exports   # one folder per channel

With this setup the Processor can write, the consumer can read and delete, and nobody else can see the files. New group memberships apply after the next login or service restart.

Using the hardened systemd unit?

deploy/pg_relay-hardened.service makes the whole filesystem read-only for the Processor. That is the ProtectSystem=strict line. To let it write files, add the spool folder, and only the spool folder:

ReadWritePaths=/var/spool/pg_relay

Do not change ProtectSystem to full. The unit file has this line ready, commented out. See Hardening the Service.

2. Set the environment variable¶

Add one line to the Processor's environment file. In the shipped systemd units that file is /etc/pg_relay/pg_relay.env. In a container, set it in the container's environment.

PG_RELAY_SPOOL_DIR=/var/spool/pg_relay

This one line works for any number of channels. Each channel writes into its own subfolder, named after the channel. The channel exports writes to /var/spool/pg_relay/exports/. You create each subfolder yourself, as shown above.

All the variables:

Variable	Required?	What it does
`PG_RELAY_SPOOL_DIR`	Yes, unless every channel has its own folder	The base folder. A channel without its own folder writes to `<base>/<channel>/`.
`PG_RELAY_SPOOL_<CHANNEL>_DIR`	No	A folder for one channel. Overrides the base. Useful when a channel's files belong somewhere specific, such as an application's own inbox.
`PG_RELAY_SPOOL_MAX_BYTES`	No. Default: no limit	The most bytes a channel's folder may hold. If a write would take the folder to this size or over, the Processor waits until the consumer removes some files.
`PG_RELAY_SPOOL_<CHANNEL>_MAX_BYTES`	No	The same limit for one channel. Overrides the global one.
`PG_RELAY_SPOOL_MAX_FILE_BYTES`	No. Default: 16 MiB (`16777216`)	The largest payload allowed. A bigger payload fails, and is not retried.

To make <CHANNEL>, take the channel name, make it upper case, and change every character that is not a letter or digit to _. Channel names are not case-sensitive, so exports, Exports, and EXPORTS all use PG_RELAY_SPOOL_EXPORTS_DIR. The channel order-events.v2 uses PG_RELAY_SPOOL_ORDER_EVENTS_V2_DIR.

Sizes are whole numbers of bytes. If a size variable is set to something that is not a whole number, the Processor refuses to start and logs spool_config_invalid. This is on purpose. A limit that silently did nothing would be worse than no limit.

Here is an example with two channels. One uses the base folder. The other has its own folder and its own limit.

PG_RELAY_SPOOL_DIR=/var/spool/pg_relay                 # 'exports' writes to /var/spool/pg_relay/exports/
PG_RELAY_SPOOL_INVOICES_DIR=/srv/billing/inbox         # 'invoices' writes to /srv/billing/inbox/
PG_RELAY_SPOOL_MAX_BYTES=1073741824                    # 1 GiB limit for every channel
PG_RELAY_SPOOL_INVOICES_MAX_BYTES=268435456            # 256 MiB limit for 'invoices' only

The Processor reads these from its own environment only, never from the database. To change one, edit the file and restart the service. Both deploy/pg_relay.env.example and deploy/pg_relay-hardened.env.example include these lines, commented out.

3. Register the channel¶

SELECT pgrelay.register('exports', '', p_action_type := 'pg_relay.file_spool');

The second argument, the action, can be empty for this action type. Empty means: use every default. The defaults are a file name of {channel}-{event_id}.json, a temporary suffix of .tmp, file mode 0640, and replace off.

To change any of those, pass a small JSON object instead. It describes the file, never the folder. Every option is explained in Filenames and Replacing Files.

SELECT pgrelay.register('exports',
    '{"filename": "{payload.order_id}.json", "replace": true}',
    p_action_type := 'pg_relay.file_spool',
    p_max_retries := 3,
    p_notes := 'One file per order, overwritten on every status change');

All the usual register() options work with this action type: p_max_retries, p_concurrency_mode, p_node_restricted, p_notes, and p_active. A file_spool channel is an ordinary channel. You can register as many as you like. update() changes the options later. disable() pauses the channel, and its events wait in the queue.

Why the folder is not stored in the database¶

The exports channel is the same row in every copy of your database. Which folder it writes to depends only on which Processor is running, and what that Processor's environment says:

Where the Processor runs	`PG_RELAY_SPOOL_DIR`	`exports` writes to
production server	`/var/spool/pg_relay`	`/var/spool/pg_relay/exports/`
test server	`/srv/test/spool`	`/srv/test/spool/exports/`
a developer's laptop	`/tmp/spool`	`/tmp/spool/exports/`

Say you restore last night's production backup into the test database and start the test Processor. Any waiting exports events are written to /srv/test/spool/exports/. That is what the test Processor's environment says. Nothing in the restored data can send the files to production's folder. You do not have to update any rows after a restore.

This is the same idea as keeping secrets out of the database. Anything that belongs to one server stays on that server.

To move every channel's output, change PG_RELAY_SPOOL_DIR and restart the Processor. No database change is needed.

Notes for each platform¶

Linux and macOS. Everything on this page applies as written.

Windows. The file is written the same safe way. Go's rename on Windows uses MoveFileEx, which replaces the target in one step. Two things are different. Windows has no way to flush a folder to disk, so that step is skipped. And the mode option is not applied, because a Windows file gets its permissions from the folder it is created in. Set the folder's permissions for the Processor's service account and the consumer's account. Set the variables in the service's environment (for example through NSSM, see deploy/nssm-install.ps1) using Windows paths: PG_RELAY_SPOOL_DIR=D:\spool\pg_relay.

Containers and cloud. The Processor's container needs the spool folder mounted as a volume. The consumer must be able to reach the same volume. Put the variables in the container's environment next to the PG* connection settings. See Running the Processor. A spool folder lives on one server by nature. If the consumer runs somewhere else, a shared volume or a webhook is a better fit.

Next: Filenames and Replacing Files.