Import data from Postgres with the @neondatabase/pg-import CLI
This topic describes migrating data from another Postgres database to Neon using the @neondatabase/pg-import
CLI. This tool is built on top of the Postgres pg_dump
and pg_restore
client utilities. It is intended to simplify data migrations for smaller, less complex databases compared to using the pg_dump
and pg_restore
client utilities directly, as described in Import data from Postgres.
important
The @neondatabase/pg-import
CLI is experimental. There may be bugs, and the API is subject to change.
The@neondatabase/pg-import
utility supports all Neon Postgres versions.
Before you begin
-
Make sure your Neon plan supports your database size. The Neon Free Plan offers 0.5 GiB of storage. For larger data sizes, upgrade to one of our paid plans: Launch, Scale, or Business. See Neon plans. If you are on a Neon paid plan, you can optimize for the migration by configuring a larger compute size or enabling autoscaling for additional CPU and RAM. See How to size your compute.
-
Retrieve the connection string for your source Postgres database.
-
Optionally, create a role in Neon to perform the restore operation. The role that performs the restore operation becomes the owner of restored database objects. For example, if you want role
sally
to own database objects, createrole
sally in Neon and perform the restore operation assally
. -
Create the target database in Neon. For example, if you are migrating a database named
pagila
, create a database namedpagila
in Neon. For instructions, see Create a database. -
Retrieve the connection string for your Neon database. You can find it in the Connection Details widget on the Neon Dashboard. If you created a role to perform the restore operation, make sure to select that role. Your connection string will look something like this:
postgresql://[user]:[password]@[neon_hostname]/[dbname]
Avoid using a pooled Neon connection string (see PgBouncer issues 452 & 976 for details). Use an unpooled connection string instead.
-
The
pg-import
utility usespg_dump
andpg_restore
. A generated dump file containing any of the following statements will produce a warning or error when data is restored to Neon:ALTER OWNER
statementsCREATE EVENT TRIGGER
statements- Any statement requiring the PostgreSQL superuser privilege or a privilege not held by the role running the migration.
ALTER OWNER
warnings can be ignored (see Database object ownership considerations).CREATE EVENT TRIGGER
or other statements requiring a privilege not held by the role performing the restore operation may require that you exclude those statements from the dump file.
Export data with @neondatabase/pg-import
Export your data from the source database with @neondatabase/pg-import
:
npx @neondatabase/pg-import --source <source_database_connection_string> --backup-file-path <dump_file_name>
The @neondatabase/pg-import
command above includes these arguments:
--source
: Specifies the source database name or connection string.--backup-file-path
: The dump file name. It can be any name you choose (./mydumpfile.bak
, for example).
For more command options, see all @neondatabase/pg-import options.
Restore data to Neon with @neondatabase/pg-import
Restore your data to the target database in Neon with @neondatabase/pg-import
.
npx @neondatabase/pg-import --destination <neon_database_connection_string> --backup-file-path <dump_file_name>
The @neondatabase/pg-import
command above includes these arguments:
--destination
: Specifies the destination database name or connection string.--backup-file-path
: The dump file name. It can be any name you choose (./mydumpfile.bak
, for example).
For more command options, see all @neondatabase/pg-import options.
@neondatabase/pg-import example
The following example shows how data from a pagila
source database is dumped and restored to a pagila
database in Neon using the commands described in the previous sections. (A database named pagila
was created in Neon before running the restore operation.)
~$ cd mydump
~/mydump$ npx @neondatabase/pg-import --source postgresql://[user]:[password]@[neon_hostname]/pagila --backup-file-path ./mydumpfile.bak
~/mydump$ ls
mydumpfile.bak
~/mydump$ npx @neondatabase/pg-import --destination postgresql://[user]:[password]@[neon_hostname]/pagila --backup-file-path ./mydumpfile.bak
Piped import with @neondatabase/pg-import
For small databases, the standard output of pg_dump
can be piped directly into a pg_restore
command to minimize migration downtime. @neondatabase/pg-import
makes it easier for you with a single command.
For example:
npx @neondatabase/pg-import --source <source_database_connection_string> --destination <neon-database-connection-string>
This method is not recommended for large databases, as it is susceptible to failures during lengthy migration operations.
Post-migration steps
After migrating your data, update your applications to connect to your new database in Neon. You will need the Neon database connection string that you used in the restore operation. If you run into any problems, see Connect from any application. After connecting your applications, test them thoroughly to ensure they function correctly with your new database.
References
Need help?
Join our Discord Server to ask questions or see what others are doing with Neon. Users on paid plans can open a support ticket from the console. For more detail, see Getting Support.