Thread: PostgreSQl newbie! -PLease help, pgAdmin3 on Debian!

PostgreSQl newbie! -PLease help, pgAdmin3 on Debian!

From
lmyho
Date:
Dear All,

I am totally new to the PostgreSQL.  I just installed the PostgreSQL and pgAdmin3 on my Debian system, using the apt-get install command.  Apparently, the initial database and the user "postgres" have also been automatically created during the installation. 

It seems good.  But I got big trouble to login to this initial db by using this auto-created username "postgres" through pgAdmin!!:(((  The first time failed due to "Ident authentication failed", then follow the suggestion on the pop-up window of pgAdmin, I changed the ident method in the pg_hda.conf file all to md5, then the database ask me for the password!!  I couldn't figure out the passwd so I tried to created rules in the pg_ident.conf file to map both the ordinary user and root user to postgres, then tried again.  But still, failed again by "ident authentication failed"!!!:(((  I've tried many times for all I could think, everytime failed, so frustrated:(((  Everytime I did "pg_ctl reload" before trying, and I could see the failure reason changed after I do reload.

Please can anyone help me here?  I've been so frustrated.:(  If there is auto-created password for this auto-created postgres user, please anyone tell me what it is??  And also otherwise, how can I login using the "ident auth" method??

Any help would be greatly appreciated!!!  Thank you so much for help!!!

leo


Talk is cheap. Use Yahoo! Messenger to make PC-to-Phone calls. Great rates starting at 1¢/min.

Re: PostgreSQl newbie! -PLease help, pgAdmin3 on Debian!

From
Richard Broersma Jr
Date:
 Please can anyone help me here?  I've been so frustrated.:(  If there is auto-created password
> for this auto-created postgres user, please anyone tell me what it is??  And also otherwise, how
> can I log-in using the "ident auth" method??

If you just getting started with postgresql you will want to change the user postgres ident to
"trust".

Then you should be able to log into postgresql using "psql -U postgres -d template1"

There you can change the postgres db-user password with

alter user postgres password 'password';

once completed then log out using "\q"

Now if you wanted to change you ident method back to md5 you will be able to use you new password.

This should work, but you would probably be better server by following the directions in the
postgresql documentation.

Regards,

Richard Broersma Jr.

Re: PostgreSQl newbie! -PLease help, pgAdmin3 on Debian!

From
lmyho
Date:
Hi Rich,

Thank you very much!! Have some further question:

>  Please can anyone help me here?  I've been so frustrated.:(  If there is
> auto-created password
> > for this auto-created postgres user, please anyone tell me what it is??  And
> also otherwise, how
> > can I log-in using the "ident auth" method??
>
> If you just getting started with postgresql you will want to change the user
> postgres ident to
> "trust".

How should I do this?  The current auto-generated pg_hba.conf file read as:
"local all postgres    ident sameuser"
on the first line, and the first line also after the # TYPE DATABASE....METHOD line.


Do you mean I should change the "ident sameuser" at the end to "trust"?

>
> Then you should be able to log into postgresql using "psql -U postgres -d
> template1"

Should I login by this way from command line?

THANK YOU!!
leo

__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

Re: PostgreSQl newbie! -PLease help, pgAdmin3 on Debian!

From
Richard Broersma Jr
Date:
> How should I do this?  The current auto-generated pg_hba.conf file read as:
> "local all postgres    ident sameuser"
> on the first line, and the first line also after the # TYPE DATABASE....METHOD line.
>
>
> Do you mean I should change the "ident sameuser" at the end to "trust"?

Below is a snippet from:

http://www.postgresql.org/docs/8.0/static/client-authentication.html#EXAMPLE-PG-HBA.CONF

Some examples of pg_hba.conf entries are shown in Example 19-1. See the next section for details
on the different authentication methods.

Example 19-1. Example pg_hba.conf entries

# Allow any user on the local system to connect to any database under
# any user name using Unix-domain sockets (the default for local
# connections).
#
# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD
local   all         postgres                          trust


The above line will allow you to connect from the command line of your postgresql server.  This
will get you started.  I can't remember what type of connect pgadmin uses.  It might be "TYPE"
host.   If it is you will need to add another line to the conf file for that type.  Also,  there
is another postgres conf (I can't remember which on off the top of my head) file that will need
the optional "-I" set to allow you to connect from remote IP addresses.  i.e. pgadmin.  If all
else fails, read the help docs; the beginning sections are newbie friendly.

> Should I login by this way from command line?

yes

Re: PostgreSQl newbie! -PLease help, pgAdmin3 on Debian!

From
lmyho
Date:

> > How should I do this?  The current auto-generated pg_hba.conf file read as:
> > "local all postgres    ident sameuser"
> > on the first line, and the first line also after the # TYPE DATABASE....METHOD
> line.
> >
> >
> > Do you mean I should change the "ident sameuser" at the end to "trust"?
>
> Below is a snippet from:
>
>
http://www.postgresql.org/docs/8.0/static/client-authentication.html#EXAMPLE-PG-HBA.CONF
>
> Some examples of pg_hba.conf entries are shown in Example 19-1. See the next
> section for details on the different authentication methods.
>
Thank you Richard!!  I've made the changes and can login to the DBs through pgAdmin
now!:) Really appreciated your help!!!  I am starting to learn about the postgresql
now.:))

>I can't remember what type of connect pgadmin uses.  It might be "TYPE" host.
>If it is you will need to add another line to the conf file for that type.
Seems like the pgAdmin3 uses also the Unix socket pipe. I left only the first line
in the pg_hba.conf enabled as "local all postgres    trust" and pgAdmin3 was able to
login.:)

>  Also,  there
> is another postgres conf (I can't remember which on off the top of my head) file
> that will need the optional "-I" set to allow you to connect from remote IP
> addresses.  i.e. pgadmin.  If all else fails, read the help docs; the beginning
sections are newbie friendly.
>
I am checking on this.;)  If you got any more hints, please let me know.  THANK
YOU!!!
leo

__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

Re: PostgreSQl newbie! -PLease help, pgAdmin3 on Debian!

From
Chris Travers
Date:
lmyho wrote:

>
>
>>Some examples of pg_hba.conf entries are shown in Example 19-1. See the next
>>section for details on the different authentication methods.
>>
>>
>>
>Thank you Richard!!  I've made the changes and can login to the DBs through pgAdmin
>now!:) Really appreciated your help!!!  I am starting to learn about the postgresql
>now.:))
>
>
The authentication methods are handled between the libraries (libpq and
libpxx) and the server.  They are transparent to the application.

identd sameuser means use the identd service to require look up the
owner of the process and log him/her in accordingly.  This means that
you cannot change your login name but don't require a password (if you
are on the DB server-- do not trust it remotely).

For remote connections kerberos can be used instead, though this takes
more setup and knowledge of Kerberos as a system.

>
>
>>I can't remember what type of connect pgadmin uses.  It might be "TYPE" host.
>>If it is you will need to add another line to the conf file for that type.
>>
>>
>Seems like the pgAdmin3 uses also the Unix socket pipe. I left only the first line
>in the pg_hba.conf enabled as "local all postgres    trust" and pgAdmin3 was able to
>login.:)
>
>
Right.  But passwords are ineffective with trust authentication.  If you
need them you will need to use password, md5, or the like instead.

Best Wishes,
Chris Travers
Metatron Technology Consulting

Re: PostgreSQl newbie! -PLease help, pgAdmin3 on Debian!

From
lmyho
Date:
> >
> >
> >>Some examples of pg_hba.conf entries are shown in Example 19-1. See the next
> >>section for details on the different authentication methods.
> >>
> >
> The authentication methods are handled between the libraries (libpq and
> libpxx) and the server.  They are transparent to the application.
>
> identd sameuser means use the identd service to require look up the
> owner of the process and log him/her in accordingly.  This means that
> you cannot change your login name but don't require a password (if you
> are on the DB server-- do not trust it remotely).
>
> For remote connections kerberos can be used instead, though this takes
> more setup and knowledge of Kerberos as a system.
>
Really thank you Chris!!  I really don't know these.:( Forgot what I  tried last
night, but I ke[t using user postgres to try everytime as I was trying to login as
an admin user. So here you mean actually with the "ident sameuser", the other
username can also login if the access is issued from the user's own process?  (not
sure how should I state it correctly, but hope you know what I mean):


> >
> >>I can't remember what type of connect pgadmin uses.  It might be "TYPE" host.
> >>If it is you will need to add another line to the conf file for that type.
> >>
> >>
> >Seems like the pgAdmin3 uses also the Unix socket pipe. I left only the first
> >line in the pg_hba.conf enabled as "local all postgres    trust" and pgAdmin3
> >was able to login.:)
> >
> >
> Right.  But passwords are ineffective with trust authentication.

Thank you for this knowledge too!! So appreciated! otherwise I may end up with my
trust and passwd setting and going to real work!:( wish the doc mentioned it  as
well.

Very kind regards,
leo

__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

Re: PostgreSQl newbie! -PLease help, pgAdmin3 on Debian!

From
Chris Travers
Date:
lmyho wrote:

>>>
>>>
>>>
>>>
>>>>Some examples of pg_hba.conf entries are shown in Example 19-1. See the next
>>>>section for details on the different authentication methods.
>>>>
>>>>
>>>>
>>The authentication methods are handled between the libraries (libpq and
>>libpxx) and the server.  They are transparent to the application.
>>
>>identd sameuser means use the identd service to require look up the
>>owner of the process and log him/her in accordingly.  This means that
>>you cannot change your login name but don't require a password (if you
>>are on the DB server-- do not trust it remotely).
>>
>>For remote connections kerberos can be used instead, though this takes
>>more setup and knowledge of Kerberos as a system.
>>
>>
>>
>Really thank you Chris!!  I really don't know these.:( Forgot what I  tried last
>night, but I ke[t using user postgres to try everytime as I was trying to login as
>an admin user. So here you mean actually with the "ident sameuser", the other
>username can also login if the access is issued from the user's own process?  (not
>sure how should I state it correctly, but hope you know what I mean):
>
>

Perhaps I wasn't clear.

With identd authentication a username is still specified.  But you have
to own the process to login with that username.  So generally speaking
(absent sudo and the like) you can generally login only with the same
username as your OS username.

The documentation of PostgreSQL goes into a bit of depth on
authentication, but if you want a little bit to get started, you might
find the following article helpful:

http://www.metatrontech.com/sql-ledger-wiki/index.php?UnderstandingPostgreSQLAuthentication

Best Wishes,
Chris Travers
Metatron Technology Consulting

>Perhaps I wasn't clear.
.
>With identd authentication a username is still specified.  But you have
>to own the process to login with that username.  So generally speaking
>(absent sudo and the like) you can generally login only with the same
>username as your OS username.

>The documentation of PostgreSQL goes into a bit of depth on
>authentication, but if you want a little bit to get started, you might
>find the following article helpful:

>http://www.metatrontech.com/sql-ledger-wiki/index.php?UnderstandingPostgreSQLAuthentication

Hi Chris,

Hope you are on line and can see this mail soon. Just turned on the Debian system computer today but got trouble at
systembooting time when it's trying to start the PostgreSQL server!! 

What I did yesterday was: changed the system created password of user postgres, by "alter user postgres password
'blabal...'", and changed the ident method to md5, in order to login as postgres through pgAdmin3 into the database.
Everythingseemed fine yesterday while I was in the system.  I loged out after 1am this very morning, and just to login
againtoday right now.   

However, the system doesn't work anymore.:(( When the Debian systen booting, it stoped at "Starting PostgreSQL 8.1
databaseserver: main", and hung there forever...  
I tried to start againfrom the Recovery mode, was able to login as root, but don't know what to do at the command line
mode.:(( When I exit from the recovery mode, the system tried to switch to the login point and seemed to use the
standardprocess to approach the login point, so it went to the point of starting the Postgresql server again and hung
thereagain with the same info: "Starting PostgreSQL 8.1 database server: main", and goes nowhere:((( 

What is the problem??  Is there angthing has gone wrong with my change of the password of user postgres yesterday?  Is
thereanyway to make the system booting go through??  By the way it has been impossible to reverse the password of user
postgresbecause the original password was generated by the system during installation, and I don't even have a clue
aboutwhat it is:(( 

Any help idea would be greatly appreciated!! Thank you for help very much!!!

leo

Re: Postgresql server does not start!:( -Re: PostgreSQl newbie!

From
Chris Travers
Date:
Things to check when PostgreSQL doesn't seem to start:

1)  Are there any postmaster programs running (ps -A | grep postmaster
is useful here)
2)  If not, is there a postmaster.pid file n the postgresql data
directory? (do a search for the file if you have to-- if no postmaster
processes are running delete the file and try again)
3)  What are the final entries in the serverlog in the data directory?

In your case, if PostgreSQL is hanging, I would suspect that the problem
is likely to be with something else.  Hopefully there would be info in
the serverlog that would help.

Best Wishes,
Chris Travers
Metatron Technology Consulting

lmyho wrote:

>
>
>
>>Perhaps I wasn't clear.
>>
>>
>.
>
>
>>With identd authentication a username is still specified.  But you have
>>to own the process to login with that username.  So generally speaking
>>(absent sudo and the like) you can generally login only with the same
>>username as your OS username.
>>
>>
>
>
>
>>The documentation of PostgreSQL goes into a bit of depth on
>>authentication, but if you want a little bit to get started, you might
>>find the following article helpful:
>>
>>
>
>
>
>>http://www.metatrontech.com/sql-ledger-wiki/index.php?UnderstandingPostgreSQLAuthentication
>>
>>
>
>Hi Chris,
>
>Hope you are on line and can see this mail soon. Just turned on the Debian system computer today but got trouble at
systembooting time when it's trying to start the PostgreSQL server!! 
>
>What I did yesterday was: changed the system created password of user postgres, by "alter user postgres password
'blabal...'", and changed the ident method to md5, in order to login as postgres through pgAdmin3 into the database.
Everythingseemed fine yesterday while I was in the system.  I loged out after 1am this very morning, and just to login
againtoday right now.   
>
>However, the system doesn't work anymore.:(( When the Debian systen booting, it stoped at "Starting PostgreSQL 8.1
databaseserver: main", and hung there forever...  
>I tried to start againfrom the Recovery mode, was able to login as root, but don't know what to do at the command line
mode.:(( When I exit from the recovery mode, the system tried to switch to the login point and seemed to use the
standardprocess to approach the login point, so it went to the point of starting the Postgresql server again and hung
thereagain with the same info: "Starting PostgreSQL 8.1 database server: main", and goes nowhere:((( 
>
>What is the problem??  Is there angthing has gone wrong with my change of the password of user postgres yesterday?  Is
thereanyway to make the system booting go through??  By the way it has been impossible to reverse the password of user
postgresbecause the original password was generated by the system during installation, and I don't even have a clue
aboutwhat it is:(( 
>
>Any help idea would be greatly appreciated!! Thank you for help very much!!!
>
>leo
>
>---------------------------(end of broadcast)---------------------------
>TIP 1: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo@postgresql.org so that your
>       message can get through to the mailing list cleanly
>
>
>
>


Attachment

Re: Postgresql server does not start!:( -Re: PostgreSQl newbie!

From
Tom Lane
Date:
Chris Travers <chris@verkiel.metatrontech.com> writes:
> Things to check when PostgreSQL doesn't seem to start:
> ...
> In your case, if PostgreSQL is hanging, I would suspect that the problem
> is likely to be with something else.  Hopefully there would be info in
> the serverlog that would help.

What I'm guessing is that the server started just fine, but the Debian
initscript is trying to connect to it (to verify that it started) and
that's failing because of the change in authentication options.  You
might have to remove the -w option from pg_ctl, or some such, in the
script ... or else fix things so that root is allowed to connect ...

            regards, tom lane

Re: Postgresql server does not start!:( -Re: PostgreSQl newbie!

From
lmyho
Date:

Chris Travers <chris@verkiel.metatrontech.com> writes:
> Things to check when PostgreSQL doesn't seem to start:
> ...
> In your case, if PostgreSQL is hanging, I would suspect that the problem
> is likely to be with something else.  Hopefully there would be info in
> the serverlog that would help.

What I'm guessing is that the server started just fine, but the Debian
initscript is trying to connect to it (to verify that it started) and
that's failing because of the change in authentication options.  You
might have to remove the -w option from pg_ctl, or some such, in the
script ... or else fix things so that root is allowed to connect ...

Hi Tom,  Thank you for the idea!

But how should I do this?  I thought pg_ctl is a binary file, how should I make change of it?  And for "or else fix
thingsso that root is allowed to connect ...", what should I do with this?  Would you please give me more hints?
Thanksalot!!! 

leo

Hi Chris,

thanks for the check list! I just came back from the Debian Recovery mode and checked all of them. please see below:

>Things to check when PostgreSQL doesn't seem to start:

>1)  Are there any postmaster programs running (ps -A | grep postmaster
is useful here)

Checked. No postmaster running.

>2)  If not, is there a postmaster.pid file n the postgresql data
directory? (do a search for the file if you have to-- if no postmaster
processes are running delete the file and try again)

Checked.  No postmaster.pid file in the postgres data directory.

>3)  What are the final entries in the serverlog in the data directory?

There is a long list of the log files of today as I started the system a few times. (By the way, The computer was
poweredoff before I started to login.)  One big log file of yesterday.  All today's log files are similar except the
firstone has a long list of:  
...
autovacuum:processing database "progres"
autovacuum: processing database "template1"
autovacuum:processing database "progres"
autovacuum: processing database "template1"
...

In more detail, the logs looks like:
...
db system was shutdown at <time>
checkingpoint record is at 0/33ABC4
redo record is at 0/33ABC4;undo record is at 0/0; sgutdown TRUE
next transaction ID: xx OID: xx
next MultiXactId:1; MultiXactOffset:0
database system is ready
transaction ID wrap limit is 2147484146, limited by database "postgres"
imcomplete startup packet
autovacuum:processing database "progres"
autovacuum: processing database "template1"
autovacuum:processing database "progres"
autovacuum: processing database "template1"
...
imcomplete startup packet
received fast shutdown request
shuttingdown
database system is shut down
logger shutting down


Something like this, each has a little different, but ends the same way.

I don't quite understand the contents here.  Can you see any problem?

I look forward any further help.  Thnaks a lot!!!

leo


>In your case, if PostgreSQL is hanging, I would suspect that the problem
>is likely to be with something else.  Hopefully there would be info in
>the serverlog that would help.

Re: Postgresql server does not start!:( -Re: PostgreSQl newbie!

From
Chris Travers
Date:
Tom Lane wrote:

>Chris Travers <chris@verkiel.metatrontech.com> writes:
>
>
>>Things to check when PostgreSQL doesn't seem to start:
>>...
>>In your case, if PostgreSQL is hanging, I would suspect that the problem
>>is likely to be with something else.  Hopefully there would be info in
>>the serverlog that would help.
>>
>>
>
>What I'm guessing is that the server started just fine, but the Debian
>initscript is trying to connect to it (to verify that it started) and
>that's failing because of the change in authentication options.  You
>might have to remove the -w option from pg_ctl, or some such, in the
>script ... or else fix things so that root is allowed to connect ...
>
>
Good guess, given the log files he posted :-)

Best Wishes,
Chris Travers
Metatron Technology Consulting

Re: Postgresql server does not start!:( -Re: PostgreSQl newbie!

From
Chris Travers
Date:
lmyho wrote:

>Chris Travers <chris@verkiel.metatrontech.com> writes:
>
>
>>Things to check when PostgreSQL doesn't seem to start:
>>...
>>In your case, if PostgreSQL is hanging, I would suspect that the problem
>>is likely to be with something else.  Hopefully there would be info in
>>the serverlog that would help.
>>
>>
>
>What I'm guessing is that the server started just fine, but the Debian
>initscript is trying to connect to it (to verify that it started) and
>that's failing because of the change in authentication options.  You
>might have to remove the -w option from pg_ctl, or some such, in the
>script ... or else fix things so that root is allowed to connect ...
>
>
To fix this you will ideally want to be familiar enough with the init
script system to be able to edit it.  In most distros (never used
Debian) it is in /etc/init.d/ and is named something like postgresql.
Look in there.  If you can't figure it out, you can post it in the email.

Best Wishes,
Chris Travers
Metatron Technology Consulting

>Hi Tom,  Thank you for the idea!
>
>But how should I do this?  I thought pg_ctl is a binary file, how should I make change of it?  And for "or else fix
thingsso that root is allowed to connect ...", what should I do with this?  Would you please give me more hints?
Thanksalot!!! 
>
>leo
>
>
>
>


Re: Postgresql server does not start!:( -Re: PostgreSQl newbie!

From
lmyho
Date:
>
>What I'm guessing is that the server started just fine, but the Debian
>initscript is trying to connect to it (to verify that it started) and
>that's failing because of the change in authentication options.  You
>might have to remove the -w option from pg_ctl, or some such, in the
>script ... or else fix things so that root is allowed to connect ...
>
>
Good guess, given the log files he posted :-)

Chris and  Tom,
You must have found some problem from the log file info.  Then what should I do and how?

Thank  you!!
leo

Re: Postgresql server does not start!:( -Re: PostgreSQl newbie!

From
lmyho
Date:
>
>>Things to check when PostgreSQL doesn't seem to start:
>>...
>>In your case, if PostgreSQL is hanging, I would suspect that the problem
>>is likely to be with something else.  Hopefully there would be info in
>>the serverlog that would help.
>>
>>
>
>What I'm guessing is that the server started just fine, but the Debian
>initscript is trying to connect to it (to verify that it started) and
>that's failing because of the change in authentication options.  You
>might have to remove the -w option from pg_ctl, or some such, in the
>script ... or else fix things so that root is allowed to connect ...
>
>
To fix this you will ideally want to be familiar enough with the init
script system to be able to edit it.  In most distros (never used
Debian) it is in /etc/init.d/ and is named something like postgresql.
Look in there.  If you can't figure it out, you can post it in the email.


I will do to check about it.  But I am not sure if I can fix something as I am new to Debian too,:( just installed it
lastweek, one of the purpose is to use postgresql.  Unfortunately, only a beginner at this time for both systems.:(   

I may post back the info of the init file,  I know there is postgresql in the init.d directory.  And hope to get your
furtherhelp from that. 

Thanks!!!
leo

Re: Postgresql server does not start!:( -Re: PostgreSQl newbie!

From
Chris Travers
Date:
lmyho wrote:

>>>Things to check when PostgreSQL doesn't seem to start:
>>>...
>>>In your case, if PostgreSQL is hanging, I would suspect that the problem
>>>is likely to be with something else.  Hopefully there would be info in
>>>the serverlog that would help.
>>>
>>>
>>>
>>>
>>What I'm guessing is that the server started just fine, but the Debian
>>initscript is trying to connect to it (to verify that it started) and
>>that's failing because of the change in authentication options.  You
>>might have to remove the -w option from pg_ctl, or some such, in the
>>script ... or else fix things so that root is allowed to connect ...
>>
>>
>>
>>
>To fix this you will ideally want to be familiar enough with the init
>script system to be able to edit it.  In most distros (never used
>Debian) it is in /etc/init.d/ and is named something like postgresql.
>Look in there.  If you can't figure it out, you can post it in the email.
>
>
>I will do to check about it.  But I am not sure if I can fix something as I am new to Debian too,:( just installed it
lastweek, one of the purpose is to use postgresql.  Unfortunately, only a beginner at this time for both systems.:(   
>
>
It is usually a simple shell script that starts/stops PostgreSQL.  At
least the ones that come with the PostgreSQL source are pretty easy to
understand.

>
>I may post back the info of the init file,  I know there is postgresql in the init.d directory.  And hope to get your
furtherhelp from that. 
>
>
That would be the file.
Best Wishes,
Chris Travers
Metatron Technology Consulting

>
>
>


Attachment
lmyho <lm_yho@yahoo.com> writes:
> In more detail, the logs looks like:
> ...
> imcomplete startup packet
> received fast shutdown request
> shuttingdown
> database system is shut down
> logger shutting down

> Something like this, each has a little different, but ends the same way.

This is not a "server does not start" problem.  This is something
sending SIGINT to the postmaster, which makes it terminate.

Do all the log files mention "incomplete startup packet" right before
the shutdown request?  That's not a very usual message, and it might
be a clue to what's causing the SIGINT.

You might try setting log_line_prefix to '%m ' in postgresql.conf to add
timestamps to the log messages --- that would tell us more about which
messages are actually appearing close together in time.

            regards, tom lane

> lmyho <lm_yho@yahoo.com> writes:
> > In more detail, the logs looks like:
> > ...
> > imcomplete startup packet
> > received fast shutdown request
> > shuttingdown
> > database system is shut down
> > logger shutting down
>
> > Something like this, each has a little different, but ends the same way.
>
> This is not a "server does not start" problem.  This is something
> sending SIGINT to the postmaster, which makes it terminate.
>
> Do all the log files mention "incomplete startup packet" right before
> the shutdown request?  That's not a very usual message, and it might
> be a clue to what's causing the SIGINT.
>
> You might try setting log_line_prefix to '%m ' in postgresql.conf to add
> timestamps to the log messages --- that would tell us more about which
> messages are actually appearing close together in time.
 Hi Tom, You are right, every log file contain "incomplete startup packet", so
sad.:(  Actually I have timestamp in the log file, but yesterday I was typing those
info to the post (as I couldn't transfer the files to Windows where I sent the
emails), I just ignored it.  I am copying two of them here and hope you can help me
figure the things out!

Also I have just finally find the reasone why system couldn't boot up last night, it
boots up now (see reason below) and I am in the system already so I can copy the log
files out.  But doesn't change the "incomplete startup packet" line in the new log
files, so the problem must still there, and will crash thesystem sometime.  So
please do help me out of it!  Thanks!!

The reason why the system couldn't boot is only because the system generated
pg_hba.conf file was modified when I altered the password of user postgres.  The
original pg_hba.conf file has a first line read as "local all postgres  ident
sameuser".  After I altered the passwd of postgres, I changed the end of this line
to "md5", which allows me to use the passwd to login to the db as postgres through
pgAdmin.  But my Debian system doesn't like it!  When booting the system, it found
this line changed, so it refuse to startup my db server! When I changed ot back, it
boots fine.

But the "incomplete startup packet" line still in the log:
The newest log (server still running):
/pg_log# more postgresql-2006-03-30_103437.log
2006-03-30 10:34:37 CSTLOG:  database system was shut down at 2006-03-30 10:33:42
CST
2006-03-30 10:34:37 CSTLOG:  checkpoint record is at 0/33B9B0
2006-03-30 10:34:37 CSTLOG:  redo record is at 0/33B9B0; undo record is at 0/0;
shutdown TRUE
2006-03-30 10:34:37 CSTLOG:  next transaction ID: 5843; next OID: 16386
2006-03-30 10:34:37 CSTLOG:  next MultiXactId: 1; next MultiXactOffset: 0
2006-03-30 10:34:37 CSTLOG:  database system is ready
2006-03-30 10:34:37 CSTLOG:  transaction ID wrap limit is 2147484146, limited by
database "postgres"
2006-03-30 10:34:37 CSTLOG:  autovacuum: processing database "template1"
2006-03-30 10:34:37 CSTLOG:  incomplete startup packet
2006-03-30 10:35:39 CSTLOG:  autovacuum: processing database "postgres"
2006-03-30 10:36:39 CSTLOG:  autovacuum: processing database "template1"
2006-03-30 10:37:39 CSTLOG:  autovacuum: processing database "postgres"
2006-03-30 10:38:39 CSTLOG:  autovacuum: processing database "template1"
2006-03-30 10:39:39 CSTLOG:  autovacuum: processing database "postgres"
2006-03-30 10:40:39 CSTLOG:  autovacuum: processing database "template1"
2006-03-30 10:41:39 CSTLOG:  autovacuum: processing database "postgres"
2006-03-30 10:42:39 CSTLOG:  autovacuum: processing database "template1"
2006-03-30 10:43:39 CSTLOG:  autovacuum: processing database "postgres"
2006-03-30 10:44:39 CSTLOG:  autovacuum: processing database "template1"
2006-03-30 10:45:39 CSTLOG:  autovacuum: processing database "postgres"
2006-03-30 10:46:39 CSTLOG:  autovacuum: processing database "template1"
...

The last one (successful bootup and shutdown):
pg_log# more postgresql-2006-03-30_103237.log
2006-03-30 10:32:37 CSTLOG:  database system was shut down at 2006-03-30 10:32:20
CST
2006-03-30 10:32:37 CSTLOG:  checkpoint record is at 0/33B96C
2006-03-30 10:32:37 CSTLOG:  redo record is at 0/33B96C; undo record is at 0/0;
shutdown TRUE
2006-03-30 10:32:37 CSTLOG:  next transaction ID: 5839; next OID: 16386
2006-03-30 10:32:37 CSTLOG:  next MultiXactId: 1; next MultiXactOffset: 0
2006-03-30 10:32:37 CSTLOG:  database system is ready
2006-03-30 10:32:37 CSTLOG:  transaction ID wrap limit is 2147484146, limited by
database "postgres"
2006-03-30 10:32:37 CSTLOG:  autovacuum: processing database "template1"
2006-03-30 10:33:37 CSTLOG:  autovacuum: processing database "postgres"
2006-03-30 10:33:42 CSTLOG:  incomplete startup packet
2006-03-30 10:33:42 CSTLOG:  received fast shutdown request
2006-03-30 10:33:42 CSTLOG:  shutting down
2006-03-30 10:33:42 CSTLOG:  database system is shut down
2006-03-30 10:33:42 CSTLOG:  logger shutting down

You can see they all have that line!  Can you see what's may be the problem?  Thank
you!!!

Kind regards,
lro


__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

Re: Postgresql server does not start!:( -Re: PostgreSQl newbie!

From
Chris Travers
Date:
lmyho wrote:

>
>The last one (successful bootup and shutdown):
>pg_log# more postgresql-2006-03-30_103237.log
>2006-03-30 10:32:37 CSTLOG:  database system was shut down at 2006-03-30 10:32:20
>CST
>2006-03-30 10:32:37 CSTLOG:  checkpoint record is at 0/33B96C
>2006-03-30 10:32:37 CSTLOG:  redo record is at 0/33B96C; undo record is at 0/0;
>shutdown TRUE
>2006-03-30 10:32:37 CSTLOG:  next transaction ID: 5839; next OID: 16386
>2006-03-30 10:32:37 CSTLOG:  next MultiXactId: 1; next MultiXactOffset: 0
>2006-03-30 10:32:37 CSTLOG:  database system is ready
>2006-03-30 10:32:37 CSTLOG:  transaction ID wrap limit is 2147484146, limited by
>database "postgres"
>2006-03-30 10:32:37 CSTLOG:  autovacuum: processing database "template1"
>2006-03-30 10:33:37 CSTLOG:  autovacuum: processing database "postgres"
>2006-03-30 10:33:42 CSTLOG:  incomplete startup packet
>2006-03-30 10:33:42 CSTLOG:  received fast shutdown request
>2006-03-30 10:33:42 CSTLOG:  shutting down
>2006-03-30 10:33:42 CSTLOG:  database system is shut down
>2006-03-30 10:33:42 CSTLOG:  logger shutting down
>
>You can see they all have that line!  Can you see what's may be the problem?  Thank
>you!!!
>
>
Interesting.... Database starts up at 10:32:37 and processes all
databases through autovacuum.
Then exactly a minute later, it processes the postgres database.
5 seconds later, you get a SIGINT.

Is there something wrong with autovacuum?  Normally I would doubt it but
the other possibility is something is waiting for just over a minute and
then shutting down the database.

If you su postgres, can you run pg_ctl and get the database up by itself?

Best Wishes,
Chris Travers
Metatron Technology Consulting

Attachment

Re: Postgresql server does not start!:( -Re: PostgreSQl

From
Scott Marlowe
Date:
On Thu, 2006-03-30 at 11:10, Chris Travers wrote:
> lmyho wrote:
>
> >
> >The last one (successful bootup and shutdown):
> >pg_log# more postgresql-2006-03-30_103237.log
> >2006-03-30 10:32:37 CSTLOG:  database system was shut down at 2006-03-30 10:32:20
> >CST
> >2006-03-30 10:32:37 CSTLOG:  checkpoint record is at 0/33B96C
> >2006-03-30 10:32:37 CSTLOG:  redo record is at 0/33B96C; undo record is at 0/0;
> >shutdown TRUE
> >2006-03-30 10:32:37 CSTLOG:  next transaction ID: 5839; next OID: 16386
> >2006-03-30 10:32:37 CSTLOG:  next MultiXactId: 1; next MultiXactOffset: 0
> >2006-03-30 10:32:37 CSTLOG:  database system is ready
> >2006-03-30 10:32:37 CSTLOG:  transaction ID wrap limit is 2147484146, limited by
> >database "postgres"
> >2006-03-30 10:32:37 CSTLOG:  autovacuum: processing database "template1"
> >2006-03-30 10:33:37 CSTLOG:  autovacuum: processing database "postgres"
> >2006-03-30 10:33:42 CSTLOG:  incomplete startup packet
> >2006-03-30 10:33:42 CSTLOG:  received fast shutdown request
> >2006-03-30 10:33:42 CSTLOG:  shutting down
> >2006-03-30 10:33:42 CSTLOG:  database system is shut down
> >2006-03-30 10:33:42 CSTLOG:  logger shutting down
> >
> >You can see they all have that line!  Can you see what's may be the problem?  Thank
> >you!!!
> >
> >
> Interesting.... Database starts up at 10:32:37 and processes all
> databases through autovacuum.
> Then exactly a minute later, it processes the postgres database.
> 5 seconds later, you get a SIGINT.
>
> Is there something wrong with autovacuum?  Normally I would doubt it but
> the other possibility is something is waiting for just over a minute and
> then shutting down the database.
>
> If you su postgres, can you run pg_ctl and get the database up by itself?

If I remember correctly, this is a problem with the debian startup
script requiring trust for the postgres superuser, and was discussed
sometime last year.  I'll wander through the archives in a free moment
and see if I can find it.

Re: Postgresql server does not start!:( -Re: PostgreSQl newbie!

From
lmyho
Date:

--- Chris Travers <chris@metatrontech.com> wrote:

> lmyho wrote:
>
> >
> >The last one (successful bootup and shutdown):
> >pg_log# more postgresql-2006-03-30_103237.log
> >2006-03-30 10:32:37 CSTLOG:  database system was shut down at 2006-03-30 10:32:20
> >CST
> >2006-03-30 10:32:37 CSTLOG:  checkpoint record is at 0/33B96C
> >2006-03-30 10:32:37 CSTLOG:  redo record is at 0/33B96C; undo record is at 0/0;
> >shutdown TRUE
> >2006-03-30 10:32:37 CSTLOG:  next transaction ID: 5839; next OID: 16386
> >2006-03-30 10:32:37 CSTLOG:  next MultiXactId: 1; next MultiXactOffset: 0
> >2006-03-30 10:32:37 CSTLOG:  database system is ready
> >2006-03-30 10:32:37 CSTLOG:  transaction ID wrap limit is 2147484146, limited by
> >database "postgres"
> >2006-03-30 10:32:37 CSTLOG:  autovacuum: processing database "template1"
> >2006-03-30 10:33:37 CSTLOG:  autovacuum: processing database "postgres"
> >2006-03-30 10:33:42 CSTLOG:  incomplete startup packet
> >2006-03-30 10:33:42 CSTLOG:  received fast shutdown request
> >2006-03-30 10:33:42 CSTLOG:  shutting down
> >2006-03-30 10:33:42 CSTLOG:  database system is shut down
> >2006-03-30 10:33:42 CSTLOG:  logger shutting down
> >
> >You can see they all have that line!  Can you see what's may be the problem?
> Thank
> >you!!!
> >
> >
> Interesting.... Database starts up at 10:32:37 and processes all
> databases through autovacuum.
> Then exactly a minute later, it processes the postgres database.
> 5 seconds later, you get a SIGINT.
>
That one was shut down by me, just to see of it will generate the bad line again,
and it did!  Then I restarted the computer and got the new log and it still has the
bad line!  One of the logs even has that line in the middle of a long list of
"autovacuum"s, I was afraid it's too long for the post, but see it below, just to
figure out waht's wrong:
pg_log# more postgresql-2006-03-30_092929.log
2006-03-30 09:29:29 CSTLOG:  database system was shut down at 2006-03-30 09:29:15
CST
2006-03-30 09:29:29 CSTLOG:  checkpoint record is at 0/33B8E4
2006-03-30 09:29:29 CSTLOG:  redo record is at 0/33B8E4; undo record is at 0/0;
shutdown TRUE
2006-03-30 09:29:29 CSTLOG:  next transaction ID: 5713; next OID: 16386
2006-03-30 09:29:29 CSTLOG:  next MultiXactId: 1; next MultiXactOffset: 0
2006-03-30 09:29:29 CSTLOG:  database system is ready
2006-03-30 09:29:29 CSTLOG:  transaction ID wrap limit is 2147484146, limited by
database "postgres"
2006-03-30 09:29:29 CSTLOG:  autovacuum: processing database "postgres"
2006-03-30 09:30:29 CSTLOG:  autovacuum: processing database "template1"
2006-03-30 09:31:29 CSTLOG:  autovacuum: processing database "postgres"
2006-03-30 09:32:29 CSTLOG:  autovacuum: processing database "template1"
2006-03-30 09:33:29 CSTLOG:  autovacuum: processing database "postgres"
2006-03-30 09:34:29 CSTLOG:  autovacuum: processing database "template1"
2006-03-30 09:35:29 CSTLOG:  autovacuum: processing database "postgres"
2006-03-30 09:36:29 CSTLOG:  autovacuum: processing database "template1"
2006-03-30 09:37:29 CSTLOG:  autovacuum: processing database "postgres"
2006-03-30 09:38:29 CSTLOG:  autovacuum: processing database "template1"
2006-03-30 09:39:29 CSTLOG:  autovacuum: processing database "postgres"
2006-03-30 09:40:29 CSTLOG:  autovacuum: processing database "template1"
2006-03-30 09:41:29 CSTLOG:  autovacuum: processing database "postgres"
2006-03-30 09:42:29 CSTLOG:  autovacuum: processing database "template1"
2006-03-30 09:43:29 CSTLOG:  autovacuum: processing database "postgres"
2006-03-30 09:44:29 CSTLOG:  autovacuum: processing database "template1"
2006-03-30 09:45:29 CSTLOG:  autovacuum: processing database "postgres"
2006-03-30 09:46:29 CSTLOG:  autovacuum: processing database "template1"
2006-03-30 09:47:29 CSTLOG:  autovacuum: processing database "postgres"
2006-03-30 09:48:29 CSTLOG:  autovacuum: processing database "template1"
2006-03-30 09:49:29 CSTLOG:  autovacuum: processing database "postgres"
2006-03-30 09:50:29 CSTLOG:  autovacuum: processing database "template1"
2006-03-30 09:51:29 CSTLOG:  autovacuum: processing database "postgres"
2006-03-30 09:52:29 CSTLOG:  autovacuum: processing database "template1"
2006-03-30 09:53:29 CSTLOG:  autovacuum: processing database "postgres"
2006-03-30 09:54:29 CSTLOG:  autovacuum: processing database "template1"
2006-03-30 09:55:29 CSTLOG:  autovacuum: processing database "postgres"
2006-03-30 09:56:29 CSTLOG:  autovacuum: processing database "template1"
2006-03-30 09:57:29 CSTLOG:  autovacuum: processing database "postgres"
2006-03-30 09:58:29 CSTLOG:  autovacuum: processing database "template1"
2006-03-30 09:59:29 CSTLOG:  autovacuum: processing database "postgres"
2006-03-30 10:00:29 CSTLOG:  autovacuum: processing database "template1"
2006-03-30 10:01:29 CSTLOG:  autovacuum: processing database "postgres"
2006-03-30 10:02:01 CSTLOG:  incomplete startup packet
2006-03-30 10:03:01 CSTLOG:  autovacuum: processing database "template1"
2006-03-30 10:04:01 CSTLOG:  autovacuum: processing database "postgres"
2006-03-30 10:05:01 CSTLOG:  autovacuum: processing database "template1"
2006-03-30 10:06:01 CSTLOG:  autovacuum: processing database "postgres"
2006-03-30 10:07:01 CSTLOG:  autovacuum: processing database "template1"
2006-03-30 10:08:01 CSTLOG:  autovacuum: processing database "postgres"
2006-03-30 10:09:01 CSTLOG:  autovacuum: processing database "template1"
2006-03-30 10:10:01 CSTLOG:  autovacuum: processing database "postgres"
2006-03-30 10:11:01 CSTLOG:  autovacuum: processing database "template1"
2006-03-30 10:12:01 CSTLOG:  autovacuum: processing database "postgres"
2006-03-30 10:13:01 CSTLOG:  autovacuum: processing database "template1"
2006-03-30 10:14:01 CSTLOG:  autovacuum: processing database "postgres"
2006-03-30 10:15:01 CSTLOG:  autovacuum: processing database "template1"
2006-03-30 10:16:01 CSTLOG:  autovacuum: processing database "postgres"
2006-03-30 10:17:01 CSTLOG:  autovacuum: processing database "template1"
2006-03-30 10:18:01 CSTLOG:  autovacuum: processing database "postgres"
2006-03-30 10:19:01 CSTLOG:  autovacuum: processing database "template1"
2006-03-30 10:20:01 CSTLOG:  autovacuum: processing database "postgres"
2006-03-30 10:21:01 CSTLOG:  autovacuum: processing database "template1"
2006-03-30 10:22:01 CSTLOG:  autovacuum: processing database "postgres"
2006-03-30 10:23:01 CSTLOG:  autovacuum: processing database "template1"
2006-03-30 10:24:01 CSTLOG:  autovacuum: processing database "postgres"
2006-03-30 10:25:01 CSTLOG:  autovacuum: processing database "template1"
2006-03-30 10:26:01 CSTLOG:  autovacuum: processing database "postgres"
2006-03-30 10:27:01 CSTLOG:  autovacuum: processing database "template1"
2006-03-30 10:27:05 CSTLOG:  received smart shutdown request
2006-03-30 10:27:05 CSTLOG:  shutting down
2006-03-30 10:27:05 CSTLOG:  database system is shut down
2006-03-30 10:27:05 CSTLOG:  logger shutting down


> Is there something wrong with autovacuum?  Normally I would doubt it but
> the other possibility is something is waiting for just over a minute and
> then shutting down the database.
>
Please help me find it out!  Thanks!! I just get in touch of postgresql, has no idea
what it suppose to be.  The databse just be installed for two days and I am
struggling on it all the time!!


> If you su postgres, can you run pg_ctl and get the database up by itself?
>
Yes I can.  And some log files were from such running.

Help!!

Kind regards,
leo



__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around
http://mail.yahoo.com


>
> If you su postgres, can you run pg_ctl and get the database up by itself?

By the way, I can run pg_ctl to start the db server only after I links to the data
directory.  Debian's auto startup doesn't go this way but by the it's own scripts.

leo

__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

Re: Postgresql server does not start!:( -Re: PostgreSQl newbie!

From
lmyho
Date:
> > >
> > >
> > Interesting.... Database starts up at 10:32:37 and processes all
> > databases through autovacuum.
> > Then exactly a minute later, it processes the postgres database.
> > 5 seconds later, you get a SIGINT.
> >
> > Is there something wrong with autovacuum?  Normally I would doubt it but
> > the other possibility is something is waiting for just over a minute and
> > then shutting down the database.
> >
> > If you su postgres, can you run pg_ctl and get the database up by itself?
>
> If I remember correctly, this is a problem with the debian startup
> script requiring trust for the postgres superuser, and was discussed
> sometime last year.  I'll wander through the archives in a free moment
> and see if I can find it.

Great Scott!! Thank you!!  This system was just installed on Debian (2 days), and
the original pg_hba.conf file authen method was "ident sameuser" and appears not
allow it to be changed!

Look forward to your information!! Thanks a lot!!

regards,
leo
>


__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

Re: Postgresql server does not start!:( -Re: PostgreSQl

From
Scott Marlowe
Date:
On Thu, 2006-03-30 at 11:38, lmyho wrote:
> > > >
> > If I remember correctly, this is a problem with the debian startup
> > script requiring trust for the postgres superuser, and was discussed
> > sometime last year.  I'll wander through the archives in a free moment
> > and see if I can find it.
>
> Great Scott!! Thank you!!  This system was just installed on Debian (2 days), and
> the original pg_hba.conf file authen method was "ident sameuser" and appears not
> allow it to be changed!
>
> Look forward to your information!! Thanks a lot!!

Can't find the thread right now (my search-fu is not strong today) but I
think this thread has covered all the basic points.  Does anyone know if
it is possible for the startup script to read the ~/.pgpass file of the
postgres user on startup and use that if you absolutely have to have the
main postgres user have a password on it in debian?

My guess is that you could also just comment out the lines in the init
script that are hanging, as the actual db startup should not require a
password.

Re: Postgresql server does not start!:( -Re: PostgreSQl

From
lmyho
Date:


> >
> > Great Scott!! Thank you!!  This system was just installed on Debian (2 days),
> and
> > the original pg_hba.conf file authen method was "ident sameuser" and appears not
> > allow it to be changed!
> >
> > Look forward to your information!! Thanks a lot!!
>
> Can't find the thread right now (my search-fu is not strong today) but I
> think this thread has covered all the basic points.  Does anyone know if
> it is possible for the startup script to read the ~/.pgpass file of the
> postgres user on startup and use that if you absolutely have to have the
> main postgres user have a password on it in debian?
>
> My guess is that you could also just comment out the lines in the init
> script that are hanging, as the actual db startup should not require a
> password.
>
That's right. And seems like the init script does not check about password either.

I also heard that Bebian install Posqtgresql with a locked password. Actually there
is no .pgpass under ~/ of postgres.  the pgAdmin3 generate one after I altered the
original password (which I don't know what it is) of progres and used pgAdmin to
login using the new password.

But still, the broken connection is always there in anycase!  How can I find out
what caused the broken connection?  Anyone has any ideas?  I need your help! as I
haven't worked on *nix for years and have forgot how to do such things. Just come
back recently for Debian, but got so many troubles on the new system.:(  I don't
want to run the system on a possibly harmful environment!

I am working on it, but I need your help please! If you have any idea for what I
should do, please let me know.  Thank you!!

leo




__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around
http://mail.yahoo.com