Using Embedded Lion Server PostgreSQL Database
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
...
pcastlibrary
postgres
radius
...
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" "http://www.apple.com/DTDs/PropertyList-1.0.dtd">
<plist version="1.0">
<dict>
<key>Disabled</key>
<true/>
<key>GroupName</key>
<string>_postgres</string>
<key>Label</key>
<string>org.postgresql.postgres</string>
<key>OnDemand</key>
<false/>
<key>ProgramArguments</key>
<array>
<string>/usr/bin/postgres</string>
<string>-D</string>
<string>/var/pgsql</string>
<string>-c</string>
<string>listen_addresses=</string>
<string>-c</string>
<string>log_connections=on</string>
<string>-c</string>
<string>log_directory=/Library/Logs</string>
<string>-c</string>
<string>log_filename=PostgreSQL.log</string>
<string>-c</string>
<string>log_lock_waits=on</string>
<string>-c</string>
<string>log_statement=ddl</string>
<string>-c</string>
<string>log_line_prefix=%t </string>
<string>-c</string>
<string>logging_collector=on</string>
<string>-c</string>
<string>unix_socket_directory=/var/pgsql_socket</string>
<string>-c</string>
<string>unix_socket_group=_postgres</string>
<string>-c</string>
<string>unix_socket_permissions=0770</string>
</array>
<key>UserName</key>
<string>_postgres</string>
</dict>
</plist>
Adding our IP address there:
listen_addresses=127.0.0.1
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):
Cheers – Andy Schaefer
Comments are closed.
Thanks a lot for your small and comprehensible how-to. It saved me another 2 hours (or more) of searching!
Have a nice evening.
Nice tips !
But still cannot access to Postgre with user “_postgres” and admin password. Any suggestions how to change this password ?
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
Problem was phpPgAdmin, something shall be changed in configuration file. With PGAdmin everything works Ok !
Thanks !
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.
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?
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
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
com.apple.devicemanager; 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!
Aron
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
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!
Aron
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