Interesting aspects of logical replication at a glance

In PostgreSQL, there is this amazing feature called logical replication, which enables you to replicate a set of table(s) from one server to other. Being a new feature it is less mature and needs many improvements. Nevertheless, it should not limit us enjoying its advantage and so here is a post to highlight some interesting aspects of the feature.

No matter how similar it might sound, it is quite a different thing. It solves a different problem altogether and in some sense it even overcomes the issues of streaming replication. Some of the important aspects of logical replication are as follows,

• Writes at the secondary

Unlike in streaming replication, here the secondary can serve as a normal server. To be particular, you can perform  inserts and updates at the secondary also. Also, unlike streaming replication we can set up logical replication for one or more tables only. This comes in handy when we want to setup additional servers for load sharing.

• Schema

The schema is not automatically copied at the secondary once you start the replication. You have to create the tables at the secondary before you start the subscription.

Now in case if there are schema changes in the primary then they will not be replicated via logical replication. To go about such changes, one could pause the replication, make the necessary changes at the secondary and resume the replication then.

• Attribute comparison

Attributes of a table are matched by name. A different order of columns in the target table is allowed, also the data type can also be different as long as the text representation of the type is same as that the secondary. The target table can have additional columns not provided by the published table. Those will be filled with their default values. This makes it easy to also optimize the schema at the time of migration of your database from one to another environment.

• Sequences

As of the PostgreSQL version 12, the sequences used in the tables are not replicated to the secondary server. What it means is that the values of the column containing sequence will be copied to the secondary but the next value, max value, etc. of the sequence will remain unaffected at the secondary.

To get around this situation one can just manually increment the next value of the sequence at the secondary to some high value. An interesting thing could be to decide what high value to choose for the purpose. A good guess would be based upon the normal rate of insertions on the required table and the max value at the primary. Important thing is to choose the value such that sequenced column does not conflict with the ones coming from the primary.

•  Privileges

Currently, the permissions for the tables are checked only at the time of creation of the subscription and never afterwards. So, be careful if some permission changes and there is subscription on that table.

• Partition tables

As you might be aware that current version of logical replication allows only normal tables to be replicated, so if you have a partition table then the root table can not be replicated. However, you may create a publication for the child tables individually and it will be replicated accordingly.