Branching — Point-in-time restore

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:

1. Navigate to the Branches page in the Neon Console.
2. Click Create branch to open the branch creation dialog.
3. Enter a name for the branch. You can call it recovery_branch, for example.
4. For the Parent branch, select the branch where the data loss occurred.
5. Select the Time option to create a branch with data as it existed at a specific date and time. For example, if the data loss occurred on July 11, 2023 at 10:01am, set the time to July 11, 2023, at 10:00am, just before the faulty query was run.
6. Leave the Create compute endpoint option selected. A compute endpoint is required to connect to the new branch.
7. Click Create Branch to create your point-in-time 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.

To connect to your branch:

1. In the Neon Console, select your project.

2. On the project Dashboard, under Connection Details, select your recovery_branch, the database, and the role you want to connect with.

3. Copy the connection string. A connection string includes your role name, password, compute endpoint hostname, and database name.

4. Connect with psql.

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_dump and 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:

1. In the Neon Console, select a project.
2. Select Branches to view the branches for the project.
3. Select your recovery_branch from the table.
4. 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.

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:

1. Remove the compute endpoint from the new branch

   For instructions, see Delete a compute endpoint.

2. 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.

Examples

• 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:

• Restore a Neon database

  A script to restore a Neon branch to a previous state while preserving the same endpoint

• Neon branch bisect demo

  Use Neon branching, the Neon API, and a bisect script to recover lost data

Need help?

Join the Neon community forum to ask questions or see what others are doing with Neon. Neon Pro Plan users can open a support ticket from the console. For more detail, see Getting Support.