Revisited: Using PostgreSQL in Lion Server

When Lion was first released we looked at using the embedded PostgreSQL with phpPgAdmin. Some people (including myself) have been seeing some issues with the setup used in that article, so in this one I intend to revisit using PostgreSQL, this time using pgAdmin.

 

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

Let’s start by opening up Server.app and enabling the web service, as in most cases people want to use postgreSQL to run a database for their web install. In this example, I want to do just that! I’ve also gone ahead and enabled PHP web applications. (Please note that this step is not important to the running of PostgreSQL, but will be required for your web applications that need PostgreSQL).

Next we need to download pgAdmin. Download the latest Mac release, open up the DMG and drop it in your Applications folder (you can also link it in your dock to make it more accessible).

Now lets check a few things before firing up postgreSQL. Open up Terminal and type:

sudo serveradmin list

This will display all of the available services of Lion Server. Check that ‘postgres’ is in there and type in the next command.

Next type in:

sudo serveradmin fullstatus postgres

This will tell you the current status of the postgres service. If you have already been using the Server.app, then it’s possible the state will already be ‘running’ (like in my screenshot).

If not, then you can start it using the following command:

sudo serveradmin start postgres

This should result in the postgres state being ‘running’.

If you were now to open pgAdmin and try to connect it will still fail. This is because postgres is not listening to connections on localhost. To fix this, we need to edit a file. But first, make sure Postgres is stopped by typing in to Terminal:

sudo serveradmin stop postgres

And then edit the following file:

/System/Library/LaunchDaemons/org.postgresql.postgres.plist

Search for the line:

<string>listen_addresses=</string>

Add in our localhost address:

<string>listen_addresses=127.0.0.1</string>

Now lets stop and start postgres. In Terminal:

sudo serveradmin stop postgres

sudo serveradmin start postgres

Now lets open up pgAdmin and connect to postgreSQL. Click the connect icon in the top left corner and enter the settings for your setup. Enter a descriptive name, Host (which we’ve just opened as 127.0.0.1), Username and Password (by default these are ‘_postgres’ and your administrator password).

Voila! You are connected and can create new databases.

If you experience issues with permissions or accessing the pg_hba.conf file, you may need to change the permissions on the pgsql folder in /private/var/ – Get Info on the pgsql folder and add your current administrative user to have read/write access.

Print Friendly

About Simon

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.

Print Friendly

  • Loic

    Very good news Simon !!!!

    I followed all of these steps and finally installed spip. WAW.

    Unfortunately, this moment of happiness was short. As I create a first userid for spip (at the end of the installing tables process), the system said “You are now ready to connect”, I entered the userid I had just created and the system said “This user is not registered”. I then took a look at my spip database on pgadmin and confirmed all the spip tables was successfully installed but I couldn’t see any registration. Tables are empty, the userid I created didn’t seem to have been registrated.

    Anyway I’m convinced I had made a significant step forward, I’m gonna look now more deeply in the database and try to understand why my userid have not been registered.

    Thanks a lot for your help !

    • http://www.mactasia.co.uk Simon

      I’m pleased I was able to help a bit, Loic.

      Best of luck with the rest of it! SPIP worked for me after install, so I don’t know what’s going wrong for you now!

      • Loic

        Thanks for everything and bravo for this extremely pedagogical step-by-step article.

        I guess I’ll have my bug fixed soon. (I wonder if it’s not an .htaccess file problem, as I had by the end of the installation process an .htaccess error message : spip said that it cannot read it).

        Good luck to you too with your mac rhapsody work of art!

  • ASH

    After editing the .plist file, when I connect I get an ‘Access denied Fatal: no pg_hba.conf entry for host…” and details of how to edit the file in pgAdmin III. But I can’t find the pg_hba.conf file anywhere. Any ideas?

    • Simon

      Hi Ash, the file is located in:

      /private/var/pgsql/

      You will likely need to be logged in as ‘root’ or change permissions on the ‘pgsql’ folder so you can access it.

  • Tony Simek

    Simon,

    As with all of your articles, VERY WELL DONE. You seem to have both timely and helpful guidance. Your articles always get me at least 95% of the way there to any task or obstacle so I really appreciate it!! KEEP up the great writing!

    • Simon

      Thanks Tony. Please don’t hesitate to ask if you need a guide for anything else.

      • Tony Simek

        Not being a superstar with file permissions and ownership in the OSX world yet, it would be really helpful if you authored anything on taking ownership etc of system files. For example, in this article, the editing of the plist file was a challenge because I was not the owner. I eventually got it, but there has to be an easier way, and I suspect you have an idea on that. In your walkthrough it did not appear the file was locked, but in my case it was.

        Thanks again, your writing is very good!

        • Simon

          OK Tony. I’ll bear it in mind for my next article. As a tip, if you have problems accessing any files, enable and log in as the ‘root’ user.

  • alek

    I followed all of these steps but….

    host:~ one$ /System/Library/LaunchDaemons/org.postgresql.postgres.plist
    -bash: /System/Library/LaunchDaemons/org.postgresql.postgres.plist: Permission denied

    step: 02

    sudo nano System/Library/LaunchDaemons/org.postgresql.postgres.plist

    but…..this file is empty !!!

    please help me

    Sorry for my english and thanks for advance

    Alex

  • alek

    sudo nano /private/var/pgsql/

    but…..this file is empty too !!!

    • Simon

      Hi Alek, are you logged in as ‘root’ ? Might be worth trying if you are having permission problems.

  • alek

    hi simon

    logged ?

    1. Lion server logged ok.

    2. open terminal.

    but more logged ?

    • Simon

      Alek, System Preferences > Users & Groups > Login Options > Join > Open Directory Utility > Edit > Enable Root User > Set a Password > Log out and Log in as “root” and the password you set. At the log in window the User “root” will not show, but you can login using “Other”.

      • Anders

        or just enter “sudo passwd root” in a terminal window if you are using an administrator account (i.e. the one you created when you installed Mac OS X). Note that neither your password (when sudo asks for it) nor the root password (when passwd asks to enter it twice) will be echoed when typing.

  • alek

    Im running lion server

    • Simon

      When Directory Utility is open, make sure the window is selected. And then go up to the Edit menu along the top. There should be the item “Enable Root User”.

  • alek

    ….along the top …. !!!! yes !!!

    logout and login : ” powerDown and powerUp the machine machine is ok ?

    I am sorry for my big ignorance, Simon

  • alek

    I can’t see root user on login !

    • Simon

      Alek, At the login window, is there your user account and one that says “other”? Click other and enter “root” as the username and the password that you set in Directory Utility.

  • alek

    host:~ root# /System/Library/LaunchDaemons/org.postgresql.postgres.plist
    -sh: /System/Library/LaunchDaemons/org.postgresql.postgres.plist: Permission denied
    host:~ root#

    ??????????????

  • alek

    no other icon on login !!!

    Interminal : sudo su -

    • Simon

      Then the root user must not be enabled. Go back to my steps on enabling the root user and make sure you set a password. Once root is enabled “Other’ will appear as an option on the login screen.

  • alek

    ok Looged root but…same….

    Last login: Tue Jan 31 08:39:00 on console
    host:~ root# /System/Library/LaunchDaemons/org.postgresql.postgres.plist
    -sh: /System/Library/LaunchDaemons/org.postgresql.postgres.plist: Permission denied
    host:~ root#

  • alek

    Ok. No problem.
    Download and install Postgre 9.1. + pg admin atach.
    No problem.
    but …in terminal the version no update . PG version is 9.0.4 !

    Is not possible update Postgre to 9.1 ?
    Is necesary remove 9.0.4 before ?

    • Simon

      Hi Alek, Sounds like you are making progress? I personally would not update the inbuilt PostgreSQL – this should (if it needs it) be taken care of by Apple updates.

      If you are still having problems, I would do a fresh install and start again.

  • alek

    Last login: Tue Jan 31 13:46:27 on console
    host:~ One$ /System/Library/LaunchDaemons/org.postgresql.postgres.plist
    -bash: /System/Library/LaunchDaemons/org.postgresql.postgres.plist: Permission denied
    host:~ One$

    Ok. I can’t go on like this.

    Your tutorial have a error.

  • alek

    host:~ root# /System/Library/LaunchDaemons/org.postgresql.postgres.plist
    -sh: /System/Library/LaunchDaemons/org.postgresql.postgres.plist: Permission denied
    host:~ root#

    • Simon

      Alek, Others have reported that this has worked for them, so i’m not so sure it is a tutorial error.

      What are you trying to do to the file to be getting these Terminal errors? Simply navigate to /System/Library/LaunchDaemons/ using Finder and right click the file

      org.postgresql.postgres.plist

      and click Edit with Text Edit (or whatever text editor you use). Can you open the file? Can you edit and save the line as per the guide?

      There must be something very simple going wrong here.

      • Simon

        If you can’t edit the .plist file in the LaunchDaemons folder – drag it to your desktop and edit that version. Save it, and then drag it back overwriting the one in there.

  • alek

    thanks again Simon.Fresh install and start again now…

  • alek

    Last login: Tue Jan 31 17:11:16 on ttys000
    servidor:~ one$ sudo serveradmin stop postgres
    postgres:error = “CANNOT_STOP_SERVICE_ERR”
    servidor:~ one$

    ok. Edit the file org.postgresql.postgres.plist …but,,,now…

    Last login: Tue Jan 31 17:11:16 on ttys000
    servidor:~ one$ sudo serveradmin stop postgres
    postgres:error = “CANNOT_STOP_SERVICE_ERR”
    servidor:~ one$

    • Simon

      Great, we’re making progress and this one is an error I’ve seen before. Enable the root user and login as root as per my instructions earlier. Stop Postgres logged in as root, edit the .plist file and start Postgres. This should work!

  • alek

    uf…
    postgresql could not connect to server : operation timed out is the server running on host 192.168.1.34 and accepting tcp/ip connections on por 5432?

  • alek

    Is here:

    http://www.mactasia.co.uk/using-postgresql-in-lion-server

    I think is more easy ?

    is posible Simon ?

    • Simon

      Hi Alek, sure. The other tutorial is just another way of getting Postgres up and running, so there is no harm in trying it. Let me know how you get on.

  • alek

    ops….

    Login disallowed for security reasons.

  • alek

    Step: 01 :

    Extract phpPgAdmin and open up “conf\config.inc.php”.

    Change:

    $conf['servers'][0]['host'] = ”;

    to

    $conf['servers'][0]['host'] = ‘localhost';

    step : 02
    Edit “conf/config.inc.php” and search through the file for:

    view sourceprint?
    1.
    $conf['extra_login_security'] = true;

    but….now

    Login failed

    brrrrrrrrrdhgcvwkhcvewkjhqcvw !!!!!

    • Simon

      Alek, Where have you got these steps from? Is this supposed to be on the phpPgAdmin post? Please email me contact [AT] mactasia.co.uk for any further assistance.

  • alek

    more info…

    alek:~ one$ /Applications/Utilities/Network\ Utility.app/Contents/Resources/stroke 127.0.0.1 5432 5432
    Port Scanning host: 127.0.0.1

    Open TCP Port: 5432 postgresql
    alek:~ one$ device_management=# create user usuario with password ‘contrasena’ create user;
    -bash: create: command not found
    alek:~ one$ psql -U _postgres -d device_management
    psql: could not connect to server: Permission denied
    Is the server running locally and accepting
    connections on Unix domain socket “/var/pgsql_socket/.s.PGSQL.5432″?
    alek:~ one$

  • alek

    hi simon

    i follow your step but when fail…

    here:

    http://twigstechtips.blogspot.com/2010/07/postgres-sql-installing-postgres-sql.html

    please help me Simon !

    • Simon

      Those steps are for Windows. I don’t understand where you failed to start following steps somewhere else!? Please post your comment on the correct article and describe where you are failing at in the guide.

  • alek
  • alek

    ok. this is the main fall:

    alek:~ one$ psql -U _postgres -d device_management
    psql: could not connect to server: Permission denied
    Is the server running locally and accepting
    connections on Unix domain socket “/var/pgsql_socket/.s.PGSQL.5432″?

    • Simon

      I’m afraid I can’t help with someone else’s article. I suggest you post on their blog for support with their guide. I only take responsibility for my own :)

  • Jake Macalister

    Hi Simon,
    I’m connecting to my server with pgAdmin3 however upon login I receive 6 error messages

    ERROR: column “datconfig” does not exist
    LINE 1: …b.dattablespace AS spcoid, spcname, datallowconn, datconfig,…

    5 x Column not found in pgSet: rolconfig

    Then listed under my server is Databases (0) any ideas what I’m doing wrong?

    • Simon

      Hi Jake, It’s sounds as if one of your default databases or PostgreSQL is not right. If at all possible, I would reinstall the server from scratch and try again. Are you running the embedded version of PostgreSQL, and the latest release of pgAdmin3?

  • Jake

    Hi Simon,
    Fresh Lion Server install 2 days ago OS X 10.7.3, pgAdmin3 1.8.4.

    I think perhaps I’m not connecting to the right database?

    I’m trying to get into the wiki content db to remove a couple of corrupted entries, I was messing around with the wiki web creator & changed/removed owners mid save, now I have font links to 4 wikis that have no back end & return server errors when trying to access them.

    Apart from that everything is working great & I’m very pleased with it.

    • Simon

      Hi Jake, I think the Wiki database is the one named Collab?

  • Jake Macalister

    I found this which enabled me to execute SQL from terminal

    http://krypted.com/iphone/working-with-postgres-from-the-command-line-in-lion-server/

    But then I retried your guide & connected successfully with a 30 day trial of Navicat

    I searched my Collab wiki database & removed all UID entries for my corrupted wikis & then deleted the corrupted wikis themselves.

    Everything is running perfectly now

    :)

  • http://twitter.com/hplogsdon hplogsdon

    This is a little late, but the unix socket does not have read/write permissions for everyone (world).

    If you are getting a “psql: could not connect to server: Permission denied” error when trying to run “psql” from the Terminal, or from some other application, you need to edit the startup plist:

    From Terminal.app

    sudo vim /System/Library/LaunchDaemons/org.postgresql.postgres.plist

    And make sure the listen_address line is:

    listen_addresses=127.0.0.1

    and at the bottom:

    unix_socket_permissions=0777

    and then __CAREFULLY__, enable read permissions on the directory the socket file exists in:

    sudo chmod 755 /private/var/pgsql_socket

    This will allow ALL users to read and write to the socket (which gives them permission to read and write to the database server)

    And not to be a dick, but if you are unsure of how to be running these commands, you might want to rethink your decision to be running OS X Server, and the reasons you want to be running a RDMS in the first place

    • http://www.mactasia.co.uk/ Simon

      Thanks for the info and explanation, hplogsdon.

  • Georg Egger

    Well i can connect, you NEED to do sudo nano on the plist file else you can’t restart postgres due to access rights… this had me up for some time…so now i can connect..but the most important item..databases..shows 0 everything else is like on the screen while connecting..any ideas ?

    • http://www.mactasia.co.uk/ Simon

      Hi Georg, I’m not sure I understand what you mean. Can you provide a screenshot? Did you follow the info below regarding permissions?

      People are reporting mixed results. I may have a look at rewriting the guide to include resolutions to permissions.

  • Fab10

    Thank you very much! This worked perfectly! Hugs!

  • JimmyBoyFromOz

    I have a mac mini server in a cupboard running itunes 24 x 7 as a media server that powers iDevices and three apple TVs :-)
    I am to interested in wiki but trying to learn databases and data warehouses etc.
    Can I access the postgres server from one of my macs on the same network do I need to play with firewall settings on the mini etc?
    thanks and great article thank you

    • http://www.mactasia.co.uk/ Simon

      Hi Jimmy, You shouldn’t need to change any firewall settings. The easiest option would be to enable Screen Sharing on your server (System Prefs > Sharing > Screen Sharing). You could use either the inbuilt screen sharing facility in OS X to connect from another machine on your network, or the Apple Remote Desktop software.

      Alternatively, you could install something like phpPgAdmin (search for it on this website) on your server and then connect to that via a web browser from any machine on your network.