Client Area

PostgreSQL Command: CREATE SUBSCRIPTION

PostgreSQL Command: CREATE SUBSCRIPTION

PostgreSQL is a powerful open-source relational database management system that offers a wide range of features and functionalities. One of its notable features is the ability to replicate data across multiple servers using the CREATE SUBSCRIPTION command. In this article, we will explore the CREATE SUBSCRIPTION command and its usage in PostgreSQL.

What is CREATE SUBSCRIPTION?

The CREATE SUBSCRIPTION command in PostgreSQL allows you to set up logical replication between a publisher database and one or more subscriber databases. It enables you to replicate specific tables or entire databases from the publisher to the subscriber(s) in real-time.

Logical replication is different from physical replication in that it replicates the changes made to the data rather than replicating the entire database cluster. This makes it more flexible and efficient, especially when dealing with large databases.

How to use CREATE SUBSCRIPTION?

To create a subscription, you need to have a publisher database and at least one subscriber database. Here is the basic syntax of the CREATE SUBSCRIPTION command:

CREATE SUBSCRIPTION subscription_name
    CONNECTION 'connection_string'
    PUBLICATION publication_name
    WITH (option_name = option_value, ...);

Let’s break down the syntax:

  • subscription_name: The name of the subscription you want to create.
  • CONNECTION 'connection_string': Specifies the connection string to the publisher database.
  • PUBLICATION publication_name: The name of the publication you want to subscribe to. A publication is a named collection of tables or entire databases that you want to replicate.
  • WITH (option_name = option_value, ...): Additional options for the subscription, such as the replication method, slot name, and synchronization method.

Once you have created the subscription, PostgreSQL will start replicating the specified tables or databases from the publisher to the subscriber(s) in real-time.

Example:

Let’s say we have a publisher database named “mydb” and a subscriber database named “mysubdb.” We want to replicate the “employees” table from the publisher to the subscriber. Here is how we can create a subscription:

CREATE SUBSCRIPTION my_subscription
    CONNECTION 'dbname=mydb host=example.com port=5432 user=myuser password=mypassword'
    PUBLICATION my_publication
    WITH (copy_data = false);

In this example, we created a subscription named “my_subscription” that connects to the publisher database using the specified connection string. We subscribed to the “my_publication” publication and set the “copy_data” option to false, which means the existing data in the subscriber will not be copied from the publisher.

Summary

The CREATE SUBSCRIPTION command in PostgreSQL allows you to set up logical replication between a publisher database and one or more subscriber databases. It provides a flexible and efficient way to replicate specific tables or entire databases in real-time. By using the CREATE SUBSCRIPTION command, you can easily keep your data synchronized across multiple servers.

If you are looking for reliable and high-performance VPS hosting solutions, consider Server.HK. With a wide range of plans and excellent customer support, Server.HK is a trusted provider in the industry.