Switching to PostgreSQL, Part 1: Installing Locally on OS X
|Mac OS X PostgreSQL||2011-08-17|
Here's an interesting tidbit: Postgres evolved from UC Berkeley's Ingres database, hence the name - "Post Ingres", or Postgres
If you've recently picked up a Mac with the Lion operating system already installed, this article probably won't be of much use to you. Apparently, Postgres is the new default for Mac desktops and servers:
Apple Ditches MySQL In Favor Of PostgreSQL [Network World - August 4, 2011]
I've been a MySQL user for almost a decade, and sometimes it feels like I've had my MBP for nearly that long. So I'm coming from the perspective of someone who switched on an older version of the OS.
My reason for switching? 50% peer pressure (all the open source kids are using Postgres) and 50% frustration with the way OS X handles (or handled) MySQL.
In January of this year, I finally upgraded to Snow Leopard. As OS X upgrades are wont to do, it borked my MySQL install. How crazy is it that I didn't even notice until a few months later? (My day job kept me so busy through the spring that I just didn't have occasion to work with MySQL locally that often.)
So this set of instructions obviously no longer works: Installing Django with MySQL on Mac OS X (from June of 2008)
I even tried using homebrew, that failed on make (probably an out of date compiler). (Incidentally, if you're still bent on trying, here's a great set of instructions: Installing MySQL on Snow Leopard using Homebrew [Trey Piepmeier - February 28, 2010])
Had I continued down that rabbit hole, I would have gotten MySQL installed and running eventually. But what a headache. So I finally listened to the advice of trusted friends, friends who care about my welfare and mental health, and went with a homebrew install of Postgres.
How to get homebrew:
How to use homebrew:
Installing postgres with homebrew was as simple as any other homebrew install:
brew install postgresql
I wish that I had saved the stdout from the install so that you could see what to expect, but the only thing I kept was the last bit:
Success. You can now start the database server using: /usr/local/Cellar/postgresql/9.0.3/bin/postgres -D /usr/local/Cellar/postgresql/9.0.3/data or /usr/local/Cellar/postgresql/9.0.3/bin/pg_ctl -D /usr/local/Cellar/postgresql/9.0.3/data -l logfile start
How helpful is that? And in case you're curious, you can confirm you have it installed thusly:
brew search postgresql
Anyway, my installation was error-free, but afterwards I did have to make a few modifications to get postgres running. For some reason the install did not include a folder to store the data (that folder also needs to have ownership set):
sudo mkdir /usr/local/Cellar/postgresql/9.0.3/data sudo chown postgres /usr/local/Cellar/postgresql/9.0.3/data
Then there was the pg_hba.conf to update. You should find it here:
And there's a sample here, showing how the entries should be formatted:
Here's an idea of what my entries look like:
# TYPE DATABASE USER CIDR-ADDRESS METHOD # IPv4 local connections: host all postgres 127.0.0.1/32 md5 host my_first_database all 127.0.0.1/32 md5 # "local" is for Unix domain socket connections only local all postgres md5 local my_first_database all md5
I'm getting a little ahead of myself - the database-specific entries (e.g., 'my_first_database') don't need to be there until after you've created your first database, but I don't think there's any harm in sticking them in there ahead of time. At least, I didn't have any trouble starting postgres with them there.
NOTE: After upgrading to Lion, I did get this error when I tried to start Postgres:
FATAL: no pg_hba.conf entry for host "::1", user "postgres", database "bshaurette_belle", SSL off
Just add this to your pg_hba.conf and you should be good to go:
# IPv6 local connections: host all all ::1/128 md5
Here's a message from the postgres archives with a sort-of explanation: Re: [GENERAL] FATAL: no pg_hba.conf entry for host "::1***" [February 22, 2011]
Now, to start postgres locally, I have to switch users:
su -l postgres /usr/local/Cellar/postgresql/9.0.3/bin/postgres -D /usr/local/Cellar/postgresql/9.0.3/data
Which returns something like this:
[postgres]$ /usr/local/Cellar/postgresql/9.0.3/bin/postgres -D /usr/local/Cellar/postgresql/9.0.3/data LOG: database system was shut down at 2011-08-15 21:31:01 PDT LOG: database system is ready to accept connections LOG: autovacuum launcher started
To stop it manually, just
LOG: received fast shutdown request LOG: aborting any active transactions LOG: autovacuum launcher shutting down LOG: shutting down LOG: database system is shut down
For more information about your install, run the 'brew info postgresql' command.
To get simple information about the status of your local postgres server (simple as in "is it running"), you'll first need to make sure you have this env variable set:
Then you can run this command (which returns status and a process id when the server is running):
[postgres]$ pg_ctl status pg_ctl: server is running (PID: 12183) /usr/local/Cellar/postgresql/9.0.3/bin/postgres "-D" "/usr/local/Cellar/postgresql/9.0.3/data"
For more information about how to use pg_ctl (and its modifiers that initialize, start, stop, or control a PostgreSQL server), check out this doc page: http://developer.postgresql.org/pgdocs/postgres/app-pg-ctl.html