Migrating postgres databases from ElephantSQL to Neon

Continuing my series of "if I push enough buttons I can get postgres to work for me" I am going to record how I migrated from ElephantSQL to Neon. This is one of my personal documentation posts - I write these for my own reference for when I need to do something similar in future but all useful information has dropped out of my head so I don't have to distil something simple from proper documentation again. They are sometimes useful to someone doing the same thing (I'm actually surprised how often I do send these links to people) but since more folk are reading my blog from LinkedIn these days this is fair warning.

The setup

I was migrating from ElephantSQL to Neon as the former was shutting down. I wish Neon all the best, but the way things are at the moment I guess it's only a matter of time before I have to do it again. Migrating a simple postgres database is straightforward, but if (like me) you don't do it often it is nice to have the process written out.

This is for my own experimental applications, so I'm dealing with small, single-node databases and I'm not worried about downtime.

Recover the data

Getting the data out of the source database is straightforward. Simply log into the control panel, copy the connection string and use pg_dump for a full download:

pg_dump -Fc -v -d <source_database_connection_string> -f <dump_file_name>

-Fc makes the output format suitable for pg_restore. -v is verbose mode, showing you all the things going wrong...

Upload to the new database

Initially, I struggled a bit with Neon. I created the database and user in the web interface, but could not find a way to associate the two so consequently pg_restore failed with permissions errors. The simple way around this was to create the database via the Neon CLI, recorded here as a bit of a gotcha.

neon roles create --name new_user
neon databases create --name new_database --owner-name new_user

And for completeness, these are the commands which list the databases and users.

neon roles list
neon databases list

Once the database is created properly, the database can be restored using the pg_restore command.

pg_restore -v -d <neon_database_connection_string> <dump_file_name>

Repointing the database

So far so simple. Now to reconfigure the application - this should be a case of updating an environment variable. For a Rails app, that is likely DATABASE_URL. Simply edit the environment variable to the new database connection string, restart the app and this is done.

Again, this is for a very simple application - one Rails node, small database, no particular need for zero downtime.

Hopefully this will be useful to someone out there even if it's just me in the future. Hello, future me. What are you up to these days?