Managed Databases in DigitalOcean are actually just PostgreSQL or MySQL instances running on dedicated VMs, with DigitalOcean managing the underlying infrastructure, patching, and backups.
Let’s see a managed PostgreSQL database in action, specifically how a point-in-time restore works.
First, we need a database to restore. I’ll create a simple PostgreSQL cluster in DigitalOcean.
doctl databases create my-pg-db \
--engine pg \
--version 14 \
--size s-2vcpu-4gb \
--region nyc3
Once it’s created, I’ll connect to it and insert some data.
PGPASSWORD='my-super-secret-password' psql "postgresql://doadmin:my-super-secret-password@my-pg-db.do-ams3.ondigitalocean.com:25060/defaultdb?sslmode=require" -c "CREATE TABLE users (id SERIAL PRIMARY KEY, name VARCHAR(100));"
PGPASSWORD='my-super-secret-password' psql "postgresql://doadmin:my-super-secret-password@my-pg-db.do-ams3.ondigitalocean.com:25060/defaultdb?sslmode=require" -c "INSERT INTO users (name) VALUES ('Alice'), ('Bob');"
Now, let’s simulate a failure and perform a point-in-time restore. We’ll restore to a point just before we inserted 'Bob'.
doctl databases restore my-pg-db \
--restore-uuid $(doctl databases list --format ID,NAME | grep my-pg-db | awk '{print $1}') \
--restore-time "2023-10-27T10:30:00Z" \
--name my-pg-db-restored
DigitalOcean’s Managed Databases handle the heavy lifting of creating snapshots and managing the WAL (Write-Ahead Logging) for point-in-time recovery. When you request a restore, DigitalOcean uses the most recent full backup before your specified restore time, and then applies the WAL segments up to that exact timestamp. This ensures you get your data exactly as it was at that moment. The restore-uuid is actually the ID of the source database cluster you’re restoring from.
You can control the cluster size, engine version, and region for your restored database independently of the original.
The most surprising thing about these managed backups is that while they are automatic and point-in-time capable, they aren’t designed for long-term archival. DigitalOcean retains daily backups for 7 days and transaction logs for 7 days, enabling point-in-time restores within that 7-day window. If you need to keep data for longer periods or meet specific compliance requirements, you’ll need to implement your own supplemental backup strategy, likely involving exporting data or using external backup tools that connect to your managed database.
After restoring, you’ll need to update your application’s connection strings to point to the new my-pg-db-restored database.