Обсуждение: First Time Starting Up PostgreSQL and Having Problems
I've done a lot of Google searching and PostgreSQL archive searching and can't seem to figure out why I'm having problems. This is a first time install of PostgreSQL version 11.3 on a CentOS server running release 7.6.1810. I've just finishedsetting up the postgresql.conf and pg_hba.conf files and am trying to start up PostgreSQL for the first time. I'mgetting errors no matter what I try. My first attempt resulted in errors that look like the following: syntax error in file "/var/lib/pgsql/11/data/pg_hba.conf" line 84, near token "all" I received this error for every configuration line that wasn't a comment. The "all" is in the USER column. No matter whatI put in the USER column, I get the same error, but listing whatever string is in that column. I wandered if this errorwas just a result of a different error someplace else, so I commented out all lines in this file. That resulted inthe following error: FATAL: exceeded maxAllocatedDescs (16) while trying to open file "/var/lib/pgsql/11/data/pg_hba.conf" I'm at a loss and don't know where to look next. Any help or pointers would be greatly appreciated. Thanks. Brent L. Bates - UNIX System Administrator Information Technology Department - Langley Federal Credit Union 11742 Jefferson Avenue Suite 240 Newport News, VA 23606 Phone: (757) 848-5443, Mobile: (757) 419-0295 COMMUNICATION CONFIDENTIALITY: All information in this communication, including attachments, is strictly confidential andintended solely for delivery to and authorized use by the addressee(s) identified above, and may contain privileged, confidential,proprietary and/or secret information entitled to protection and/or exempt from disclosure under applicablelaw. If you are not the intended recipient, please destroy or delete this communication and any/all attached documentsas the possession, distribution and/or copying of this communication is unauthorized and may be unlawful. If youhave received this communication in error, please remove it in its entirety from your computer and notify the sender.
Brent Bates <BBates@langleyfcu.org> writes: > This is a first time install of PostgreSQL version 11.3 on a CentOS server running release 7.6.1810. I've just finishedsetting up the postgresql.conf and pg_hba.conf files and am trying to start up PostgreSQL for the first time. I'mgetting errors no matter what I try. My first attempt resulted in errors that look like the following: > syntax error in file "/var/lib/pgsql/11/data/pg_hba.conf" line 84, near token "all" Hm ... could we see exactly what you have in pg_hba.conf? > I received this error for every configuration line that wasn't a comment. The "all" is in the USER column. No matterwhat I put in the USER column, I get the same error, but listing whatever string is in that column. I wandered ifthis error was just a result of a different error someplace else, so I commented out all lines in this file. That resultedin the following error: > FATAL: exceeded maxAllocatedDescs (16) while trying to open file "/var/lib/pgsql/11/data/pg_hba.conf" That's just weird. Zero entries in pg_hba.conf isn't a useful situation (since the postmaster wouldn't allow any connections), but if I try it I get a failure like this: 2019-06-19 15:43:24.257 EDT [20959] LOG: starting PostgreSQL 12beta2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.720120313 (Red Hat 4.4.7-23), 64-bit 2019-06-19 15:43:24.281 EDT [20959] LOG: listening on IPv4 address "0.0.0.0", port 5440 2019-06-19 15:43:24.281 EDT [20959] LOG: listening on IPv6 address "::", port 5440 2019-06-19 15:43:24.282 EDT [20959] LOG: listening on Unix socket "/tmp/.s.PGSQL.5440" 2019-06-19 15:43:24.303 EDT [20959] LOG: configuration file "/home/postgres/testversion/data/pg_hba.conf" contains no entries 2019-06-19 15:43:24.303 EDT [20959] FATAL: could not load pg_hba.conf 2019-06-19 15:43:24.305 EDT [20959] LOG: database system is shut down Reading between the lines suggests that something might be trying to recursively read pg_hba.conf if it's empty, but it's hard to say what that something could be. Are you sure this is a stock build of Postgres? Where did you get it from? Another question is what non-default (not-commented) entries you have in postgresql.conf. One idea that came to me while thinking about the syntax error is that maybe you have one of the other configuration-file-name variables pointing to pg_hba.conf. That error message tells us that the server is trying to read pg_hba.conf, right enough, but it doesn't actually say what it's expecting to find in that file ... regards, tom lane
I tried putting the pg_hba.conf back to stock defaults and that didn't help any. Here is a snippet of the current file: # TYPE DATABASE USER ADDRESS METHOD # "local" is for Unix domain socket connections only local all all trust I've tried single and multiple tabs or a single space between columns. It doesn't make any difference. It always complainsabout the 3rd column. Whatever is in that column is the 'near token' value. I know zero entries in the pg_hba.confvalid, but I was just trying to see if the real error was upstream of this file. As far as I know, I've got a 'stock' build of PostgreSQL. I just double checked the yum configuration and it pulledPostgreSQL from: https://download.postgresql.org/pub/repos/yum/11/redhat/rhel-$releasever-$basearch I wish I had kept the original postgresql.conf file for comparison. It is primarily the original file with all the defaultvalues uncommented. I like making sure I know what all the settings are and if it is supposed to be a default value,I'd like to make sure it is. The pg_hba.conf file listed in postgresql.conf is one of the few things I didn't touchat all. #hba_file = 'ConfigDir/pg_hba.conf' # host-based authentication file # (change requires restart) I suppose if I had to, I could wipe the PostgreSQL stuff clean and start over from scratch. This is a brand new install. I can keep backups of the these 2 files I changed for comparison after the reinstall. Thanks for responding soquickly. Brent L. Bates - UNIX System Administrator - Langley Federal Credit Union Phone: (757) 848-5443, Mobile: (757) 419-0295 COMMUNICATION CONFIDENTIALITY: All information in this communication, including attachments, is strictly confidential andintended solely for delivery to and authorized use by the addressee(s) identified above, and may contain privileged, confidential,proprietary and/or secret information entitled to protection and/or exempt from disclosure under applicablelaw. If you are not the intended recipient, please destroy or delete this communication and any/all attached documentsas the possession, distribution and/or copying of this communication is unauthorized and may be unlawful. If youhave received this communication in error, please remove it in its entirety from your computer and notify the sender.
Brent Bates <BBates@langleyfcu.org> writes: > I tried putting the pg_hba.conf back to stock defaults and that didn't help any. Here is a snippet of the currentfile: > # TYPE DATABASE USER ADDRESS METHOD > # "local" is for Unix domain socket connections only > local all all trust Well, that's certainly a perfectly valid pg_hba.conf entry ... my file contains a line that looks exactly like that, and the parser is *not* particularly picky about what the whitespace is. I'm still suspicious that the server is reading pg_hba.conf but trying to interpret it as some other kind of config data, so I'd advise again to double-check what you've put in postgresql.conf. regards, tom lane
On 6/19/19 2:49 PM, Tom Lane wrote: > Brent Bates <BBates@langleyfcu.org> writes: >> I tried putting the pg_hba.conf back to stock defaults and that didn't help any. Here is a snippet of the currentfile: >> # TYPE DATABASE USER ADDRESS METHOD >> # "local" is for Unix domain socket connections only >> local all all trust That line has four values and the header has 5. > Well, that's certainly a perfectly valid pg_hba.conf entry ... my file > contains a line that looks exactly like that, and the parser is *not* > particularly picky about what the whitespace is. > > I'm still suspicious that the server is reading pg_hba.conf but trying > to interpret it as some other kind of config data, so I'd advise again > to double-check what you've put in postgresql.conf. > > regards, tom lane > >
>>>>> "Rob" == Rob Sargent <robjsargent@gmail.com> writes: >>> local all all trust Rob> That line has four values and the header has 5. That's standard for "local" lines, which lack an ADDRESS field. -- Andrew (irc:RhodiumToad)
Does the file have any <CR> \015's in it?
On Wednesday, June 19, 2019, 7:03:25 PM EDT, Andrew Gierth <andrew@tao11.riddles.org.uk> wrote:
>>>>> "Rob" == Rob Sargent <robjsargent@gmail.com> writes:
>>> local all all trust
Rob> That line has four values and the header has 5.
That's standard for "local" lines, which lack an ADDRESS field.
--
Andrew (irc:RhodiumToad)
>>> local all all trust
Rob> That line has four values and the header has 5.
That's standard for "local" lines, which lack an ADDRESS field.
--
Andrew (irc:RhodiumToad)
I found the problem. I cleared everything out and started from scratch, then slowly added my changes back to the configurationfiles. The problem was in the postgresql.conf. At the bottom of the file I had uncommented all the ‘include’lines, so they looked like this: include_dir = '' # include files ending in '.conf' from # a directory, e.g., 'conf.d' include_if_exists = '' # include file only if it exists include = '' # include file The first one is what caused all the headaches. I recommented out these lines and everything works now. Thanks for allthe quick suggestions. Brent L. Bates - UNIX System Administrator - Langley Federal Credit Union Phone: (757) 848-5443, Mobile: (757) 419-0295 COMMUNICATION CONFIDENTIALITY: All information in this communication, including attachments, is strictly confidential andintended solely for delivery to and authorized use by the addressee(s) identified above, and may contain privileged, confidential,proprietary and/or secret information entitled to protection and/or exempt from disclosure under applicablelaw. If you are not the intended recipient, please destroy or delete this communication and any/all attached documentsas the possession, distribution and/or copying of this communication is unauthorized and may be unlawful. If youhave received this communication in error, please remove it in its entirety from your computer and notify the sender.
Brent Bates <BBates@langleyfcu.org> writes: > I found the problem. I cleared everything out and started from scratch, then slowly added my changes back to theconfiguration files. The problem was in the postgresql.conf. At the bottom of the file I had uncommented all the ‘include’lines, so they looked like this: > include_dir = '' # include files ending in '.conf' from > # a directory, e.g., 'conf.d' > include_if_exists = '' # include file only if it exists > include = '' # include file > The first one is what caused all the headaches. I recommented out these lines and everything works now. Thanks for allthe quick suggestions. Ah-hah! I wonder if we should disallow empty values for these GUCs? And/or put in some kind of test for recursive opening of the same config file? I don't think it'd occurred to anyone that it's this easy to get the code to try to do that. regards, tom lane
On 6/20/19 11:55 PM, Tom Lane wrote: > Brent Bates <BBates@langleyfcu.org> writes: >> I found the problem. I cleared everything out and started from scratch, then slowly added my changes back to theconfiguration files. The problem was in the postgresql.conf. At the bottom of the file I had uncommented all the ‘include’lines, so they looked like this: >> include_dir = '' # include files ending in '.conf' from >> # a directory, e.g., 'conf.d' >> include_if_exists = '' # include file only if it exists >> include = '' # include file > >> The first one is what caused all the headaches. I recommented out these lines and everything works now. Thanks for allthe quick suggestions. > > Ah-hah! I wonder if we should disallow empty values for these GUCs? > And/or put in some kind of test for recursive opening of the same > config file? I don't think it'd occurred to anyone that it's this > easy to get the code to try to do that. Funnily enough I was looking at that code the other day for totally other reasons and vaguely wondered if there was any potential for circular inclusions and the like. There's a check for nesting depth, but I'm not sure what else. I could take a look in the next few days if no-one else wants to. Regards Ian Barwick -- Ian Barwick https://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
> From: Tom Lane <tgl@sss.pgh.pa.us > Brent Bates <BBates@langleyfcu.org> writes: > > I found the problem. I cleared everything out and started from scratch, then slowly added my changes back to theconfiguration files. The problem was in the postgresql.conf. At the bottom of the file I had uncommented all the ‘include’lines, so they looked like this: > > include_dir = '' # include files ending in '.conf' from > Ah-hah! I wonder if we should disallow empty values for these GUCs? > And/or put in some kind of test for recursive opening of the same config file? I don't think it'd occurred to anyone thatit's this easy to get the code to try to do that. I would encourage this. 😊 I know on one of my early installs some time back I accidentally did: data_directory = '' and had a devil of a time figuring out why the postmaster wouldn't start (in fact it was you Tom that pointed me in the rightdirection to eventually find the misconfiguration). So I think it would be a great idea to add checks for empty stringsin places where that's a problem. An unset value (as in the config is commented out) can be OK as any defaults willbe used, but to set some values to the empty string just hurts and it would be a help to new users, or even those ofus who make typos, 😊 to get better error messages so we can fix the problem faster on our own. Kevin This e-mail transmission, and any documents, files or previous e-mail messages attached to it, may contain confidential information.If you are not the intended recipient, or a person responsible for delivering it to the intended recipient, youare hereby notified that any disclosure, distribution, review, copy or use of any of the information contained in or attachedto this message is STRICTLY PROHIBITED. If you have received this transmission in error, please immediately notifyus by reply e-mail, and destroy the original transmission and its attachments without reading them or saving them todisk. Thank you.
On 6/21/19 12:34 AM, Ian Barwick wrote: > On 6/20/19 11:55 PM, Tom Lane wrote: >> Brent Bates <BBates@langleyfcu.org> writes: >>> I found the problem. I cleared everything out and started from scratch, then slowly added my changes back to theconfiguration files. The problem was in the postgresql.conf. At the bottom of the file I had uncommented all the ‘include’lines, so they looked like this: >>> include_dir = '' # include files ending in '.conf' from >>> # a directory, e.g., 'conf.d' >>> include_if_exists = '' # include file only if it exists >>> include = '' # include file >> >>> The first one is what caused all the headaches. I recommented out these lines and everything works now. Thanks forall the quick suggestions. >> >> Ah-hah! I wonder if we should disallow empty values for these GUCs? >> And/or put in some kind of test for recursive opening of the same >> config file? I don't think it'd occurred to anyone that it's this >> easy to get the code to try to do that. > > Funnily enough I was looking at that code the other day for totally other > reasons and vaguely wondered if there was any potential for circular > inclusions and the like. There's a check for nesting depth, but I'm not > sure what else. I could take a look in the next few days if no-one else > wants to. Some proposed fixes submitted here: https://www.postgresql.org/message-id/8c8bcbca-3bd9-dc6e-8986-04a5abdef142%402ndquadrant.com Regards Ian Barwick -- Ian Barwick https://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
On 6/25/19 2:27 AM, Kevin Brannen wrote: >> From: Tom Lane <tgl@sss.pgh.pa.us > >> Brent Bates <BBates@langleyfcu.org> writes: >>> I found the problem. I cleared everything out and started from scratch, then slowly added my changes back to theconfiguration files. The problem was in the postgresql.conf. At the bottom of the file I had uncommented all the ‘include’lines, so they looked like this: >>> include_dir = '' # include files ending in '.conf' from > >> Ah-hah! I wonder if we should disallow empty values for these GUCs? >> And/or put in some kind of test for recursive opening of the same config file? I don't think it'd occurred to anyonethat it's this easy to get the code to try to do that. > > > I would encourage this. 😊 I know on one of my early installs some time back I accidentally did: > > data_directory = '' > and had a devil of a time figuring out why the postmaster wouldn't start (in > fact it was you Tom that pointed me in the right direction to eventually find > the misconfiguration). So I think it would be a great idea to add checks for > empty strings in places where that's a problem. An unset value (as in the > config is commented out) can be OK as any defaults will be used, but to set > some values to the empty string just hurts and it would be a help to new > users, or even those of us who make typos, 😊 to get better error messages so > we can fix the problem faster on our own I've submitted fixes for the empty include directives (which are a special case), I'll see if I can have a look at these ones too. Regards Ian Barwick -- Ian Barwick https://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services