Subscriptionの管理
スタンバイサーバー上でSubscriptionを設定・管理し、Publicationから変更を受信して適用します。
「Subscriptionの管理」はCoddyKit上の無料Advanced PostgreSQL: Indexing, Partitioning, Replicationレッスンです。 これはレッスン3/4です。 下記で完全なレッスンを無料で読むことができます。その後、ブラウザ内の組み込みコードエディタと24時間対応のAIチューターでハンズオン演習できます。 これはAdvanced PostgreSQL: Indexing, Partitioning, Replication学習パスの一部であり、ウェブとCoddyKitアプリ全体で進捗が同期されます。 Advanced PostgreSQL: Indexing, Partitioning, Replicationコースには全4レッスンが含まれています。
このレッスンの一部はまだ翻訳されておらず、英語で表示されています。
What is a Logical Subscription?
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 Subscriber Server Role
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.
Creating a New Subscription
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.
CREATE SUBSCRIPTION my_app_sub
CONNECTION 'dbname=source_db host=192.168.0.10 user=repl_user password=securepass'
PUBLICATION app_data_pub;Essential Subscription Options
When creating a subscription, several options control its behavior:
COPY_DATA: Iftrue(default), existing data from the published tables is copied.ENABLED: Iftrue(default), replication starts immediately.CREATE_SLOT: Iftrue(default), a replication slot is created on the publisher.SLOT_NAME: You can specify an existing slot name instead of creating a new one.
Full Subscription Example
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.
-- Execute this on the subscriber database
CREATE SUBSCRIPTION sales_replica
CONNECTION 'dbname=sales_db host=10.0.0.5 user=repl_admin password=mysecret'
PUBLICATION sales_publication
WITH (copy_data = true, create_slot = true, enabled = true);Monitoring Subscription Status
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.
-- On the subscriber server
SELECT
subname,
subenabled,
substate,
subslotname,
subconninfo
FROM pg_stat_subscription;Enabling and Disabling Subscriptions
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.
-- Temporarily stop replication for 'sales_replica'
ALTER SUBSCRIPTION sales_replica DISABLE;
-- Resume replication
ALTER SUBSCRIPTION sales_replica ENABLE;Refreshing Publication List
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.
-- Update 'sales_replica' to include any newly added tables
ALTER SUBSCRIPTION sales_replica REFRESH PUBLICATION;Dropping a Subscription
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.
-- First, disable the subscription if it's enabled
ALTER SUBSCRIPTION sales_replica DISABLE;
-- Then, drop the subscription
DROP SUBSCRIPTION sales_replica;Subscription Management Check
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.
Recap: Managing Subscriptions
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.
よくある質問
「Subscriptionの管理」レッスンは無料ですか？
はい。「Subscriptionの管理」の完全なテキストはこのウェブで無料で読めます。インタラクティブに演習し（組み込みコードエディタと24時間対応のAIチューター）、Advanced PostgreSQL: Indexing, Partitioning, Replicationコースの残りをアンロックするには、CoddyKit PROにアップグレードしてください。 Advanced PostgreSQL: Indexing, Partitioning, Replicationコースには全4レッスンが含まれています。
「Subscriptionの管理」で何を学びますか？
スタンバイサーバー上でSubscriptionを設定・管理し、Publicationから変更を受信して適用します。 ブラウザで直接実行するハンズオンコードでAdvanced PostgreSQL: Indexing, Partitioning, Replicationを演習し、24時間対応のAIチューターがレッスンを進める中での質問に答えます。
Advanced PostgreSQL: Indexing, Partitioning, Replicationを始めるのに経験は必要ですか？
事前経験は必要ありません。CoddyKitのAdvanced PostgreSQL: Indexing, Partitioning, Replicationは初級者から上級者向けに構成されているため、ここから始めるか最初から始めて、自分のペースで進むことができます。 これはレッスン3/4です。
「Subscriptionの管理」レッスンにはどのくらい時間がかかりますか？
ほとんどのCoddyKitレッスンは約5～10分かかります。各レッスンはコンパクトでインタラクティブなので、着実に進歩し、ウェブとアプリ全体で正確に前回の場所から再開できます。
このAdvanced PostgreSQL: Indexing, Partitioning, Replicationレッスンでコードを書いて実行できますか？
はい。すべてのAdvanced PostgreSQL: Indexing, Partitioning, Replicationレッスンに組み込みコードエディタが含まれているため、ブラウザでリアルコードを書いて実行し、即座のAIフィードバックを取得できます。ローカル設定は不要です。
このコースのすべてのレッスン
- 論理レプリケーションの基礎
- Publicationの設定
- Subscriptionの管理
- 論理レプリケーションにおける競合解決