PostgreSQL on Heroku and the pgbackup add-on

In this post you will learn to configure and use PostreSQL in your Heroku app.

Moreover, you’ll learn how to perform database backups using the PG Backups add-on.

PostgreSQL Database – Heroku

On Heroku, you can check if your application already has a database provisioned and what plan it is (if it is free or payed) with:

$ heroku addons | grep POSTGRES

If you don’t have any database provisioned, the terminal won’t show any message. Otherwise, you will see something like heroku-postgresql:dev HEROKU_POSTGRESQL_RED. See the official post for more information.

To create a new free postgres database (dev option) attached to your Heroku application:

$ heroku addons:add heroku-postgresql:dev

Once Heroku Postgres has been added, there will be a HEROKU_POSTGRESQL_COLOR_URL setting available in the app configuration (in my case COLOR was YELLOW). This variable will contain the url used to access the new Postgresql service on Heroku. This can be confirmed using

$ heroku config | grep HEROKU_POSTGRESQL

you should see the corresponding url. Heroku recomends using the variable DATABASE_URL to store the location of your primary database. Hence, the config variable created before must be promoted to this new config variable:

$ heroku pg:promote HEROKU_POSTGRESQL_YELLOW_URL

To see all PostgreSQL databases provisioned by your application and its characteristics, use

$ heroku pg:info

To establish a psql session, the native PostgreSQL interactive terminal, with your remote database use

$ heroku pg:psql

More useful commands for your database

There are some useful commands that can be used to control your Postgres database.

Pull can be used to pull data from your remote Heroku Postgres database to your local machine database.

$ heroku pg:pull HEROKU_POSTGRESQL_YELLOW mylocaldb –app myapponheroku

This command takes the Heroku database of the app myapponheroku and saves its data to a local database in your machine, named mylocaldb. If the local database already exists, you will be asked to remove it before proceeding, which can be done via

$ drop mylocaldb

You can also pull the database with:

$ heroku pg:pull myapponheroku::YELLOW mylocaldb

And if you want to set the user and password for the local database you can use:

$ PGUSER=user PGPASSWORD=password heroku pg:pull myapponheroku::YELLOW mylocaldb

where the previous command is only one line of code.

Push is the inverse of pull, and takes data from your local database and push it to the database in Heroku:

$ heroku pg:push mylocaldb HEROKU_POSTGRESQL_YELLOW –app myapponheroku

If the remote database is not empty, you will be prompted to reset it with

$ heroku pg:reset HEROKU_POSTGRESQL_YELLOW

PG Backups add-on

A useful add-on to create and manage backups of your database is pgbackups. Note that all Heroku Postgres databases are already managed and backed up, but with this add-on you will be able to create your own backups.

Here we will use the default plan (plus), which only supports manual backups — the other plans also offer automatic and periodic backups. You can activate it with:

$ heroku addons:add pgbackups

Next, you can create a backup using

$ heroku pgbackups:capture

which will create a backup of your primary database — located at the DATABASE_URL config variable. You might specify the database with:

$ heroku pgbackups:capture HEROKU_POSTGRESQL_YELLOW

Moreover, each plan allows to retain a maximum number of backups. To capture a backup while automatically deleting the oldest manual backup you can use

$ heroku pgbackups:capture –expire

Another useful command is:

$ heroku pgbackups

which will list all your backups, with names like a001 or b001 — the first letter indicates if the backup was automatic, a,  or manual, b.

Finally, to delete a backup:

$ heroku pgbackups:destroy b001

Restore the database from a backup

If you need to restore the database on Heroku from a pgbackup you have to use:

$ heroku pgbackups:restore HEROKU_POSTGRESQL_YELLOW b001

where b001 is the id of your pgbackup. Note however that this operation is destructive, and you won’t be able to recover the data in your existing database.

Export the database to your local machine

Let’s focus on how you can export the existing database on Heroku to your local machine. First, you have to create the backup:

$ heroku pgbackups:capture

The command

$ heroku pgbackups:url

generates an url from which you can download the database. It is the latest backup generated, but if you want you can select another one with:

$ heroku pgbackups:url b001

The bash command

$ curl -o home.html https://marinamele.com

opens the url https://marinamele.com and stores its contents in the file home.html.

Then, if we want to download the latest backup and store it in the file latest.dump we can simply use:

$ curl -o latest.dump `heroku pgbackups:url`

Finally, you can dump this data into your local database with:

$ pg_restore –verbose –clean –no-acl –no-owner -h localhost -U myuser -d mylocaldb latest.dump

note that is only one line of code, in which:

–verbose indicates verbose mode
–clean indicates that it drops database objects before recreating
–no-acl prevents restoration of access privileges
–no-owner skips restoration of object ownership
-h localhost sets the database server host
-U myuser connects the user myuser to the database
-d mylocaldb sets the name of the database
and latest.dump is the file that stores the database backup. You can find more info here.

Upgrade your dev database into a basic database

First, we need to get a new basic database:

$ heroku addons:add heroku-postgresql:hobby-basic

This will create a new database with the newest version. If you want to select a particular version, you should use the version flag.

When you use the previous command, you will see the name of the new database, like HEROKU_POSTGRESQL_MAROON_URL. You will also see the price of this database ($9/mo).

Now, before transferring the data from our main database to this new database, we need to set our app in a maintenance mode. This is because we need to stop new processes or new entries to the database while we perform the database transfer.

First, we set the maintenance mode on — this will also scale dawn the web dynos:

$ heroku maintenance:on

And then, we need to scale down the workers (if any):

$ heroku ps:scale worker=0

Now, we can transfer the data to the new database,

$ heroku pgbackups:transfer HEROKU_POSTGRESQL_MAROON

were we use the new database name (note that we removed the end _URL).

Next, as we want this new database to be the primary database of our app, we need to promote it:

$ heroku pg:promote HEROKU_POSTGRESQL_MAROON 

Finally, we need to restore the workers (only if you have any before starting this process — workers are not free!) and turn off the maintenance mode:

$ heroku ps:scale worker=1
$ heroku maintenance:off

Now you can manage and control your PosgreSQL database on Heroku!

Hope it was useful! Share and comment!!