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:
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