Обсуждение: PostgreSQl newbie! -PLease help, pgAdmin3 on Debian!
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.
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.
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.
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
> 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
> > 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
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
> > > > > >>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
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
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 > > > >
Вложения
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
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.
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
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 > > > >
> >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
> >>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
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 > > >
Вложения
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
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
Вложения
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.
--- 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
> > > > > > > > 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
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.
> > > > 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