Postgresql 9.2 won’t start after rerunning installer

postgresqlpostgresql-9.2

All,

I made a fatal mistake and accidentally ran the core installer for postgresql rather than the stackinstaller. I didn't go all the way through the install process but it still set something that is preventing it from starting at all now. This is a production DB and I really need some insight in to what I can do to troubleshoot this problem.

I am running 9.2 on a Windows 2008R2 server.

Best Answer

Sorry, I don't have enough rep to participate in the comments yet, but I use PostgreSQL a lot on Linux and Windows.

I ran into this same problem the other day. I ran the 9.1.7 installer and it broke my clusters. As Mike Christensen said, the key is running pg_ctl START in a command window to get a useful error message.

I got the command to use from the Services panel. When you view properties on your cluster definition, you'll see the pg_ctl command to start the service. Copy that command and paste it into the command window, then change the "runservice" parameter to "start", and use runas.exe to launch the command as the postgres user (9.1 and below) or LOCALSERVICE account (later versions).

It should already contain the path to your cluster data directory. This is what mine looks like:

C:/PROGRA~1/PostgreSQL/9.1/bin/pg_ctl.exe runservice -N "PostgreSQL Dev Cluster" -D "C:/PROGRA~1/PostgreSQL/9.1/data"

Check the error message you get from that command, and check the database logs also.

In my case I had two problems. First, I couldn't get the installer to work until I ran it under postgres local admin account. But when I did that, my domain account no longer had privileges. I had to re-grant myself full control on PostgreSQL directory tree. ( Edit CR: Don't mess with permissions on the PostgreSQL data directory unless you know exactly what you are doing, you are likely to make any problem much worse not better.)

Then I tried starting it again and got a different message indicating port 5432 was already in use. This turned out to be true. Somehow the installer had left an orphaned postgres.exe process open on port 5432, even though the Services panel showed that service as not started.

I had to use netstat -ano to find the PID and kill the orphan from task manager. Then everything started normally.

Hope that helps.

Brian

Related Solutions

Postgresql – How to wait for PostgreSQL to be startable / restorable

initdb doesn't return until it's finished, so there shouldn't be any pause needed between it and server startup. There have been bugs in PostgreSQL where it completed without flushing everything to disk first though. I don't know of any left right now, but the nature of bugs is that you don't always know about them.

If you use the pg_ctl command to start the database, use the "-w" parameters for that to wait until startup is finished before returning. It doesn't do anything fancy--it just does the "is it ready yet?" loop for you.

Note that if you get a server crash with a lot of data that needs to be replayed before the server can start, the timeout set by "-t" on the pg_ctl waiting might be too low.

There is no reason to VACUUM the source databases before doing a pg_dump of them. While it might speed the dump up a bit, the vacuum itself will take longer than that improvement.

PostgreSQL – Fix ‘Database Cluster Initialization Failed’ Error

It appears that the root cause of this issue may have been that @Celeritas's computer had an incorrect value for the COMSPEC environment variable. It had a trailing semicolon, so instead of the normal:

C:\Windows\system32\cmd.exe

it was:

C:\Windows\system32\cmd.exe;

This one-character difference is enough. The above isn't a valid command prompt path, so popen() calls were failing. Unfortunately, instead of something useful like:

'"C:\Windows\system32\cmd.exe;"' is not recognized as an internal or external command, operable program or batch file

it instead reports the delightfully useful error No error:

initdb: could not execute command ""C:/Program Files/PostgreSQL/9.2/bin/postgres.exe" --boot -x1 -F ": No error

See related:

I've reported this to the installer team and written a wiki entry to describe it. See blog post.

The issue was eventually resolved by:

Editing the COMSPEC environment variable
Uninstalling PostgreSQL
Rebooting
Reinstalling PostgreSQL

though there was a lot more before that which shouldn't actually be necessary to resolve this, including doing a total manual clean uninstall of PostgreSQL by hand.

Now I just want to find the people who wrote the program that modified this environment variable.

Best Answer

Related Solutions

Postgresql – How to wait for PostgreSQL to be startable / restorable

PostgreSQL – Fix ‘Database Cluster Initialization Failed’ Error

Related Question