Introduction

PostgreSQL is a powerful, open-source object-relational database system that has earned a reputation for reliability, feature robustness, and performance. Among its many features is the capability for high availability and disaster recovery through a mechanism known as streaming replication, often used in combination with continuous archiving. This article provides a comprehensive look into PostgreSQL streaming replication, illustrating how continuous archiving complements this feature to ensure data integrity and uptime.

Streaming Replication

Streaming replication in PostgreSQL allows for the real-time synchronization of a primary database with one or more standby databases. It is an asynchronous process by default, which means that changes to the primary database are propagated to the standby database(s) after they are committed on the primary. However, it can be configured for synchronous replication if minimal data loss is crucial.

How It Works

1. WAL Segments and Shipping: PostgreSQL uses a Write-Ahead Logging (WAL) mechanism to ensure durability. The primary database writes changes first to the WAL before applying to the actual database. Streaming replication involves these WAL segments being continuously shipped and replayed on standby nodes.
2. Replication Protocol: PostgreSQL 9.0 and later versions employ streaming replication using a protocol at the database level that allows the standby to continuously request WAL segments before the primary server archives them. This results in low-latency data streaming.
3. Replication Slots: Introduced in PostgreSQL 9.4, replication slots prevent the primary server from discarding WAL segments until they are replayed by all standby servers. This helps maintain data consistency and ensures all changes are propagated efficiently.

-- Example of Creating a Replication Slot
SELECT * FROM pg_create_physical_replication_slot('my_slot');

Configuration

To set up streaming replication, a few key configurations are needed in the postgresql.conf and pg_hba.conf files:

• Primary Server (postgresql.conf):

  wal_level = replica
  max_wal_senders = 3
  listen_addresses = '*'

• Standby Server (recovery.conf or within postgresql.conf):

  primary_conninfo = 'host=primary_host port=5432 user=replication_user password=yourpassword'

• Security (pg_hba.conf on Primary):

  host replication replication_user 192.168.1.0/24 md5

Continuous Archiving

Continuous archiving complements streaming replication by providing a mechanism to backup WAL segments for long-term storage, allowing point-in-time recovery (PITR). It ensures that data can be restored to any point before a failure, even if the failure affected both primary and standby databases.

How It Works

• WAL Archiving: In continuous archiving, PostgreSQL saves copies of each WAL segment to a designated storage location as they are completed, usually an external storage system or a different filesystem.
• Recovery: If a failure occurs, archived WAL segments can be replayed on a base backup to bring the database up to the latest possible consistent state.

Configuration

Configuration involves setting archive parameters in postgresql.conf:

• Configure Archive Mode:

  archive_mode = on
  archive_command = 'cp %p /mnt/server/archivedir/%f'

Combining Streaming Replication with Continuous Archiving

Combining streaming replication with continuous archiving ensures maximum data durability and availability. The streaming setup allows for real-time synching with standbys for high availability, while archiving provides robust backup and recovery capabilities in case of more extensive data corruption or loss.

Key Points Summary

Below is a summary table of key features and configurations for both streaming replication and continuous archiving in PostgreSQL.

Additional Considerations

• Monitoring: Regularly monitor the replication lag and WAL segment generation rates.
• Testing: Periodically test failover processes and PITR to ensure smooth operations during an actual need.
• Network Bandwidth: Ensure sufficient network bandwidth for WAL shipping, especially with high transaction rates.
• Security: Use secure connections (e.g., SSL) for replication traffic, and secure the standby nodes physically and logically.

By implementing both streaming replication and continuous archiving, PostgreSQL users can achieve a robust, high availability system with adequate disaster recovery options.