Yansolo

Q: How to connect to embedded POSTGRESQL on Mac OSX Lion Server

Hi all,

 

I've upgraded my Mac OSX 10.6 server to 10.7 server 2 days ago.

 

My web sites databases are still under MySQL server cause it's still running after the upgrade.

 

I manage to migrate the databases to PostgreSQL, so i've enabled the wiki from the server.app to launch the PostgreSQL server.

 

I can see that it's now running through a ps -ef | grep sql and that it's listening on the unix domain socket 5432 with netstat -an | grep SQL in a terminal shell.

 

The problem is that I'am unable to connect to it.

 

I tried psql -U postgres, and installed the Navcat for postgreSQL Lite, but they failed to connec to the database server twith the same error :

psql: could not connect to server : Permission Denied

Is the server running locally and accepting connections on Unix domain socket "/var/pgsql_socket/.s.PGSQL.5432"?

 

It seems that there is a permission problem on the Unix Domain Socket /var/pgsql_socket/.s.PGSQL.5432.

It has a 0770 permission on it, but i don't know if it's relly the problem.

 

I've compared with MySQL server, which I am able to manage through Navcat for MySQL Lite without any problem, and the socket permission are 0777 on /var/mysql/mysql.sock.

 

Does somebody able to connect to the embedded PostgreSQL server ?

 

Or am I doing something wrong ?

 

Thanks.

Posted on Jul 22, 2011 5:59 AM

Close

Q: How to connect to embedded POSTGRESQL on Mac OSX Lion Server

  • All replies
  • Helpful answers

first Previous Page 3 of 3
  • by Andy MPC,

    Andy MPC Andy MPC Sep 7, 2011 2:57 PM in response to Evian
    Level 1 (0 points)
    Sep 7, 2011 2:57 PM in response to Evian

    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

  • by Evian,

    Evian Evian Sep 7, 2011 4:26 PM in response to Andy MPC
    Level 1 (0 points)
    Sep 7, 2011 4:26 PM in response to Andy MPC

    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

  • by Andy MPC,

    Andy MPC Andy MPC Sep 9, 2011 12:24 PM in response to Evian
    Level 1 (0 points)
    Sep 9, 2011 12:24 PM in response to Evian

    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

  • by Evian,

    Evian Evian Sep 9, 2011 2:35 PM in response to Andy MPC
    Level 1 (0 points)
    Sep 9, 2011 2:35 PM in response to Andy MPC

    Hi Andy

     

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

     

    Julio

  • by Evian,

    Evian Evian Sep 9, 2011 2:42 PM in response to Andy MPC
    Level 1 (0 points)
    Sep 9, 2011 2:42 PM in response to Andy MPC

    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

  • by Andy MPC,

    Andy MPC Andy MPC Sep 9, 2011 3:12 PM in response to Evian
    Level 1 (0 points)
    Sep 9, 2011 3:12 PM in response to Evian

    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

  • by Androidi,

    Androidi Androidi Sep 10, 2011 2:57 AM in response to Andy MPC
    Level 1 (0 points)
    Sep 10, 2011 2:57 AM in response to Andy MPC

    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

  • by Evian,

    Evian Evian Sep 10, 2011 8:21 AM in response to Androidi
    Level 1 (0 points)
    Sep 10, 2011 8:21 AM in response to Androidi

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

  • by Evian,

    Evian Evian Sep 10, 2011 9:36 AM in response to Evian
    Level 1 (0 points)
    Sep 10, 2011 9:36 AM in response to Evian

    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.

  • by Mark23,

    Mark23 Mark23 Apr 25, 2012 11:13 AM in response to Andy MPC
    Level 3 (975 points)
    Apr 25, 2012 11:13 AM in response to Andy MPC

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

  • by Tarte TinTin,

    Tarte TinTin Tarte TinTin Oct 18, 2012 2:59 PM in response to Andy MPC
    Level 1 (5 points)
    Oct 18, 2012 2:59 PM in response to Andy MPC

    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.

  • by matthewfromdanvers,

    matthewfromdanvers matthewfromdanvers Apr 5, 2013 11:36 AM in response to Yansolo
    Level 1 (0 points)
    Apr 5, 2013 11:36 AM in response to Yansolo

    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

first Previous Page 3 of 3