How to import heroku postgres locally from prod rails

1 Comment

Step 1: Setup Postgres locally and get working with your app.

Heroku db:pull only works if you have postgres setup locally on your machine.

Go here for some tips on how to do that.

Step 2: Find out your postgres database name.

Before you can run the db:pull command, you need to know what postgres calls your prod database.

Get the name of your app
$ heroku apps

Then get the name of your postgres instance
$ heroku addons –app yourappname | grep POSTGRES
heroku-postgresql:dev HEROKU_POSTGRESQL_ORANGE

Step 3: Export db from prod.

You are now ready to pull the db from prod. Note this is going to create a new local database (to ensure you don’t accidentally blow over anything).

$ heroku pg:pull HEROKU_POSTGRESQL_ORANGE –app

Step 4: Import locally.

Now you just need to point your local database.yaml file to the newly generated postgres instance just created.

  adapter: postgresql
  host: localhost
  encoding: unicode
  database: <newdatabase>
  pool: 5
  username: jrasmusson

Step 5: Voila! You are done.

Restart your web server, fire up your app, and you should now see prod data locally running on your box.

Have fun!

Links that help:

How to install postgres rails


Download and install

This will automatically create a postgres user with the name of your machine (i.e. jrasmusson).

Add the ‘pg’ gem to your project.

gem ‘pg’

$ bundle install (or)
$ gem install pg

Remove any old pg gems that might interfere:

$ gem list
$ gem cleanup pg

Check that pg installed correctly and that nothing was already running on port 5432.
$ netstat -anp tcp | grep 5432

Then update your database.yml to use the new username created.

  adapter: postgresql
  host: localhost
  encoding: unicode
  database: nutshell_development
  pool: 5
  username: jrasmusson

Then configure databases:

$ rake db:create:all

Links that help

Connecting with pgadmin


Trouble shooting

psql: FATAL: database “user” does not exist
$ createdb

How to setup Rails3 postgres on mac



A much better way to install Postgres locally is to use

Do this and use the following commands to create your local root user (with no password) and database instance.

psql template1 -h localhost
CREATE DATABASE <your db name>;
GRANT ALL PRIVILEGES ON DATABASE <your db name> to root;


Wanting to align with Heroku I decided to setup a local instance of postgres.
It was more complicated than I would have liked, but here are some sketchy notes that might help if you need to go through the same thing.

Install postgres

$ brew install postgresql

After trying the EnterpriseDB one click deploy I found the homebrew install most stable.

Create Rails3 app with postgres as database

$ rails new pg -d postgres

Create new postgres database user

$ cd
$ sudo su postgres
$ createuser jr
$ Shall the new role be a superuser? (y/n) y
$ exit

if you get a getcwd: cannot access parent directories: Permission denied
just type ‘cd’ or ‘cd /’ and goto a directory where permissions aren’t an issue.

Create databases

$ sudo su postgres
$ psql template1

template1=# \l
template1=# CREATE DATABASE pg_development;
template1=# CREATE DATABASE pg_test;
template1=# \q

Login to terminal

$ psql -U postgres -d pg_development


adapter: postgresql
encoding: unicode
database: pg_development
pool: 5
username: postgres
password: root

adapter: postgresql
encoding: unicode
database: pg_test
pool: 5
username: postgres
password: root

This was all setup and pre-populated. Only had to set username and password.


$ rake db:create
$ rails generate scaffold Post name:string title:string content:text
$ rake db:migrate

Should be good to go. If you run into problems let me know and I will try to keep this up to do.

kill hanging postgres processes

Sometimes I have to manually kill postgres instances when I do a redeploy.
Pattern I follow is:
> (rebuild database)
> sudo kill -9
> manually restart server using pgadmin
> restart local rails server

— it’s a pain and I will update this when I find a better way


Links that helped

%d bloggers like this: