PDA

View Full Version : Postgres cannot connect after using new optimize tool



discretely
10-27-2010, 08:21 PM
So after I downloaded the latest update patch I decided to try to use the new "optimize Postgres database" tool and HEM was working fine for the remainder of that session. Before I used HEM the next time I updated Microsft Update for the first time in a while. When I opened up HEM, it gave the "cannot connect to 127.0.0.1 message" and I have not been able to get this fixed. Not sure if this is from the new optimize tool or from microsoft update.

I tried everything in the sticky and the FAQ without success. One problem is that I am still using PostgresQL 8.2 and am not sure if I can upgrade to 8.4 or not without losing all of my info. If you have any suggestions it would be greatly appreciated.

loustic
10-28-2010, 08:31 AM
You should check these pages, maybe a windows firewall or defender problem:

FAQ - Hold'em Manager Poker Tracking Software :: Windows Firewall / Defender Problems (http://faq.holdemmanager.com/questions/275/Windows+Firewall+%7B47%7D+Defender+Problems)

FAQ - Hold'em Manager Poker Tracking Software :: Windows Update (http://faq.holdemmanager.com/questions/276/Windows+Update)

discretely
10-28-2010, 11:37 AM
I have already tried both of those things, I have Norton Antivirus, and I turn off the 'Smart Firewall', windows alerts me I have no firewall active, and Holdem Manager still will not connect to the database.

As for Windows Defender, I have windows XP and I can't find it on my computer.

Do I need to upgrade Postgres? Will I lose my database going from 8.2 to 8.4?

The Windows Update link mentions something about rolling back updates, I'm not sure how to do this, but I don't get how a windows update would only affect my computer and not everyone else using windows.

loustic
10-28-2010, 11:53 AM
First, you should check if the postgresql service is running.

In the start menu / execute, and type: services.msc

You should be in the Services window, just check the postgresql-8.2, and check if the state is "started", and if it is set on automatic start. (by right clicking, and properties)

If the service isn't started, try to start it.

If it fails, in start menu / execute, and type: eventvwr.msc

It will open a window, check in the application tab if you find an error for PostgreSQL

discretely
10-28-2010, 12:09 PM
When I try to start Postgres it says the service started then stopped.

In the Event Viewer, there was an error for Postgres with the following description:
"FATAL: unrecognized configuration parameter "checkpoint_completion_target"

loustic
10-28-2010, 12:39 PM
Ok,

Go in this postgresql directory:

Program Files\PostgreSQL\8.2\data

make a copy of file postgresql.conf, and in the original file, search for these lines:

# - Checkpoints -

#checkpoint_segments = 3 # in logfile segments, min 1, 16MB each
#checkpoint_timeout = 5min # range 30s-1h
#checkpoint_completion_target = 0.5 # checkpoint target duration, 0.0 - 1.0
#checkpoint_warning = 30s # 0 disables


Check that you have the same pink values (which are default ones)

If something differs, modify and save the file, and try to start the service again

discretely
10-28-2010, 01:04 PM
The file was different, the check-completion-target was missing so I copy and pasted over the text you had posted, and saved the file. When trying to start PostgreSQL, the service is still starting then stopping immediately.

loustic
10-28-2010, 01:13 PM
You should try to reboot and return on eventvwr.msc to see if you still have a postgresql error, and if it's still the same

Also, please copy/paste what was in your original postgresql.conf file regarding # - Checkpoints - and following lines

Finally, do you have system restore enabled ?

You could try to restore before you launched the windows update.

If the restore is fine and postgresql is working:

- make a backup of all your databases with PgAdmin III (in a secure location)
- make a backup of all your databases and config files with Holdem Manager
- upgrade to PostgreSQL 8.4

If everything is working fine with postgres 8.4, try the windows update again if you want

discretely
10-28-2010, 02:19 PM
Still saying the same error after I restart:

"FATAL: unrecognized configuration parameter "checkpoint_completion_target"

This is what the log initially said:

# - Checkpoints -

### REPLACED ### #checkpoint_segments = 3 # in logfile segments, min 1, 16MB each
#checkpoint_timeout = 5min # range 30s-1h
#checkpoint_warning = 30s # 0 is off

discretely
10-28-2010, 02:20 PM
Just this morning Norton detected a virus that they recommended turning off system restore and rescanning the computer in safe mode to get rid of the virus, so thats what I did.

I have since enabled system restore again, but did I lose the ability to go back beyond this morning now?

loustic
10-28-2010, 02:25 PM
ok,

change


### REPLACED ### #checkpoint_segments = 3 # in logfile segments, min 1, 16MB each
#checkpoint_timeout = 5min # range 30s-1h
#checkpoint_warning = 30s # 0 is off

by


#checkpoint_segments = 3 # in logfile segments, min 1, 16MB each
#checkpoint_timeout = 5min # range 30s-1h
#checkpoint_warning = 30s # 0 is off

And I think it should be better

discretely
10-28-2010, 02:40 PM
Still saying the same error after I changed it again and restarted:
"FATAL:
unrecognized configuration parameter "checkpoint_completion_target"

loustic
10-28-2010, 02:50 PM
Make a search in your postgresql.conf file, and make a search for checkpoint_completion_target

Have you done any backup of your databases recently ??

discretely
10-28-2010, 02:51 PM
Here it is, bottom of the file:

#---------------------------------------------------------------------------
# CUSTOMIZED OPTIONS
#---------------------------------------------------------------------------

#custom_variable_classes = '' # list of custom variable class names
shared_buffers = 1024MB
effective_cache_size = 1024MB
work_mem = 16MB
maintenance_work_mem = 16MB
commit_delay = 10000
checkpoint_segments = 128
checkpoint_completion_target = 0.9
wal_buffers = 2MB

loustic
10-28-2010, 02:58 PM
omg, i just realize how much i'm tired.

All these lines with # are comments ones, so we don't need to do anything with them....

You should add a # too on your checkpoint_completion_target = 0.9 line to get this :

#checkpoint_completion_target = 0.9

loustic
10-28-2010, 03:04 PM
And if you want to revert all these things that the optimization system did, just remove all these lines and save your conf file:

shared_buffers = 1024MB
effective_cache_size = 1024MB
work_mem = 16MB
maintenance_work_mem = 16MB
commit_delay = 10000
checkpoint_segments = 128
checkpoint_completion_target = 0.9
wal_buffers = 2MB

discretely
10-28-2010, 03:27 PM
That did it! Its working now!!

Thanks so much for the help..

loustic
10-28-2010, 03:35 PM
Great :)

I'm wondering that maybe optimization failed because you use an older release of PostgreSQL.

What you should do, now that it's working, is make a backup of all your databases with PgAdmin III

Look how to do it here:
FAQ - Hold'em Manager Poker Tracking Software :: Backup & Restore Database (http://faq.holdemmanager.com/questions/72/Backup+%26+Restore+Database)

Make sure you save them in a secure location.

When all your backups are done, you can try to install PostgreSQL 8.4, that you can find here:

https://www.enterprisedb.com/getfile.jsp?fileid=1528

And after the install is done, you restore your databases :)

But maybe you already had too much problems today and you prefer do that later ;)

Svega
11-07-2010, 09:58 PM
For your information this happened to me as well, on a clean installation of 1.11.05a (full installer), and the postgresql server included with it (which is 8.4.1., if I'm not mistaken). The optimization made postgresql unable to start, I had to manually restore the config file. Postgresql 8.4.3. had no such issues on my other pc.

Just tried 8.4.5.1, it fails as well, it's probably not version related. Also checked the service logs in windows:
"could not create shared memory segment: 8"

Setting the shared_buffers to a smaller value did fix it. I will check google about that, but I would strongly recommend some button in HEM to restore the config file modifications in case they don't work (since you can't open that window again in HEM, once the postgresql server stopped responding), otherwise the user is forced to mess around the files himself.

netsrak
11-08-2010, 04:26 AM
There is a restore button in the optimize process. I have tested this a lot of times and for me the restore worked without problems after i got a message that postgresql could not be started.

Did you close the optimization window before the postgresql restart was completed?

Svega
11-08-2010, 08:52 AM
Yes, I did close it. The optimization window mentioned it's going to restart the service, but since I didn't expect the service to fail due to the modifications, also there was no indication or warning telling me to wait and see if it really did restart, I simply closed the window. That's why I'm saying this is a problem, since after closing it you can't back to the restore button, and it's a lot of trouble for technically less qualified users.

I would suggest some sort of warning text on the Tuning tab in future releases, something telling the users that they should make sure the service restarted before they exit, and/or a button outside the optimization window to restore the settings by restoring the config file from backup, which shouldn't be a problem, since the wast majority of users probably have their postgresql on localhost.

morny
11-08-2010, 10:35 PM
Thanks were looking into this and those sound like good suggestions so ill pass the information on