Search code examples
ruby-on-railsdatabasepostgresqlherokupartial

Download partial database from heroku

I have a ruby on rails application hosted on heroku using postgresql as its database. Since the database is getting pretty large, I was wondering if there's a way to download only a specific part of it off of heroku. For example, is it possible to download only one specific table, or download only rows with parent_id == x.

Solution

  • In addition to Steve's quite correct answer, you also have the option of connecting using psql to the DATABASE_URL and using \copy, e.g.

    $ psql "$(heroku config:get DATABASE_URL)"

mydb=> \copy mytable TO 'mytable.csv' WITH (FORMAT CSV, HEADER)

mydb=> \copy (SELECT col1, col2 FROM mytable2 WHERE ...) TO 'mytable2_partial.csv' WITH (FORMAT CSV, HEADER)

    You can extract whole tables, or the output of arbitrary queries (including joins etc). The table definition (DDL) is not exported this way, but can be dumped with pg_dump --schema-only -t ....