This topic describes how to create a backup of your Neon database using the Postgres pg_dump utility and how to restore a backup using pg_restore.

important

Avoid using pg_dump over a pooled connection string (see PgBouncer issues 452 & 976 for details). Use an unpooled connection string instead.

Prerequisites

  • Make sure pg_dump and pg_restore are installed. You can verify by running pg_dump -V.
  • We recommend using the latest versions of pg_dump and pg_restore, and ensuring that the client version matches your Neon project's Postgres version (14–17).

Install pg_dump and pg_restore

If you don't have the pg_dump and pg_restore utilities installed locally, you'll need to install them on your preferred platform.

  1. Install PostgreSQL using the official installer from https://www.postgresql.org/download/windows/.
  2. pg_dump and pg_restore are installed by default and can be found in the PostgreSQL bin directory.

Creating a backup with pg_dump

Following this procedure will create a database backup locally, where you're running the pg_dump command.

  1. Retrieve the connection string for your Neon database by navigating to your Neon Project Dashboard and clicking the Connect button to open the Connect to your database modal.

  2. Deselect the Connection pooling option. You need a direct connection string, not a pooled one.

    Your connection string should look something like this:

    postgresql://alex:AbC123dEf@ep-cool-darkness-123456.us-east-2.aws.neon.tech/neondb?sslmode=require

  3. Create a backup of your Neon database by running the following pg_dump command with your Neon database connection string.

    pg_dump -Fc -v -d "<neon_database_connection_string>" -f <dump_file_name>

    After adding your Neon database connection string and a dump file name, your command will look something like this:

    pg_dump -Fc -v -d "postgresql://alex:AbC123dEf@ep-cool-darkness-123456.us-east-2.aws.neon.tech/neondb?sslmode=require" -f mydatabase.bak

    The pg_dump command above includes these arguments:

    • -Fc: Sends the output to a custom-format archive suitable for input into pg_restore.
    • -v: Runs pg_dump in verbose mode, allowing you to monitor what happens during the dump operation.
    • -d: Specifies the connection string for your Neon database.
    • -f <dump_file_name>: The dump file name. It can be any name you choose (mydumpfile.bak, for example).

    For more command options, see Advanced pg_dump and pg_restore options.

Restoring a backup with pg_restore

This procedure shows how to restore a database using the pg_restore utility from a backup file created using pg_dump, as described above.

  1. Create a new Neon project.

  2. Create a database with the same name as the one you backed up. The pg_dump instructions above created a backup of a database named neondb. Your database name is likely different.

  3. Retrieve the connection string for your Neon database:

    Go to your Neon project and click the Connect button to open the Connect to your database modal.

    Deselect the Connection pooling option. You need a direct connection string, not a pooled one.

    Your connection string should look something like this:

    postgresql://alex:AbC123dEf@ep-dry-morning-a8vn5za2.us-east-2.aws.neon.tech/neondb?sslmode=require

  4. Restore your data to the target database in Neon with pg_restore.

    pg_restore -v -d "<neon_database_connection_string>" <dump_file_name>

    After adding your Neon database connection string and the dump file name, your command will look something like this:

    pg_restore -v -d "postgresql://alex:AbC123dEf@ep-dry-morning-a8vn5za2.us-east-2.aws.neon.tech/neondb?sslmode=require" mydatabase.bak

    The example above includes these arguments:

    • -v: Runs pg_restore in verbose mode, allowing you to monitor what happens during the restore operation.
    • -d: Specifies the Neon database to connect to. The value is a Neon database connection string. See Before you begin.
    • <dump_file_name> is the name of the dump file you created with pg_dump.

    For more command options, see Advanced pg_dump and pg_restore options.

pg_dump and pg_restore example

The following example shows how data is dumped from source database named neondb in one Neon project and restored to a neondb database in another Neon project using the commands described in the previous sections. (A database named neondb was created in the Neon project prior to running the restore operation.)

Before performing this procedure:

  • A new Neon project was created for the destination database, and a database with the same name as the source database was created (neondb)
  • Connection strings for the source and destination databases were collected:
    • source: postgresql://neondb_owner:npg_AbC123dEf@ep-dry-morning-a8vn5za2.us-east-2.aws.neon.tech/neondb?sslmode=require
    • destination: postgresql://neondb_owner:npg_AbC123dEf@ep-dry-morning-a8vn5za2.us-east-2.aws.neon.tech/neondb?sslmode=require
~$ cd mydump
~/mydump$ pg_dump -Fc -v -d "postgresql://alex:AbC123dEf@ep-cool-darkness-123456.us-east-2.aws.neon.tech/neondb?sslmode=require" -f mydatabase.bak

~/mydump$ ls
mydatabase.bak

~/mydump$ pg_restore -v -d "postgresql://alex:AbC123dEf@ep-dry-morning-a8vn5za2.us-east-2.aws.neon.tech/neondb?sslmode=require" mydatabase.bak