SimpleBackups

How to create a Neon database backup

Last updated on

Originally posted on

In this Guide, we will go through creating a backup of your Neon PostgreSQL database using SimpleBackups. We'll cover everything from obtaining your Neon database credentials to creating and restoring backups. By the end you will have scheduled pg_dump backups of your Neon database landing in storage you own, outside your Neon account.

Neon's own instant restore and snapshots cover recent mistakes well. This setup adds what they can't: a copy that survives a deleted project, retention beyond Neon's 35-day snapshot limit, and a file you can restore into any Postgres host. If you would rather script it yourself, backing up Neon with pg_dump shows the manual commands.

Prerequisites:

  • Register for a SimpleBackups for Neon account, this tutorial uses SimpleBackups as a backup provider.
  • An active Neon account with a database to backup
  • Network Access from SimpleBackups to your Neon database

Retrieving Neon Database Connection String

Step 1: Create a Backup User in Neon

  1. Log in to your Neon dashboard.
  2. Navigate to the database you wish to back up.
  3. Click on “Overview” then “Connect” or just on the Dashboard, refer to “Connection Details”
  4. Create a new user (or role) with RW or read-only access to the necessary database. This user will be used by SimpleBackups to connect to your database securely.
  5. Copy the connection string with connection pooling turned off (make sure you replace the **** by the actual password). Neon's pg_dump guide notes that pooled connections cause errors for dumps. Neon Connection String

Step 2: Whitelist SimpleBackups IP Addresses

Note: if your database is behind a firewall, follow the following steps.

  1. In the Neon dashboard, go to the "Project settings" then "IP Allow"
  2. Add the IP addresses provided by SimpleBackups to the whitelist. This allows SimpleBackups to connect to your database. Neon IP Whitelist

Creating a New Neon Database Backup on SimpleBackups

Step 1: Create a New Database Backup on SimpleBackups

  1. Navigate to the "Backups" section and click on "Create Backup." SimpleBackups Create Backup
  2. Select "Database Backup."
  3. You may use your own server to dump the backups, but we recommend the “serverless” option.
  4. Step 4: Configure Database Backup Settings
    • Choose PostgreSQL as the database type.
    • Click “Paste connection string instead”
    • Fill in the complete connection string for your Neon database (make sure the password is added as well and not the asterisks *****)
    • Click “Validate Connection” SimpleBackups Connection String`

Step 2: Schedule and Customize Your Backup

Select a name for your backup name, set a Schedule and a Storage to store your backup, then click on Create backup. SimpleBackups Backup Settings

Step 3: Save and Activate the Backup

  1. Review your backup configuration to ensure all details are correct & accurate.
  2. Save your backup configuration. SimpleBackups will automatically start the backup process according to your schedule.

You’ll be redirected to your backup job’s page, click on Run now to test this backup, once the backup runs successfully, you’ll be able to see a Backup success indicator. SimpleBackups Backup Success

Restoring your backup

Step 1: Download the Neon Backup File

First, locate the URL of your backup from the restore tab in your desired backup log dialog, and download it to your local system:

wget 'http://example.com/path/to/your-neon-backup.tar.gz' -O backup.tar.gz

Step 2: Extract the Backup File

Extract the downloaded file containing your Neon PostgreSQL backup:

tar -xzvf backup.tar.gz

Navigate into the extracted directory:

cd your_backup_folder

Step 3: Restore the Backup

Now that you have the backup file, you can restore it to your Neon database, or to any other Postgres host. A plain .sql dump goes through psql; a custom-format archive goes through pg_restore.

A plain SQL dump, using psql:

psql -U username -d dbname < database-dump.sql

A custom-format archive, using pg_restore:

pg_restore -U username -d dbname database-dump.dump

For the other restore paths on Neon, instant restore and snapshots, and for recovering a single table, see how to restore a Neon database.

What to do next

Run the backup once by hand, then restore that file into a scratch Neon branch and check the row counts of your largest tables. A backup you have restored once is one you can rely on. For the full picture of which risks this copy closes, and which ones Neon's native recovery already handles, read what Neon's native backup doesn't cover.


This article is part of the complete guide to Neon backup: instant restore, snapshots, pg_dump, and the copy that lives outside Neon.