Recover lost data with branching
Use Neon branching to restore your data to past state
The following instructions demonstrate how to use Neon's branching feature to recover lost data.
Suppose that you tried to populate the
elements table with more data but you accidentally inserted several duplicate rows, which you discover when you run the following query:
You decide to write a query to remove the duplicate rows, but an error in your
DELETE statement deletes most of your data. There is an
= where there should have been a
> in the last line of your query:
Upon inspecting the data in your table, you find that your
DELETE query left you with only the duplicate rows:
With Neon, you can recover from data loss scenarios like this very easily.
Create a branch to recover lost data
You can use the Neon branching feature to recover lost data in seconds. The only requirement is that you know the point in time to recover to. Since you ran the
DELETE query from the Neon SQL Editor, you can check the History for the date and time you ran the problematic query.
Now that you know when the data loss occurred, you can restore your data to a point in time just before that by creating a database branch.
- Navigate to the Branches page in the Neon Console.
- Click New Branch to open the branch creation dialog.
- Enter a name for the branch.
- Select the parent branch. The data loss occurred on your project's primary branch (
main), so select that branch as the parent.
- Select the Time option to create a branch with data up to a specific date and time. You determined that the data loss occurred on March 20, 2023 at 8:58am, so you set it to 8:57am, just before you ran the
- Click Create Branch to create your branch. You should see a dialog similar to the following with the connection details for your new branch.
Verify that data was recovered
To verify that the new branch includes the lost data:
- Navigate to the SQL Editor.
- Select the new branch.
- Run the following query:
You should see the data as it existed before you ran the problematic
DELETE query. You can now run a revised
DELETE statement to remove the duplicate rows, which you will do in the next part of the tutorial.
What have you seen in this example?
To recover the data, you can use Neon's branching feature to create a branch with data from a past point in time. Neon keeps a 7-day history, by default, to enable point-in-time recovery.
Neon also supports creating branches from Head (the most up-to-date state of the database) or from an LSN (Log Sequence Number), which is a unique identifier assigned to each transaction in the database.
For another data recovery example using Neon's branching feature, refer to Time Travel with Serverless Postgres. This example uses a bisect script and the Neon API to create branches to recover to the last known good.