`pg_logical_slot_peek_changes` is a PostgreSQL function used to **read changes from a logical replication slot without consuming them**. This means you can inspect the changes multiple times, as they remain in the slot until explicitly consumed with `pg_logical_slot_get_changes`.
—
## **Purpose**
– Used for **debugging** or inspecting changes before applying them.
– Changes stay in the slot, so they can be read again later.
– Helps avoid losing changes if your consumer crashes before processing.
—
## **Syntax**
“`sql
pg_logical_slot_peek_changes(
slot_name text,
upto_lsn pg_lsn,
upto_nchanges integer,
VARIADIC options text[]
)
RETURNS SETOF record
“`
—
## **Parameters**
1. **`slot_name`** – Name of the logical replication slot.
2. **`upto_lsn`** – Read changes up to this LSN (Log Sequence Number). Use `NULL` to read all available changes.
3. **`upto_nchanges`** – Maximum number of changes to return. Use `NULL` for no limit.
4. **`options`** – Optional parameters (e.g., `’include-xids’`, `’skip-empty-xacts’`).
—
## **Common Options**
– `’include-xids’` – Include transaction IDs (default: true).
– `’skip-empty-xacts’` – Skip empty transactions (default: false).
– `’include-timestamp’` – Include commit timestamp.
– `’only-local’` – Only include local changes (no forwarded changes).
—
## **Example Usage**
### 1. **Peek all changes without consuming**
“`sql
SELECT * FROM pg_logical_slot_peek_changes(‘my_slot’, NULL, NULL);
“`
### 2. **Peek with limit**
“`sql
SELECT * FROM pg_logical_slot_peek_changes(‘my_slot’, NULL, 10);
“`
### 3. **Peek with options**
“`sql
SELECT * FROM pg_logical_slot_peek_changes(
‘my_slot’,
NULL,
NULL,
‘include-timestamp’, ‘true’,
‘include-xids’, ‘false’
);
“`
### 4. **Read changes up to a specific LSN**
“`sql
SELECT * FROM pg_logical_slot_peek_changes(‘my_slot’, ‘0/1ABCD123′, NULL);
“`
—
## **Output Columns**
Typical output includes:
– `lsn` – Log sequence number of the change.
– `xid` – Transaction ID.
– `data` – Change data in logical decoding format (e.g., JSON, wal2json, or text).
Example row:
“`
lsn | xid | data
————-+——+———————————————————–
0/1ABCD123 | 1234 | table public.users: INSERT: id[integer]:1 name[text]:’Alice’
“`
—
## **Important Notes**
– **Requires** `REPLICATION` privilege or superuser access.
– **Does not advance the slot** – changes remain until consumed via `pg_logical_slot_get_changes`.
– If the slot is not advanced, WAL files will be retained (can cause disk full).
– Use `pg_replication_slots` view to monitor slot status.
—
## **Consuming Changes**
To **consume** (remove) changes after processing:
“`sql
SELECT * FROM pg_logical_slot_get_changes(‘my_slot’, NULL, NULL);
“`
—
## **Monitoring Slot**
Check slot status:
“`sql
SELECT slot_name, confirmed_flush_lsn, active FROM pg_replication_slots;
“`
—
## **Use Case Example**
“`sql
— 1. Create a logical replication slot
SELECT * FROM pg_create_logical_replication_slot(‘test_slot’, ‘test_decoding’);
— 2. Make some changes
INSERT INTO users(name) VALUES (‘Bob’);
— 3. Peek changes (without consuming)
SELECT * FROM pg_logical_slot_peek_changes(‘test_slot’, NULL, NULL);
— 4. Peek again (same output)
SELECT * FROM pg_logical_slot_peek_changes(‘test_slot’, NULL, NULL);
— 5. Consume changes
SELECT * FROM pg_logical_slot_get_changes(‘test_slot’, NULL, NULL);
— 6. Now peek returns nothing
SELECT * FROM pg_logical_slot_peek_changes(‘test_slot’, NULL, NULL);
“`
—
## **Troubleshooting**
– **No changes?** Ensure:
– Slot is logical (not physical).
– Changes were made after slot creation.
– WAL level is `logical` (`wal_level = logical` in `postgresql.conf`).
– **Slot not advancing?** Use `pg_logical_slot_get_changes` to consume.
– **Disk filling up?** Old WAL files are retained if slot doesn’t advance. Monitor with `pg_ls_waldir()`.
—
Let me know if you need help with a specific logical decoding plugin (like `wal2json`) or more advanced examples!
