PostgreSQL 8.4 on Mac OS X 10.7 (Lion)
on
This should probably work for any version but 8.4 is what I needed and tested. If you want to make life easier, you should consider using the homebrew install method instead.
Important: If you also need the Ruby gem you may want to skip to the alternate install method below and save yourself some time. Or you can try to see how to get this method working where I failed (I’d greatly appreciate a comment with your fix if you do.)
First, create the postgres user as there is an issue with the automatic installers creating it [1]:
sudo dscl . -create /Users/postgres UserShell /usr/bin/false
Next, download the installer dmg from the PostgreSQL site
Run the installer. It may give you a message about shared memory. If so, click OK, restart your computer and run the installer again.
After the installer finishes I still got the following error message:
Problem running post-install step. Installation may not complete correctly The database cluster initialization failed.
Supposedly, the creation of the postgres user is the cause for this, though doing it first doesn’t seem to avoid the error.
Now if you type psql at the command prompt it may still blow up on you as it did for me. This is probably because it’s pointing to the system install of PostgreSQL and not the one we just installed. Confirm that by doing:
which psql
and you’ll probably get
/usr/bin/psql
We really want the version in /Library/PostgreSQL/{version}/bin so we fix that by adding that directory to our path. If you use bash you can add the following to your ~/.bash_profile (substituting 8.4 for your actual version number if you installed a different one – from now on I’ll just assume 8.4 though):
export PATH=/Library/PostgreSQL/8.4/bin:$PATH
If you already have an export PATH line in there then don’t replace it, just add the /Library/PostgreSQL/8.4/bin part to somewhere before $PATH, being sure to separate it from the next path via a colon (:). Now save ~/.bash_profile and go to the command prompt and type
source ~/.bash_profile
and you should be able to now do
which psql
and get
/Library/PostgreSQL/8.4/bin/psql
Make sure the data directory is owned by the appropriate user (if that data directory doesn’t exist, make it first):
sudo chown postgres /Library/PostgreSQL/8.4/data/
And then initialize it:
sudo -u postgres initdb -D /Library/PostgreSQL/8.4/data
You can now start the database server using:
sudo -u postgres postgres -D /Library/PostgreSQL/8.4/data
or
sudo -u postgres pg_ctl -D /Library/PostgreSQL/8.4/data -l logfile start
You should be all good to go now. Some other helpful things to get you started:
Create a database:
sudo -u postgres createdb new_database_name
Connect to that database:
sudo -u postgres psql -d new_database_name
Create a new role as a superuser and assign a password:
sudo -u postgres createuser -P -s -e new_user_name
Alternate installation from source if you have trouble building the Ruby gem for Rails
The above got a PostgreSQL server running for me but I had issues using it with the Rails gem. Seemed to be incompatible architectures and it didn’t matter which arch flags I used to compile the gem. So instead I installed the 64 bit version of 8.4 from source. (If you want the latest version you can try using homebrew or macports instead.) If you already tried the above and it failed just use the uninstaller provided in /Library/PostgreSQL/8.4 and start over this way.
Extract the download and then go into the extracted directory:
cd postgresql-8.4.8
Then (assuming you have already created the postgres user as above):
./configure make make install mkdir /usr/local/pgsql/data
Open ~/.bash_profile and add:
export PATH=/usr/local/pgsql/bin:$PATH
Then reload your profile:
source ~/.bash_profile
Initialize:
initdb -D /usr/local/pgsql/data
Start the database:
postgres -D /usr/local/pgsql/data
or with:
pg_ctl -D /usr/local/pgsql/data -l logfile start
Important note: this method will make the owner of your database the current Mac OS user, not the conventional postgres user.
Now to install the Ruby gem:
export PATH=/usr/local/pgsql/bin:$PATH env ARCHFLAGS="-arch x86_64" gem install pg
You should be all good to go now. Some other helpful things to get you started:
Create a database:
createdb new_database_name
Connect to that database:
psql -d new_database_name
Create a new role as a superuser and assign a password:
createuser -P -s -e new_user_name
Note: If you have trouble starting apache via the built in web sharing or even MAMP, check the Console app and verify that you are getting an error similar to this:
dyld: Library not loaded: /usr/lib/libpq.5.dylib
If so do the following:
sudo mv /usr/lib/libpq.5.dylib /usr/lib/libpq.5.dylib.backup sudo ln -s /usr/local/pgsql/lib/libpq.5.dylib /usr/lib/libpq.5.dylib
Credits:
sudo chown postgres /Library/PostgreSQL/9.0/data/
chown: postgres: Invalid argument
I get that error message when trying to run that command. I don’t have the postgres user created by the installer
lucassp, try running the first command from the first installation method first:
sudo dscl . -create /Users/postgres UserShell /usr/bin/false
I can confirm that the same method works for PostgreSQL 9.1 Installer !
Just need to replace the version number in the directory paths
Jonathan, Do you know how to create a start up item for for postgres, so that we can start the service using the launchctl command? I tried the startupcfg.sh file in the PostgreSQL installation directory, but I am new to Mac and it didn’t work for me (atleast not yet).
Hi, how can I set database to autostart at boot? Thanks!
I created the user as described, but still I’m getting an error when starting the server.
FATAL: role “postgres” does not exist
I also see that I don’t have a /Users/postgres directory (shouldn’t there be one?), but
sudo dscl . -list /Users
does list me a user named ‘postgres’.
Any idea what’s missing?
Ok, I found this article helpful: wasn’t aware that you need to create the postgres user via regular user system preferences. http://www.docmoto.com/en/node/196, I thought dscl does the creation already, but obviously wasn’t sufficient.
Hi, I chown to ‘postgres’ and when I list it it says the data folder belongs to ‘nobody’.
I’ve run sudo dscl . -create /Users/postgres UserShell /usr/bin/false several times. Any ideas?
Andy, what’s the output of “id postgres”? If the user doesn’t exist it would say something like “id: postgres: no such user”
Raj and Janko, I’m not sure how to have it auto start on boot, sorry!
I installed postgres using the enterprisedb one click installer years ago and it did create the startup script on /Library/LaunchDaemons/com.edb.launchd.postgresql-8.4.plist, and it works. Since years ago, Apple replaced the startupItems with the LaunchDaemons / LaunchAgents scripts. Search for the file i mentioned on the web and how to “bless” it so it can work.
Pingback: Solution of “Can’t turn on Web sharing on Mac OSX LION!” | Namit kewat's Blog
Dear Jonathan,
Thanks for the good instructions. I got it to work immediately, but it did cause something else: php (at least command line) fails.
The box-standard OSX 10.7 PHP installation is compiled against libpq.5.3.dylib and not libpq.5.2.dylib. The installation (DMG version) of PostgreSQL replaces the symbolic link /usr/lib/libpq.5.dylib to point to the /Library/PostgreSQL/8.4/lib/libpq.5.dylib (which again points to the 5.2 version in the PostgreSQL lib folder). This breaks the PHP program in /usr/bin/ causing it to fail with the following error:
dyld: Library not loaded: /usr/lib/libpq.5.dylib
Referenced from: /usr/bin/php
Reason: no suitable image found. Did find:
/usr/lib/libpq.5.dylib: no matching architecture in universal wrapper
/usr/lib/libpq.5.dylib: no matching architecture in universal wrapper
Trace/BPT trap: 5
To repair I did the following:
- Create backup of PostgreSQL’s link:
sudo mv /usr/lib/libpq.5.dylib /usr/lib/libpq.5.dylib.postgres
- Restore a link to 5.3
sudo ln -s /usr/lib/libpq.5.3.dylib /usr/lib/libpq.5.dylib
This solves the issue for PHP, and also does not break PostgreSQL.
Hope this helps.
Hey, thank you for the tip. I knew about the user issue but since I`m new to OSX, I didn’t know how to create it and set all the scripts properly from terminal.
Another questions: can it run as a service, just like as in Windows or Linux? Any way to put it to start with the session?
[]‘s
Sorry, I haven’t tried to run it as a service. So far I have been just starting it manually since I don’t need it all of the time. If I try that out at some point I will post my results. Thanks for reading!
FWIW, if you use the sys-preferences -> user & groups dialog and create a postgres user vs. using the “dscl . -create” command the postgres installer will complete successfully. I used 8.4.11-2 installer on a new lion install.
I think that dscl command isnt working on Mountain Lion, because chown is returning a illegal username error. Any help?
I’m not that familiar with that part of things, sorry. Maybe someone else on this thread has an idea? Another idea is to just install PostgreSQL using homebrew, at which point installation isn’t much more than typing
brew install postgresql. More info on that method is atI get the same issue as Andy, chown goes to nobody. I tried changing the primary group id with no luck
i get this on id postgres
uid=4294967294(postgres) gid=20(staff) groups=20(staff),404(com.apple.sharepoint.group.3),403(com.apple.sharepoint.group.2),12(everyone),61(localaccounts),4294967294(nobody)
Guys having trouble with that CHOWN command:
Delete the user using “sudo dscl . delete /Users/postgres”
Create the user using the “Users and Groups” tool in Mac OSX. Make sure to make it admin.
Mountain Lion and Lion both messed with what is required for dscl usage. Try the following:
dscl . -create /Users/postgres
dscl . -create /Users/postgres UserShell /usr/bin/false
dscl . -create /Users/postgres RealName “Postgres Service Account”
dscl . -create /Users/postgres PrimaryGroupID 80
dscl . -append /Groups/admin GroupMembership postgres
dscl . -passwd /Users/postgres
dscl . -create /Users/postgres UniqueID
@Ben Thanks a lot for the tips, i did look for a solution for weeks!!!
Thanks again!
I think that the reason of problem with creating a user is the fact that there is already a user _postgres. So if you use _postgres instead of postgres in all scripts it will work perfectly
I found some more better then PostgreSQL, free tool – Valentina Studio. Does everything you need, and does it very well. http://www.valentina-db.com/en/valentina-studio-overview
Pingback: Setup a Virtualenv and Python on Mac with MacPorts | Mario Orlandi's Snippets
Pingback: Setup Postgresql on Mac with MacPorts | Mario Orlandi's Snippets