Branching — Point-in-time restore
Restore your data to previous state using Neon's branching feature
Neon retains a history of changes for all branches in a Neon project, which allows you to create a branch that restores data to any time within the defined history retention period. You can use this capability to recover lost data, which is a form of Point-in-time restore (PITR).
The history retention period is configurable. The supported range is 0 to 7 days for Neon Free Tier users, and 0 to 30 days for Neon Pro Plan users. For configuration instructions, see Configure history retention.
This guide shows how to recover your data to a point in time before a data loss occurred using Neon's branching feature.
Create a point-in-time branch
Suppose that you have a table named
orders that was accidentally deleted by a faulty query. If you know the time you ran the faulty query or when the data loss was first discovered, you can create a point-in-time branch with the data as it existed before the data loss occurred.
To create a point-in-time branch:
In the Neon Console, select a project.
Click New Branch to open the branch creation dialog.
Enter a name for the branch.
Select a parent branch.
Under Include data up to, select the Specific date and time option, which creates a branch with data up to the specified date and time. For example, if the data loss occurred on Nov 26, 2023 at 5:01pm, select Nov 11, 2023, at 5:00pm, just before the faulty query was run.
The Specific date and time option does not include data changes that occured after the specified date and time, which means the branch contains data as it existed previously. You can only specify a date and time that falls within your history retention window, which is 7 days by default. See Configure history retention.
Click Create new branch to create your branch.
You are directed to the Branches page where you are shown the details for your new branch.
Connect to your branch
Connecting to your newly created branch requires connecting via the branch's compute endpoint. The following steps describe how to connect using
psql and a connection string obtained from the Neon Console.
You can also query the databases in a branch from the Neon SQL Editor. For instructions, see Query with Neon's SQL Editor.
To connect to your branch:
In the Neon Console, select your project.
On the project Dashboard, under Connection Details, select your
recovery_branch, the database, and the role you want to connect with.
Copy the connection string. A connection string includes your role name, password, compute endpoint hostname, and database name.
Verify the data
Check to see if the lost data is now present. For instance, if you lost an
orders table, you might run a query like this one to verify that the data is available in your newly created branch:
Change your primary branch
You now have a production branch with lost data and a recovery branch with the data in the desired state. You could dump data from the recovery branch and load it into the production branch using dump and restore utilities like
pg_restore, or you can make the recovery branch your new primary branch and use it for production.
To make the recovery branch your new primary:
- In the Neon Console, select a project.
- Select Branches to view the branches for the project.
- Select your
recovery_branchfrom the table.
- On the branch details page, select Set as Primary.
Making a branch your primary branch ensures that access to data on the branch is never interrupted, even when you exceed project limits. For more information, see Primary branch.
If your previous primary branch was your project's root branch (the initial branch created with your project), it cannot be deleted. Deleting a root branch is not yet supported. In the meantime, you can rename a root branch (perhaps adding an
DO_NOT_USE prefix to its name) and remove data from it to ensure that it's not used accidentally or consuming storage space.
Update your connections
To use your new primary branch with your applications, update your application connection details. To do so, replace your current connection details with the connection details for your new primary branch, which you retrieved earlier when connecting to your branch.
Alternatively, if you do not want change connection details, you can move the compute endpoint from your old primary branch to the new branch. See Reassign the compute endpoint for instructions.
Reassign the compute endpoint
To avoid changing connection details in your application, you can reassign the compute endpoint from your old primary branch to your new branch. If you followed the steps above, you created a branch with a compute endpoint. In this case, you have to:
Remove the compute endpoint from the new branch
For instructions, see Delete a compute endpoint.
Move the compute endpoint from the old primary branch to the new branch
This action is currently only supported in the Neon API. See Update a compute endpoint with the CLI for instructions.
- Using Neon branching for instant point-in-time restore. The blog post describes point-in-time restore and provides a script for creating a recovery branch, reassigning a compute endpoint, and setting the new branch as the primary.
- Time Travel with Serverless Postgres. This blog post (with video) describes a data recovery example that uses Neon's branching feature, the Neon API, and a bisect script to recover lost data.
The following GitHub repositories are available for these examples:
Last updated on