1 2 3 Previous Next 41 Replies Latest reply: Apr 5, 2013 11:36 AM by matthewfromdanvers Go to original post
  • 30. Re: How to connect to embedded POSTGRESQL on Mac OSX Lion Server
    Andy MPC Level 1 Level 1 (0 points)

    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

  • 31. Re: How to connect to embedded POSTGRESQL on Mac OSX Lion Server
    Evian Level 1 Level 1 (0 points)

    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

  • 32. Re: How to connect to embedded POSTGRESQL on Mac OSX Lion Server
    Andy MPC Level 1 Level 1 (0 points)

    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

  • 33. Re: How to connect to embedded POSTGRESQL on Mac OSX Lion Server
    Evian Level 1 Level 1 (0 points)

    Hi Andy

     

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

     

    Julio

  • 34. Re: How to connect to embedded POSTGRESQL on Mac OSX Lion Server
    Evian Level 1 Level 1 (0 points)

    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

  • 35. Re: How to connect to embedded POSTGRESQL on Mac OSX Lion Server
    Andy MPC Level 1 Level 1 (0 points)

    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

  • 36. Re: How to connect to embedded POSTGRESQL on Mac OSX Lion Server
    Androidi Level 1 Level 1 (0 points)

    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

  • 37. Re: How to connect to embedded POSTGRESQL on Mac OSX Lion Server
    Evian Level 1 Level 1 (0 points)

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

  • 38. Re: How to connect to embedded POSTGRESQL on Mac OSX Lion Server
    Evian Level 1 Level 1 (0 points)

    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.

  • 39. Re: How to connect to embedded POSTGRESQL on Mac OSX Lion Server
    Mark23 Level 3 Level 3 (975 points)

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

  • 40. Re: How to connect to embedded POSTGRESQL on Mac OSX Lion Server
    Tarte TinTin Level 1 Level 1 (5 points)

    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.

  • 41. Re: How to connect to embedded POSTGRESQL on Mac OSX Lion Server
    matthewfromdanvers Level 1 Level 1 (0 points)

    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

1 2 3 Previous Next