Управление подписками

In PostgreSQL's logical replication, a subscription is the receiving end. It's configured on a subscriber server to connect to a publisher server and pull data changes from a specific publication.

Think of it as subscribing to a newspaper. The publisher creates the newspaper (publication), and you (the subscriber) sign up to receive it.

The server where you create a subscription becomes the subscriber. This server will connect to the publisher, receive the changes (inserts, updates, deletes, truncates), and apply them to its local tables.

It's crucial that the tables on the subscriber have the same structure as the published tables on the master for replication to work correctly.

To start receiving data, you use the CREATE SUBSCRIPTION command. You need to specify the connection details to the publisher and the name of the publication you want to subscribe to.

This command is run on the subscriber database.

When creating a subscription, several options control its behavior:

• COPY_DATA: If true (default), existing data from the published tables is copied.
• ENABLED: If true (default), replication starts immediately.
• CREATE_SLOT: If true (default), a replication slot is created on the publisher.
• SLOT_NAME: You can specify an existing slot name instead of creating a new one.

Let's create a subscription named sales_replica. It connects to a publisher at 10.0.0.5, user repl_admin, for the publication sales_publication.

We explicitly ask to copy existing data and enable it right away.

After creating a subscription, you'll want to ensure it's running correctly. PostgreSQL provides the pg_stat_subscription view for this.

It shows details like connection info, enabled status, and the current state of replication.

You might need to temporarily pause replication, for example, during maintenance or schema changes. You can enable or disable a subscription using ALTER SUBSCRIPTION.

• DISABLE: Stops the replication process.
• ENABLE: Resumes the replication process.

If the publisher adds new tables to a publication, the subscriber won't automatically start replicating them. You need to tell the subscriber to refresh its understanding of the publication.

The REFRESH PUBLICATION command updates the subscription's table list.

When a subscription is no longer needed, you can drop it. This command is executed on the subscriber server. By default, it also attempts to drop the associated replication slot on the publisher.

Ensure the subscription is disabled before dropping it to prevent errors.

You have a subscription named user_data_sub that is actively replicating data. You need to perform some schema changes on the subscriber and want to temporarily stop the data flow without removing the subscription entirely.

In this lesson, you learned how to manage subscriptions in PostgreSQL logical replication. You can create new subscriptions, monitor their status, enable/disable them for maintenance, refresh their publication list, and finally drop them when no longer needed.

Mastering these commands is key to maintaining a robust and flexible logical replication setup.