Mis vahe on füüsilisel ja loogilisel replikatsioonil PostgreSQL-is?

Physical and logical replication in PostgreSQL differ in the method of data transfer and capabilities:

Physical replication:

• Copies binary data (WAL — Write-Ahead Log) from the primary server to the replica.
• The replica is an exact copy of the master at the file system level.
• Used for creating hot standby backups.
• The replica is usually read-only.
• Does not allow selective replication of individual tables or data.

Logical replication:

• Reproduces changes at the SQL level (DML) — inserts, updates, deletes.
• Allows replication of specific tables or data sets.
• The replica may not be an exact copy of the master, allowing data transformations.
• Used for migrations, integrations, distributed systems.
• Permits having replicas with different structures or data filtering.

Example: if you need to replicate only part of the data or specific tables, use logical replication. For high availability and quick synchronization of the entire database — physical.