Will writes

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!