Starting from PostgreSQL 10, we have logical replication built into the PostgreSQL source. This way, we can perform replication between PostgreSQL 10 and later using built-in logical replication and do not need to rely on the pglogical extension. In this recipe, we shall see the steps involved in configuring logical replication between PostgreSQL 10 and PostgreSQL 13 so that the upgrade can happen with less downtime.
Getting ready
In order to use built-in logical replication to replicate to PostgreSQL 13, the old cluster should be using PostgreSQL 10 or 11. This type of replication can be used to replicate all the tables. However, we need to set the replica identity for those tables that do not contain any primary keys.
If there is no primary key, use either a unique index that has no null values or the entire row:
ALTER TABLE sales REPLICA IDENTITY USING INDEX unique_index_name;
ALTER TABLE sales REPLICA IDENTITY...
