The case
The database went down with the VM. And the backup was on the same VM.
Isaac installed PostgreSQL on the same VM that runs the order system. On a Friday, the VM failed, and the database never came back. The backup was a script running on that same VM, and its last file was three days old.
That week, the store lost three days of orders.
This post builds on Cloud Fundamentals, Cloud Storage: block, file and object and Regions and Zones.
The problem
A database on a VM is your database
Installing the database on a VM works on the first day. After that, someone has to do the rest: apply security updates, set up backups, test that they restore, keep a copy somewhere else, and move to another server when the current one fails.
Usually, nobody does all of that with the same discipline as a team dedicated to databases.
The solution
Managed database
A managed database is a service where the provider installs and maintains the database engine. It takes care of updates, automatic backups and switching servers when something fails. You remain responsible for your data, the schema, the queries and who has access.
Each provider has its own: Cloud SQL on Google Cloud, Amazon RDS on AWS, Azure SQL Database on Azure and OCI Database on Oracle Cloud.
Automatic backups
The provider makes periodic copies and keeps the transaction log, so it can restore to a specific moment.
At the store: the orders database goes back to its state before the mistake, with no homemade script.
High availability
A standby replica in another zone takes over if the primary fails.
At the store: the failure of one zone doesn't take the sales down.
Read replicas
Copies that serve read queries and relieve the primary instance.
At the store: the sales reports run on a replica, not on the database that receives orders.
Updates and patches
The provider applies engine patches in maintenance windows you can choose.
At the store: the database gets the security fix without Isaac waking up at dawn.
Don't mix them up
Managed × on the VM
Managed database
“Do I want the provider to handle installation, patches, backups and failover?”
- Example
- The store's orders database, with automatic backups and a standby in another zone.
- Focus
- Less administration. You control the data, the schema and access.
Database on a VM
“Do I need full control of the operating system, or a version the service doesn't offer?”
- Example
- A database with an extension the managed service doesn't support.
- Focus
- Full control, and all the administration is yours.
Protection
Backup and restore
A backup answers the question: how much do I lose if something goes wrong? Two measures help answer it:
- RPO (Recovery Point Objective): how much data can be lost, measured in time. With one backup a day, the RPO is up to 24 hours.
- RTO (Recovery Time Objective): how long the system can stay down before it's back.
Many managed databases also keep the transaction log. With it, the restore is point-in-time: you go back to the second before the mistake, not just to the last daily copy. This feature brings the RPO close to zero.
Availability
Replicas
There are two kinds of replica, and the difference matters:
- Read replica: receives a copy of the data and serves queries. It helps reduce the load, and it doesn't always take over on its own when the primary goes down.
- Standby replica: exists for high availability. If the primary fails, it takes over, usually automatically.
In practice
Choosing the setup
| Situation | What to do |
|---|---|
| Online orders, critical data, small team | Managed database, with a standby in another zone |
| Heavy reports that slow down the orders | A read replica for the reports |
| Data that must stay in another region | Backup copied to another region |
| A database version the managed service doesn't offer | Database on a VM, taking on the administration |
Key terms
Six terms to remember
Tap a card to see the definition.
Quick test
Three questions, exam style
01
The team wants the provider to make automatic backups and switch servers by itself if one fails. Which option meets that?
02
An accidental DELETE removed important data. Which feature helps you go back to the state before the mistake?
03
A team needs a database version the managed service doesn't offer. Which path makes sense?
A database is where the store keeps what matters most: orders, customers and payments. The question that decides everything is the usual one: who does what. With a managed database, the provider handles the machine, the backups and the server switch. You remain responsible for the data and for who accesses it. The next step is to understand what runs on the system that hosts this database: virtual memory and file systems.