CODEWITHSUNDEEP
ruby-on-railsdatabasepostgresqlheroku
Nona
Nona

Reputation: 5462

Exporting individual tables from a Heroku production database

Our production database for our Rails 4 application is fairly large and it takes a while to download. I'd like to grab a couple of tables such as Users and Addresses without having to take the time to download the production database from Heroku (https://devcenter.heroku.com/articles/heroku-postgres-import-export) and then import the table data into a local and/or staging database for testing.

Is there a way:

  1. Do this quickly?
  2. Do this without impacting performance on the production database?

And if not, what is the best way to do this?

Upvotes: 2

Views: 973

Answers (1)

MZaragoza
MZaragoza

Reputation: 10111

the way that I would go to get a single table is that this:

$ 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 [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 of staging app]" < table.dump
SET
SET
...
...
...
...
$

Please look at https://stackoverflow.com/a/16151503/1380867 for more info and thanks to catsby for his great answer

Upvotes: 1

Related Questions