Edit

Logical decoding

  • Article

APPLIES TO: Azure Database for PostgreSQL - Single Server

Important

Azure Database for PostgreSQL - Single Server is on the retirement path. We strongly recommend that you upgrade to Azure Database for PostgreSQL - Flexible Server. For more information about migrating to Azure Database for PostgreSQL - Flexible Server, see What's happening to Azure Database for PostgreSQL Single Server?.

Logical decoding in PostgreSQL allows you to stream data changes to external consumers. Logical decoding is popularly used for event streaming and change data capture scenarios.

Logical decoding uses an output plugin to convert Postgres’s write ahead log (WAL) into a readable format. Azure Database for PostgreSQL provides the output plugins wal2json, test_decoding and pgoutput. pgoutput is made available by PostgreSQL from PostgreSQL version 10 and up.

For an overview of how Postgres logical decoding works, visit our blog.

Note

Logical replication using PostgreSQL publication/subscription is not supported with Azure Database for PostgreSQL - Single Server.

Set up your server

Logical decoding and read replicas both depend on the Postgres write ahead log (WAL) for information. These two features need different levels of logging from Postgres. Logical decoding needs a higher level of logging than read replicas.

To configure the right level of logging, use the Azure replication support parameter. Azure replication support has three setting options:

  • Off - Puts the least information in the WAL. This setting is not available on most Azure Database for PostgreSQL servers.
  • Replica - More verbose than Off. This is the minimum level of logging needed for read replicas to work. This setting is the default on most servers.
  • Logical - More verbose than Replica. This is the minimum level of logging for logical decoding to work. Read replicas also work at this setting.

Using Azure CLI

  1. Set azure.replication_support to logical.

    az postgres server configuration set --resource-group mygroup --server-name myserver --name azure.replication_support --value logical

  2. Restart the server to apply the change.

    az postgres server restart --resource-group mygroup --name myserver

  3. If you are running Postgres 9.5 or 9.6, and use public network access, add the firewall rule to include the public IP address of the client from where you will run the logical replication. The firewall rule name must include _replrule. For example, test_replrule. To create a new firewall rule on the server, run the az postgres server firewall-rule create command.

Using Azure portal

  1. Set Azure replication support to logical. Select Save.

    Azure Database for PostgreSQL - Replication - Azure replication support

  2. Restart the server to apply the change by selecting Yes.

    Azure Database for PostgreSQL - Replication - Confirm restart

  3. If you are running Postgres 9.5 or 9.6, and use public network access, add the firewall rule to include the public IP address of the client from where you will run the logical replication. The firewall rule name must include _replrule. For example, test_replrule. Then select Save.

    Azure Database for PostgreSQL - Replication - Add firewall rule

Start logical decoding

Logical decoding can be consumed via streaming protocol or SQL interface. Both methods use replication slots. A slot represents a stream of changes from a single database.

Using a replication slot requires Postgres's replication privileges. At this time, the replication privilege is only available for the server's admin user.

Streaming protocol

Consuming changes using the streaming protocol is often preferable. You can create your own consumer / connector, or use a tool like Debezium.

Visit the wal2json documentation for an example using the streaming protocol with pg_recvlogical.

SQL interface

In the example below, we use the SQL interface with the wal2json plugin.

  1. Create a slot.

    SELECT * FROM pg_create_logical_replication_slot('test_slot', 'wal2json');

  2. Issue SQL commands. For example:

    CREATE TABLE a_table (
   id varchar(40) NOT NULL,
   item varchar(40),
   PRIMARY KEY (id)
);

INSERT INTO a_table (id, item) VALUES ('id1', 'item1');
DELETE FROM a_table WHERE id='id1';

  3. Consume the changes.

    SELECT data FROM pg_logical_slot_get_changes('test_slot', NULL, NULL, 'pretty-print', '1');

    The output will look like:

    {
      "change": [
      ]
}
{
      "change": [
               {
                        "kind": "insert",
                        "schema": "public",
                        "table": "a_table",
                        "columnnames": ["id", "item"],
                        "columntypes": ["character varying(40)", "character varying(40)"],
                        "columnvalues": ["id1", "item1"]
               }
      ]
}
{
      "change": [
               {
                        "kind": "delete",
                        "schema": "public",
                        "table": "a_table",
                        "oldkeys": {
                              "keynames": ["id"],
                              "keytypes": ["character varying(40)"],
                              "keyvalues": ["id1"]
                        }
               }
      ]
}

  4. Drop the slot once you are done using it.

    SELECT pg_drop_replication_slot('test_slot');

Monitoring slots

You must monitor logical decoding. Any unused replication slot must be dropped. Slots hold on to Postgres WAL logs and relevant system catalogs until changes have been read by a consumer. If your consumer fails or has not been properly configured, the unconsumed logs will pile up and fill your storage. Also, unconsumed logs increase the risk of transaction ID wraparound. Both situations can cause the server to become unavailable. Therefore, it is critical that logical replication slots are consumed continuously. If a logical replication slot is no longer used, drop it immediately.

The 'active' column in the pg_replication_slots view will indicate whether there is a consumer connected to a slot.

SELECT * FROM pg_replication_slots;

Set alerts on Storage used and Max lag across replicas metrics to notify you when the values increase past normal thresholds.

Important

You must drop unused replication slots. Failing to do so can lead to server unavailability.

How to drop a slot

If you are not actively consuming a replication slot you should drop it.

To drop a replication slot called test_slot using SQL:

SELECT pg_drop_replication_slot('test_slot');

Important

If you stop using logical decoding, change azure.replication_support back to replica or off. The WAL details retained by logical are more verbose, and should be disabled when logical decoding is not in use.

Next steps