Kingdou

Q: start postgres service failed

i'm sure i had made the embedded postgres worked  several days ago ,but now it just stopped working

 

sh-3.2# serveradmin stop postgres

postgres:state = "STOPPED"

sh-3.2# serveradmin start postgres

postgres:error = "CANNOT_START_SERVICE_TIMEOUT_ERR"

 

 

could anyone tell me how to figure out the problem?

MacBook Pro, Mac OS X (10.7.1)

Posted on Sep 20, 2011 12:30 AM

Close

Q: start postgres service failed

  • All replies
  • Helpful answers

  • by Kingdou,

    Kingdou Kingdou Sep 20, 2011 12:40 AM in response to Kingdou
    Level 1 (0 points)
    Sep 20, 2011 12:40 AM in response to Kingdou

    sh-3.2# serveradmin fullstatus postgres

    postgres:dataDirHasBeenInitialized = yes

    postgres:PG_VERSION = "9.0.4"

    postgres:dataDir = "/var/pgsql"

    postgres:postgresIsResponding = no   <<-------------

    postgres:dataDirIsDirectory = yes

    postgres:PGserverVersion = 0

    postgres:dataDirExists = yes

    postgres:setStateVersion = 1

    postgres:state = "RUNNING"

  • by Kingdou,

    Kingdou Kingdou Sep 20, 2011 1:03 AM in response to Kingdou
    Level 1 (0 points)
    Sep 20, 2011 1:03 AM in response to Kingdou

    sh-3.2# cat org.postgresql.postgres.plist

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

  • by Kingdou,

    Kingdou Kingdou Sep 20, 2011 1:05 AM in response to Kingdou
    Level 1 (0 points)
    Sep 20, 2011 1:05 AM in response to Kingdou

    here is the content of pg_hba.conf

     

    # "local" is for Unix domain socket connections only

    local   all             all                                     trust

    # IPv4 local connections:

    host    all             all             127.0.0.1/32            trust

    # IPv6 local connections:

    host    all             all             ::1/128                 trust

  • by Mandrid,

    Mandrid Mandrid Sep 24, 2011 6:58 PM in response to Kingdou
    Level 1 (0 points)
    Sep 24, 2011 6:58 PM in response to Kingdou

    I just mucking with the embedded Postgres instance myself, doing essentially what it appears you where/are. So I think we might of had the same issue.

     

    Postgres needs an administrator account to start it, however you maynot have permission on your administrator account for the /var/pgsql directory which is owned by _postgres. So if you try the 'serveradmin start postgres' it will fail.

     

    I got a message when I tried to restart the service as _postgres saying it didn't have permission. So I added my administrator account to the /var/pgsql folder and it started fine.

     

    Its probably best to revert any permission changes you make if you try my solution for security sake

  • by quarac,

    quarac quarac Sep 29, 2011 12:01 PM in response to Kingdou
    Level 1 (0 points)
    Sep 29, 2011 12:01 PM in response to Kingdou

    I have the same error, tried the Mandrid solution but didn't work. I have exactly the same configuration as Kingdou except the listen_addresses=127.0.0.1 ( I saw this here ) I also tried to delete the pgsql_socket but the problem persist...

    One day ago the server was running ok, I can connect with Navicat and I created the database tables without problem but now the server appears to be off. If I make a "ps -ef | postgres", there's no proccess running, although the command "serveradmin fullstatus postgres" says RUNNING, like Kingdou post number 2.

     

    ¿Any solution?

     

    ¿reinstall the server? No please!!!

  • by Switon,

    Switon Switon Oct 9, 2011 10:27 AM in response to Mandrid
    Level 1 (10 points)
    Oct 9, 2011 10:27 AM in response to Mandrid

    Hi Mandrid,

     

    Sorry for my naivete, but when you say you added your administrator accout to the /var/pgsql folder, what exactly does this mean?

     

    Could you please give commandline commands for accomplishing this?

     

    Thanks,

    Switon

  • by Switon,

    Switon Switon Oct 9, 2011 1:04 PM in response to Switon
    Level 1 (10 points)
    Oct 9, 2011 1:04 PM in response to Switon

    just a little more information...

     

    I stopped the devicemgr via: sudo serveradmin stop devicemgr

    I stopped postgres (it wasn't running anyway) via: sudo serveradmin stop postgres

    I deleted the /var/pgsql_socket

    I copied one of the older /var/pgsql.pre-restore-<timestamp> directories to my /var/pgsql

     

    I tried to start postgres, but had the same error: CANNOT_START_SERVICE_TIMEOUT_ERR

    I deleted all ACLs from /var/pgsql via: chmod -R -a# 0 /var/pgsql

    I still cannot start postgres.

     

    My problem literally started overnight ... when I went to bed, everything was working, when I woke up my wiki server wasn't working which I traced back to postgres not working.  Reboot did not solve the problem. 

     

    Thanks,

    Switon

  • by Mandrid,

    Mandrid Mandrid Oct 9, 2011 2:04 PM in response to Switon
    Level 1 (0 points)
    Oct 9, 2011 2:04 PM in response to Switon

    Hi,

     

    I didn't add permission myself via the command line, I used Finder to do it. Sounds like you already have permissions set if you are in the '/var/pgsql' folder and altering files. By default my account didn't have access, so maybe you have another issue. I'll write out the explanation anyway incase its of help to anyone else.

     

    Also its worth pointing out I also installed the Server Admin Tools. I have no idea if that changes teh base install in anyway or not. They can be found here: http://support.apple.com/kb/DL1419

     

    Anyway to add access navigate to the '/var/' directory you should see a small percentage of the folders have the red no access icon on them. If you try to open 'pgsql' you will get a message saying you don't have permission to view it.

     

    To add yourself to the user list with permissions right click on the folder and select 'Get Info'. The get info window should appear, if you expand the bottom option 'Sharing & Permission' you'll notice you account isn't listed. Click the Padlock icon to elevate your permissions, then click the addition icon.

     

    From there a form should appear with a list of users on the computer. Select your account and click the select button. In the 'Sharing & Permissions' area there should now be 3 user entire _postgres, everyone, and your account name. Set the permissions next to your account to be the same as _postgres which is 'Read & Write'

     

    Now from that point onwards when you start the postgres services with your user using sudo from the commandline your account will have all teh nesecary privileges.

  • by Switon,

    Switon Switon Oct 10, 2011 6:01 AM in response to Kingdou
    Level 1 (10 points)
    Oct 10, 2011 6:01 AM in response to Kingdou

    Thanks Mandrid,

     

    Yes, I already have RW access to /var/pgsql...I didn't realize that is what you had meant in your earlier posting.  Thanks for responding.

     

    As you see, I have eliminated all ACLs from /var/pgsql and even this did not help me.  It seems from reading these several discussions on postgres, that each person is experiencing a different problem that is solved by different means.  I've tried everything I've read in these discussions, but to no avail.  I'm now preparing for a "clean" reinstall of Lion and Lion Server in an effort to solve my problems.  I require my wikis, which became unavailable over the weekend, for business so am relatively frantic to get them up and running once again.

     

    Thanks,

    Switon

  • by Switon,

    Switon Switon Oct 13, 2011 7:16 AM in response to Switon
    Level 1 (10 points)
    Oct 13, 2011 7:16 AM in response to Switon

    ...just to let anyone interested know...

     

    I was having trouble with Open Directory, not being able to connect to the OD master from a client.  I was having trouble with VPN, not being able to connect.  I was having some minor trouble with Screen Sharing.  I was having trouble with network accounts, the account names were converted to 30+ character long gibberish.  I am still having trouble with postgres.

     

    Basically, I updated to 10.7.2 yesterday and this update fixed many of my problems (probably a single source in the OD caused all of my issues) without my having to intervene.  It fixed my inability to connect to the OD master, it fixed VPN, it even fixed the corrupted network account names.  It did not fix my broken postgres -- unfortunate for me.  So, I'm still without the wiki and devicemgr services.

     

    On a different note and a different server (with a working postgres), I also note that 10.7.2 fixes the Custom Sidebar issue of not being able to delete tags in Custom Sidebars under 10.7.1 -- yeah!

     

    Switon

  • by frameloss,

    frameloss frameloss Oct 14, 2011 2:24 PM in response to Kingdou
    Level 1 (0 points)
    Oct 14, 2011 2:24 PM in response to Kingdou

    Had the same problem . . .  here is how I fixed it:

     

    bash-3.2# tail system.log

    . . .

     

    Oct 14 15:21:11 rpm-mac-0001 org.postgresql.postgres[1360]: 2011-10-14 15:21:11 MDT FATAL:  could not create log file "/Library/Logs/PostgreSQL.log": Permission denied

     

    . . .

     

    bash-3.2# ls -l /Library/Logs/PostgreSQL.log

    ls: /Library/Logs/PostgreSQL.log: No such file or directory

    bash-3.2# touch /Library/Logs/PostgreSQL.log

    bash-3.2# chown _postgres:_postgres /Library/Logs/PostgreSQL.log

    bash-3.2# ls -l /Library/Logs/PostgreSQL.log

    -rw-r--r--  1 _postgres  _postgres  0 Oct 14 15:21 /Library/Logs/PostgreSQL.log

     

    Afterwards serveradmin could start the database.

  • by Brian Brumfield,

    Brian Brumfield Brian Brumfield Oct 16, 2011 11:31 AM in response to frameloss
    Level 1 (130 points)
    Oct 16, 2011 11:31 AM in response to frameloss

    frameloss - THANKS! This was a new twist on this problem, keeping postgres running without losing all my configs, and I had to chmod 777 /Library/Logs because the PostgreSQL.log didn't even exist.

     

    I am back up now! Thanks again.

  • by Dan_Henese,

    Dan_Henese Dan_Henese Oct 23, 2011 4:10 PM in response to frameloss
    Level 1 (10 points)
    Oct 23, 2011 4:10 PM in response to frameloss

    Thanks frameloss, that got me back up and running.

  • by BDAqua,

    BDAqua BDAqua Dec 3, 2011 12:37 AM in response to frameloss
    Level 10 (123,745 points)
    Dec 3, 2011 12:37 AM in response to frameloss

    Great frameloss, fixed my month old problem except I had to use...

     

    sudo touch /Library/Logs/PostgreSQL.log

     

    sudo chown _postgres:_postgres /Library/Logs/PostgreSQL.log

     

    Never did get an answer over here...

     

    https://discussions.apple.com/message/16284049#16284049

     

    You deserve a Correct Answer over there!