Help Center/ Data Warehouse Service/ SQL Syntax Reference/ SQL Syntax Reference (9.1.1.x)/ DDL Syntax/ Publications and Subscriptions/ CREATE SUBSCRIPTION
Updated on 2026-07-02 GMT+08:00
View PDF
Share

CREATE SUBSCRIPTION

Function

CREATE SUBSCRIPTION adds a new subscription (SUBSCRIPTION) to the current database.

A subscription is the process by which a database instance (called the subscriber) receives data changes from another database instance (called the publisher). The subscriber achieves data synchronization and distribution by subscribing to data changes from the publisher.

This syntax has been deprecated in clusters of version 9.1.1.100 or later. An error "SUBSCRIPTION is not yet supported" will be reported when the syntax is executed.

Precautions

  • Only system administrators can create subscriptions.
  • The name of a newly created subscription must not be the same as any existing subscription name in the current database.

Syntax

1
2
3
4
 
CREATE SUBSCRIPTION name
    CONNECTION 'conninfo'
    PUBLICATION publication_name [, ...]
    [ WITH ( subscription_parameter [= value] [, ... ] ) ]

Parameter Description

Table 1 CREATE SUBSCRIPTION parameters

Parameter

Description

Value Range

name

Specifies the name of a subscription.

A string, which must comply with the naming convention. For details, see Identifier Naming Conventions.

conninfo

Specifies the string for connecting to the publication side.

  • host: IP address of the publication side. You can specify the IP addresses of both the primary and standby nodes of the publication side. Separate the IP addresses with commas (,).
  • port: Port of the publication side. The port cannot be the primary port. Instead, it must be the primary port number plus 1. Otherwise, the port number conflicts with the thread pool. You can specify the ports of both the primary and standby nodes of the publication side. Separate the ports with commas (,). The number of hosts must be the same as that of ports, and they must correspond to each other.
  • dbname: Database where the publication is located.
  • user and password

    Username and password used to connect to the publication side. The user has the system administrator permission (SYSADMIN) or O&M administrator permission (OPRADMIN).

Example: host=1.1.1.1,2.2.2.2 port=10000,20000 dbname=postgres user=repusr1 password=password

publication_name

Specifies the name of the publication you want to subscribe to on the publication side. A subscription corresponds to multiple publications.

An existing publication name.

WITH ( subscription_parameter [= value] [, ... ] )

Specifies the optional parameters for a subscription.

  • enabled specifies whether a subscription should be actively replicated, or whether it should be just created but not run immediately.

    Value range: true or false

    Default value: true

  • create_slot specifies whether a replication slot is created on the publisher.

    Value range: true or false

    Default value: true

  • slot_name specifies the name of the replication slot to be used on the publisher.

    Value range: a string

    Default value: The subscription name is used as the replication slot name by default.

-

Examples

For details about the sample data published by the database, see Examples.

  • Create a subscription to subscribe to the publication of the upstream database. 
    CREATE SUBSCRIPTION sub_demo
    CONNECTION 'host=10.***.*.** port=10000 user=u_admin dbname=testdb password=********'
    PUBLICATION pub_sales_only;
  • Subscribe to multiple publications. 
    CREATE SUBSCRIPTION sub_demo_1
    CONNECTION 'host=10.***.*.** port=10000 user=u_admin dbname=testdb password=********'
    PUBLICATION pub_sales_only,pub_sales_all_ops;
  • Subscribe to all tables (the upstream publication must contain ALL TABLES IN SCHEMA). 
    CREATE SUBSCRIPTION sub_demo_1
    CONNECTION 'host=10.***.*.** port=10000 user=u_admin dbname=testdb password=********'
    PUBLICATION pub_all_tables;

Helpful Links

ALTER SUBSCRIPTION, DROP SUBSCRIPTION

Parent Topic: Publications and Subscriptions