Question: What are the different types of replication in PostgreSQL?

Answer

PostgreSQL, a powerful open-source object-relational database system, supports various types of replication to ensure data availability, fault tolerance, and scalability. Replication involves copying and maintaining database objects, like tables, in multiple database instances. This article discusses the primary types of replication available in PostgreSQL:

1. Logical Replication

Introduced in PostgreSQL 10, logical replication allows the selective replication of data at the table level. It uses a publish-subscribe model where changes are captured in a logical log on the publisher side and then applied on the subscriber side. Logical replication can be used for scenarios such as replicating specific tables or databases, migrating between major versions of PostgreSQL, or consolidating databases.

-- To set up logical replication, first, create a publication on the publisher database:
CREATE PUBLICATION my_publication FOR TABLE my_table;

-- Then, subscribe to it on the subscriber database:
CREATE SUBSCRIPTION my_subscription CONNECTION 'dbname=mydb host=otherhost' PUBLICATION my_publication;

2. Streaming Replication

Streaming replication is a physical replication method available since PostgreSQL 9.0. It continuously streams WAL (Write-Ahead Logging) records from the primary server to one or more standby servers. Standby servers can be either in hot standby mode, allowing read-only queries, or in warm standby mode for disaster recovery purposes. Streaming replication is suitable for high availability setups and load balancing read operations.

-- Example configuration settings on the primary server (postgresql.conf):
wal_level = replica
max_wal_senders = 3
archive_mode = on

-- Corresponding settings on the standby server (recovery.conf):
standby_mode = 'on'
primary_conninfo = 'host=primaryhost port=5432 user=replicator password=secret'

3. Snapshot Replication

Although not a built-in type of replication, snapshot replication refers to the process of creating a point-in-time copy of the database, often used for backups or creating read-only replicas. Tools and extensions like pg_dump and Barman can facilitate this process.

4. Synchronous vs. Asynchronous Replication

While discussing replication types, it's crucial to differentiate between synchronous and asynchronous replication modes. Synchronous replication ensures that a transaction is considered committed only if it's confirmed by both the primary and the standby. Asynchronous replication, while faster and less impactful on write performance, does not guarantee immediate consistency across replicas.

Each replication type serves different needs, from disaster recovery and high availability to data migration and reporting. Choosing the right replication strategy depends on your specific application requirements and infrastructure.