Setting up PostgreSQL for Ruby on Rails development on OS X

One of the reasons people used to give for using MySQL over PostgreSQL (just ‘Postgres’ from here on in) was that Postgres was considered hard to install. It’s a shame, because it’s a great database (I’ve been using it for personal and some work projects for years, like my current side project, sendcat). Luckily it’s now really simple to get it going on your Mac to give it a try. This is how you do it.

What this guide is

This is a guide to getting PostgreSQL running locally on your Mac, then configuring Rails to use that for development.

What this guide is not

  • An advanced PostgreSQL guide.
  • Suitable for using in production.
  • Anything to do with why you might want to use PostgreSQL over any other database.

Installation

You can get binaries for most systems from the Postgresql site, but it’s even easier if you’ve got homebrew installed, if you haven’t got homebrew it’s worth it, pick it up here. I’m going to assume you are installing from Homebrew for this post, but you should find the information useful even if you are installing directly or using Macports.

With homebrew just run:

$ brew install postgres

You will get a load of output, but the most important part is this:

If this is your first install, create a database with:
    initdb /usr/local/var/postgres

If this is your first install, automatically load on login with:
    mkdir -p ~/Library/LaunchAgents
    cp /usr/local/Cellar/postgresql/9.0.4/org.postgresql.postgres.plist ~/Library/LaunchAgents/
    launchctl load -w ~/Library/LaunchAgents/org.postgresql.postgres.plist

If this is an upgrade and you already have the org.postgresql.postgres.plist loaded:
    launchctl unload -w ~/Library/LaunchAgents/org.postgresql.postgres.plist
    cp /usr/local/Cellar/postgresql/9.0.4/org.postgresql.postgres.plist ~/Library/LaunchAgents/
    launchctl load -w ~/Library/LaunchAgents/org.postgresql.postgres.plist

Or start manually with:
    pg_ctl -D /usr/local/var/postgres -l /usr/local/var/postgres/server.log start

And stop with:
    pg_ctl -D /usr/local/var/postgres stop -s -m fast

If you want to install the postgres gem, including ARCHFLAGS is recommended:
    env ARCHFLAGS="-arch x86_64" gem install pg

There’s a lot to read, but don’t worry, you don’t need most of the information there. You can get to that information again by running:

brew info postgres

As the instructions say, if this is your first install, create a database with:

$ initdb /usr/local/var/postgres

Do this now. You should see output like this:

$ initdb /usr/local/var/postgres
The files belonging to this database system will be owned by user "will".
This user must also own the server process.

The database cluster will be initialized with locale en_GB.UTF-8.
The default database encoding has accordingly been set to UTF8.
The default text search configuration will be set to "english".

creating directory /usr/local/var/postgres ... ok
creating subdirectories ... ok
selecting default max_connections ... 20
selecting default shared_buffers ... 2400kB
creating configuration files ... ok
creating template1 database in /usr/local/var/postgres/base/1 ... ok
initializing pg_authid ... ok
initializing dependencies ... ok
creating system views ... ok
loading system objects' descriptions ... ok
creating conversions ... ok
creating dictionaries ... ok
setting privileges on built-in objects ... ok
creating information schema ... ok
loading PL/pgSQL server-side language ... ok
vacuuming database template1 ... ok
copying template1 to template0 ... ok
copying template1 to postgres ... ok

WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the -A option the
next time you run initdb.

Success. You can now start the database server using:

    postgres -D /usr/local/var/postgres
or
    pg_ctl -D /usr/local/var/postgres -l logfile start

Again, there’s a lot of output, but you can pretty much ignore most of it.

Startup/Shutdown

Next, as the instructions suggest you can set Postgres to start and stop automatically when your mac starts. Run these  three commands to have this happen (Postgres will start when you run the last command so there is no need to manually start it if you do this):

mkdir -p ~/Library/LaunchAgents
cp /usr/local/Cellar/postgresql/9.0.4/org.postgresql.postgres.plist ~/Library/LaunchAgents/
launchctl load -w ~/Library/LaunchAgents/org.postgresql.postgres.plist

I’ve done this because I use Postgres for all my personal projects. If you’re just experimenting and want to control when it is running you can start and stop Postgres with these commands (perhaps with a shell alias). EDIT: Someone on the Hacker News thread suggested Lunchy for managing launchctl stuff, I’ve not tried it, but it looks useful.

Start Postgres:

pg_ctl -D /usr/local/var/postgres -l /usr/local/var/postgres/server.log start

Stop Postgres:

pg_ctl -D /usr/local/var/postgres stop -s -m fast

That’s it, Postgres is up and running. You can see it in the process list. Run “ps auxwww | grep postgres” and you should see output like this:

$ ps auxwww | grep postgres
will     33206   0.4  0.0  2435116    528 s004  S+    6:52pm   0:00.00 grep postgres
will     33011   0.0  0.0  2445360    880   ??  Ss    6:41pm   0:00.14 postgres: writer process
will     33007   0.0  0.1  2445360   2412   ??  S     6:41pm   0:00.25 /usr/local/Cellar/postgresql/9.0.4/bin/postgres -D /usr/local/var/postgres -r /usr/local/var/postgres/server.log
will     33014   0.0  0.0  2441392    420   ??  Ss    6:41pm   0:00.03 postgres: stats collector process
will     33013   0.0  0.0  2445492   1460   ??  Ss    6:41pm   0:00.03 postgres: autovacuum launcher process
will     33012   0.0  0.0  2445360    504   ??  Ss    6:41pm   0:00.10 postgres: wal writer process

Create a user and database

Now that the Postgres server is running we need to create a database for use in our rails app. This is really simple using the shell commands that ship with Postgres. First lets create a new user. Running the createuser command you will get an interactive prompt asking some questions about the user, answering ‘n’ is OK for all of them:

$ createuser shawsome
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) n
Shall the new role be allowed to create more new roles? (y/n) n

Next create the two databases you will need, development and test. Here you can see the options are given on the command line, the -O specifies the owner of the database (the user we just created) and -U specified the character encoding scheme to be used in the database.

$ createdb -Oshawsome -Eutf8 shawsome_development
$ createdb -Oshawsome -Eutf8 shawsome_test

You can verify everything worked by connecting. Postgres ships with a shell just as MySQL does, it’s called ‘psql’. Run the following command, you should find yourself at a database prompt:

$ psql -U shawsome shawsome_development
psql (9.0.4)
Type "help" for help.

shawsome_development=>

That’s the DB all done with. Hit ctrl-d to exit the shell. Next install the postgres gem.

$ sudo env ARCHFLAGS="-arch x86_64" gem install --no-ri --no-rdoc pg
Building native extensions.  This could take a while...
Successfully installed pg-0.11.0
1 gem installed

For Macports you might have more luck with:

$ sudo env ARCHFLAGS="-arch x86_64" gem install pg -- --with-pg-config=/opt/local/lib/postgresql84/bin/pg_config

If you want to read further on these commands check out the docs for createuser, createdb and psql.

Create the Rails app

Now we need to create the app. Run “rails new” but specify –database=postgresql to get a database.yml pre-configured. We won’t need to edit the database.yml from the generated file, but it does contain some information that could be useful if you’re using Macports so open it up and see what got generated.

$ rails new shawsome --database=postgresql
… Much output

Head into the new app and create a scaffold. It’s not going to be anything fancy, just enough to get some data into the database:

$ cd shawsome
$ rails g scaffold Post title:string author:string body:text
      invoke  active_record
      create    db/migrate/20110528180734_create_posts.rb
      create    app/models/post.rb
      invoke    test_unit
      create      test/unit/post_test.rb
      create      test/fixtures/posts.yml
       route  resources :posts
      invoke  scaffold_controller
      create    app/controllers/posts_controller.rb
      invoke    erb
      create      app/views/posts
      create      app/views/posts/index.html.erb
      create      app/views/posts/edit.html.erb
      create      app/views/posts/show.html.erb
      create      app/views/posts/new.html.erb
      create      app/views/posts/_form.html.erb
      invoke    test_unit
      create      test/functional/posts_controller_test.rb
      invoke    helper
      create      app/helpers/posts_helper.rb
      invoke      test_unit
      create        test/unit/helpers/posts_helper_test.rb
      invoke  stylesheets
      create    public/stylesheets/scaffold.css

You will now have a migration. We’re going to edit it a bit from the default to add some sensible restrictions and an index.

Run your super shiny migration:

$ rake db:migrate
(in /Users/will/shawsome)
==  CreatePosts: migrating ====================================================
-- create_table(:posts)
NOTICE:  CREATE TABLE will create implicit sequence "posts_id_seq" for serial column "posts.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "posts_pkey" for table "posts"
   -> 0.0060s
-- add_index(:posts, :author)
   -> 0.0033s
==  CreatePosts: migrated (0.0097s) ===========================================

Now start up a Rails server.

$ rails s

Poking around in the database

Add a few posts and you can re-run the database shell (see above) and start poking around. You can use ? to get help in the shell, but we will jump straight to dt to get a list of tables:

shawsome_development=> dt
               List of relations
 Schema |       Name        | Type  |  Owner
--------+-------------------+-------+----------
 public | posts             | table | shawsome
 public | schema_migrations | table | shawsome
(2 rows)

Great, our posts table is there, lets take a look at the posts table in more detail:

shawsome_development=> d posts
                                     Table "public.posts"
   Column   |            Type             |                     Modifiers
------------+-----------------------------+----------------------------------------------------
 id         | integer                     | not null default nextval('posts_id_seq'::regclass)
 title      | character varying(255)      | not null
 body       | text                        |
 author     | character varying(255)      | not null default 'Anonymous'::character varying
 created_at | timestamp without time zone |
 updated_at | timestamp without time zone |
Indexes:
    "posts_pkey" PRIMARY KEY, btree (id)
    "index_posts_on_author" btree (author)

You can see we get a fair amount of detail here, including column type, null/not null flags, and default values, as well as any indexes on the table. Lets select some data. This should be familiar to anyone who has used a relational database before (hint: try tab completion, it’s really good in the Postgres shell)

shawsome_development=> select id, title, author, created_at from posts;
 id |     title      |  author   |         created_at
----+----------------+-----------+----------------------------
  1 | Book 1         | Anonymous | 2011-05-28 18:09:13.965425
  2 | Bobski's dream | Anonymous | 2011-05-28 18:09:30.122767
(2 rows)

Done!

What now?

Check out the Postgres docs, they’re really good, and go forth and develop excellent sites on top of PostreSQL!

Tagged , , ,

23 thoughts on “Setting up PostgreSQL for Ruby on Rails development on OS X

  1. szimek says:

    The only thing that one has to watch out is when deploying to Heroku. They are still using PostgreSQL 8.3 (unless you pay at least $200/month) and homebrew installs 9.0, so it’s possible that something that works fine on your local machine won’t work on Heroku.

    It would be nice if there was “postgres83″ formula for homebrew – the one for 9.0 looks a bit complicated, so I’m not sure how hard it is to port it to 8.3.

  2. S Javeed says:

    Excellent post! One thing I’d add is the “foreigner” gem that I heard about during RailsConf 2011. It adds support for foreign key constraints like we’re used to in PostgreSQL.

  3. Thanks for the tutorial, this was really needed.

    I tried doing this a while back and gave up as it was too much hassle. Instead I went for the prebuild EnterpriseDB package and Navicat for Postgres. It’s not perfect but it works fine and saved me a lot of hassle.

  4. Darren says:

    Great article, and very close to what I’ve been doing for the last 4 years (well, the brew part is younger than that).

    I would suggest the following differences…

    Add this to your shell profile…

    export PGDATA=$HOME/.pgdata
    alias pgstart=’pg_ctl -l $PGDATA/logfile start’
    alias pgstop=’pg_ctl stop’

    The location of the data directory is a personal preference, and is a holdover from before using brew to install PG (when the /usr/local/ wasn’t writable).

    I never have the system start/stop postgres. Instead, I do it with my own user so that everything is writable by whomever I’m logged in as.

    I usually create application users as super users (createuser -s). This allows the Rails rake tasks to work properly (e.g., db:create db:drop).

  5. Corey says:

    What really prevents me from using PostgreSQL in development is the lack of a good management application. Being able to poke around with a couple of clicks in Sequel Pro is much preferred to writing SQL to browse data, especially when you want to make a couple quick changes to a row without firing up the console.

  6. Benoit Daloze says:

    szimek: That should not be a problem.

    You have ‘versioned’ brews at homebrew-alt:

    https://github.com/adamv/homebrew-alt/blob/master/versions/postgresql8.rb

    And you could also poke with the git repository of homebrew to find back the old formula (I would recommend to rename it in this case)

  7. Sean Gaffney says:

    @szimek, and others trying to get a more Heroku-compatible PostreSQL installation, here’s a gist to install 8.4.6 that works brilliantly.

    Just get the “raw” path and do: brew install [path]

    You may be able to change the “url” and “md5″ variables to an 8.3.x version as well. Not sure though, haven’t tried it. :)

  8. Adam Vandenberg says:

    Homebrew has an “ALT” repo ( https://github.com/adamv/homebrew-alt ) that contains older versions of brews, which can be installed via URL:

    brew install https://github.com/adamv/homebrew-alt/raw/master/versions/postgresql8.rb

    That’s 8.4 specifically, but I’d accept a specifically versioned 8.3 formula as well, if someone wanted to submit one.

  9. Thanks Will, great tutorial.

    If someone is migrating an existing rails app from another DBMS to PostgreSQL do not forget to add the pg gem to your Gemfile.

    For data migration I recommend the taps gem.

  10. Nakort Valles says:

    I am getting this error, any ideas?

    nakorts-macbook-pro:test
    nakort$ createuser nakort
    Shall the new role be a superuser? (y/n) n
    Shall the new role be allowed to create databases? (y/n) y
    Shall the new role be allowed to create more new roles? (y/n) y
    createuser: could not connect to database postgres: FATAL: role “nakort” does not exist

  11. Florian Aßmann says:

    Hi Szimek, since brew is git based you can cd into your brew installation directory (aka /usr/local) and `git log Library/Formula/postgresql.rb`

    Find the commit that introduced the version 8.x and `git checkout COMMIT_SHA Library/Formula/postgresql.rb`.

    You can then install the old version, IMHO.

    P.S. don’t forget checkout postgresql.rb file again or brew wont be able to update the postgresql formula anymore.

  12. David Zhang says:

    Great post. Really – this is no doubt beneficial to many.
    Maybe show what the Gemfile looks like? For mysql there’s that gem ‘mysql2′ needed.. anything like that for postgres? gem ‘pg’, or is that not necessary?

  13. David Zhang says:

    Also, maybe a showing of the database.yml file… just a few thoughts. Again, great post.

  14. Tekin says:

    Just a warning for any of you installing Postgresql on a fresh Lion install – if you get the following error when you try creating a user:

    Is the server running locally and accepting
    connections on Unix domain socket “/var/pgsql_socket/.s.PGSQL.5432″?

    Then chances are your $PATh needs adjusting so that /usr/local/bin is ahead of /usr/bin.

    This is because Lion already has parts of Postgresql on it, causing a conflict. You can run “brew doctor” to check if everything is good with your $PATH.

    For more details, see https://github.com/mxcl/homebrew/issues/5004

  15. Shailesh Hegde says:

    @Nakort: You need to create a ‘postgres’ user to create databases using your own admin privileges.

    Run:
    createuser –superuser -U postgres

    Then, createdb and dropdb should work.

  16. standup75 says:

    After trying a macport solution that was kind of weird, this one worked like a charm, beautiful!

  17. Clay says:

    This is great, thanks. I would like to see a copy of a database.yml file that accompanies this!

    • Will Jessop says:

      It’s going to vary depending on how you are connecting to postgres, but this one is similar to what I use:

      production:
        adapter: postgresql
        encoding: unicode
        database: dbname
        username: user
        host: localhost
        password: somepassword

  18. Mark says:

    createuser does not give you the questions shown unless you pass it the “–interactive” parameter (as of v. 9.1)

  19. gronski says:

    First off, thank you Will & those who followed up. Very helpful. I am trying to setup pg with an existing rails app, but beibg a new comer hit many roadblocks with pg. Is there any chance of thus article being revised for 2013 or can anyone point me to anither updated post?

  20. Will Jessop says:

    gronski: The article is still pretty accurate, but you should also check out http://postgresapp.com/.

  21. Thank you! I was looking for the
    initdb /usr/local/var/postgres

    You can use this command to initialize databases for different users on the same machine
    initdb /usr/local/var/user1/postgres
    initdb /usr/local/var/user2/postgres

  22. joseph says:

    Can we also install PostGIS to MacOS?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.