Thread: 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)
Re: [EXT EMAIL] Re: First Time Starting Up PostgreSQL and HavingProblems
From
"pbj@cmicdo.com"
Date:
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