Basic idea
Logical replication follows a publish and subscribe model. In a publisher node, a publication is created, which is a set of changes from a table or a group of tables. In a subscriber node, a subscription is created, which can subscribe to one or more publications.
But why logical replication? Well in traditional replication, the standby server is a bit for bit copy of the leader server but what if you want to copy only some data? Logical replication gives you that flexibility of what should be sent and where to send the data.
How to use logical replication in pgAdmin 4
To use logical replication, you need to connect to the database and you will see two nodes—publication and subscription.
Publication
You can create a publisher using a publication node.
You can define the publication by giving it a name and then specifying whether you want to publish the data of all the tables. Instead of publishing all the tables, we can also specify a certain set of tables to publish. Not only that, but a publication can choose to limit the changes they publish to any combination of INSERT, UPDATE, DELETE, and TRUNCATE operations.
Subscription
You can define the subscription by giving the name and connection details. You can also define whether you want to go for SSL or standard connection to connect to the publisher server.
Then, specify the publications to get the data from. You can subscribe to multiple publications.
Next, specify the SSL configuration details if you wish to use SSL security for connection with the publisher server. Then you can specify some optional configuration settings, like whether you want to enable or disable the subscription or whether you want to create a slot or not. You can specify the slot name of your choice, but if you choose not to it will default to a subscription name as slot name. Finally, click on the save button to create a subscription.
This creates a new subscription subscription1 which will start replicating tables that are part of the publication1 publication. And that’s all that’s needed to get basic replication working. By default, the new subscription will also copy any preexisting data in those tables but you can disable this using the Copy data clause. Note that the definition of the table is not copied at this time, so we need to create the tables ourselves, as the replication worker will produce an error if it can’t find the table locally.
Each subscriber can subscribe to multiple publications, and each publication can publish changes to multiple subscribers. The column names must match, but the order of the columns in the subscriber table don't matter. Additionally, there can be the same or more number of columns in a subscribed table.
Limitation of logical replication
Logical replication has some limitations on which the community is continuously working to overcome. Tables must have the same full qualified name between publication and subscription. Tables must have a primary key or unique key. Bi-directional Replication is not supported and does not replicate schema/DDL. Subscriptions can have more columns or a different order of columns, but the types and column names must match between Publication and Subscription. Superuser privileges are required to add all tables.
Conclusion
Getting logical replication into PostgreSQL was a large undertaking. Logical replication is a very useful feature used to replicate part of data or all data to a replica server from a leader. You can also specify on which action it should replicate the data. You can replicate data between different major versions of PostgreSQL/EDB Postgres Advanced Server. You can send incremental changes in a single database or a subset of a database to other databases.
Read more: How to Get the Most Out of the Schema Diff Tool in pgAdmin 4