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