How to migrate from Heroku Postgres to CockroachDB serverless

How to migrate from Heroku Postgres to CockroachDB serverless

In case you hadn’t already heard, Heroku is shutting down its free plans.

The change has left many developers scrambling to replace what Heroku offered with other free services. And while CockroachDB doesn’t replace everything Heroku’s free tier offered, developers looking to replace the free cloud Postgres database that Heroku offered can already access an excellent replacement in CockroachDB serverless, a forever-free cloud database that actually offers some major advantages over Heroku Postgres.

Why move to CockroachDB?

It has a generous free tier

The free tier of Heroku Postgres, when it existed, offered a free cloud Postgres instance, but it came with quite a few limitations. Among them: a maximum storage limit of 1 GB and a maximum row count of 10,000.

CockroachDB serverless’s free tier offers significantly more, allowing for 5 GB of free storage, with no limitation on rows. Free users are guaranteed a minimum performance of 100 Request Units per second, with an additional 10 million RUs each month in “burst capacity” – enough to run many smaller apps in production for free.

CockroachDB serverless also offers more flexible paid plans for when that level of performance becomes necessary. Where Herkou only offered predefined subscription tiers (only one of which cost less than $50 per month), CockroachDB serverless allows users to set a custom monthly spend limit for their cluster and pay up to (but never more than) that amount each months, depending on their usage.

It’s easy to scale

Because it’s a cloud-native distributed database, CockroachDB was built for automated scale in ways that Postgres never was. While Heroku Postgres scaling relies on active-passive configurations, CockroachDB is active-active, and serverless databases scale up and down automatically based on usage and on your preset spending limit.

For more on how CockroachDB compares to Postgres, check out this blog post which digs into some of the details.

How to export a database from Heroku Postgres using pg_dump

Thankfully, exporting a Postgres database from Heroku and importing it into CockroachDB serverless isn’t too complicated. There are a number of ways it can be done, but in this post we’ll focus on an approach using pg_dump and psql, with which many Postgres users may already be familiar.

Note that Heroku doesn’t recommend pg_dump for exports of databases larger than 20 GB. If you were a Heroku free user, this won’t matter – Heroku free accounts were limited to 1 GB of database storage. But if you were a paying user with a larger database, you should follow these steps instead of what’s below.

Step 1: Create a backup of the Heroku Postgres database

The first step is to create a backup of the database by running the command below, replacing example-app with the name of the app whose database we’re planning to export.

heroku pg:backups:capture --app example-app

Step 2: Download the backup

Once we’ve created the backup, we can download it with the download command:

heroku pg:backups:download --app example-app

This will download a .dump file that we’ll import to set up our database on CockroachDB serveless. We can use pg_restore to quickly convert this to a .sql file (replacing the paths and filenames below with the desired and actual paths and filenames for the .sql and .dump files, respectively):

pg_restore -f /path/file.sql /path/file.dump 

How to import a database to CockroachDB serverless using psql

Step 3: Create a CockroachDB cloud account

Note: If you already have a CockroachDB Cloud account and are familiar with the process of spinning up and accessing a free serverless cluster, you can jump directly to step 6.

To start, we’ll need to create a free CockroachDB Cloud account. There are several single-sign-on options (Google, Github, and Microsoft) to choose from, and we can also create an account using an email address.

Step 4: Spin up a free serverless cluster

Once the account has been created, log in and navigate to the clusters page. We’ll click on the “Create Cluster” button and then select our preferred option, including:

  • Serverless or Dedicated – For a free account, we will need to select serverless.
  • Cloud provider – AWS or GCP, both are good options
  • Regions – We recommend selecting the available region that is geographically closest
  • Spend limit – To create a free account, we’ll keep this set to $0. (Note that if you set a higher spend limit, you still get 5 GB of storage and 250 million request units (compute) for free, you’ll only be charged for what you use above the free allowance in a given month.)
  • Cluster name – a default name will be generated, but we can customize it if desired

Once we’ve selected our options, we’ll hit “Create your free cluster” and wait 5-10 seconds for the cluster to be created.

Step 5: Get the connection string

Once the cluster has been created, a Connection window will appear that will prompt us to create a SQL username, generate a password for that user – save this somewhere safe, we’ll need it shortly – and then provide a variety of options for getting connected to the database.

For the purposes of this tutorial, all we’ll need is the general connection string, which can be found in the connection window under “Select language/option > general connection string.”

It’ll look something like this (but the username, password, numbers, etc. will be specific to the cluster we just created):

postgresql://username:password@free-tier11.gcp-us-east1.cockroachlabs.cloud:00000/defaultdb?sslmode=verify-full&options=--cluster%3Dcluster-name-0000

All we need to do is copy that string somewhere safe, and then we can proceed to the next step.

Step 6: Import the file to CockroachDB using psql

While there are a number of ways to get data into CockroachDB, we’ll focus on one that uses a tool you may already be familiar with: psql.

(If you don’t already have psql installed, you can run the command brew install libpq and then brew link brew link --force libpq to get it set up and ready on Mac. Installing Postgres on any system will also install psql, so you can follow the instructions for downloading Postgres on any system and you should be good to go.)

Once psql is installed, we can run a version of the following command from the command line:

psql 'your-connection-string-here' -f /path/file.sql

For this to work, we’ll need to replace your-connection-string-here with our connection string (inside single quotes) and /path/file.sql with whatever the correct file path is for the .sql file we downloaded from Heroku in step 2. Here’s an example of what the full command might look like:

psql 'postgresql://username:password@free-tier11.gcp-us-east1.cockroachlabs.cloud:26257/defaultdb?sslmode=verify-full&options=--cluster%3Dcluster-name-0000' -f /Users/username/Downloads/file.sql

Running this command will connect to our CockroachDB serverless database and then execute the contents of the .sql file, importing our tables and data into our new CockroachDB database.

Troubleshooting

Depending on your database’s specific schema, you may encounter some errors while importing that require adjusting the schema. For example, in writing this tutorial we encountered an error related to encoding in the database we exported from our Heroku app. Removing a single SET client_encoding = 'LATIN1'; line from the top of our .sql file allowed us to avoid the error and import the data successfully.

We can’t provide step-by-step guidance for how to adjust every possible schema, but if you do get an error, the error message you get from CockroachDB should point you in the right direction in terms of what needs to be changed in the .sql file.

We also have a community Slack that’s full of people ready to help, so if you encounter something you can’t figure out, jump onto the Slack and we’ll get you sorted!

Note, also, that we’ve got something coming soon that’ll make this process even simpler. Keep your eyes on our social media channels for updates about that!

A quick note on database names

The above instructions will import data into a CockroachDB serverless database called defaultdb. This is fine for testing and experimentation. For use in production, we may want to either create a different database to use prior to importing or rename defaultdb after importing.

If we’d like to create a different database first, we can use psql to connect to our serverless cluster:

psql ‘your-connection-string-here’

And then run the CREATE DATABASE command:

CREATE DATABASE testdb;

Then we can exit psql using \qand run through the import instructions above, replacing defaultdb in our connection string with the name of the database we just created, like so:

psql 'postgresql://username:password@free-tier11.gcp-us-east1.cockroachlabs.cloud:26257/testdb?sslmode=verify-full&options=--cluster%3Dcluster-name-0000' -f /Users/username/Downloads/file.sql

That’s it!

Congratulations! You’ve imported your Heroku Postgres database to CockroachDB serverless. Change the appropriate connection information within your application to point to your new CockroachDB cluster – quickstart instructions for various languages available here – and you’ll be up and running!

We’ve also got a free Introduction to Serverless Databases and CockroachDB serverless course that you can check out to learn more about how CockroachDB serverless works and (as of this writing) get some free swag in the process!

About the author

Charlie Custer

Charlie is a former teacher, tech journalist, and filmmaker who’s now combined those three professions into writing and making videos about databases and application development (and occasionally messing with NLP and Python to create weird things in his spare time).

github link linkedin link

Keep Reading

Database Migration Guide: How to Migrate from Oracle to CockroachDB

Why do Companies Migrate from Oracle to CockroachDB?

There is not one specific answer to this question, but rather a …

Read More
Why Switch from Cassandra to CockroachDB

Today’s guest author, Daniel Perano, is a Full-Stack Developer & Founder of MyWorld. He is an extraordinarily …

Read More
How to do a Postgres Database Migration – Tips from LaunchDarkly

What’s the best way to do a database migration?

It’s a challenging question. No single approach is going to be the best …

Read More
x
Developer Resources