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?

Homebrew postgres

> Hi me. Remember when you used to do all your development on a Linux machine? Wasn't that nice and easy?

> Well, no, but at least tools were consistent with the server.

> Ok, good point. But you're using postgres installed through homebrew now aren't you?

> Yes...?

> So how do you create a database now?

> Oh, well you since it runs as your user you can just do:
bundle exec rake db:create

With this in your config/database.yml:

default: &default
  adapter: postgresql
  encoding: unicode
  # For details on connection pooling, see Rails configuration guide
  # http://guides.rubyonrails.org/configuring.html#database-pooling
  pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>

development:
  <<: *default
  database: mydb_development

# Warning: The database defined as "test" will be erased and
# re-generated from your development database when you run "rake".
# Do not set this db to the same as development or production.
test:
  <<: *default
  database: mydb_test

> That's remarkably easy.

> Mac!

> Traitor...

Exporting a postgres database from Heroku and importing to local install

Continuing with my efforts to learn some basic, useful postgres admin commands it’s time to look at importing and exporting data. We are going to export a postgres database from Heroku and import it to a local postgres install for development.

I’m assuming the Heroku toolbelt and postgres are installed locally and myuser is already created. I’ve written some very basic pointers to (local dev) postgres installation and administration already.

We are going to export the database used in myapp and import it locally to mydatabase to be owned by myuser. Brace thyself.

Export from Heroku

This is the easy bit.

heroku pg:backups capture --app myapp
curl -o latest.dump `heroku pg:backups public-url --app myapp`

Boom.

Import to local

We are going to use the pg_restore command, but that needs to import as a postgres superuser. It will also prompt for a password, even if the user is set up for peer authentication (as per my last post) so we’re going to create an importer user with superuser powers. There is probably a better way to do this, but life is short…

Logged in to postgres as a superuser:

CREATE USER importer WITH PASSWORD 'mypassword';
ALTER USER importer WITH SUPERUSER;

We also need a target database:

CREATE DATABASE mydatabase;

Then to import the database (back on the command line):

pg_restore --verbose --clean --no-acl --no-owner -h localhost -U importer -d mydatabase latest.dump

This will throw some errors when the DROP commands in the Heroku export fail. This seems to be ok, but check nothing else has gone wrong. There is probably a way to have Heroku export the database without the drop statements to eliminate these messages.

Back in postgres as a superuser, switch to the new database and assign the correct ownership:

\c mydatabase
REASSIGN OWNED BY importer TO myuser;

Done!

Creating a database and user for local postgres development

So, yeah. I'm a postgres n00b. But I'm a n00b who wants to be able to create a non-superuser account and database, relate the two and also be able to remember how to do this again in two weeks time.

Connecting

As superuser via UNIX user authentication:
sudo -u postgres psql postgres

As superuser directly (-W forces password prompt):
psql -U postgres -W

As a user to a specific database:
psql database -U username -W

Basic commands

Show tables:
\d

List users:
\du

List databases:
\l

Quit:
\q

User management

CREATE USER username WITH PASSWORD 'password';
DROP USER username;

Database management

CREATE DATABASE database;
DROP DATABASE database;


Granting ownership and permissions

ALTER DATABASE database OWNER TO username;
GRANT ALL PRIVILEGES ON DATABASE database TO username;
Dumb settings for local dev.

Also, if you're getting problems connecting try replacing local peer with local md5 in /etc/postgresql/VERSION/main/pg_hba.conf.

Much of this came from this post. I'm planning on using pgAdmin3 as a database explorer when I want something quicker than the command line (on Ubuntu).