Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

migrated from stackoverflow.com Dec 22 '12 at 5:46

This question came from our site for professional and enthusiast programmers.

1  
Any error messages? Log files? –  Mike Christensen Dec 21 '12 at 17:21
    
None what so ever unfortunately. –  AdamEstrada Dec 21 '12 at 17:24
    
You might get better support at the DBA sibling site. –  A.H. Dec 21 '12 at 17:28
    
If you try running pg_ctl start what happens? –  Mike Christensen Dec 21 '12 at 17:29
    
hmm...pg_ctl start says that it can't find the data directory referenced by PGDATA. Its int here and referenced properly. –  AdamEstrada Dec 21 '12 at 19:18

3 Answers 3

up vote 3 down vote accepted

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

share|improve this answer
    
Ahh...This makes sense and I will give it a try later this week! –  AdamEstrada Dec 24 '12 at 11:27

If you ran a 9.2 installer on a host with a running 9.2 DB then cancelled it part way through, your best bet is probably to just run it again and let it finish. The installer is used for upgrades as well as fresh installs and it will never remove or mess with an existing data directory, so this is quite safe. Nonetheless, you should stop the PostgreSQL service (if it's running) in services.msc and then take a file-system-level copy of your database before you attempt to fix the problem.

Once you're back up and running, ensure that you have a good backup regime in place.

share|improve this answer
    
Hi Craig. Thanks a bunch for the feedback! I did actually run through the entire install and the DB still did not come back to life. Does the installer change anything like permissions on the machine. Again this is a Windows server. Cheers ~a –  AdamEstrada Dec 24 '12 at 11:26
    
@AdamEstrada I know it's windows, that's why I mentioned services.msc. Before you do anything else, copy the data directory as a backup unless the data is of no value to you. Then examine the PostgreSQL server logs in the pg_log directory in the Pg data directory, and examine the Event Viewer for PostgreSQL service related messages. Update your question with what you find and comment here. –  Craig Ringer Dec 24 '12 at 13:07

All,

Everyone's feedback has been wonderful! It turns out that a colleague of mine set the maintenance_work_mem way outside the range it should have been. When the installer ran last Friday it forced the database to restart which threw the error. It wasn't until I ran through these steps that I was able to figure out what the problem was. This was my own little apocalypse! ~a

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.