Subscription management

Background

If inactive logical replication slots are not promptly removed, WAL files can accumulate, consuming significant storage space that can fill the disk and lock the cluster. A locked cluster cannot accept data writes, disrupting your business.

In the PolarDB console, you can check the active status of logical replication slots for PolarDB for PostgreSQL. You can then manually delete inactive slots to clear accumulated WAL files.

Prerequisites

For a centralized PolarDB for PostgreSQL cluster (the product edition is Enterprise Edition or Standard Edition), you can set the wal_level parameter to logical in the PolarDB console. Changing this parameter restarts the cluster. Plan your business accordingly.

View logical replication slots

1. Log on to the PolarDB console. In the left-side navigation pane, click Clusters. Select the region where your cluster is located, and then click the cluster ID to open its details page.

2. In the left-side navigation pane, choose Configuration and Management > Subscription Management. On the WAL File Management tab, you can view the logical replication slots.

   WAL parameters	Description
   Slot Name	The name of the logical replication slot.
   Slot Type	The type of the logical replication slot. Only logical is supported.
   Use Plug-in	PolarDB for PostgreSQL supports the following plugins by default: decoderbufs decoder_raw wal2json pgoutput
   Temporary Slot	Indicates whether the slot is temporary. A temporary slot exists only for the current session and is automatically deleted when the session ends. true: a temporary slot. false: a persistent slot.
   Database Name	The database where the slot resides.
   Accumulated WAL Files	The size of accumulated WAL files for this slot, in megabytes (MB).
   Logical Subscription Latency	The replication lag of the logical subscription for this slot, in seconds (s).
   Whether Active	Indicates whether the logical replication slot is currently in use. INACTIVE: No subscriber is connected. ACTIVE: A subscriber is connected.

3. (Optional) If the Whether Active column for a logical replication slot shows INACTIVE, it means WAL files are accumulating. Resolve this issue promptly using one of the following methods:

   • Use this slot in your application to change its status to ACTIVE.

   • If the slot is redundant, delete it by running the following command: SELECT pg_drop_replication_slot('slot_name');.

(Optional) Create a logical replication slot

1. Create a publication in the publisher database.

   CREATE PUBLICATION <publication_name> FOR TABLE <table_name>;

   Example:

   CREATE PUBLICATION pub1 FOR TABLE public.t1;

   Note

   To list existing publications, run SELECT * FROM pg_publication;.

2. Create a new subscription on the subscriber. In this scenario, the publisher and subscriber are different databases within the same cluster.

   1. Use a privileged account to create a logical replication slot in the publisher database.

      SELECT pg_create_logical_replication_slot('<slot_name>', 'pgoutput');

   2. In the subscriber database, create a table with the same schema and name as the table in the publisher database.

   3. Create a logical subscription in the subscriber database.

      CREATE SUBSCRIPTION my_subscription
      CONNECTION 'channel_name=localhost dbname=<publisher_database_name> user=<privileged_account> password=<password>'
      PUBLICATION pub1 WITH (create_slot=false,slot_name=<slot_name>);

      Important

      When the publisher database and subscriber database are in the same cluster, you must include create_slot=false in the WITH clause. This ensures the subscriber uses the existing replication slot instead of creating a new one.