Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to export table from heroku production database locally to excel from console using Ruby?

I know how to export table in Rails to formated Excel file: http://railscasts.com/episodes/362-exporting-csv-and-excel

But how to do that from console.

like image 429
tomekfranek Avatar asked Mar 22 '13 19:03

tomekfranek


2 Answers

Connect to your Heroku database by using

heroku pg:psql

Then run the sql command to get the csv file e.g.

\copy (SELECT * FROM users) TO dump.csv CSV DELIMITER ','

Use \q to exit.

After executing the commands dump.csv would have been created in your local environment

like image 59
algometrix Avatar answered Oct 01 '22 18:10

algometrix


There's a couple of option here.

Firstly, you could wrap up some ruby code into a rake task that creates the Excel spreadsheet and then spits it onto S3 for you to pick up later. This would be run via the CLI:

heroku run rake export_data

OR

You could spin up a Postgres console heroku pg:psql and export your data to a CSV locally directly with a query such as those discussed here: http://ru05team.blogspot.co.uk/2011/03/export-postgresql-into-csv.html

Note: pg:psql gives you a full interactive PSQL session with your production database as if it were local. Be careful when messing direct with a production database.

like image 26
Neil Middleton Avatar answered Oct 01 '22 18:10

Neil Middleton