Delivering Events as Files¶
Who needs this book
Read this if you have a program that can watch a folder but cannot receive a webhook, or should not be given a database login. Examples: a log shipper, a nightly load job, a batch loader, an agent on the same server, an export to a network with no internet. If all your consumers can take a webhook or an email, you can skip this book.
Since v1.6, pg_relay can hand an event over in the simplest way there is: as a file in a folder.
pg_relay.file_spool is an action type. You register a channel with it, then send events to that channel with pgrelay.notify(), the same as any other channel. For each event, the Processor writes the payload to a file in a folder on its own server. That folder is called the spool directory.
The file is written safely. A program watching the folder either does not see the file yet, or sees the whole file. It never sees a half-written file.
Why this is better than writing the file yourself¶
The file follows the transaction. You call notify() inside the same transaction as the change it describes. If the transaction commits, the file will appear. If the transaction rolls back, no file appears. PostgreSQL cannot do that on its own. COPY TO needs superuser rights, writes on the database server, and cannot be rolled back. On a managed cloud database it is not available at all.
The folder is not stored in the database. The channel registration says nothing about where files go. The Processor reads the folder from its own environment variables. So the same registration works in production, test, and development without change. If you restore a production backup into a test database, the test Processor writes into the test folder. Nothing in the data can point it at the production folder. See Setting Up the Spool Directory.
You get retries and an audit log for free. Every event lands in pgrelay.log like any other. If the folder is missing or full, the events wait in the queue. Nothing is lost.
File or webhook?¶
| Use a file when… | Use a webhook when… |
|---|---|
| the consumer is a batch job that runs on its own timetable | the consumer is a service that is listening right now |
| the consumer is on the same server and must not have a database login | the consumer is on another server and speaks HTTP |
| you want a file you can look at, copy, keep, or replay | you want the receiver to confirm it got the message |
| the server has no internet access | the receiver tells you whether to retry |
the file should always hold the latest state of something, and be overwritten each time (replace) |
every event is a separate message |
The Processor never reads or changes the payload. It writes the exact bytes you passed to notify(). It does not add a newline. It does not check that the payload is JSON. What happens to the file after it is written is up to the consumer. The Processor never deletes, moves, or cleans up files.
The whole thing on one screen¶
On the Processor server, once:
sudo install -d -m 0770 -o pgrelay -g spool /var/spool/pg_relay/exports
echo 'PG_RELAY_SPOOL_DIR=/var/spool/pg_relay' | sudo tee -a /etc/pg_relay/pg_relay.env
sudo systemctl restart pg_relay
In the database, once:
Then, for every event:
About a second later, the file /var/spool/pg_relay/exports/exports-<event_id>.json exists. It contains exactly {"order_id": 4471, "status": "shipped"}. The result is in the audit log:
SELECT channel, status, error, elapsed_ms
FROM pgrelay.log
WHERE channel = 'exports'
ORDER BY actioned_at DESC
LIMIT 5;
The payload does not have to be JSON. It can be a CSV line, an XML document, or plain text. Whatever you pass to notify() is what the file holds.
The other notify() options work too. p_run_at delays the file until a later time. p_expire_at drops the event if it has not been written by then. p_deduplicate skips an event that is already waiting with the same payload. See Sending Events.
How the file is written¶
- The Processor works out the file name from the channel's template. The default is
{channel}-{event_id}.json. See Filenames and Replacing Files. - It writes the bytes to a temporary file,
<name>.tmp, in the same folder. - It makes sure the bytes are on disk (an
fsync), sets the file permissions, and closes the file. - It renames the temporary file to its final name. On every supported system, a rename inside one folder is a single step that cannot be interrupted halfway. So the final name only ever points at a complete file.
If the Processor crashes after the rename but before it records success, the event is delivered again. The second attempt finds the file already there and reports success without rewriting it. If two Processors share one queue, only one of them ever gets a given event. The database lock on the queue row guarantees that.
What the action never does¶
- Read, check, change, or reformat the payload.
- Create folders or change folder permissions.
- Delete, move, or clean up files. That is the consumer's job.
- Follow a symbolic link where the file should go, or accept a file name containing
... - Store any path, size, or server-specific value in the database.
- Write the payload or the file name to the Processor's log. Only the channel, the event id, and the folder path appear there.
The rest of this book¶
| Page | Read it when… |
|---|---|
| Setting Up the Spool Directory | you are creating the folder or setting the environment variables |
| Filenames and Replacing Files | you want the file name to come from the payload, or you want one file kept up to date with replace: true |
| Reading the Spool | you are writing the program that picks the files up |
| Configuration and Failure Reference | you need every option, variable, outcome, and log line in one place |