ALTER SUBSCRIPTION
Function
ALTER SUBSCRIPTION modifies subscription attributes.
Precautions
- This statement is supported by version 8.2.0.100 or later clusters.
- Only the owner of a subscription can execute ALTER SUBSCRIPTION, and the new owner must be a system administrator.
Syntax
- Update the connection information of a subscription.
1ALTER SUBSCRIPTION name CONNECTION 'conninfo';
- Update the name of the publication on the publisher side.
1ALTER SUBSCRIPTION name SET PUBLICATION publication_name [, ...];
-
1ALTER SUBSCRIPTION name ENABLE;
-
1ALTER SUBSCRIPTION name DISABLE;
- Set subscription parameters.
1ALTER SUBSCRIPTION name SET ( subscription_parameter [= value] [, ... ] );
- Change the subscription owner.
1ALTER SUBSCRIPTION name OWNER TO new_owner;
- Rename the subscription.
1ALTER SUBSCRIPTION name RENAME TO new_name;
Parameter Description
| Parameter | Description | Value Range |
|---|---|---|
| name | Specifies the name of the subscription to be modified. | Name of an existing subscription. |
| CONNECTION 'conninfo' | Modifies the parameter attributes set by CREATE SUBSCRIPTION for connecting to the publisher database. | For details about the parameters, see Parameter Description in CREATE SUBSCRIPTION. |
| publication_name | Specifies the name of a new publication. | A string compliant with the identifier naming rules. |
| ENABLE | Enables a previously disabled subscription and starts logical replication at the end of a transaction. | - |
| DISABLE | Disables a running subscription and stops logical replication at the end of a transaction. | - |
| SET ( publication_parameter [= value] [, ... ] ) | Modifies the publication parameters initially set by CREATE PUBLICATION. | For details about the parameter, see Parameter Description in CREATE SUBSCRIPTION. |
| new_owner | Specifies the name new subscription owner. | Name of an existing user. |
| new_name | Specifies the new name of the subscription. | A string compliant with the identifier naming rules. |
Examples
- Create the subscription sub_demo.
DROP SUBSCRIPTION IF EXISTS sub_demo; CREATE SUBSCRIPTION sub_demo CONNECTION 'host=10.***.*.** port=10000 user=u_admin dbname=testdb password=********' PUBLICATION pub_sales_only; - Change the publication of the subscription to insert_only.
1ALTER SUBSCRIPTION sub_demo SET PUBLICATION insert_only;
- Rename the subscription.
1ALTER SUBSCRIPTION sub_demo RENAME TO new_sub_demo;
- Disable the subscription.
ALTER SUBSCRIPTION new_sub_demo DISABLE;
Helpful Links
Checking the Subscription Information in the Database
You can use the PG_SUBSCRIPTION system catalog provided by DWS to check the subscription information created in the current database, including the subscription name, owner, and parameter settings.