ALTER SUBSCRIPTION

Function Description

ALTER SUBSCRIPTION alters the attributes of a subscription specified in CREATE SUBSCRIPTION.

Precautions

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.

  AlterSubscription ::= ALTER SUBSCRIPTION name CONNECTION 'conninfo'

• Update the name of the publication on the publisher side.

  AlterSubscription ::= ALTER SUBSCRIPTION name SET PUBLICATION publication_name [, ...]

• Update the subscription list on the subscriber.

  AlterSubscription ::= ALTER SUBSCRIPTION name REFRESH PUBLICATION [ WITH ( refresh_option [= value] [, ... ] ) ]

• Enable a subscription.

  AlterSubscription ::= ALTER SUBSCRIPTION name ENABLE

• Update the attributes defined in CREATE SUBSCRIPTION.

  AlterSubscription ::= ALTER SUBSCRIPTION name SET ( subscription_parameter [= value] [, ... ] )

• Update the owner of a subscription.

  AlterSubscription ::= ALTER SUBSCRIPTION name OWNER TO { new_owner | CURRENT_USER | SESSION_USER }

• Change the name of a subscription.

  AlterSubscription ::= ALTER SUBSCRIPTION name RENAME TO new_name

Parameter Description

• name

  Specifies the name of the subscription whose attributes are to be altered.

• CONNECTION 'conninfo'

  Alters the connection attributes initially set by CREATE SUBSCRIPTION.

• ENABLE (boolean)

  Specifies whether a subscription should be actively replicated, or whether it should be just set but not started. The default value is true.

• SET ( subscription_parameter [= value] [, ... ] )

  Modifies the parameters set by CREATE SUBSCRIPTION. The allowed parameters are slot_name and synchronous_commit.

  • If enabled is set to false during subscription creation, slot_name is forcibly set to NONE, that is, null. In this case, the replication slot does not exist even if the value of slot_name is specified.
  • Change the value of enabled from false to true. When the subscription is enabled, the publication side is connected to create a replication slot. If you do not specify the value of slot_name, the default value (subscription name) is used.
  • If enabled is set to true, the subscription is in the normal state. In this case, slot_name cannot be left empty, but the name of the replication slot can be changed to a valid name.

• REFRESH PUBLICATION

  Obtains the missing table information from the publisher. Tables added to the subscription publication are copied since the last REFRESH PUBLICATION or CREATE SUBSCRIPTION call.

  The refresh_option specifies additional options for the refresh operation. The options are as follows:

  copy_data (boolean)

  Determines whether to copy existing data in the publication that is being subscribed to after copy starts. The default value is true. (Tables previously subscribed to will not be copied.)

• new_owner

  Specifies the username of the new owner of a subscription.

• new_name

  Specifies the new name of a subscription.

Example

For details, see Examples.

Helpful Links

CREATE SUBSCRIPTION, DROP SUBSCRIPTION