Stratégies de réplication (en continu, logique)

Imagine your database goes down! Replication is key to preventing data loss and ensuring your application stays online. It creates copies of your database.

It's vital for:

• High Availability (HA): If the main server fails, a copy can take over.
• Read Scaling: Distribute read queries across multiple servers, reducing load on the primary.

The most common replication setup is a Primary-Replica (or Master-Slave) model. One database server acts as the primary, handling all writes.

• Primary: Processes all write operations (INSERT, UPDATE, DELETE).
• Replica(s): Receive copies of data changes from the primary and can serve read-only queries.

This ensures data consistency and offloads read traffic.

Streaming Replication is PostgreSQL's built-in, physical replication method. It works by continuously shipping the Write-Ahead Log (WAL) records from the primary to one or more replicas.

WAL records are low-level descriptions of every change made to the database. Replicas apply these changes to stay in sync.

Here's how streaming replication works:

• The primary server writes all database changes to its WAL files.
• A dedicated process (WAL sender) on the primary sends these WAL records to replica servers.
• A process (WAL receiver) on each replica receives and applies these records.

This keeps the replica's data identical to the primary's, block by block.

To enable streaming replication on the primary, you need to configure postgresql.conf:

• wal_level = replica (or higher)
• max_wal_senders = 10 (number of replicas/connections)
• listen_addresses = '*' (allow connections from replicas)

You'll also need to set up pg_hba.conf to allow replica connections.

Before a replica can stream WAL, it needs a full copy of the primary's data. pg_basebackup is the standard tool for this.

It creates a consistent snapshot of the primary's data directory, which then becomes the replica's initial data.

After pg_basebackup, the replica needs a few files:

• A standby.signal file in its data directory (for PostgreSQL 12+).
• A postgresql.conf with hot_standby = on (if you want to run queries).
• A primary_conninfo setting in postgresql.conf to tell it where to connect to the primary.

This allows it to start up and connect as a replica.

Logical Replication is a newer, more flexible replication method introduced in PostgreSQL 10. Unlike streaming replication, it replicates data based on its logical representation (row changes, not physical blocks).

This allows for selective replication of tables or even databases, and cross-version/cross-platform replication.

Logical replication uses a publish-subscribe model:

• A Publication is created on the primary, defining which tables or changes to publish.
• A Subscription is created on the replica, defining which publications to subscribe to.

The primary then sends logical decoding output (changes) to the subscribers, which apply them.

Here's a simplified example of setting up logical replication:

On Primary:

On Replica:

Remember to configure wal_level = logical and max_replication_slots on the primary.

You need to set up replication for a PostgreSQL database. You want an exact, block-for-block copy of your entire database on a standby server for high availability. Which replication method is best suited for this goal?

We've explored two key PostgreSQL replication strategies:

• Streaming Replication: A physical, continuous copy of the entire database using WAL files. Great for high availability and read scaling with identical replicas.
• Logical Replication: A flexible, publish-subscribe model replicating logical changes (rows). Ideal for selective replication, cross-version upgrades, or integrating with other systems.

Choosing the right strategy depends on your specific needs for consistency, flexibility, and performance.