Postgres Logical Replication

Here are some useful references to help understand logical replication. If you want to dig into a particular command or message these are the places to start:

• The Protocol
• The Message Formats

We will use outbox as a concrete example of what logical replication can do for us.

In the outbox pattern instead of directly publishing a message to a message broker your write it to an outbox table in the same transaction you do the rest of your database work. Once the entry has been added to the outbox table a separate process will pick it up and publish it to the message broker. The result is that the database work and publish are recorded in the same atomic write which maintains perfect consistency between the publish and the work that triggered the publish.

First we'll add a table to write the messages to:

CREATE TABLE "outbox" (
  ...
);

In order to use logical replication you will need to create a publication:

CREATE PUBLICATION outbox_pub FOR TABLE "outbox";

And then create a replication slot for the publication:

SELECT * FROM pg_create_logical_replication_slot('outbox_slot', 'pgoutput');

You will want to select a library that speaks Postgres logical replication. The Go library pglogrepl is a good example of one of these, and most popular languages will have a library you can make use of.

The current position of a client in a replication slot is designated with something called a Log Sequence Number (LSN). It's effectively a cursor in the stream of messages. The client is responsible for informing Postgres when it has processed a message by advancing the cursor, and it does this by sending a Standby status update message with the furthest LSN it has successfully processed.

However you cannot always wait until you need to advance the cursor to send a Standby status update message; since if you wait too long to send one of those messages Postgres will assume the client is gone and close the connection. One technique you use here is a background heartbeat process that sends the current cursor position on an interval whether it has changed or not.

Be aware that you must advance the cursor. If you don't then the files needed for this process will start to pile up on the database server. In extreme circumstances this can cause issues on the database server, or it can result in messages never making it to the client.

Since we are implementing outbox the only thing we care are about is inserts into the outbox table.

The first thing you will do with your library of choice is start replication on the replication slot you previously created using START_REPLICATION. This takes an LSN as a parameter, but if you are advancing the cursor as discussed above an LSN of 0 will work fine for resuming work.

From there on out messages will be streamed to the client. Your logical replication library of choice should be able to both get and parse these messages into one of four messages that we actually care about:

• Begin (B): A transaction was started
• Commit (C): A transaction was committed
• Relation (R): Data needed to decode the insert message
• Insert (I): The data that was inserted into the table

These messages map naturally to transactions:

BEGIN;

INSERT INTO "outbox"(...)
VALUES (...)

COMMIT;

In that case BEGIN maps to B, INSERT INTO maps to I, and COMMIT maps to C. Here are two examples of how the messages might come across:

• B R I I I C
• B R I I I C B I I I C

As you can see you will want to record the R message so you can decode the future I messages. You will also want to hold off on publishing the message to the message broker until the C message comes across. At that point (assuming the publish succeeds) you can advance the cursor in the message stream.

Postgres logical replication is extremely powerful tool to maintain consistency across data stores in your systems.