Skip to content

August 6, 2011


Using Embedded Lion Server PostgreSQL Database

by Andreas Schaefer

One of the new features of Mac OS X Lion Server is that Apple replaced MySQL with PostgreSQL as internal database server. The drawback is that it is not easy accessible by default. Here I want to show you what to do and how you can manage it.

Attention: if you want to use the PostgreSQL server from EnterpriseDB then I would strongly recommend to make a backup of your server because if it fails the uninstallation might corrupt your embedded PostgreSQL server. Yesterday, August 4th 2011, I installed Enterprise DB’s server and because it did not start I uninstall it but then my embedded server was corrupted as well. Now this might be just an issue with the startup script but nevertheless I lost the DB. It seems that the installation from Enterprise DB is not compatible with Lion and so I wouldn’t use until they provide one that is. Their own recommendation is to use the embedded server instead.

When you installed the Lion Server then you can use ps -ef in order to see if PostgreSQL is running:

bash-3.2# ps -ef | grep post
216   468     1   0  8:33AM ??         0:00.13 /usr/bin/postgres -D /var/pgsql -c listen_addresses= -c log_connections=on -c log_directory=/Library/Logs -c log_filename=PostgreSQL.log -c log_lock_waits=on -c log_statement=ddl -c log_line_prefix=%t  -c logging_collector=on -c unix_socket_directory=/var/pgsql_socket -c unix_socket_group=_postgres -c unix_socket_permissions=0770

You can get some more information about the service with that:

sudo serveradmin list

and some more details:

sudo serveradmin fullstatus postgres
postgres:dataDirHasBeenInitialized = yes
postgres:PG_VERSION = "9.0.4"
postgres:dataDir = "/var/pgsql"
postgres:postgresIsResponding = yes
postgres:dataDirIsDirectory = yes
postgres:PGserverVersion = 90004
postgres:dataDirExists = yes
postgres:setStateVersion = 1
postgres:state = "RUNNING"

So far so good. Now if we try to connect to the DB using telnet:

telnet localhost 5432

we see that there is no socket listening which means that PGAdmin cannot be used to administer the server nor can it be used by a JDBC driver. If you look above in the process description you will see an option called listen_addresses which is empty. Maybe if we could set our server address there we might be able to connect.

Initially I found the original PostgreSQL server configuration file under /var/pgsql/postgresql.conf but that did not do the trick. Knowing a little bit about Mac internals I started to look for a file inside /System/Library with the name postgres in it. So I found the file: /System/Library/LaunchDaemons/org.postgresql.postgres.plist. This file has an array of properties including the listen addresses:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE plist PUBLIC "-//Apple//DTD PLIST 1.0//EN" "">
<plist version="1.0">
		<string>log_line_prefix=%t </string>

Adding our IP address there:


saving the file and restarting the server:

sudo serveradmin stop postgres
sudo serveradmin start postgres

did the trick. Now I could download and install PGAdmin and connect to the server using the user _postgres and my password (the original administrator):

PGAdmin with Local PostgreSQL.png

Cheers – Andy Schaefer

Read more from Mac
  1. Stefan
    Aug 6 2011

    Thanks a lot for your small and comprehensible how-to. It saved me another 2 hours (or more) of searching! :)

    Have a nice evening.

  2. Victor
    Aug 29 2011

    Nice tips !
    But still cannot access to Postgre with user “_postgres” and admin password. Any suggestions how to change this password ?

    • Andreas Schaefer
      Aug 29 2011

      Can you access postgres with telnet (telnet localhost 5432) ? If not then you did not change the configuration file correct (listening-address=). If you got passed that then you would need to use the password of the administrator that setup the Lion box I would think (not sure if it would work with any administrator).

      – Andy

  3. Victor
    Aug 29 2011

    Problem was phpPgAdmin, something shall be changed in configuration file. With PGAdmin everything works Ok !
    Thanks !

    • Andreas Schaefer
      Aug 29 2011

      Great news. I heard that some people had success with phpPgAdmin but I never tried it.

      – Andy

      BTW I am sorry but I removed your URL because I don’t speak Russian and there so much spam lately that I just wanted to be on the save side.

  4. Aaron
    Aug 30 2011

    Unfortunately I did the same thing as you and I think the embedded PostgreSQL server is corrupted. I tried the steps you listed here, but I can’t get PostgreSQL to even start. I get CANNOT_START_SERVICE_ERR. What did you do to fix it, or are these instructions what you did?

    • Andreas Schaefer
      Aug 30 2011

      Did you try “sudo serveradmin list” ? Did you find “postgres” ? If so, did you try “sudo serveradmin start postgres” ? If so please check the error log (Applications -> Utilities -> Console) and see what it reports to you.

      – Andy

      • Aaron
        Aug 30 2011

        the list does show me postgres is there but starting postgres gives me an error “CANNOT_START_SERVICE_ERR”

        When I check Console I get the following:
        sudo: mctadmin : TTY=ttys000 ; PWD=/ ; USER=root ; COMMAND=/urs/sbin/serveradmin start postgres
        serveradmin: posting dist not; DEBUG; Initializing DeviceManagerDaemon with ports 3320,3321,3322,3323 (physmem = 4GB)
        DEBUG: Making sure Rails is configured properly
        DEBUG: Running rake command: /usr/bin/rake db:migrate
        rake aborted!
        could not connect to server: No such file or directory
        Is the server running locally and accepting connections on Unix domain socket “/var/pgsql_socket/.s.PGSQL.5432″?
        (See full trace by running task with –trace)
        (in /usr/share/devicemgr/backend)
        /usr/share/devicemgr/backend/devicemgrd:109:in ‘rake': Rake command failed with 256 (RuntimeError)
        from /usr/share/devicemgr/backend/devicemgrd:103:in ‘setupRails’
        from /usr/share/devicemgr/backend/devicemgrd:33:in ‘run’
        from /usr/share/devicemgr/backend/devicemgrd:131

        Thank you so much for any help you might be able to give. Boy do I wish I hadn’t done this!

        • Andreas Schaefer
          Aug 30 2011


          Can you try to shut it down? If that doesn’t work you might want to blow away the /var/pgsql_socket directory to avoid an outdated lock.

          This is strange because the Lion Server comes with Postgres and it uses it in order to store server configuration data.

          – Andy

          • Aaron
            Aug 30 2011

            I did all that and still no go. I think I’m just going to start from scratch with a clean install. Thanks for your help, though!

          • Andreas Schaefer
            Aug 30 2011


            If you do so I would suggest you check the “postgres” service with serveradmin right away. After that shut it down, update the listener address in the postgres configuration file and start it up again. Use telnet first, then pgAdmin3 to test if you can access before you do anything else. I hope you have more luck then.

            BTW I can to scratch my entire Server installation as well because of Mail server issues. So I know your pain.

            – Andy

Comments are closed.