PostgreSQL Physical Replication: Ensuring High Availability

Physical replication in PostgreSQL is a powerful mechanism for creating and maintaining exact copies of your primary database. This process is crucial for achieving high availability (HA), disaster recovery (DR), and improving read performance by offloading read-heavy queries to replica servers.

Understanding the Core Concept

Physical replication copies database blocks from the primary to replicas.

Physical replication works by sending Write-Ahead Log (WAL) records from the primary server to one or more replica servers. These WAL records contain the changes made to the database. The replica server then applies these WAL records to its own data files, ensuring it remains an exact copy of the primary.

The fundamental principle of PostgreSQL's physical replication lies in the replication of the Write-Ahead Log (WAL). When a transaction commits on the primary server, its changes are first recorded in the WAL. These WAL records are then streamed to the replica(s). The replica server receives these records and applies them to its data files, effectively replaying the changes that occurred on the primary. This block-level copying ensures that the replica's data files are identical to the primary's at any given point in time.

How Physical Replication Works

PostgreSQL supports two primary methods for physical replication: streaming replication and file-based log shipping. Streaming replication is generally preferred for its near real-time data synchronization.

Streaming Replication

Streaming replication involves a primary server (sender) and one or more replica servers (receiver). The sender continuously streams WAL records to the receivers as they are generated. Receivers then apply these records to their data directories.

What is the primary mechanism used in PostgreSQL streaming replication to synchronize data?

Streaming of Write-Ahead Log (WAL) records from the primary to the replica.

File-Based Log Shipping

In file-based log shipping, WAL files are archived by the primary server and then copied to the replica server(s). The replica then reads these WAL files and applies them. This method is simpler but typically has higher latency compared to streaming replication.

Feature	Streaming Replication	File-Based Log Shipping
Synchronization Method	Continuous WAL streaming	Archived WAL file transfer
Latency	Near real-time (low)	Higher (dependent on archive/copy frequency)
Complexity	Slightly more complex setup	Simpler setup
Use Case	High Availability, read scaling	Disaster Recovery, less critical read scaling

Key Components and Configuration

Setting up physical replication involves configuring both the primary and replica servers. Key parameters include

code

wal_level

code

max_wal_senders

code

wal_keep_segments

(or

code

wal_keep_size

), and

code

hot_standby

`wal_level` must be set to 'replica' or 'logical' on the primary.

The wal_level parameter dictates the amount of information written to the WAL. For physical replication, it must be set to at least 'replica' to ensure all necessary data changes are logged.

The wal_level parameter in postgresql.conf is fundamental. Setting it to replica (or logical if you also plan to use logical replication) ensures that the WAL contains enough information for a replica to reconstruct the database state. Other critical parameters include max_wal_senders on the primary (to allow multiple streaming connections) and hot_standby on the replica (to allow read queries on the replica while it's receiving WAL).

Replication Modes

PostgreSQL offers synchronous and asynchronous replication. The choice between them impacts data consistency and availability.

Asynchronous Replication

In asynchronous mode, the primary server commits a transaction as soon as it writes the WAL locally. It then sends the WAL to the replica(s) without waiting for confirmation. This offers the best performance but carries a risk of data loss if the primary fails before the WAL is sent and applied to the replica.

Synchronous Replication

Synchronous replication requires the primary server to wait for confirmation from at least one synchronous replica that the WAL records have been received (and optionally, written to disk or applied). This guarantees no data loss on commit but can introduce latency to write operations on the primary.

Choosing between asynchronous and synchronous replication is a trade-off between write performance and data durability. For critical applications, synchronous replication (or a hybrid approach) is often preferred.

Failover and High Availability

Physical replication is the foundation for building highly available PostgreSQL clusters. In the event of a primary server failure, a replica can be promoted to become the new primary. This process, known as failover, can be manual or automated using external tools.

What is the term for promoting a replica to become the primary server after a primary failure?

Failover

Use Cases for Physical Replication

Beyond high availability, physical replication is also used for:

Disaster Recovery: Replicas in different geographical locations can serve as backups in case of a site-wide failure.
Read Scaling: Offloading read-intensive queries to replicas reduces the load on the primary server, improving overall performance.
Backups: Replicas can be used for taking backups without impacting the primary's performance.

Considerations for Production

When implementing physical replication in a production environment, consider network bandwidth, disk I/O on both primary and replicas, monitoring replication lag, and having a robust failover strategy.

Physical replication in PostgreSQL involves the primary server sending Write-Ahead Log (WAL) records to one or more replica servers. The replica server then applies these WAL records to its data files, ensuring it remains an up-to-date copy of the primary. This process can be visualized as a continuous stream of changes flowing from the source to the destinations, maintaining data consistency across the cluster.

📚

Text-based content

Library pages focus on text content

Learning Resources

PostgreSQL Documentation: Replication(documentation)

The official PostgreSQL documentation provides a comprehensive overview of replication concepts, setup, and configuration options.

PostgreSQL Streaming Replication Tutorial(tutorial)

A step-by-step guide to setting up and configuring streaming replication in PostgreSQL.

High Availability with PostgreSQL(blog)

This blog post discusses strategies for achieving high availability in PostgreSQL, with a focus on replication.

Understanding PostgreSQL WAL(blog)

An in-depth explanation of PostgreSQL's Write-Ahead Log (WAL) and its importance in replication and recovery.

PostgreSQL Replication: Synchronous vs. Asynchronous(blog)

Compares and contrasts synchronous and asynchronous replication, helping to understand the trade-offs.

PostgreSQL Replication Slots(documentation)

Details on replication slots, a crucial feature for managing WAL and preventing data loss on replicas.

PostgreSQL Replication: A Deep Dive(presentation)

A presentation offering a detailed look at PostgreSQL replication mechanisms and best practices.

PostgreSQL Replication for High Availability and Disaster Recovery(video)

A video explaining how PostgreSQL replication contributes to high availability and disaster recovery strategies.

PostgreSQL Replication: Setup and Monitoring(tutorial)

A practical tutorial on setting up streaming replication, often with a focus on a specific operating system.

PostgreSQL Physical Replication Explained(blog)

A clear explanation of the concepts and mechanics behind PostgreSQL physical replication.