First Time Starting Up PostgreSQL and Having Problems

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 finished setting up the postgresql.conf and pg_hba.conf files and am trying to start up PostgreSQL for the first time. I'm getting 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 matter what I put in the USER column, I get the same error, but listing whatever string is in that column. I wandered if this error was just a result of a different error someplace else, so I commented out all lines in this file. That resulted in 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.7 20120313 (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 complains about the 3rd column. Whatever is in that column is the 'near token' value. I know zero entries in the pg_hba.conf valid, 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 pulled PostgreSQL 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 default values 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 touch at 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 so quickly.

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 and intended 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 applicable law. If you are not the intended recipient, please destroy or delete this communication and any/all attached documents as the possession, distribution and/or copying of this communication is unauthorized and may be unlawful. If you have 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 current file:
# 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 current file:
# 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.

"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)

I found the problem. I cleared everything out and started from scratch, then slowly added my changes back to the configuration 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 all the 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 and intended 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 applicable law. If you are not the intended recipient, please destroy or delete this communication and any/all attached documents as the possession, distribution and/or copying of this communication is unauthorized and may be unlawful. If you have 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 the configuration 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 all 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.

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 the configuration 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 all 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.

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 the configuration 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 that 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.

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, you are hereby notified that any disclosure, distribution, review, copy or use of any of the information contained in or attached to this message is STRICTLY PROHIBITED. If you have received this transmission in error, please immediately notify us by reply e-mail, and destroy the original transmission and its attachments without reading them or saving them to disk. 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 the configuration 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 all 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:

/messages/by-id/8c8bcbca-3bd9-dc6e-8986-04a5abdef142@2ndquadrant.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 the configuration 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 that 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