PGS Tuning Wizard destroys my login

On Wed, Apr 8, 2009 at 10:48 AM, Jennifer Trey <jennifer.trey@gmail.com> wrote:

Hi,
What does the Tuning Wizard do? The one you can run on PGS ? Does it just
touch up the postgresql.conf ? Does it play with other stuff as well?

It only touches postgresql.conf, and only after you're given a chance
to review those changes.

I am asking because, every time I run the wizard I cannot login with the
password I choose when I installed PGS. What is going on? Replacing the
generated postgresql.conf with the old to "go back" does not help either.
First time I reformatted my whole disk, and tried install PGS and ran Tuning
Wizard again. Same thing happened!

If replacing postgresql.conf with the old version doesn't help, then
it's clearly not the tuning wizard at fault.

This time I just uninstalled, and installed PGS again without the tuning
wizard and I haven't had any problems since, but I need the tuning!

The un-installation left some of the Data files behind, which i cannot
delete, I have not access to them, but I am logged in as Administrator.
What is going on!?

Just take ownership of them and then delete them.

--
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com

Look, I am telling now what I did.

I don't want to hear claims about how its not the tuning wizards fault. It
is!

And it does more than the postgresql.conf because thats not true.
Just until today I have been able to login and logout, using pgAdmin to my
DB.

Now, after considering what you said, I tried once more! And I am getting
tired of this whole process.

I installed the tuning wizard. I got myself a new postgresql.conf and it
renamed the old one which I noted.

I opened my pgAdmin, logged in, worked fine (I guess the changes require
some kind of restart). I restarted my computer and we are back with the old
pattern where I cannot login, not with pgAdmin, not with my java-app and not
with sql shell :
Username [postgres]:
psql: could not connect to server: Connection refused (0x0000274D/10061)
Is the server running on host "localhost" and accepting
TCP/IP connections on port 5432?

Press any key to continue . . .

looking at the pgadmin.log i see this :
2009-04-08 02:03:01 ERROR : server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.

I replaced the new postgresql.conf with the old one. Restarted my computer
again. And still the problems are there. Last time I had to uninstall
postgre (the time before I reformatted the computer) .. so I would like this
to work this time around.

About taking ownership, its not working for me either, (I am trying to
delete the data folder that was left from the previous installation) but it
refuses to let me delete two folders (base, pg_log) but I guess thats more a
windows server issue.
Lets concentrate on the first problem.

How can I fix it ?
/ Jennifer

On Wed, Apr 8, 2009 at 12:16 PM, Jennifer Trey <jennifer.trey@gmail.com> wrote:

Look, I am telling now what I did.

I don't want to hear claims about how its not the tuning wizards fault. It
is!

And it does more than the postgresql.conf because thats not true.

I'm sorry to hear you think I'm being untruthful. The source code for
the tuning wizard is at
http://www.enterprisedb.com/openDownloads.do?productId=417&amp;redirectReason=true&amp;productVersion=otherDownload
- you can see exactly what it modifies there.

--
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com

Hehe, I am not saying that you are untruthful, but obviously there is more
to this issue than just the .conf file.

Because when returning to the old conf the problem is still there and I am
also certain that this problem was not there before I just installed the
tuning wizard.

Would you at least agree that there is something fishy going on, and that
its related to the tuning wizard? Things seldom work as expected

I just un-installed the tuning-wizard (I still have the generated config
file, so I dont have to repeat this step once more), restarted, the problem
is still there. The problem does not seem to be login related. But more to
that the server doesn't start up.

Running the program, 'Start Server', it just sits there and does nothing.
After long time it says:

The service did not report an error.

More help is available by typing NET HELPMSG 3534

Not sure what is going on. Server refuses to start it seems. Please help me
debug this problem :)

Sincerely / Jen

On Wed, Apr 08, 2009 at 02:16:35PM +0300, Jennifer Trey wrote:

looking at the pgadmin.log i see this :
2009-04-08 02:03:01 ERROR : server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.

Why aren't you looking at the postmaster's log? Not sure where this
lives under Windows, but it should tell you why the server is closing
the connection.

--
Sam http://samason.me.uk/

On Wed, Apr 8, 2009 at 7:50 AM, Jennifer Trey <jennifer.trey@gmail.com>wrote:

The service did not report an error.

More help is available by typing NET HELPMSG 3534

Not sure what is going on. Server refuses to start it seems. Please help me
debug this problem :)

My first guess as to why this is happening:

If you've been manually placing the config files, it probably means
that the windows user who is logging on and running the postgres service
does not have permission to read the postgresql.conf file. Try to set the
permissions of that file to the same as the other files in the directory
before trying to start the service.

--Scott

Jennifer,

obviously you are on windows. And the problem you are describing seems to
be:

"after running the tuning wizzard, the PostgreSQL Service does not start any
more"

AT the same time you are describing some problem of

"you are not able to delete some Files / Directories connected with
PostgreSQL-Databases, and you are not able to change ownership / take
ownership of those files."

THAT gives reason to the assumption that you are using something Vistaish or
Windows-7ish.

AND that the right structure of your file system around PostgreSQLs Files is
rather screwed; not clear, by what or who.

SO, to help you to get to some solution:

1.) you should state the exact version of your operating system
2.) you should state the exact PostgreSQL Version
3.) exactly describe your Windows Authorization. Is there some Domain /
ActiveDirectory going on? Do you really have MasterChiefAdministrator
rights?

and to continue your diagnostics:

4.) Check the application event log of windows. "eventvwr" from the Command
line, or dig through the Administration Panels
5.) Check for the logfiles of PostgreSQL within the pg_log directory.

Most likely reason is that postgres.exe is not allowed to open some file or
port; to write to a log file or read it's databases or even read its
configuration file postgresql.conf

So, next of this: check the file-ownerships and rights of your PostgreSQL
Data directory. Especially, is the user owning the postgresql service
allowed to read an change all the files within the PostgreSQL Data
directory? Especially the postgresql.conf?

Best luck,

Harald

On Wed, Apr 8, 2009 at 1:50 PM, Jennifer Trey <jennifer.trey@gmail.com>wrote:

Hehe, I am not saying that you are untruthful, but obviously there is more
to this issue than just the .conf file.

Because when returning to the old conf the problem is still there and I am
also certain that this problem was not there before I just installed the
tuning wizard.

Would you at least agree that there is something fishy going on, and that
its related to the tuning wizard? Things seldom work as expected

I just un-installed the tuning-wizard (I still have the generated config
file, so I dont have to repeat this step once more), restarted, the problem
is still there. The problem does not seem to be login related. But more to
that the server doesn't start up.

Running the program, 'Start Server', it just sits there and does nothing.
After long time it says:

The service did not report an error.

More help is available by typing NET HELPMSG 3534

Not sure what is going on. Server refuses to start it seems. Please help me
debug this problem :)

Sincerely / Jen

On Wed, Apr 8, 2009 at 2:31 PM, Dave Page <dpage@pgadmin.org> wrote:

On Wed, Apr 8, 2009 at 12:16 PM, Jennifer Trey <jennifer.trey@gmail.com>
wrote: > Look, I am telling now what I did. > > I don't want to hear
claims about how its not the tuning wizards fault. It > is! > > And it
does more than the postgresql.conf because thats not true.
I'm sorry to hear you think I'm being untruthful. The source code for the
tuning wizard is at
http://www.enterprisedb.com/openDownloads.do?productId=417&amp;redirectReason=true&amp;productVersion=otherDownload- you can see exactly what it modifies there. --Dave PageEnterpriseDB UK:
http://www.enterprisedb.com

--
GHUM Harald Massa
persuadere et programmare
Harald Armin Massa
Spielberger Straße 49
70435 Stuttgart
0173/9409607
no fx, no carrier pigeon
-
LASIK good, steroids bad?

Jenifer;

Can you send a copy of both your before and after config files to the list?

I just tried to change the permission on all users, admin to user to
postgres. Access Denied for all! The machines have taken over!

It seems like my long message didnt make it :S Here it is again. (Perhaps it
was because of the attached pic, I will leave it out this time)------

On Wed, Apr 8, 2009 at 3:17 PM, Massa, Harald Armin <chef@ghum.de> wrote:

Jennifer,

obviously you are on windows. And the problem you are describing seems to
be:

"after running the tuning wizzard, the PostgreSQL Service does not start
any more"

AT the same time you are describing some problem of

"you are not able to delete some Files / Directories connected with
PostgreSQL-Databases, and you are not able to change ownership / take
ownership of those files."

THAT gives reason to the assumption that you are using something Vistaish
or Windows-7ish.

AND that the right structure of your file system around PostgreSQLs Files
is rather screwed; not clear, by what or who.

SO, to help you to get to some solution:

1.) you should state the exact version of your operating system

**

*Windows Web Server 2008 x64*
**

2.) you should state the exact PostgreSQL Version

**

*The latest available, installed this : postgresql-8.3.7-1-windows.exe*

3.) exactly describe your Windows Authorization. Is there some Domain /
ActiveDirectory going on? Do you really have MasterChiefAdministrator
rights?

**

*As far as I know, *I have one user and that is the Administrator. Not sure
if that is the commander in chief but obviously not, because he is screwing
with me allot.

running *lusrmgr.msc * I can see under user that there is a username :

postgre

with description : PostgreSQL service account

There is allot of changes that can be made on the properties here.

and to continue your diagnostics:

4.) Check the application event log of windows. "eventvwr" from the Command
line, or dig through the Administration Panels

**

*Ok, I did that. *The export generated uglyness so I will be pasting those I
found relevant and there was a few!

By Date ASC (Oldest first, and only errors)

FATAL: could not open configuration file
"E:/PostgreSQLDATA/postgresql.conf": Permission denied
FATAL: could not open configuration file
"E:/PostgreSQLDATA/postgresql.conf": Permission denied
FATAL: could not open configuration file
"E:/PostgreSQLDATA/postgresql.conf": Permission denied and some
more of these ....
Timed out waiting for server startup
pg_ctl: PID file "E:/PostgreSQLDATA/postmaster.pid" does not exist
Is server running?
pg_ctl: PID file "E:/PostgreSQLDATA/postmaster.pid" does not exist

5.) Check for the logfiles of PostgreSQL within the pg_log directory.
**

*I cannot enter that folder! *Crazy! I command you to let me in!
No, I try to change the settings and permissions (there is like tousand
entries to those things), i give full access but no difference! I don't get
it! Make me the super chief damn it! Freaking Windows! Just crap and I am
stuck with it.
Most likely reason is that postgres.exe is not allowed to open some file or
port; to write to a log file or read it's databases or even read its
configuration file postgresql.conf
**

*I enter the properties.* postgres is among the users. The only checked (for
the entire folder) is 'Special Permissions', nothing else. (Oh yeah, postgre
is the owner). Changing to full access says that Access denied. Please look
at the JPEG I attached to this email, its marked with the steps.

So, next of this: check the file-ownerships and rights of your PostgreSQL
Data directory. Especially, is the user owning the postgresql service
allowed to read an change all the files within the PostgreSQL Data
directory? Especially the postgresql.conf?

*You seem to be on something here. Keep going! :)*

Best luck,

Harald

Sincerely / Jennifer

Jennifer,

1.) you should state the exact version of your operating system

**
*Windows Web Server 2008 x64*
**

2.) you should state the exact PostgreSQL Version

**
*The latest available, installed this : postgresql-8.3.7-1-windows.exe*

3.) exactly describe your Windows Authorization. Is there some Domain /
ActiveDirectory going on? Do you really have MasterChiefAdministrator
rights?

**
*As far as I know, *I have one user and that is the Administrator. Not
sure if that is the commander in chief but obviously not, because he is
screwing with me allot.

You have digged down to the root of the problem:

FATAL: could not open configuration file

"E:/PostgreSQLDATA/postgresql.conf": Permission denied

so to sum it up: you ran the Tuning Wizard, which gave you a new
postgresql.conf. Tuning wizard, running as your user, copied this
postgresql.conf OR you put back your back-upped postgresql.conf, also as
"Administrator"

The permissions of that directory seem to be very screwed :)

5.) Check for the logfiles of PostgreSQL within the pg_log directory.
**
*I cannot enter that folder! *Crazy! I command you to let me in!
No, I try to change the settings and permissions (there is like tousand
entries to those things), i give full access but no difference! I don't get
it! Make me the super chief damn it!

A quick google for NTFS permissions points to a lot of second source
manuals; that is a hint that it is complicated enough for people to make
money to write books about it. As I am always working WITH the system
adminstrators when implementing PostgreSQL on Win32, I have no MCP or MSC or
whatever that title for knowing about Windows-Permissions is.

So my hints to solve your problem:

a) check if there is some higher authority - as "this computer is member of
a domain / Active Directory", and rights for those file systems are managed
elsewhere out of your control

b) read up on Windows NTFS File Permissions on Server 2008. Do you have to
use another account? Do you have to elevate privileges in any way?

c) after learning enough about windows File Permissions, make sure that the
user "Postgres" -> PostgreSQL Service Account has the appropriate
permissions to access all the files within the PostgreSQL Data directory.

*You seem to be on something here. Keep going! :)*

Now it's you and windows. Good luck!

Harald

--
GHUM Harald Massa
persuadere et programmare
Harald Armin Massa
Spielberger Straße 49
70435 Stuttgart
0173/9409607
no fx, no carrier pigeon
-
LASIK good, steroids bad?

After doing allot of clicks with the permissions. It automagically fixed
itself. Fantastic!

The root problem was Windows itself!

I would recommend the EnterpriseDB folks to consider this though, because in
someway its affecting your software.

Thank you all. Now join my new thread for real discussions, instead of
problem solving :)

On Wed, Apr 8, 2009 at 4:31 PM, Massa, Harald Armin <chef@ghum.de> wrote:

Jennifer Trey wrote:

*As far as I know, *I have one user and that is the Administrator. Not
sure if that is the commander in chief but obviously not, because he
is screwing with me allot.

unlike Unix derivatives, the Windows Administrator account does NOT have
file access override rights, it has to obey rights just like any other
user. The one special power Adminstrator does have is the right to
change file ownership, but they made this as obfuscated as possible.

The standard Windows PostgreSQL install creates a special unprivileged
user 'postgres' which must own the data directory and have read/write
access to all files in it, and is used to run the postgresql server
process. This postgres account needs and has no special privileges,
except the right to run as a service.

In Unix derived systems, when you update an existing file, it tends to
NOT create a new file, just replace the data in the existing file, so by
default ownership and privileges remain the same. In Windows,
instead, its more common to create a new file, then remove the old one
and rename the new one, this means when user X edits an existing file,
he tends to end up being the owner. So, if user "Administrator"
runs this tuning tool, odds are pretty good that user running the tool
will own the postgresql.conf file it creates. AFAIK, there's no
special requirement that Postgres owns this file, it just needs read
privileges, so I'd check the file access rights after running the tool.
yeah, I just checked on my XP system here, my own account 'owns' the
file, but the postgres user has modify/read/write/execute privileges on it.

C:\> cacls D:\postgres\8.3\data\postgresql.conf
D:\postgres\8.3\data\postgresql.conf BUILTIN\Administrators:F
NT AUTHORITY\SYSTEM:F
PORKER\pierce:F
BUILTIN\Users:R
PORKER\postgres:C

one method of 'fixing' this, then, would be...

C:\> cacls \path\to\data\postgresq.conf /e /g:postgres:RWC

now try starting the postgres service process...
C:\> net start pgsql-8.3
The PostgreSQL Database Server 8.3 service is starting.
The PostgreSQL Database Server 8.3 service was started successfully.