19 September 2011

Installing PostgreSQL 9.0 on Mac OS X 10.6.8 via MacPorts



While installing PostgreSQL 9.0 on Mac OS X again, I had to figure out all these steps yet again. So I'm documenting this process for my own sake because I have been through this now twice on two computers recently, having to hunt down all of these commands each time. I'm hopeful that this will help others as well.

Use the MacPorts command port to install PostgreSQL 9.0, then create and own a data directory and a logs directory:

$ sudo port install postgresql90-server
$ sudo mkdir -p /opt/local/var/db/postgresql90/defaultdb
$ sudo chown -R postgres:postgres /opt/local/var/db/postgresql90
$ sudo mkdir -p /opt/local/var/log/postgresql90
$ sudo chown -R postgres:postgres /opt/local/var/log/postgresql90

Now you need to initialize the database using the data directory that was created above:

$ sudo -u postgres /opt/local/lib/postgresql90/bin/initdb -D /opt/local/var/db/postgresql90/defaultdb

I prefer to change the postgres user's shell to bash:

$ sudo dscl . -create /Users/postgres UserShell /bin/bash

View the postgres user account just to make sure it all looks OK:

$ dscl . -read /Users/postgres
AppleMetaNodeLocation: /Local/Default
GeneratedUID: 5B38F583-CBBF-4082-A32D-C17947394A27
NFSHomeDirectory: /opt/local/var/db/postgresql90
Password: *
PrimaryGroupID: 501
RealName:
PostgreSQL-90 Server
RecordName: postgres
RecordType: dsRecTypeStandard:Users
UniqueID: 502
UserShell: /bin/bash

Also check the postgres group:

$ dscl . -read /Groups/postgres
AppleMetaNodeLocation: /Local/Default
GeneratedUID: 715FEB22-D0F1-443F-BC93-55896210DB44
Password: *
PrimaryGroupID: 501
RealName: postgres
RecordName: postgres
RecordType: dsRecTypeStandard:Groups

Now edit the /opt/local/var/db/postgresql92/defaultdb/pg_hba.conf file to add the appropriate permissions:

# "local" is for Unix domain socket connections only
local all all trust
# IPv4 local connections:
host all all 127.0.0.1/32 trust

This should allow you to connect easily using the psql utility.

I prefer to use the pg_ctl command to start and stop PostgreSQL. This is simply because I learned PostgreSQL on Linux and that's all there was. To prevent having to retype the full command every time I want to start or stop the database, create a start script and a stop script in the postgres user's home directory named pg_start and pg_stop. Below are the contents of the pg_start file. Make sure to create these files as the postgres user in the home directory:

$ sudo su - postgres
$ vim ./pg_start
#!/bin/sh
/opt/local/lib/postgresql90/bin/pg_ctl -D /opt/local/var/db/postgresql90/defaultdb -l /opt/local/var/log/postgresql90/postgres.log start &

Below are the contents of the pg_stop file:

$ vim ./pg_stop
#!/bin/sh
/opt/local/lib/postgresql90/bin/pg_ctl -D /opt/local/var/db/postgresql90/defaultdb -l /opt/local/var/log/postgresql90/postgres.log stop

Don't forget to make them executable:

$ chmod +x ./pg_start
$ chmod +x ./pg_stop

(There is a Mac OS X way of starting PostgreSQL using launchctl but I don't tend to use that because I'm used to the standard pg_ctl command.)

Now use the pg_start script to start up PostgreSQL. Execute it as the postgres user (sudo su - postgres) I tend to cat the log file just to make sure it's running correctly:

$ ./pg_start
$ server starting

$ cat ../../log/postgresql90/postgres.log
LOG: database system is ready to accept connections
LOG: autovacuum launcher started

Looks good so we'll create my user:

$ /opt/local/lib/postgresql90/bin/createuser bsnyder
Shall the new role be a superuser? (y/n) y

Because I made my user a superuser, I can create my own db schema, so log out of the postgres user account and back to my own account first:

$ exit
$ /opt/local/lib/postgresql90/bin/createdb
CREATE DATABASE

The createdb command automatically uses my username as the schema name.

The only thing left to do before starting up the database is edit your ~/.profile or ~/.bash_profile to put the path to the PosgreSQL bin directory into the PATH:

export PATH=/opt/local/lib/postgresql90/bin:$PATH

Now just log into the PostgreSQL server using psql to make sure we're ready to roll:

bsnyder@skunk [~] $ . ~/.bash_profile
bsnyder@skunk [~] $ /opt/local/lib/postgresql90/bin/psql
psql (9.0.4)
Type "help" for help.

bsnyder=# select version();
version
------------------------------------------------------------------------------------------------------------------------------------------
PostgreSQL 9.0.4 on x86_64-apple-darwin10.8.0, compiled by GCC i686-apple-darwin10-gcc-4.2.1 (GCC) 4.2.1 (Apple Inc. build 5664), 64-bit
(1 row)

bsnyder=#

And we're good to go!

UPDATE: For those who want a nice seamless experience stopping/starting Postgres via a Mac OS X preference pane, check out Preference Pane for administering PostgreSQL on Mac OS X . It is configurable so there is no restriction on where PosgreSQL is installed.

26 comments:

  1. Great help! Walked through the steps with no glitches. Thanks much!

    ReplyDelete
  2. Thanks! Worked like a charm!

    ReplyDelete
  3. This is great, thanks v much! Actually, we've just released a PostgreSQL Mac Preferences utility, which allows you to start/stop PostgreSQL from System Preferences. So hopefully next time you won't need to hunt around for so many commands!

    ReplyDelete
  4. I followed the instructions. WHen I got to:

    sudo -u postgres /opt/local/lib/postgresql90/bin/initdb -D /opt/local/var/db/postgresql90/defaultdb


    I got the following error:


    sudo -u postgres /opt/local/lib/postgresql90/bin/initdb -D /opt/local/var/db/postgresql90/defaultdb
    Password:
    The files belonging to this database system will be owned by user "postgres".
    This user must also own the server process.

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

    fixing permissions on existing directory /opt/local/var/db/postgresql90/defaultdb ... ok
    creating subdirectories ... ok
    selecting default max_connections ... 10
    selecting default shared_buffers ... 400kB
    creating configuration files ... ok
    creating template1 database in /opt/local/var/db/postgresql90/defaultdb/base/1 ... FATAL: could not create shared memory segment: Cannot allocate memory
    DETAIL: Failed system call was shmget(key=1, size=1703936, 03600).
    HINT: This error usually means that PostgreSQL's request for a shared memory segment exceeded available memory or swap space, or exceeded your kernel's SHMALL parameter. You can either reduce the request size or reconfigure the kernel with larger SHMALL. To reduce the request size (currently 1703936 bytes), reduce PostgreSQL's shared_buffers parameter (currently 50) and/or its max_connections parameter (currently 14).
    The PostgreSQL documentation contains more information about shared memory configuration.
    child process exited with exit code 1
    initdb: removing contents of data directory "/opt/local/var/db/postgresql90/defaultdb"

    ReplyDelete
    Replies
    1. @Gordon, it looks like you need to adjust your shared memory settings for the MacOS. Take a look at the following StackOverflow question for information about changing the shared memory settings for Mac OS X:

      http://stackoverflow.com/questions/6861945/postgresql-failing-with-below-error-on-lion-os-x

      The tool for making the changes is the 'sysctl' command.

      Delete
  5. I can't edit the pg_hba.conf, actually, I can't find it. I think this is due to some lack of knowledge regarding users, I think it might me located in the defaultdb folder, but when I try to access it it says: Permission Denied.

    ReplyDelete
    Replies
    1. @feelAnalytic, This sounds like just a simple permissions issue. You probably need to edit it as the root user.

      Delete
  6. Hi , i ran the ./pg_start with the posgressql user(created earlier) and the error i am getting is below

    postgres$ ./pg_start: line 4: /opt/local/var/log/postgresql90/postgres.log: Permission denied

    ReplyDelete
    Replies
    1. You may need to create the log file manually in that location using the sudo utlity. Once it's there with the correct permissions, the scripts should run without error.

      Delete
    2. Hi

      I created the log file manually and when i ran the pg_start , i am still getting the same error as previously

      Delete
    3. Did you give the postgres user write permission to the log file? I suggest just re-running the chown command on the entire directory:

      $ sudo chown -R postgres:postgres /opt/local/var/log/postgresql90

      Delete
  7. Thanks bruce it worked finally for me. I have couple of other questions also regarding exporting my schema into the db i created .

    ReplyDelete
  8. Works well for me. Only issue for me is getting pg_start to run in the background.

    ReplyDelete
    Replies
    1. Make sure that the pg_ctl command is using the ampersand symbol (&) at the end (note that this is all on a single line, not multiple lines):

      /opt/local/lib/postgresql90/bin/pg_ctl -D /opt/local/var/db/postgresql90/defaultdb -l /opt/local/var/log/postgresql90/postgres.log start &

      Delete
  9. Thank you very much!! It turned out great!! I used it with postgresql93

    ReplyDelete
  10. Not so far I have found new cool tool to work with postgresql on mac os x - Valentina Studio. Its free edition can do things more than many commercial tools!!
    I very recommend check it. http://www.valentina-db.com/en/valentina-studio-overview
    You can install Valentina Studio (FREE) directly from Mac App Store: https://itunes.apple.com/us/app/valentina-studio/id604825918?ls=1&mt=12

    ReplyDelete
    Replies
    1. Thank you for the info about Valentina. I have not heard of it before so I will definitely check it out.

      Delete
  11. Hello, I'm on a Mac OS X 10.6.8, and followed every step you point out. Now, I cannot find 'pg_hba.conf'. You don't say where it should be (in any case, I don't have it). Also, when I try starting the server I get:

    $ ./pg_start
    $ server starting

    $ cat ../../log/postgresql90/postgres.log
    postgres cannot access the server configuration file "/opt/local/var/db/postgresql90/defaultdb/postgresql.conf": No such file or directory

    So any idea what could be wrong (after following all your steps)?

    Thanks,
    -fernando

    ReplyDelete
    Replies
    1. I no longer have PostgreSQL 9.0 installed as I have upgraded to 9.2. But I do still find the conf file is located here:

      /opt/local/var/db/postgresql92/defaultdb/postgresql.conf

      I'm not sure if there was a bug in the 9.0 install from MacPorts or if something just went wrong during your installation. I would try clobbering everything and performing a fresh installation.

      Delete
  12. This comment has been removed by the author.

    ReplyDelete
  13. Dear Bruce, Thank you so much for your post, its the best post for Installing Postgresql on a Mac OS X.

    ReplyDelete
    Replies
    1. I am very happy to hear that it helped you! Thank you for the kind note.

      Delete
  14. it seems that with the last updated of OS X (10.9.3) postgres cant start automatically

    ReplyDelete
  15. I am able to start up PostrgeSQL 9.2.4 on Mac OS X 10.9.3 using the script that I created above. After it is started, I use psql to log in to PostgreSQL and check the version, below is the output:

    $ psql
    psql (9.2.4)
    Type "help" for help.

    bruce.snyder=# select version();
    version
    ------------------------------------------------------------------------------------------------------------------------------------------------
    PostgreSQL 9.2.4 on x86_64-apple-darwin12.3.0, compiled by Apple clang version 4.1 (tags/Apple/clang-421.11.66) (based on LLVM 3.1svn), 64-bit
    (1 row)

    bruce.snyder=#

    ReplyDelete