Thread: Can't connect after restart
Hi all, I've tried posting to the General list but it's been almost a week and I didn't get an much of an answer... I need help trying to identify what my problem is, and I don't know pgSQL well enough to do much troubleshooting myself. pgSQL 8.0.3 install on WinXP SP1. The guys on the General list told me I didn't need SP2. Install goes fine, DB works perfectly until we restart the machine, then we can't connect. First time we thought something was corrupted because we had a power outage, we uninstalled/reinstalled and it was fine until, again, we decided to reboot. Our developer thought it might be a problem with permissions, but that seems very unlikely. I'm wondering if it might not be a firewall issue, but then I can't figure out why the initial install would be fine. One hypothesis was the pid not getting deleted properly, but the postmaster.pid is created and deleted properly on startup/shutdown. There is no postgresql.pid on my machine, but that's likely to be normal if Postgress doesn't start properly. Here are some other symptoms: If I shut down the service, then try to start it up again, sometimes it simply refuses to start again (bad user/pwd). I have to input the password again to get it to start. Seems like it's not remembering the info. In the EventLog, I have one application error: 2005-07-26 16:27:57 LOG: logger shutting down That seems to happen every time I shut down the service. There is no error on startup. If I try to connect with pgAdmin III: An error has occured: could not connect to server: Connection refused (0x0000274D/10061) Is the server running on host "127.0.0.1" and accepting TCP/IP connections on port 5432? The answer to that is, apparently, no, because I can't telnet 127.0.0.1 5432... Trying to connect through pgAdminIII doesn't generate either a pg log or an event log, so I have to guess there is really nothing happening. pgSQL logs seem normal (to my untrained eye) ---------------------------------------------- 2005-07-26 16:24:52 LOG: database system was shut down at 2005-07-26 16:24:47 Est 2005-07-26 16:24:52 LOG: checkpoint record is at 0/D33F08 2005-07-26 16:24:52 LOG: redo record is at 0/D33F08; undo record is at 0/0; shutdown TRUE 2005-07-26 16:24:52 LOG: next transaction ID: 3313; next OID: 33614 2005-07-26 16:24:52 LOG: database system is ready 2005-07-26 16:27:56 LOG: received fast shutdown request 2005-07-26 16:27:56 LOG: shutting down 2005-07-26 16:27:56 LOG: database system is shut down 2005-07-26 16:27:57 LOG: logger shutting down --------------------------------------------- 2005-07-26 16:28:17 LOG: database system was shut down at 2005-07-26 16:27:56 Est 2005-07-26 16:28:17 LOG: checkpoint record is at 0/D33F48 2005-07-26 16:28:17 LOG: redo record is at 0/D33F48; undo record is at 0/0; shutdown TRUE 2005-07-26 16:28:17 LOG: next transaction ID: 3313; next OID: 33614 2005-07-26 16:28:17 LOG: database system is ready ----------------------------------------------- I had also sent a bunch of logs made after In any case, I copied the logs written since we rebooted (around 14:05) below. The first log was named postgresql-2005-07-12_000000.log and was empty. That's the first one written on the day of the first reinstall. Following is every log, up to and after the point where we rebooted at 14:05. --------------------------------------------------- 2005-07-21 11:01:25 LOG: database system was interrupted at 2005-07-11 14:24:13 Est 2005-07-21 11:01:25 LOG: checkpoint record is at 0/D33C48 2005-07-21 11:01:25 LOG: redo record is at 0/D33C48; undo record is at 0/0; shutdown FALSE 2005-07-21 11:01:25 LOG: next transaction ID: 3313; next OID: 33614 2005-07-21 11:01:25 LOG: database system was not properly shut down; automatic recovery in progress 2005-07-21 11:01:25 LOG: record with zero length at 0/D33C88 2005-07-21 11:01:25 LOG: redo is not required 2005-07-21 11:01:25 LOG: database system is ready 2005-07-21 11:02:04 FATAL: password authentication failed for user "jbondc" 2005-07-21 11:02:05 FATAL: password authentication failed for user "jbondc" 2005-07-21 11:02:06 FATAL: password authentication failed for user "jbondc" 2005-07-21 11:02:07 FATAL: password authentication failed for user "jbondc" 2005-07-21 11:02:38 FATAL: password authentication failed for user "jbondc" 2005-07-21 11:02:38 FATAL: password authentication failed for user "jbondc" 2005-07-21 11:02:39 FATAL: password authentication failed for user "jbondc" 2005-07-21 11:02:40 FATAL: password authentication failed for user "jbondc" 2005-07-21 11:03:18 FATAL: password authentication failed for user "jbondc" 2005-07-21 14:04:53 LOG: received fast shutdown request 2005-07-21 14:04:54 LOG: shutting down 2005-07-21 14:04:54 LOG: database system is shut down 2005-07-21 14:04:58 LOG: logger shutting down ******************************** 2005-07-21 11:02:36 LOG: database system was interrupted at 2005-07-21 11:01:25 Est 2005-07-21 11:02:36 LOG: checkpoint record is at 0/D33C88 2005-07-21 11:02:36 LOG: redo record is at 0/D33C88; undo record is at 0/0; shutdown TRUE 2005-07-21 11:02:36 LOG: next transaction ID: 3313; next OID: 33614 2005-07-21 11:02:36 LOG: database system was not properly shut down; automatic recovery in progress 2005-07-21 11:02:36 LOG: record with zero length at 0/D33CC8 2005-07-21 11:02:36 LOG: redo is not required 2005-07-21 11:02:36 LOG: database system is ready 2005-07-21 14:04:53 LOG: received fast shutdown request 2005-07-21 14:04:54 LOG: shutting down 2005-07-21 14:04:55 LOG: database system is shut down 2005-07-21 14:04:58 LOG: logger shutting down ********************************* 2005-07-21 14:06:10 LOG: database system was shut down at 2005-07-21 14:04:54 Est 2005-07-21 14:06:10 LOG: checkpoint record is at 0/D33D48 2005-07-21 14:06:10 LOG: redo record is at 0/D33D48; undo record is at 0/0; shutdown TRUE 2005-07-21 14:06:10 LOG: next transaction ID: 3313; next OID: 33614 2005-07-21 14:06:11 LOG: database system is ready 2005-07-21 14:28:28 LOG: received fast shutdown request 2005-07-21 14:28:28 LOG: shutting down 2005-07-21 14:28:28 LOG: database system is shut down 2005-07-21 14:28:28 LOG: logger shutting down *********************************** 2005-07-21 14:29:10 LOG: database system was shut down at 2005-07-21 14:28:28 Est 2005-07-21 14:29:10 LOG: checkpoint record is at 0/D33D88 2005-07-21 14:29:10 LOG: redo record is at 0/D33D88; undo record is at 0/0; shutdown TRUE 2005-07-21 14:29:10 LOG: next transaction ID: 3313; next OID: 33614 2005-07-21 14:29:10 LOG: database system is ready 2005-07-21 14:29:54 LOG: received fast shutdown request 2005-07-21 14:29:54 LOG: shutting down 2005-07-21 14:29:54 LOG: database system is shut down 2005-07-21 14:29:55 LOG: logger shutting down **************************************** 2005-07-21 14:29:56 LOG: database system was shut down at 2005-07-21 14:29:54 Est 2005-07-21 14:29:56 LOG: checkpoint record is at 0/D33DC8 2005-07-21 14:29:56 LOG: redo record is at 0/D33DC8; undo record is at 0/0; shutdown TRUE 2005-07-21 14:29:56 LOG: next transaction ID: 3313; next OID: 33614 2005-07-21 14:29:56 LOG: database system is ready 2005-07-21 15:04:50 LOG: received fast shutdown request 2005-07-21 15:04:50 LOG: shutting down 2005-07-21 15:04:50 LOG: database system is shut down 2005-07-21 15:04:52 LOG: logger shutting down ************************************* 2005-07-21 15:06:03 LOG: database system was shut down at 2005-07-21 15:04:50 Est 2005-07-21 15:06:03 LOG: checkpoint record is at 0/D33E48 2005-07-21 15:06:03 LOG: redo record is at 0/D33E48; undo record is at 0/0; shutdown TRUE 2005-07-21 15:06:03 LOG: next transaction ID: 3313; next OID: 33614 2005-07-21 15:06:03 LOG: database system is ready --------------------------------------------------- Thanks for any help you can provide. __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
> I've tried posting to the General list but it's been almost a > week and I didn't get an much of an answer... It's definitly a weird situation, never seen this before. > I need help trying to identify what my problem is, and I > don't know pgSQL well enough to do much troubleshooting myself. > > pgSQL 8.0.3 install on WinXP SP1. The guys on the General > list told me I didn't need SP2. Correct. <snip> > Our developer thought it might be a problem with permissions, > but that seems very unlikely. I'm wondering if it might not > be a firewall issue, but then I can't figure out why the > initial install would be fine. Could be something aobut the firewall that doesn't trigger until a reboot. But yeah, it seems a bit far-fetched. > Here are some other symptoms: > > If I shut down the service, then try to start it up again, > sometimes it simply refuses to start again (bad user/pwd). > I have to input the password again to get it to start. Seems > like it's not remembering the info. You're saying the service control manager complains about a bad password? That's interesting. It's not a postgresql thing, but it can certainly cause problems. I havne't heard of this happening. I've heard of problems with the account losing the right to log in as a service because of a group policy. But tha certainly wouldn't be fixed by you putting the password back in. That said, if you're in a domain environment, I'd check if there are any group policy settings tha tmight affect it anyway. > In the EventLog, I have one application error: > 2005-07-26 16:27:57 LOG: logger shutting down This is normal. It shouldn't be an error, and this will be fixed. > The answer to that is, apparently, no, because I can't telnet > 127.0.0.1 5432... Right. Does the output of "netstat -an" show anything for 5432? Which of the following processes, and how many, do you get running when you start up the service: pg_ctl.exe, postmaster.exe, postgres.exe? If you get a postmaster.exe, can you attach to it with process explorer from sysinternals.com, and see what you have on the TCP/IP tab? > pgSQL logs seem normal (to my untrained eye) <snip> Yup, can't find anything obviously wrong there. Finally, try using runas to get a commandprompt running as the service account (runas /user:postgres cmd.exe), and start the database manually from there (pg_ctl -D <data directory> start), and see if that shows up any other messags. //Magnus
Hi, One question before we start, I want to make sure we're set up correctly. The service is running on an account intra_rpm_bd that was created especially for it (with no Admin rights, of course). The "postgres" account we talk about is for pgSQL itself, right? The postmaster and postgres services are started from the intra_rpm_bd account, is that right? This is the way it was set up by our developer, and fiddling with the various accounts has always confused me immensely. Just want to make sure our setup is okay. I'm starting to wonder if our developer wasn't right about it being a problem with permissions... Still doesn't make sense to me that it would work fine until reboot if that's the problem though... > Could be something aobut the firewall that doesn't > trigger until a reboot. But yeah, it seems a bit > far-fetched. If you think there's even a small chance it might be a firewall issue, I'll ask around if we don't find the answer... but if it's a firewall issue, we'll have a really hard time fixing things, since we can't play with the firewall... we'll have to find a way of going around it. >> If I shut down the service, then try to start it >> up again, sometimes it simply refuses to start >> again (bad user/pwd). I have to input the password >> again to get it to start. Seems like it's not >> remembering the info. > You're saying the service control manager complains > about a bad password? That's interesting. It's not > a postgresql thing, but it can certainly cause > problems. I havne't heard of this happening. I've > heard of problems with the account losing the right > to log in as a service because of a group policy. > But tha certainly wouldn't be fixed by you putting > the password back in. > That said, if you're in a domain environment, I'd > check if there are any group policy settings tha > tmight affect it anyway. Actually, I think it "loses" the password when I try to start through the Windows Start menu shortcut. It's happened a few times, and I haven't been able to reproduce the problem so far when starting/stopping the service directly through Windows Service manager. We asked, the account isn't part of any group (except the "User" group, obviously, or so they say), so there shouldn't be any group policy in effect (I can ask for any restriction on the "User" group but it wouldn't make sense). It might be that it loses the right to log in as a service, because when I put login/password back in it gives the message "has been granted right... service..." etc., but then why would it give me the "bad user/pwd" error? We are in a domain environment. Is there a way I can check if a domain setting is causing problems without trying to get hold of the technician ?(It can take a few days...)(everything's centralized and I can't play with things myself) In any case, that doesn't change the fact that I can't connect to the DB. Even when I restart the service after having logged in again, it's still not right. >> The answer to that is, apparently, no, because I >> can't telnet 127.0.0.1 5432... > Right. > Does the output of "netstat -an" show anything for > 5432? No, it's not showing up at all. > Which of the following processes, and how many, do > you get running when you start up the service: > pg_ctl.exe, postmaster.exe, postgres.exe? pg_ctl.exe once; postmaster.exe once; postgres.exe four times > If you get a postmaster.exe, can you attach to it > with process explorer from sysinternals.com, and > see what you have on the TCP/IP tab? I downloaded it and looked at postmaster.exe but... TCP/IP tab? Where can I find that? Handle or DLL view, and where? > Finally, try using runas to get a commandprompt > running as the service account > (runas /user:postgres cmd.exe), and start the > database manually from there (pg_ctl -D <data > directory> start), and see if that shows up > any other messags. Weird, the cmd window shuts down immediately after I input the pwd. I tried running runas from a command prompt and the msg I got is bad user/pwd. Thank you for all your help. //Magnus ____________________________________________________ Start your day with Yahoo! - make it your home page http://www.yahoo.com/r/hs
> Hi, > > One question before we start, I want to make sure we're set > up correctly. > > The service is running on an account intra_rpm_bd that was > created especially for it (with no Admin rights, of course). > The "postgres" account we talk about is for pgSQL itself, > right? The postmaster and postgres services are started from > the intra_rpm_bd account, is that right? This is the way it > was set up by our developer, and fiddling with the various > accounts has always confused me immensely. Just want to make > sure our setup is okay. I was referring to the service account. My bad, shoulve' been clearer. The account inside the db does not come into play until the system actually starts. > I'm starting to wonder if our developer wasn't right about it > being a problem with permissions... Still doesn't make sense > to me that it would work fine until reboot if that's the > problem though... Yeah, it sounds a bit weird. Did you install with the MSI installer? > > Could be something aobut the firewall that doesn't trigger until a > > reboot. But yeah, it seems a bit far-fetched. > > If you think there's even a small chance it might be a > firewall issue, I'll ask around if we don't find the > answer... but if it's a firewall issue, we'll have a really > hard time fixing things, since we can't play with the > firewall... we'll have to find a way of going around it. > I don't think it's that. But I won't exclude it, I've seen host based firewalls do some really weird things. > > You're saying the service control manager complains about a bad > > password? That's interesting. It's not a postgresql thing, > but it can > > certainly cause problems. I havne't heard of this happening. I've > > heard of problems with the account losing the right to log in as a > > service because of a group policy. > > But tha certainly wouldn't be fixed by you putting the > password back > > in. > > That said, if you're in a domain environment, I'd check if > there are > > any group policy settings tha tmight affect it anyway. > > Actually, I think it "loses" the password when I try to start > through the Windows Start menu shortcut. It's happened a few > times, and I haven't been able to reproduce the problem so > far when starting/stopping the service directly through > Windows Service manager. Hmm. That one just does a "net start" through the SCM, so there should be no difference :-) > We asked, the account isn't part of any group (except the > "User" group, obviously, or so they say), so there shouldn't > be any group policy in effect (I can ask for any restriction > on the "User" group but it wouldn't make sense). Group policy has nothing to do with groups :-) Blame the redmond boys. It's a policy assigned at OU level in the Active Directory. > It might be > that it loses the right to log in as a service, because when > I put login/password back in it gives the message "has been > granted right... service..." etc., but then why would it give > me the "bad user/pwd" error? It sohuldn't. > We are in a domain environment. Is there a way I can check if > a domain setting is causing problems without trying to get > hold of the technician ?(It can take a few > days...)(everything's centralized and I can't play with things myself) There is a MMC snapin called "Resulting set of policy". Run it in logging mode, and check things under "User Rights". > >> The answer to that is, apparently, no, because I can't telnet > >> 127.0.0.1 5432... > > > Right. > > Does the output of "netstat -an" show anything for 5432? > > No, it's not showing up at all. Ok. That shows we didn't get that far. Assuming you haven't changed the port it should listen to? What's the value of "listen_addresses" in your postgresql.conf? > > Which of the following processes, and how many, do you get running > > when you start up the service: > > pg_ctl.exe, postmaster.exe, postgres.exe? > > pg_ctl.exe once; postmaster.exe once; postgres.exe four times Hmm. That's the way it should be. > > If you get a postmaster.exe, can you attach to it with process > > explorer from sysinternals.com, and see what you have on the TCP/IP > > tab? > > I downloaded it and looked at postmaster.exe but... > TCP/IP tab? Where can I find that? Handle or DLL view, and where? Right click -> Properties. That should give you a tab called TCP/IP. > > Finally, try using runas to get a commandprompt running as > the service > > account (runas /user:postgres cmd.exe), and start the database > > manually from there (pg_ctl -D <data > > directory> start), and see if that shows up > > any other messags. > > Weird, the cmd window shuts down immediately after I input > the pwd. I tried running runas from a command prompt and the > msg I got is bad user/pwd. Ok. Then you need to solve that first - seems to be similar to the issues you had before with the service.. //Magnus
> What's the value of > "listen_addresses" in your > postgresql.conf? UGH!! 5435... WHY??? I changed it to 5432 and it works (obviously). As in most cases, it was a *stupid* thing, that I *should* have thought of checking out. We changed some settings in the conf file the first time we installed, and someone might have made a stupid typo. But when we reinstalled, we didn't change it. Is it an installer problem, or is it that pgSQL installer simply doesn't overwrite the conf file on a new install? Another question: why did it work right after the install? Is the service started without the conf file the first time around? One thing left to do: restart the machine and make sure it works. Again, I can not thank you enough for your help and patience. __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
> > What's the value of > > "listen_addresses" in your > > postgresql.conf? > > UGH!! 5435... WHY??? I changed it to 5432 and it works (obviously). Yay! > As in most cases, it was a *stupid* thing, that I > *should* have thought of checking out. Well, at least it wasn't a bug, so that leaves us both happy now :-) > We changed some settings in the conf file the first time we > installed, and someone might have made a stupid typo. But > when we reinstalled, we didn't change it. Is it an installer > problem, or is it that pgSQL installer simply doesn't > overwrite the conf file on a new install? No, the installer doesn't touch the data directory, which is where the config file sits. > Another question: why did it work right after the install? Is > the service started without the conf file the first time around? Um. No. That's still weird :-) You are absolutely sure this was it? Any chance the old postmaster was still around servicing requests without having reloaded the config filE? //Magnus
Audrey Bergeron-Morin wrote: >>What's the value of >>"listen_addresses" in your >>postgresql.conf? >> >> > >UGH!! 5435... WHY??? I changed it to 5432 and it works >(obviously). > > That should be an IP address, or '*'. you have it confused with the port setting apparently. cheers andrew
> >>What's the value of > >>"listen_addresses" in your > >>postgresql.conf? > >UGH!! 5435... WHY??? I changed it to 5432 and it > works > >(obviously). > That should be an IP address, or '*'. you have it > confused with the port > setting apparently. > cheers > andrew Yes, sorry: I mean the port was wrong. Looking for listen_adresses made me look for port setting too, that's how I found it :-) __________________________________ Yahoo! Mail for Mobile Take Yahoo! Mail with you! Check email on your mobile phone. http://mobile.yahoo.com/learn/mail
> No, the installer doesn't touch the data directory, > which is where the > config file sits. That might explain why the setting was wrong. I was watching when our developer first installed it, and I know for sure he changed some settings. And I know he didn't change them the second time around, so if the installer didn't overwrite the file then it might have carried over. Still doesn't explain the next question... > > Another question: why did it work right after the > install? Is > > the service started without the conf file the > first time around? > Um. No. That's still weird :-) You are absolutely > sure this was it? It's working fine now, and the port setting is the only thing I changed, so yes I'm pretty sure that was it. I still haven't solved the "lost password" issue though, but I'm less worried about that. > Any chance the old postmaster was still around > servicing requests > without having reloaded the config filE? Probably. The guy who installed it was going pretty fast, and I'm not sure he went and stopped the postmaster in between; he probably just hit uninstall and then double-click on the installer file. But it's still weird. Install -> (work fine) -> reboot -> (not working) -> Uninstall -> Reinstall -> (work fine) -> reboot -> (not working) If the old postmaster was still servicing that would explain why the first install worked up to the point we rebooted, and then stopped working. But then why did the reinstall make the DB work again? I must be missing something really obvious again... __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
> > Any chance the old postmaster was still around servicing requests > > without having reloaded the config filE? > > Probably. The guy who installed it was going pretty fast, and > I'm not sure he went and stopped the postmaster in between; > he probably just hit uninstall and then double-click on the > installer file. But it's still weird. Now that I think of it, ther eis another option. Did you only use pgadmin and/or the icons added to the start menu for psql? Because those access the registry for the port number to the backend, and there may be something happening there with the uninstlal/reinstall thing. //Magnus
> > > Any chance the old postmaster was still around > servicing requests > > > without having reloaded the config filE? > > > > Probably. The guy who installed it was going > pretty fast, and > > I'm not sure he went and stopped the postmaster in > between; > > he probably just hit uninstall and then > double-click on the > > installer file. But it's still weird. > > Now that I think of it, ther eis another option. Did > you only use > pgadmin and/or the icons added to the start menu for > psql? Because those > access the registry for the port number to the > backend, and there may be > something happening there with the > uninstlal/reinstall thing. We started/stopped the service a couple of times in Windows Services manager, but other than that we used only pgAdmin and the start menu icons. I'm not sure what you're saying here, what's your theory? __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
> > > > Any chance the old postmaster was still around > > servicing requests > > > > without having reloaded the config filE? > > > > > > Probably. The guy who installed it was going > > pretty fast, and > > > I'm not sure he went and stopped the postmaster in > > between; > > > he probably just hit uninstall and then > > double-click on the > > > installer file. But it's still weird. > > > > Now that I think of it, ther eis another option. Did you only use > > pgadmin and/or the icons added to the start menu for psql? Because > > those access the registry for the port number to the backend, and > > there may be something happening there with the uninstlal/reinstall > > thing. > > We started/stopped the service a couple of times in Windows > Services manager, but other than that we used only pgAdmin > and the start menu icons. I'm not sure what you're saying > here, what's your theory? I wouldn't call it a theory, more like a guess ;) The idea is that windows sometimes to some funky stuff with startmenu shortcuts - and since some connection information is stored there, it might be that those weren't of the same version as the installed postgresql. //Magnus