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!
