How can I get a plain text postgres database dump on Heroku?

Extracting data from Heroku's PostgreSQL

Tapping into your database is like tapping into a gold mine. Follow the steps below to extract all the value in your Heroku PostgreSQL database:

Get the configuration details

Before beginning, check that Heroku CLI is installed and you have logged in:

heroku login


Get the Heroku PostgreSQL database URL using:

heroku config:get DATABASE_URL -a your-app-name


This command unlocks the pathway to your data, returning the URL in the format: postgres://<username>:<password>@<host_name>:<port>/<dbname>.

Dump the Database using pg_dump

Use pg_dump to mine all the data from your database:

pg_dump -F p -c --no-acl --no-owner -h <host_name> -U <username> -d <dbname> -f mydb_dump.sql  # ssh into your data mine


This command will ask for the password. Handle with care!

Bring it Home with psql

Now that we've got our precious mydb_dump.sql, it's time to bring it back to local home base using psql:

psql -d my_local_database -f mydb_dump.sql  # secure the payload


Tip: -d denotes your local database and -f tells it to read from a file.

Streamlined Approach with Heroku's built-in commands

Alternatively, data retrieval can be more streamlined directly in the Heroku environment:

heroku pg:backups:capture
heroku pg:backups:download
pg_restore -f mydb.sql latest.dump  # easy breezy


Remember to go "hexadecimal byte sequence" when you encounter 'literal carriage return found in data' error.

Pull in the Data with heroku pg:pull

Use heroku pg:pull to clone your Heroku database straight into your local environment, no export required!

heroku pg:pull DATABASE_URL <DEV_DB_NAME> -a your-app-name  # data clone, engage!


<DEV_DB_NAME> is the name of your local database. No fuss, straight to the point.

Bulletproof your database operations

Here are a few strategies you can adopt to make your data operations foolproof:

Tweaking pg_dump for Easier Restoration

Adding -xO to pg_dump makes it way easier to restore the data into any database:

pg_dump -xO -F p -h <host_name> -U <username> -d <dbname> > mydb_dump.sql  # no strings attached, in both sense ;)


Handling Custom Formats from Heroku

Sometimes Heroku provides backup data in a custom format. For these instances, you have pg_restore at your disposal:

pg_restore --verbose --clean --no-acl --no-owner -f mydb.sql latest.dump  # the Swiss army knife


Catching Errors before they Escalate

Always read error messages closely. Hunt down errors such as 'literal carriage return found in data' and others may improve the copy operation. Investigate further on PostgreSQL documentation.

Never forget to specify -a your-app-name and make sure your commands reference the correct local environment paths.