Partition Tables in PostgreSQL 12.4

For both log based and trigger based replications, it is always possible to create single replications for each partition; this approach works fine, but has the limitation that it doesn’t handle newly created partitions automatically. At the same time, if a partition is dropped, the application will keep using a replication for a partition that is no longer needed (until the replication is manually removed).

The alternative to this solution would be to create one single replication for the master table only. In this case:

For TRIGGER-BASED replications: Every operation done on the partitions and/or the master is reflected through triggers. However, dropping a partition doesn’t generate delete triggers on the deleted records (this can make sense as a drop table normally doesn’t have effect on replication).

For LOG-BASED replications: Creating a replication on the master table doesn’t work with partitions. The reason is that if we insert/update/delete a record on the master table that belongs to a partition, it has the same effect as modifying the record directly to the partition: the change is logged as belonging to the partition and not to the master. The record change is then found by the log reader, but it’s skipped because the internal table id is the one of the partition and doesn’t match the master table id.

Partition Tables in PostgreSQL 9.5

In version 9.5, the syntax is different and partition tables are actually different tables that inherit from the master table: https://www.postgresql.org/docs/9.4/ddl-partitioning.html. Therefore, in this case replication is achieved by putting  each single partition table in a replication.