Using PostgreSQL in Lion Server

Using PostgreSQL in Lion Server

With the recent release of Lion and Lion Server we covered a few of the missing features we were previous used to seeing in an OS X Server release. One noticeable absentee was MySQL, which we later discovered to be replaced by PostgreSQL. In this article we will take a look at how to connect to Lion’s built in PostgreSQL services.

UPDATE: I have now made a video walkthrough on how to get Postgres up and running on Lion Server 10.7.4. Click here.

There is an alternate guide to PostgreSQL in Lion Server by using pgAdmin instead of phpPgAdmin, you can find the article here.

Open up the Server App and turn on the Web Server. Also check the box next to “Enable PHP web applications”.

Head over to the phpPgAdmin website and download the latest release. Unzip the contents and rename the folder to something like “phpPgAdmin” to make it easier to browse to. You need to place this folder in your web documents folder in the following location:

/Library/Server/Web/Data/Sites/Default/

Open up Safari and browse to the phpPgAdmin folder on your localhost web server:

http://localhost/phppgadmin/

At this point the PostgreSQL server should have a red X next to it, as it’s not running.

There are two ways to start the PostgreSQL service. Either simply by opening Server App and starting the “Wiki” service. You may want to change the “Wiki Creators” to be Admin’s only or otherwise everybody can create Wikis. Or, by opening Terminal and running the following command:

sudo serveradmin start postgres

Now if you head back to the phpPgAdmin page in Safari and select the database you should now be able to login with the user “_postgres” and your administrator password.

About the Author

I'm an enthusiastic technology lover and work daily in a mixed AD/OD IT environment. Like most, I strive to work on the Apple platform and tolerate the Windows one. I also have a burning passion for music/bass playing and supporting my football club, Arsenal. I use this blog to write useful articles on cool Mac things, and other technology experiences - my trials and tribulations.

haykong

For some reason, I can’t get phppgadmin to recognize that postgresql server is active when I turn on the wiki.

In phppqadmin, the server icon still has red X through it.

Is there any .conf file I need to edit on psql directory? or the .ini file in phppgadmin directory?

Thanks
- Simon
  
  My article may be a little misleading there. The red X basically means you haven’t authenticated to the database, not that it is isn’t running. Sorry.
  
  Open up WGM, under the ‘View’ menu you select ‘Show System Records’ – look for the user ‘PostgreSQL Server’.
  
  Under the ‘Groups’ tab select the ‘PostgreSQL Users’ group and check that the ‘PostgreSQL Server’ user is in their – short name should be ‘_postgres’.
  - Simon
    
    If the user is already in the group, switch back to the ‘Users’ tab and change the password of the ‘PostgreSQL Server’ user and then try logging in.
haykong

Thanks this clarifies things :) Now I got it working.
Jules

It’s quite possible I’m being a total clown, but what are you actually downloading from the postgresql site? The OS X one-step installer doesn’t seem to work (and doesn’t contain anything that I can seem to unzip) – please can you tell me exactly what you downloaded at the start? Thanks!!! Jules
- Simon
  
  Hi Jules, no you are not being a clown – my guide needs editing! You don’t need anything from the PostgreSQL site, that is already included in Lion Server. Head to the phppgAdmin site an download the latest release, probably the .zip is easiest. http://phppgadmin.sourceforge.net/doku.php?id=start
  - Jules
    
    Thanks for replying. I think I’ve just barfed my mac because having run the os x self installer from the postgres website, even after uninstalling it, now I get “error reading settings” on the wiki part of server. Man… they could have made this a bit easier! Now to try and figure out how to reinstall lion server…
    - Simon
      
      What if you leave the postgreSQL installed? Does Wiki still not work?
      - Jules
        
        nope – I think it messes the users up somehow… boo. Am reinstalling snow leopard, then again upgrade to lion, then again install lion server, then I’ll try your instructions again :-) It’s going to be one of those days I think!
Simon

Sorry Jules. My fault – I’ve now fixed the article.
TC

“sudo serveradmin start postgres” is the command you’re looking for.

That said, I haven’t rebooted yet, so I don’t know if postgres will start automatically after the reboot. Setting the wiki server to on in Server.app *will* do that, obviously.
- Simon
  
  Thanks TC. I have updated the article accordingly. ;)
Ben

Nice article…nice site. It is good to see someone tackling the curveball thrown by Apple with Mac OS X Lion, although, I for one am very happy to see the adoption of postgres although I did try to install over it with EnterpriseDB after doing a clean install of Lion (before stumbling on to your site). I now find myself in need of a posting of the /etc/postgres-reg.ini file that I deleted after trying to undue the damage after the EnterpriseDB install failed. I have looked everywhere included the Mac Developer area with no luck…hoping that you could simply post the contents of the /etc/postgres-reg.ini file from Lion. Thanks in advance.
- Simon
  Hi Ben, I can’t seem to find that file anywhere within the /etc/ directory…so I searched the disk, could it be the .conf file?:
```
sudo find / -name 'postgre*'
/Library/WebServer/Documents/postgresql
/Library/WebServer/Documents/postgresql/html/postgres-user.html
/private/etc/raddb/sql/postgresql
/private/var/pgsql/postgresql.conf
```
  - Ben
    
    Simon…
    
    Thanks for replying…it may only be a file placed there by the enterprisedb installation of postgreSQL with which I am familiar, not Mac OS X Lion’s native installation which means I have other problems. Just so you know where I was coming from I did a complete clean install of Mac OS Lion…reinstalling Mac OS X Snow Leopard from scratch, running Software Update to Update Mac OS Leopard to the latest version as recommended before downloading the update to Mac OS X Lion. Not knowing that there was a postgres superuser existing under Lion I tried to install the enterprisedb installer version of postgres but it bombed because the superuser postgres already existed…there are numerous (now!) postings of this issue; however, I tried to uninstall postgres, first using the enterprisedb’s uninstaller and then manually according to enterprisedb’s posting (obviously this is where my problem lies) and then trying to delete the postgres user which failed. I then installed Mac OS Lion Server hoping this would fix the issue since postgres is preinstalled on Server, but upon examining the console and logs, postgres is not loading on my machine. I assumed it was because the *.ini file was missing which led to my initial question to you. As I see it, I can look through the uninstaller logs to see if the uninstaller uninstalled something that it should not have or go back through the complete process of installing Leopard, then Lion, then Server again…Unfortunately this is zero documentation on the mechanics of Lion Server’s native installation of postgres.
    
    Any helpful thoughts would be appreciated.
    - Simon
      
      If I were you I would do a clean install without doing Snow Leopard first, that’s how I do it. Create a bootable USB or DVD from the SharedSupport folder in the Lion Installer, and then boot from it. As long as you have an internet connection you can do a fresh install immediately and save some time.
      - Ben
        
        Gotcha…thanks.
        
        BTW, what do you use for admin on postgres…Navicat, PgAdminIII or other?
Simon

Ben, As per the article, I’m using phpPgAdmin.
Ben

Simon…

After reinstalling Mac OS X Lion and Server from the new Mac OS X Lion Recovery System I am up and running (btw I did create the bootable USB thumb drive you recommended, thx). After additional research, your site is the only one I have found that explains the process of accessing the native postgres…go figure. Do you use the native postgres for both development and production and if not, how do you keep the two separated? I will be using postgres for a large development project in addition to building my own site…

I also noted you used jQuery on your site…very nice. What other tools did you use.

ps. If I get to be a pest, please just let me know…it is difficult not to use a good resource once you have found one. :)
- Simon
  
  Hi Ben, No problem! That’s why this site is here, so i’m pleased it is useful.
  
  At present I don’t use OS X Lion Server for anything production – i’m still at the tinkering point myself, and I wouldn’t trust it at this stage. For production sites i’m still using 10.6.
  
  I would always use separate machines for production and development anyway.
  
  JQuery and @font-faces are the only things i’m using here really.
  
  Hope this helps.
http://simon.geek.nz/ Simon Welsh

I had to set postgres to listen on 127.0.0.1 before I was able to connect to it. I did this by running:
$ sudo serveradmin settings postgres:listen_addresses = 127.0.0.1
$ sudo serveradmin restart postgres

Then I was able to connect fine.
Larue

Thanks so much for your help, it was invaluable. When I found out MySQL was gone from Lion Server, I nearly had a heart attack, but you have put me on the path to recovery.
Newbie

I’m a newbie trying to run a wiki for three groups to collaborate. Unfortunately, I learned the hard way that the Lion Server info does not get backed up by time machine. It appeared to get corrupted recently and we lost the data as despite my searching I was unable to locate where the data was kept. Now it appears it is in the postgresql database. So I have followed your directions here and I was just wondering, is there an easy way to export this data (make a backup) so that if the wiki was corrupted again, that I could just install a backup that gets periodically saved? I’m such a newbie that I’m not even sure if this is the right place to ask such a question but from what I’ve read you are the most knowledgeable coherent voice on the matter of this postgresql stuff. Also I love your webpage/wiki format…outstanding.
- Simon
  
  Hi, by far the easiest way of making sure everything is being backed is by using Carbon Copy Cloner (check the links at the bottom of the page). I use CCC to backup entire servers to separate HDD’s on a set schedule.
  
  It has saved my ass a few times! ;)
NEWBIE

Simon,

Thanks for the reply. I actually used CCC to test a backup. Works great. However, from what I have read it seems you should shut off the server services prior to the backup and then restart them after the backup. It looks like CCC will let you execute scripts before and after the backup. I was wondering if you know what terminal commands I need to execute get the wiki, web and ical services to shut down and then restart?
- Simon
  
  I don’t know the exact scripts you are after, but this area of the Bombich site may help: http://bit.ly/oPiUtM
  
  I’ve always scheduled my backups for the early hours of the morning when service activity is at it’s lowest. I’ve never had a problem yet from not stopping any services.
NEWBIE

Thanks…that page was very helpful. the commands I was looking for are:

sudo serveradmin start wiki
sudo serveradmin stop wiki

my understanding is that you have to shut down the service in order to correctly back it up. Do you have a different experience?
- Simon
  
  Thanks, very useful.
  
  No, I’ve never had any problems from not stop pig the services first. I can see that it may be safer to stop the services, so there are no actively running files/databases.
  
  I take it those commands will also work for the ical and web services by replacing the appropriate word?
  - Simon
    
    I just answered my own question by testing! For reference:
    
    sudo serveradmin start web
    sudo serveradmin start calendar
NEWBIE

Exactly! Thanks for all your help. If I learn more I will post. I’m hoping that they release an update to the server that will automate this backup to TM. Unfortunately, I had to find out the hard way that currently TM doesn’t back it up.
- Simon
  
  I’ve added two Scripts for starting and stopping some services (Calendar, Wiki, Web) here: http://www.mactasia.co.uk/downloads/scripts
  
  I’ve only tested these with Terminal, but they should work from within CCC (maybe you can test that for me?).
  
  I may do a post in the future about using CCC to backup an Apple Server.
Loic

Hi Simon,

Thanks for this very helpful post. I managed to access the postrgre database with phppgadmin, I tried to create a new database (using the link “create database”) but I dont know how to access it (what is its adress).

I’m trying to install locally a CMS software (called SPIP), but I don’t know which adress to declare so the script can install the tables in my new database (I tried with “localhost”, 127.0.0.1, mac45.home etc… The script says that it cannot connect to database.
- Simon
  
  Hi Loic, I’m not familiar with SPIP, but I would guess that as with most CMS stuff you have the files in to your web server root? Once they are there you should be able to access them through the same path as you accessed phppgadmin (minus the folder for phppgadmin) – so localhost should work fine?!
  
  If you can get to the setup of SPIP then your shouldn’t need to declare an address for the the database, simply the name you gave the database and the user you assigned the database.
  
  Can you provide a screenshot of the SPIP setup page? I may be able to help you fill out the details.
  - Loic
    
    Thank you Simon for replying.
    
    Yes, all the files are in my server root, so i don’t understand where the trouble is.
    
    Here are the screenshots :
    
    - Information I provide : http://barbarie.org/install1.png
    - Result I get : http://barbarie.org/install2.png
    
    The database I created with phppgadmin is called ‘barbarie’. I’m using ‘_postgres’ as connection login and my administrator password as password.
    - Simon
      
      Well I think using localhost is correct, so there must be some problem with authentication.
      
      Try connecting with your administrator account details, also with ‘root’ user if you have one?
      - Loic
        
        That doesn’t work. Grr.
        
        I only have one administrator account, which is also the root user.
      - Simon
        
        Hmm. Very strange. I’ll try to set something up tomorrow to see if I can assist further.
Simon

Hi Loic, I spent an awful long time trying to figure out your issue, and I think I’ve solved it. For future reference (after hours of trying different configurations) I looked inside /Library/Logs/PostgreSQL.log and got my answer!

2011-10-18 14:32:41 BST FATAL: database "spip" does not exist

SPIP requires that you have a database called ‘SPIP’ already in place! Simple.

When you create the database named ‘SPIP’ remember to assign permission to the ‘_postgres’ user.

Hope this helps!
- Loic
  
  Hi Simon, thanks a lot for your help, I really appreciate. The worst thing is that my problem is not solved. I created a database calles “SPIP” and continue to have the same message again and again. I know I’m forgetting something somewhere, something very small, but what? You can’t imagine how I was confident reading your post…
  
  Do you succeed yourself in installing spip locally on lion server (localhost / _postgres / admin password) ?
  - Simon
    
    Sorry Loic, I too was sure that would solve your problem :(
    
    Does the log file I mentioned above give you any clue as to why it won’t connect?
    
    If not, how about getting pgAdmin installed and following the article I made on that today? I mention a postgres.plist in that, which may be your issue?
    - Simon
      
      Oh, also worth mentioning that the database name needs to be in lowercase ‘spip’ not ‘SPIP’.
      - Loic
        
        I tried both. They both failed.
        
        I’m going to have a look at postgres.plist now, and read your article carefully.
Timothy

Hi Simon,

Your article was very helpful and I am just where your articles leaves the reader, which is great. I came here as there doesn’t seem to be an easy way to remove Lion Wiki Users when they leave the company, for example. I understand that the Users can be “hidden” through a GUI setting, but that “hide” function is not anywhere in my Wiki installation (let alone where reports indicate it should be). I have there, resorted to modifying the Postgres db to remove them.

I see the users I need to delete under /collab/Schemas/public/Tables/user_entity/login. However, I am unsure how to remove them from the login table. Can you direct me?

Many thanks if possible.
- Simon
  
  Hi Timothy,
  
  I have very little experience with Wiki itself, does deleting the users account not delete the Wiki?
  - Simon
    
    This may also be of use?
    
    Control User Access
    - Timothy
      
      Thank you for the kind links, but neither deleting a user from WGM, nor restricting access removes a user from view in the People area of the Wiki. Access restriction is per-group anyway. Thinking about it, I could add the user to a unique group and then deny that group….
      
      But thanks again for your thoughts.
Keith

Thanks so much for this guide. I followed this first when I was installing Habari on OSX Lion Server.

From this page I was able to get a database set up with permissions but I still couldn’t connect to postgres. The “revisited” guide helped me by opening the listening port: http://www.mactasia.co.uk/revisited-using-postgresql-in-lion-server
James

Thanks for this informative and useful guide.

Unfortunately I needed to re-boot the server. Try as I might, I can’t get postgres running again.

Any thoughts that might help?
- Simon
  
  Hi James, have you seen this newer post?
  
  http://www.mactasia.co.uk/revisited-using-postgresql-in-lion-server
  
  If you have trouble getting postgres running again, it is likely due to permissions. You can either edit the permissions on the postgres folder, or run things as “root” user.
  - James
    
    Hi there Simon,
    
    Thanks for the pointer, it was an interesting read. I am running as root, and try as I might, I still get the dreaded “CANNOT_START_SERVICE_ERR”.
    
    I’m new to unix, so I’m not yet discouraged. This “permissions” thing is annoying as I can’t “see” the folders that I want to change permissions for.
    
    I have until Friday to prove to my employer that our mac mini server experiment is not a folly. Otherwise, the mac mini gets junked.
    - Simon
      
      It’s certainly not ready for the junk pile! :)
      
      In my experience, “CANNOT_START_SERVICE_ERR” is usually permissions.
      
      If you do a “Go To Folder” on /private/var/ can you see the pgsql folder?
      - James
        
        Hi there again Simon,
        
        Thanks for all your help, very generous of you and selfless too.
        
        I now have a “CANNOT_START_SERVICE_TIMEOUT_ERR”
        
        I’ve squandered 2 days now trying to start a RDMS service… This is getting ridiculous.
        
        I’ve given up and pulled the plug on the mac mini server and pulled a shitty PC out of storage. At least it works, between frequent reboots.
        
        Thanks again Simon for your help.
      - http://www.mactasia.co.uk/ Simon
        
        I’m sorry to hear that James. I’m sure changing the permissions on the pgsql folder would have solved your problem.
      - http://twitter.com/urbanxl Urban XL
        
        HEllo,
        I have the same issue. I can see the /private/var/pgsql folder. I Had no access but following “sudo chmod 777 /private/var/pgsql” command I have now an access. But … “sudo serveradmin start postgres” still provide me the “CANNOT_START_SERVICE_ERR” message … :-(
      - http://www.mactasia.co.uk/ Simon
        
        See my brand new video guide on setting up Postgres: http://www.mactasia.co.uk/video-how-to-configure-postgres-in-lion-server-10-7-4
Simon

I’m sorry to hear that James. I’m sure changing the permissions on the pgsql folder would have solved your problem.
Loopeelopez

This info has been a great help, thanks so much
Essene

“CANNOT_LOAD_BUNDLE_ERR”

HERE is an obvious answer for newbies (like myself) who suffer from the ”CANNOT_LOAD_BUNDLE_ERR”

Bearing in mind that I ran this as “root” -try this sudo command:

###############################
$ sudo serveradmin start postgres
###############################

If you get a result of: “RUNNING”

Then you can try the above login procedure described in this article…
Using PostgreSQL in Lion Server
by Simonhttp://www.mactasia.co.uk/using-postgresql-in-lion-server
It was very useful to first perform that diagnostic command to confirm that postgres was “RUNNING”

###############################$ sudo serveradmin start postgres###############################

Despite the fact that I was getting a ”CANNOT_LOAD_BUNDLE_ERR”
I was able to login using the “_postgres” user in combination with the admin password that I used when INSTALLING the Lion Server 10.7.3 for the first time.

USER: _postgres
PsWd: (admin -at the time of install-)

Also, it is a very common mistake to use “postgre” whithout the “s” SO BE SURE TO PUT THE “s’ in “postgres” for the start command.I found so mannnnny instances where the suggested command didn’t have the “s” in it!!
- http://www.mactasia.co.uk/ Simon
  
  Thank you very much for your input, Essene.
  
  Much appreciated.
Nothing

The problem is everything is “encoded” or “encrypted” and it is impossible to get the “textual” information of each site. I would like to port this wiki information to another wiki to be able to “get control” of my data, but it looks impossible because of the “encoding” situation of the wiki.
iantbaldwin

Thanks for this guide.

I followed your directions and unfortunately I am unable to input form data into my postgresql database. Postgres is running, everything there is okay; for whatever reason and information that I try to enter through forms and php doesn’t show up in my database.

Do I need to change the string for listen_addresses = to listen_addresses = 127.0.0.1 to tell it to search on the localhost? Or am I doing something else wrong?
- http://www.mactasia.co.uk/ Simon
  
  Hi, please try using my newer guide: http://www.mactasia.co.uk/revisited-using-postgresql-in-lion-server
  
  And see if it solves your problem.
  - Iantbaldwin
    
    I would try that, if I wasn’t getting a “CANNOT_START_SERVICE_ERR” when trying to start postgres using $ sudo serveradmin start postgres
    - http://www.mactasia.co.uk/ Simon
      
      If you browse to “/private/var/” can you access the pgsql folder?
      
      If not, change the permissions on that folder (and contents) so you can. That should allow the service to start.
      - iantbaldwin
        
        I ran $ sudo chmod -R 775 /private/var/pgsql
        No hiccups there. Then I tried to strat postgres again and got a different error. “CANNOT_START_SERVICE_TIMEOUT_ERR”
      - http://www.mactasia.co.uk/ Simon
        
        Try the fix posted by frameloss <a href="https://discussions.apple.com/thread/3336521?start=0&tstart=0"here. Hopefully that will fix your problem.
iantbaldwin

Please forgive my constant barrage of questions. Where can I find frameloss’s fixed post?
- http://www.mactasia.co.uk/ Simon
  
  Sorry, my link was dodgy. https://discussions.apple.com/thread/3336521?start=0&tstart=0
  - iantbaldwin
    
    Unfortunately, that link did not help me. As per my reading, I checked /private/var/pqsql_socket/ and alas the directory is empty! Is this correct or am I somehow missing my postgres socket?
    - http://www.mactasia.co.uk/ Simon
      
      Your best bet is going to be to check the log file: /Library/Logs/PostgreSQL.log
      
      Check for any errors as to why you might be getting these issues.
      - iantbaldwin
        
        The log file is completely empty. However I did find PostgreSQL logs in /Library/Logs/PostgreSQL/
        
        This log’s last entry is “database system is shut down”
      - http://www.mactasia.co.uk/ Simon
        
        Did you try changing the permissions on that logs folder, as per the post by “frameloss” on the Apple forum link I posted?
iantbaldwin

Yeah, I have read and write permissions on /Library/Log/ and /Library/Log/PostgreSQL/
iantbaldwin

When changing permissions using Finder, I am noticing that I have two ‘_postgres’ users listed. Could this also be a problem?
- http://www.mactasia.co.uk/ Simon
  
  It could be. Try removing both users, add both your administrator user and the _postgres user and apply read/write to the folder and enclosed files/folders.
  - iantbaldwin
    
    Tried that now. No luck. At this point, I’m considering yet another reinstall. My problem with a clean reinstall is that when I attempt to add data to a table in my database, nothing is added. Checked my logs, the connection was made but not data was “INSERTED INTO” my tables. Am I correct in thinking that my troubles are all linked? Should I try to install MySQL to avoid mucking up PostgreSQL again?
    - http://www.mactasia.co.uk/ Simon
      
      Try a new install following my guide for pgAdmin – many people have used it and seems to be more reliable.
      - iantbaldwin
        
        Reinstalled, backed up and, followed your new guide. As soon as I changed the .plist and tried to stop postgres it once again told me “CANNOT_STOP_SERVICE_ERR”. So I went back and changed the permissions on /var/pgsql /var/pgsql_socket and /library/logs. Postgres just doesn’t want to work for me
      - iantbaldwin
        
        Any other ideas?
      - http://www.mactasia.co.uk/ Simon
        
        OK, so I have done a fresh install. If you follow the guide here: http://www.mactasia.co.uk/revisited-using-postgresql-in-lion-server
        
        I ran in to the “CANNOT_STOP_SERVICE_ERR” error, and I think this was because of editing the org.postgresql.postgres.plist file BEFORE stopping the service. So I have edited that guide to include stopping the service before editing the listening port in that file.
        
        I was then able to connect using pgAdmin and create databases.
        
        I hope this helps.
      - iantbaldwin
        
        Yep, that took care of it. Thanks for your help Simon.
      - http://www.mactasia.co.uk/ Simon
        
        Pleased to help ;)
      - http://www.mactasia.co.uk/ Simon
        
        Out of interest, which version of Lion are you running?
      - iantbaldwin
        
        10.7.4
      - http://www.mactasia.co.uk/ Simon
        
        OK. The best I can advise at this stage is that I do a clean install and try and get Postgres going. Give me a couple of days though.