{"course":{"slug":"building-apps-with-ai","title":"Building Apps with AI","url":"https://addaly.com/learn/building-apps-with-ai","level":"beginner","track":"build"},"lesson":{"slug":"backups-and-restore-once","title":"Backups, and restoring one before you need to","url":"https://addaly.com/learn/building-apps-with-ai/backups-and-restore-once","position":55,"of":87,"minutes":7,"answer":"A backup you have never restored is a hope, and the free tier's restore window is measured in hours, so make a dump you own and restore it into a fresh database once.","keyIdea":"A backup you have never restored is a hope, and the free tier's restore window is measured in hours, so make a dump you own and restore it into a fresh database once."},"body":{"format":"markdown","text":"## What the free tier actually keeps\n\nHosted databases advertise backups, and the word covers very different things. Neon's free plan keeps a history you can restore to for a short window: a day at the time of writing, longer on paid plans. Supabase's free plan has no automatic backups at all; daily backups start on the paid tier. Turso and D1 have their own windows. Read the current page for your provider, because these change, and write the number down somewhere you will see it.\n\nThe number is the point. A window of 24 hours means that a deletion noticed 25 hours later is permanent. Deletions are noticed late (a user writes in a week after their data vanished) and an app whose only backup is the provider's window is protected against the mistakes you catch quickly and nothing else.\n\n## A dump you own\n\n`pg_dump` writes the whole database to a file:\n\n```bash\npg_dump \"$DATABASE_URL\" --format=custom --file=backup-2026-09-06.dump\n```\n\nThat file is yours. It can sit in a bucket in object storage (the same R2 or B2 account as the uploads) or on a disk you control. A small app's database is a few megabytes; the storage cost is nothing.\n\nAutomate it. A scheduled job on the host, a GitHub Action on a cron schedule, or a one-line script on any machine that is on daily. Once a day, kept for thirty days, is the shape that catches the week-late report. The script is twenty lines and the agent will write it correctly if you tell it where the dump should go.\n\nFor SQLite, the database is a file, and the backup is a copy of the file, but copy it with the `.backup` command or the `VACUUM INTO` statement rather than `cp`, because a copy taken mid-write can be corrupt.\n\n## Restore it once, now\n\nA backup that has never been restored is not known to work. The dump might be of the wrong database, missing a schema, taken with a version of the tool the restore does not accept, or empty because the connection string in the script pointed at development. All of these have happened to people who had a green tick on their backup job.\n\nSo, once, and again after any change to the script:\n\n```bash\ncreatedb restore_test\npg_restore --dbname=restore_test backup-2026-09-06.dump\npsql restore_test -c \"SELECT count(*) FROM bookings;\"\n```\n\nCompare the count with production. Open the app against the restored database if you can. Then drop it. Now you know two things: that the backup is real, and how long a restore takes, which is the number you will want at the worst possible moment.\n\n## What the database backup does not contain\n\nUploaded files live in the bucket, not the database, so the dump does not have them. Bucket providers offer versioning (keeping old copies of overwritten or deleted objects) which is the equivalent and is usually a checkbox.\n\nEnvironment variables live on the host. Keep a copy of the names, and of the values, somewhere safe: a password manager entry is the usual answer. Losing the host account with no record of the variables means reconstructing every key from every provider.\n\nAnd the host's own configuration (domains, build settings, the production branch) is nowhere but the host. A screenshot of the settings page is a crude and effective backup.\n\n## The scenario this is for\n\nThe data block keeps returning to one event: an agent, or you, running a destructive command against production. A reset, a migration that dropped a column, a `DELETE` with no `WHERE`. The rules file, the separate credentials and the forbidden list make it rare. The backup is what makes it survivable, and it is only that if the restore has been rehearsed.\n\nSet a reminder for the first of each month: restore last night's dump into a scratch database and count one table. Five minutes, and the difference between a bad morning and the end of the project."},"check":{"question":"A hosted Postgres free tier offers point-in-time restore for the last 24 hours. An agent ran a reset against production on Friday evening and nobody noticed until Monday. What can be recovered?","options":[{"text":"Everything, because the reset is itself logged by the database and the provider can replay the log backwards to undo it.","correct":false,"tempting":"A reset is a set of ordinary drop and create statements; the database does not keep an undo for them beyond the restore window."},{"text":"Everything, if you contact the provider's support and explain.","correct":false,"tempting":"Support can act only within what the plan retains; a closed window is closed for them too."},{"text":"The schema but not the rows, since migrations are in git.","correct":false,"tempting":"True that the schema can be rebuilt from migrations, but that is not recovery: the data is what was lost and git never held it."},{"text":"Nothing from the host, because the window has closed; only a dump you made yourself, if one exists.","correct":true}]},"prev":{"slug":"files-and-uploads","title":"Files and uploads, and where they must not go","url":"https://addaly.com/learn/building-apps-with-ai/files-and-uploads"},"next":{"slug":"the-database-and-the-agent","title":"The database and the agent","url":"https://addaly.com/learn/building-apps-with-ai/the-database-and-the-agent"},"authorship":"Founder and series editor: Dr Mustafa Mun. Written by AI models to a written standard. Exam keys checked blind by a second model: 3,665 of 3,666 agreed. Academic review invited.","review":{"status":"Draft, open for academic review"},"cite":"Addaly (2026) 'Backups, and restoring one before you need to', in Building Apps with AI. Edited by M. Mun. Addaly. Available at: https://addaly.com/learn/building-apps-with-ai/backups-and-restore-once (Accessed: 13 October 2026).","licence":{"name":"CC BY-NC-SA 4.0","url":"https://creativecommons.org/licenses/by-nc-sa/4.0/"},"reader":{"schema":1,"title":"Backups, and restoring one before you need to","courseTitle":"Building Apps with AI","module":{"index":6,"slug":"the-parts-that-persist","title":"The parts that persist: data, accounts and files"},"answer":null,"keyIdea":"A backup you have never restored is a hope, and the free tier’s restore window is measured in hours, so make a dump you own and restore it into a fresh database once.","hero":{"kind":"plate","image":null,"alt":null,"caption":null},"blocks":[{"t":"h2","id":"what-the-free-tier-actually-keeps","runs":[{"text":"What the free tier actually keeps"}]},{"t":"p","runs":[{"text":"Hosted databases advertise backups, and the word covers very different things. Neon’s free plan keeps a history you can restore to for a short window: a day at the time of writing, longer on paid plans. Supabase’s free plan has no automatic backups at all; daily backups start on the paid tier. Turso and D1 have their own windows. Read the current page for your provider, because these change, and write the number down somewhere you will see it."}]},{"t":"p","runs":[{"text":"The number is the point. A window of 24 hours means that a deletion noticed 25 hours later is permanent. Deletions are noticed late (a user writes in a week after their data vanished) and an app whose only backup is the provider’s window is protected against the mistakes you catch quickly and nothing else."}]},{"t":"h2","id":"a-dump-you-own","runs":[{"text":"A dump you own"}]},{"t":"p","runs":[{"text":"pg_dump","code":true},{"text":" writes the whole database to a file:"}]},{"t":"code","lang":"bash","label":"Bash","text":"pg_dump \"$DATABASE_URL\" --format=custom --file=backup-2026-09-06.dump"},{"t":"p","runs":[{"text":"That file is yours. It can sit in a bucket in object storage (the same R2 or B2 account as the uploads) or on a disk you control. A small app’s database is a few megabytes; the storage cost is nothing."}]},{"t":"p","runs":[{"text":"Automate it. A scheduled job on the host, a GitHub Action on a cron schedule, or a one-line script on any machine that is on daily. Once a day, kept for thirty days, is the shape that catches the week-late report. The script is twenty lines and the agent will write it correctly if you tell it where the dump should go."}]},{"t":"p","runs":[{"text":"For SQLite, the database is a file, and the backup is a copy of the file, but copy it with the "},{"text":".backup","code":true},{"text":" command or the "},{"text":"VACUUM INTO","code":true},{"text":" statement rather than "},{"text":"cp","code":true},{"text":", because a copy taken mid-write can be corrupt."}]},{"t":"h2","id":"restore-it-once-now","runs":[{"text":"Restore it once, now"}]},{"t":"p","runs":[{"text":"A backup that has never been restored is not known to work. The dump might be of the wrong database, missing a schema, taken with a version of the tool the restore does not accept, or empty because the connection string in the script pointed at development. All of these have happened to people who had a green tick on their backup job."}]},{"t":"p","runs":[{"text":"So, once, and again after any change to the script:"}]},{"t":"code","lang":"bash","label":"Bash","text":"createdb restore_test\npg_restore --dbname=restore_test backup-2026-09-06.dump\npsql restore_test -c \"SELECT count(*) FROM bookings;\""},{"t":"p","runs":[{"text":"Compare the count with production. Open the app against the restored database if you can. Then drop it. Now you know two things: that the backup is real, and how long a restore takes, which is the number you will want at the worst possible moment."}]},{"t":"h2","id":"what-the-database-backup-does-not-contain","runs":[{"text":"What the database backup does not contain"}]},{"t":"p","runs":[{"text":"Uploaded files live in the bucket, not the database, so the dump does not have them. Bucket providers offer versioning (keeping old copies of overwritten or deleted objects) which is the equivalent and is usually a checkbox."}]},{"t":"p","runs":[{"text":"Environment variables live on the host. Keep a copy of the names, and of the values, somewhere safe: a password manager entry is the usual answer. Losing the host account with no record of the variables means reconstructing every key from every provider."}]},{"t":"p","runs":[{"text":"And the host’s own configuration (domains, build settings, the production branch) is nowhere but the host. A screenshot of the settings page is a crude and effective backup."}]},{"t":"h2","id":"the-scenario-this-is-for","runs":[{"text":"The scenario this is for"}]},{"t":"p","runs":[{"text":"The data block keeps returning to one event: an agent, or you, running a destructive command against production. A reset, a migration that dropped a column, a "},{"text":"DELETE","code":true},{"text":" with no "},{"text":"WHERE","code":true},{"text":". The rules file, the separate credentials and the forbidden list make it rare. The backup is what makes it survivable, and it is only that if the restore has been rehearsed."}]},{"t":"p","runs":[{"text":"Set a reminder for the first of each month: restore last night’s dump into a scratch database and count one table. Five minutes, and the difference between a bad morning and the end of the project."}]}],"check":{"question":[{"text":"A hosted Postgres free tier offers point-in-time restore for the last 24 hours. An agent ran a reset against production on Friday evening and nobody noticed until Monday. What can be recovered?"}],"options":[{"text":[{"text":"Everything, because the reset is itself logged by the database and the provider can replay the log backwards to undo it."}],"correct":false,"tempting":[{"text":"A reset is a set of ordinary drop and create statements; the database does not keep an undo for them beyond the restore window."}]},{"text":[{"text":"Everything, if you contact the provider’s support and explain."}],"correct":false,"tempting":[{"text":"Support can act only within what the plan retains; a closed window is closed for them too."}]},{"text":[{"text":"The schema but not the rows, since migrations are in git."}],"correct":false,"tempting":[{"text":"True that the schema can be rebuilt from migrations, but that is not recovery: the data is what was lost and git never held it."}]},{"text":[{"text":"Nothing from the host, because the window has closed; only a dump you made yourself, if one exists."}],"correct":true,"tempting":null}],"order":[1,2,3,0],"why":null},"prev":{"slug":"files-and-uploads","title":"Files and uploads, and where they must not go","minutes":8},"next":{"slug":"the-database-and-the-agent","title":"The database and the agent","minutes":8}}}