Postgres logical replication relies on pub/sub mechanism. Primary node decodes WAL segments and extrapolates SQL queries from them, then it publishes the commands while replica node subscribes to it and passes received queries directly to query executor.
Logical replication:
– is a bit slower then physical replications
– allows replication across Postgres versions
– allows selective data replication e.g. only certain databases or tables
– copies only Data Modification Language (DML). So DBA has to ensure that the replica and primary node have synchronized schemas and settings.
– allows small clean copy e.g. for testing purposes
Physical replication:
– fast
– replicates entire cluster on page level. This means that unlogged tables are not replicated.
– replicates both Data Definition Language – DDL and Data Modification Language – DML
– works only on corresponding major versions of Postgres
– it is a exact copy of entire cluster