`pg_logical_slot_peek_binary_changes` is a PostgreSQL function used for **logical decoding**. It allows you to inspect changes (inserts, updates, deletes) from a **logical replication slot** in **binary format** without consuming them (i.e., the slot’s position doesn’t advance).
—
## **Key Characteristics**
1. **Binary Format**
Returns changes in a binary-encoded format (as `bytea`), which is more efficient than text representation but requires decoding with an output plugin.
2. **Non-consuming (“Peek”)**
Unlike `pg_logical_slot_get_binary_changes`, this function does **not** advance the replication slot’s LSN (Log Sequence Number). You can call it repeatedly and see the same changes.
3. **Logical Replication Slot**
Requires a logical replication slot created with `pg_create_logical_replication_slot()`.
4. **Output Plugin**
Needs a logical decoding output plugin (e.g., `pgoutput`, `wal2json`, `decoderbufs`) that supports binary output.
—
## **Function Signature**
“`sql
pg_logical_slot_peek_binary_changes(
slot_name name,
upto_lsn pg_lsn,
upto_nchanges integer,
VARIADIC options text[]
)
RETURNS SETOF record
“`
—
## **Parameters**
| Parameter | Type | Description |
|———–|——|————-|
| `slot_name` | `name` | Name of the logical replication slot. |
| `upto_lsn` | `pg_lsn` | Stop reading when the LSN exceeds this value (optional). |
| `upto_nchanges` | `integer` | Stop after this many changes (optional). |
| `options` | `text[]` | Plugin-specific options (e.g., `’include-xids’`, `’skip-empty-xacts’`). |
—
## **Return Columns**
Typically returns:
– `lsn` (`pg_lsn`) – Log sequence number of the change.
– `xid` (`xid`) – Transaction ID.
– `data` (`bytea`) – Binary-encoded change data.
—
## **Example Usage**
### 1. Create a logical replication slot with binary output
“`sql
SELECT * FROM pg_create_logical_replication_slot(
‘my_binary_slot’,
‘pgoutput’ — or another binary-capable plugin
);
“`
### 2. Peek at binary changes
“`sql
SELECT lsn, xid, data
FROM pg_logical_slot_peek_binary_changes(
‘my_binary_slot’,
NULL, — upto_lsn
NULL, — upto_nchanges
‘include-xids’, ‘1’
);
“`
### 3. Decode binary output
The `data` column is `bytea`. You’d typically use a client-side library (e.g., a PostgreSQL logical decoding client) to decode it according to the plugin’s format.
—
## **Common Use Cases**
– **Debugging**: Inspect changes without affecting replication progress.
– **Monitoring**: Check what changes are pending in the slot.
– **Binary replication**: Efficiently stream changes to external systems.
—
## **Important Notes**
– Requires `REPLICATION` privilege or superuser access.
– The slot must be created with a binary-capable output plugin.
– Binary output is **not human-readable** without decoding.
– If you want to consume changes, use `pg_logical_slot_get_binary_changes`.
– The slot retains WAL until changes are consumed via `get` functions.
—
## **Example with Decoding**
If using the `pgoutput` plugin (native PostgreSQL logical replication), the binary format is a PostgreSQL-specific protocol. You’d typically consume it via a logical replication client or `pg_recvlogical`.
For example, using `pg_recvlogical` to decode:
“`bash
pg_recvlogical -d mydb –slot=my_binary_slot –start -o format=binary -f –
“`
—
## **Plugin Support for Binary**
– `pgoutput` (PostgreSQL core) – supports binary.
– `decoderbufs` (used by Debezium) – binary protocol for Protobuf.
– `wal2json` – text-only (no binary support).
Check your plugin’s documentation for binary output capabilities.
