Skip to content
Français
Account: not signed in, sync available My learning
Select theme
Français

Replicate, back up and fail over a database

By updated

Improve this page

Replicating a database protects it from an outage, backing it up protects it from a mistake, and neither mechanism replaces the other. This lesson teaches you to choose the protection of a production database: what each mechanism covers, what high availability costs, which replica to create, and what a failover really does. It is aimed at intermediate-level readers who have already decided to use a database in the cloud.

What you will learn

  • Distinguish replication and backup according to the risk covered.
  • Estimate the extra cost of a highly available database.
  • Choose between a read replica and a standby replica.
  • Describe what happens during a failover, and test it.

To follow this lesson, you need to know what a managed database is and how it differs from a self-managed one, as covered in the lesson Managed data: databases, caches and queues in the cloud.

Replication and backup do not protect from the same risk

It is a very costly confusion in database management. Both mechanisms are useful, and they do not replace each other. The full vocabulary of this course, which distinguishes high availability, replication, snapshot, backup, point-in-time recovery, archive and disaster recovery, is laid out in resilience, high availability and recovery (in French).

ReplicationBackup
Protects fromthe failure of a server or a zonehuman error and corruption
Time to recoverseconds to minutesminutes to hours
Against a DROP TABLE (deleting a table)useless, the deletion is replicated immediatelythe only protection
Against ransomwareuseless, the encryption is replicateduseful, if the backups are out of reach

The first row rests on the notion of a failure domain: a set of resources that can fail together, such as a server, an availability zone or an entire region. A database whose copies are all in the same failure domain goes down with it. Spreading the database over several failure domains, for example the primary in one zone and the replica in another, prevents a single failure from taking it all.

A replica is not a backup. Everything you do to the primary database, including an accidental deletion, reaches the replica within a few seconds. You therefore need both: replication for availability, backup for mistakes.

Point-in-time recovery, often called PITR (Point-In-Time Recovery), is the feature that really matters: it lets you return to the state before the incident, to the minute. Check that it is enabled, and above all which window it covers. The duration depends on the provider. On Amazon RDS, backup retention can be set from 0 to 35 days, and the default is 7 days if you create the database from the console, but only 1 day through the API or the CLI. Seven days is short if nobody notices the problem before the second week.

What does high availability cost?

A highly available database costs noticeably more than a simple one, and that is logical: you pay for at least a second instance that only waits. At OVHcloud, compute and storage are billed per node: two nodes, twice the bill for those two items. On Amazon RDS, a Multi-AZ cluster has three instances, and a Multi-AZ database with a single standby replica has two. Look up your provider's exact price before deciding.

Before putting figures on it, a second acronym: the RPO (Recovery Point Objective) is the amount of data you accept to lose, expressed as a duration. The RTO, seen above, is the downtime you accept. Their reference definition is laid out in resilience, high availability and recovery (in French).

The items to anticipate, beyond the compute:

  • The storage is duplicated, so billed twice.
  • Traffic between zones is billed by several providers, and synchronous replication generates it permanently.
  • Backups beyond the included quota are billed per gigabyte, and their volume grows with retention.

This does not mean you must give it up, but that you must choose where to put it. A production database carries high availability; a staging database does not need it, and giving it one inflates the bill without reducing any real risk.

  1. Write down the acceptable RPO and RTO, by talking with those who will suffer the outage. "How many minutes of data can we lose?" and "how long can we stay down?".

  2. Deduce the architecture from them, rather than the other way round. An RPO of a few minutes is satisfied by frequent backups; an RPO close to zero requires synchronous replication, more expensive and more restrictive.

  3. Measure what you really get, through a timed restore. It is the only figure that counts, and it almost always differs from the contract's.

Read replica or standby replica?

Both are called "replica", they are created in the same place in the console, and they do not serve the same purpose at all. Confusing them leads you to believe you are protected when you have only relieved the reads, which is the classic bad surprise on the day of the outage.

Read replicaStandby replica
What it bringsextra read capacityservice continuity if the primary instance fails
Replication modeasynchronous, with a variable lagsynchronous at most providers
Can it be queried?yes, that is its purposeoften no: it waits, without serving traffic
What it coststhe price of one more instancethe price of one more instance, with no capacity in return
Its promotion on failurepossible, but it loses the writes not yet replicatedautomatic and lossless

The two replication modes in the table differ by the moment of confirmation. An asynchronous replication confirms the write before it has been copied to the replica, hence a possible lag; a synchronous replication waits for the copy before confirming, which removes the lag but slows down every write.

The read replica's lag is the point to understand. It is usually counted in milliseconds, climbs to several seconds under heavy writes, and is invisible from the application. A piece of data written and then immediately read back from the replica may be missing: it is a concrete case of eventual consistency, and the perceived symptom is a form that was saved but "did not work".

The rule of use fits in one sentence: send to a read replica what tolerates seeing a slightly old state, reports, dashboards, searches, exports. Keep on the primary instance everything a user rereads right after writing it.

What really happens during a failover?

An automatic failover does not make the outage transparent: it shortens it. For a few dozen seconds to a few minutes, the database accepts nothing, open connections are broken, and your application sees errors. What distinguishes a solid architecture is not avoiding this moment, it is getting through it without human intervention.

The mechanism is almost always the same: the provider switches the domain name of the database to the new primary instance. You keep using the same address, it now points elsewhere.

Key points

  • A replica is not a backup: a DROP TABLE replicates within a few seconds. You need both.
  • Check the point-in-time recovery window: on Amazon RDS, the default is seven days through the console and a single one through the API or the CLI.
  • An untested backup is a hypothesis. Restore every quarter, stopwatch in hand, to get a measured RTO.
  • High availability noticeably inflates the bill: reserve it for what must really survive, and deduce the architecture from the RPO and the RTO.
  • A read replica and a standby replica do not protect from the same risk. The first adds capacity, the second continuity.
  • A read replica lags, sometimes by several seconds: send it nothing a user has just written.
  • A failover shortens the outage, it does not remove it, and a failover never deliberately triggered remains a hypothesis.
  • The application that stays down after a failover has cached the domain name.

Knowledge check

Check that you have grasped the essentials of this guide. The questions cover only what has just been explained here.

Knowledge check

Check what you have learned with this interactive quiz

6 questions
6 min
70% to pass

How it works

  • The timer starts when you click Start
  • Multiple choice, true/false and short answer questions
  • You can move between questions
  • Detailed results are shown at the end

Starts the quiz and the timer