How to connect to embedded POSTGRESQL on Mac OSX Lion Server

First question: what are you actually doing with postgres?

Did you check with "serveradmin" that postgres is listed and running (serveradmin list, serveradmin fullstatus postgres)

Did you enter the listener address in the progres configuration file?

If yes, did you try to connect using telnet: telnet localhost 5432

Finally what is not working ?

- Andy

Hi Andy

Thanks for taking the time. Below my answers:

First question: what are you actually doing with postgres?

I would like to backup and restore addressbook data but not at the client level

Did you check with "serveradmin" that postgres is listed and running (serveradmin list, serveradmin fullstatus postgres)

Yes I did. It's running. All lion services based on Postgres working as expected

Did you enter the listener address in the progres configuration file?

Yes I did. (including restarting the database)

If yes, did you try to connect using telnet: telnet localhost 5432

Yes I did. Port still closed

Finally what is not working ?

- Andy

Hi Evian

Did you restart postgres after changing the configuration file (serveradmin stop postgres, serveradmin start postgres)?

Keep in mind that you need to make changes to Postgres as root (aka using "sudo"). Here is my configuration I use on my laptop:

<?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=127.0.0.1</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_line_prefix=%t </string>

<string>-c</string>

<string>log_lock_waits=on</string>

<string>-c</string>

<string>log_statement=ddl</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>

Use a text comparison tool to see if there is anything incorrect with your settings. Sometimes a little type can make all the difference.

If you find any changes please fix it, restart the service and try to telnet again.

- Andy

Hi Andy

Now it is working. Thanks. Definately it was a typing error.

Julio

Hi Andy

"One more thing". Any idea how to connect to the PostgreSQL database from a mac connected to the network and not from the server itself?

Thanks

I would think that you just need to use the "public" IP address within your network (most likely 192.168.X.X if behind a firewall with NAT) and then if you have an internal firewall you must open that port (try telnet <IP Address> 5432).

Attention: I wouldn't expose the DB to the Internet if not really necessary. Another way is to keep your settings where the DB is only bound to the localhost IP address (127.0.0.1) and then use VNC to connect to the server and manage it this way.

- Andy

Evian, Andy, you can always set your DB to listen for both the 127.0.0.1 address, and all 192.168.*.* addresses. The 192.168.*.* address family is specified to be a LAN space, so an address like that is never exposed to the internet.

To access your DB from all local network computers, just specify listen_addresses=*

And update your pg_hba.conf file (/var/pgsql/pg_hba.conf) to contain the line:

host all all 192.168.0.0/16 trust

(If your LAN is in 10.*.*.* space, just use e.g. 10.1.1.0/24 for 10.1.1.* addresses)

Check out: http://www.postgresql.org/docs/9.0/static/auth-pg-hba-conf.html

for more detailed info and for more advanced setup.

And, last but not least - always remember to use strong enough passwords! 😉

Happy hacking,

/Sami

Hi Sami

I have now a huge problem.I did - I think - what you said but didn't work. So I restored the original files. Now the database does not want to start. I get the error message "CANNOT_START_SERVICE_TIMEOUT". What do I do now? Please do not answer me I need to reinstall the server ....

Hi Sami

I found the error. My fault. Some permissions were changed in the /var/pgsql folder. I repaired them . Now it is working again.

Thank you Andy!!!!!!!!!!!

When I updated from Lion to Mountain Lion, and more recently when I applied the latest Moutain Lion patch I had to update the /Library/Server/PostgreSQL/Config/org.postgresql.postgres.plist as Andy outlines above.

I am using Mountain Lion server and in order to change the listen_addresses settings I need to use the "serveradmin settings" command.

If you run "serveradmin settings postgres" it will give you all the settings. I changed my listen address by adding the IP of my server as follows.

serveradmin settings postgres:listen_addresses = "127.0.0.1,192.168.1.100,::1"

I then restarted the postgres server:

serveradmin stop postgres

serveradmin start postgres