How to Get a Plain Text Postgres Database Dump on Heroku

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

You could just make your own pg_dump directly from your Heroku database.

First, get your postgres string using heroku config:get DATABASE_URL.

Look for the Heroku Postgres url (example: HEROKU_POSTGRESQL_RED_URL: postgres://user3123:passkja83kd8@ec2-117-21-174-214.compute-1.amazonaws.com:6212/db982398), which format is postgres://<username>:<password>@<host_name>:<port>/<dbname>.

Next, run this on your command line:

pg_dump --host=<host_name> --port=<port> --username=<username> --password --dbname=<dbname> > output.sql

The terminal will ask for your password then run it and dump it into output.sql.

Then import it:

psql -d my_local_database -f output.sql

Postgres on Heroku and dumping single table to dump file

You can dump a single table of data like so:

$ pg_dump --no-acl --no-owner -h [host ip].compute-1.amazonaws.com -U [user name] -t [table name] --data-only [database name] > table.dump

You can get all of the values needed with this:

$ heroku pg:credentials:url [DATABASE] -a [app_name]
Connection info string:
   "dbname=[database name] host=[host ip].compute-1.amazonaws.com port=5432 user=[user name] password=[password] sslmode=require"
Connection URL:
    postgres://[username]:[password]@[host ip].compute-1.amazonaws.com:5432/[database name]

This will prompt you for your password. Enter it, and you should then proceed to get a file table.dump on your local drive.

You probably want to truncate the table on staging:

$ echo "truncate [table];" | heroku pg:psql [DATABASE] -a staging_app

With that file, you can use psql with the Connection URL:output of a new call to pg:credentials for the staging app and restore just that table.

$ psql "[pasted postgres:// from pg:credentials:url of staging app]" < table.dump
SET
SET
...
...
...
...
$ 

Dumping Database to Heroku app

I added the following modifiers to the dump command as suggested here,

pg_dump -Fc --no-acl --no-owner -h localhost -U myuser mydb > mydb.dump 

After a few minutes the data did show up.

Heroku: Dump Postgres database into local SQLite3 database for Rails project

Please read through

https://devcenter.heroku.com/articles/heroku-postgres-import-export

and follow those to export.

Though the above link can be used to export alone, it does not have solution for the entire process. So you can look on the similar question and its solution

Copy a heroku postgres db to a local sqlite db

How to import a big database from Heroku to local mysql or sqlite3?

Set up PostgreSQL locally, use PG backups to copy the data from Heroku to your local machine, then pg_restore to import it into your new local PostgreSQL. Then you can copy it from PostgreSQL to MySQL or SQLite locally without having to worry about timeouts. Or, since you'd have a functional PostgreSQL installation after that, just start developing on top of PostgreSQL so that your development stack better matches your deployment stack; developing and deploying on the same database is a good idea.

You're probably getting binary dumps (i.e. pg_dump -Fc) from Heroku, that would explain why the dump looks like some sort of UTF-16 nonsense.

You can use the pgbackups addon to export the database dump

$ heroku addons:add pgbackups # To install the addon
$ curl -o latest.dump `heroku pgbackups:url` # To download a dump

---

Related Topics