Standard

Posted by

Posted on

November 11, 2013

Posted under

Toolkit

Comments

17 Comments

PostgreSQL on OS X Mavericks

Few days ago my company computer was upgraded. I waved goodbye to ~3,5 years old 5400rpm HDD and said hello to the brand new SSD.

Because replacing hard drive in my case signifies system installation I decided to migrate from (not so fresh..) Snow Leopard to the latest OS X release.

In this post I’ll show you how to install & run PostgreSQL 9.x on the fresh OS X 10.9 Mavericks instance.

If you don’t have fresh installation (but upgraded one and you experienced any issues related to existing postgres installation) you could try uninstall pg and follow steps #0 and #1.
It may work, but doesn’t have to..
I didn’t have opportunity to test it in a such case so regardless of the outcome any feedback will be welcome.

Step #0 – Install homebrew

Homebrew is a missing package manager for OSX that enables you to install 3rd party software (with satisfying their dependencies) using one command.

Installation is pretty easy and comes down to run a single command in the terminal:

Step #1 – Install PostgreSQL

You can install the latest version of PostgreSQL by typing:


brew install postgresql

view raw

brew_install_pg.sh

hosted with ❤ by GitHub

However, if you want to install a specific version (let’s assume latest 9.2.x instead of 9.3.x) then you should use ‘brew versions’:


cd /usr/local/Library/Formula/
brew versions postgresql
# 9.3.1 git checkout 89b2d08 /usr/local/Library/Formula/postgresql.rb
# 9.3.0 git checkout ae59e09 /usr/local/Library/Formula/postgresql.rb
# 9.2.4 git checkout e3ac215 /usr/local/Library/Formula/postgresql.rb
git checkout e3ac215 /usr/local/Library/Formula/postgresql.rb
brew install postgres

view raw

brew_install_specific_pg_version.sh

hosted with ❤ by GitHub

Step #2 – Initialize database cluster

You have to create a database cluster. It’s not so complicated as it sounds:


initdb /usr/local/var/postgres -E utf8

view raw

initdb.sh

hosted with ❤ by GitHub

This command creates all the necessary directories in which the database data will live. For more information see the initdb manual.

Step #3 – Run postgres

It’s time to run installed postgres instance:


postgres -D /usr/local/var/postgres

view raw

run_postgres.sh

hosted with ❤ by GitHub

The path given as an argument must match path used with initdb in Step #2

If you want to start PostgreSQL automatically after log-in run the following command:

view raw

launchAgents_symlink.sh

hosted with ❤ by GitHub

It will create symbolic link to /usr/local/opt/postgresql/homebrew.mxcl.postgresql.plist inside ~/Library/LaunchAgents/ directory.

Step #4 – Create a role

Last but not least – you have to create some user which will be able to create databases:


createuser -d -P postgres

view raw

create_user.sh

hosted with ❤ by GitHub

For more informations visit createuser documentation.

Step #5 – Do the victory dance, install additional tools

That’s all! From now you have full-fledged postgres instance running on your Mac 🙂

Please help me improve this guide and share you suggestions in comments below.

17 thoughts on “PostgreSQL on OS X Mavericks”

  2. Definitely helpful. However, I prefer not to add extraneous user accounts so I skipped step #4. Instead, I downloaded pgAdmin3 and used it to add new connections, databases, and logins – including an “admin” login with appropriate permissions.

    Reply

    • Good point.
      In my case I needed control over pg’s version – which seems to be straightforward [*] when using homebrew.

      [*] – for those who are enough ‘geeky’ to be familiar with git & homebrew, of course 😉

      Reply

    • I was using this app before, but it was adding to much extra to my Mac. It was taking quite an effort to remove everything to clean the system, what would be even more difficult for not so geeky people. And honestly not so geeky people wouldn’t want to install postgres anyway 😉

      Reply
  5. Pingback: Installing Postgres on Mac Mavericks | Thinking Aloud

  9. Hi, thanks for putting this up.
    When I run step 2 initdb /usr/local/var/postgres -E utf8, I get the following:

    $ initdb /usr/local/var/postgres -E utf8
    The files belonging to this database system will be owned by user “ben”.
    This user must also own the server process.

    The database cluster will be initialized with locale “en_US.UTF-8”.
    The default text search configuration will be set to “english”.

    Data page checksums are disabled.

    initdb: directory “/usr/local/var/postgres” exists but is not empty
    If you want to create a new database system, either remove or empty
    the directory “/usr/local/var/postgres” or run initdb
    with an argument other than “/usr/local/var/postgres”.

    Installing homebrew and postgres was no problem.

    Further, when I run just “postgres”, I get

    $ postgres
    postgres does not know where to find the server configuration file.
    You must specify the –config-file or -D invocation option or set the PGDATA environment variable.

    This is different than when I had the “postmaster.pid” in the /var/postgres folder. There’s very little literature on this on the web.

    Any ideas?

    Reply

    • Hi, Ben.

      1. Initdb:
        It looks like you already have some postgresql cluster on your system.
      2. Starting postgres:
        You have to pass path to the configuration file or cluster directory.
        For example (passed cluster directory, as described in Step #3):

        $ postgres -D /usr/local/var/postgres

        However, since homebrew brings some launchctl plists, you don’t need to start postgres manually. It can start automatically with other deamons/system services, see “Step #3 – Run postgres” for more details.

        You can also use pg_ctl to check if the postgres is running on the given db cluster and what parameters was passed to it:

        $ pg_ctl -D /usr/local/var/postgres status
pg_ctl: server is running (PID: 238)
/usr/local/Cellar/postgresql/9.2.4/bin/postgres "-D" "/usr/local/var/postgres" "-r" "/usr/local/var/postgres/server.log"

      I hope this helps.

      Reply

      • Hi, I’m running postgres on maverics, installed by admin user – trying to access postgres as any other user (even other administrators) gives the same error as Ben. Specifying the path postgres -D /usr/local/var/postgres (which is also where it is on my system) doesn’t work and gives the error.

        postgres cannot access the server configuration file “/usr/local/var/postgres/postgresql.conf”: Permission denied
        Do you have any idea of the permissions setup for running a postgres cluster with diff users.

      • Hi Phil,

        First – please forgive me late reply.

        Second – what did you mean by “access postgres as any other user”?
        If you want to connect with postgres, use psql command instead (it’s a client app).
        The command that you has been using (postgres -D ..) is aimed to start a new postgres server instance. Is it your goal?

  10. I’ve also installed launchy gem to be able to start and stop database.
    $ gem install launchy
    Launchy uses plist files found from Library/LaunchAgents directory to launch services. To start and stop postgres I just need to execute following commands:
    $ launchy start postgres
    $ launchy stop postgres

    Reply

Leave a comment