Thread: [GENERAL] Upgradede -9.6.1 to -9.6.2; postmaster will not start

[GENERAL] Upgradede -9.6.1 to -9.6.2; postmaster will not start

From
Rich Shepard
Date:
   I've tried resolving this but do not see what changed.

   1. Shut down postgres:
     pg_ctl stop -D /var/lib/pgsql/9.6/data

   2. Upgraded version (using SlackBuilds.org package as usual).

   3. Tried re-starting postgres:

postgres@salmo:~$ postgres -D /var/lib/pgsql/9.6/data/ &
[1] 17585
postgres@salmo:~$ LOG:  could not open secondary authentication file
"@authcomment@" as "/var/lib/pgsql/9.6/data/authcomment@": No such file or
directory
LOG:  could not open secondary authentication file
"@remove-line-for-nolocal@" as
"/var/lib/pgsql/9.6/data/remove-line-for-nolocal@": No such file or
directory
LOG:  could not open secondary authentication file
"@remove-line-for-nolocal@local" as
"/var/lib/pgsql/9.6/data/remove-line-for-nolocal@local": No such file or
directory
LOG:  could not open secondary authentication file "@authmethodlocal@" as
"/var/lib/pgsql/9.6/data/authmethodlocal@": No such file or directory
LOG:  could not open secondary authentication file
"@remove-line-for-nolocal@" as
"/var/lib/pgsql/9.6/data/remove-line-for-nolocal@": No such file or
directory
LOG:  invalid connection type "all"
CONTEXT:  line 80 of configuration file
"/var/lib/pgsql/9.6/data/pg_hba.conf"
FATAL:  could not load pg_hba.conf
LOG:  database system is shut down

   4. Line 80 in pg_hba.conf:
@remove-line-for-nolocal@local   all    all   @authmethodlocal@

   5. Tried commenting out that line to match backup, but that also did not
allow postgres to start.

   6. All files are owned by postgres.users.

   Please teach me how to fix this.

TIA,

Rich



Re: [GENERAL] Upgradede -9.6.1 to -9.6.2; postmaster will not start

From
Tom Lane
Date:
Rich Shepard <rshepard@appl-ecosys.com> writes:
> postgres@salmo:~$ postgres -D /var/lib/pgsql/9.6/data/ &
> [1] 17585
> postgres@salmo:~$ LOG:  could not open secondary authentication file
> "@authcomment@" as "/var/lib/pgsql/9.6/data/authcomment@": No such file or
> directory
> LOG:  could not open secondary authentication file
> "@remove-line-for-nolocal@" as
> "/var/lib/pgsql/9.6/data/remove-line-for-nolocal@": No such file or
> directory
> LOG:  could not open secondary authentication file
> "@remove-line-for-nolocal@local" as
> "/var/lib/pgsql/9.6/data/remove-line-for-nolocal@local": No such file or
> directory

It looks like what you have in pg_hba.conf is a raw copy of
pg_hba.conf.sample, without any of the editing that initdb normally
applies to it (to say nothing of manual adjustments you might make
later).  It's hard to tell from the information given whether this
is pilot error or something broken in SlackBuilds' upgrade script.

In any case, you should be able to fix it by making a copy of your
pre-upgrade pg_hba.conf and shoving that back into the data directory
afterwards; there's no reason to change that during a minor-version
upgrade.  I'd check the other configuration files in $PGDATA too,
just in case SlackBuilds is fat-fingering them as well.

            regards, tom lane


Re: [GENERAL] Upgradede -9.6.1 to -9.6.2; postmaster will notstart

From
Rich Shepard
Date:
On Fri, 10 Mar 2017, Tom Lane wrote:

> It looks like what you have in pg_hba.conf is a raw copy of
> pg_hba.conf.sample, without any of the editing that initdb normally
> applies to it (to say nothing of manual adjustments you might make later).

Tom,

   Actually, it has the modifications I've made over the years. That's why I
could not see what changed.

> It's hard to tell from the information given whether this is pilot error
> or something broken in SlackBuilds' upgrade script.

   I've not modified the file in a while, and don't overwrite the existing
one when a new one is available. This is a new error for me.

> In any case, you should be able to fix it by making a copy of your
> pre-upgrade pg_hba.conf and shoving that back into the data directory
> afterwards;

   I'll restore a backup copy from last month.

Thanks,

Rich



Re: [GENERAL] Upgradede -9.6.1 to -9.6.2; postmaster will notstart

From
Rich Shepard
Date:
On Fri, 10 Mar 2017, Rich Shepard wrote:

> Actually, it has the modifications I've made over the years. That's why I
> could not see what changed.

   Well, postgres was running yesterday and allowed me to access my
bookkeeping software so obviously something did change without my manually
editing pg_hba.conf. Here are the log records:

LOG:  could not open secondary authentication file "@authcomment@" as
"/var/lib/pgsql/9.6/data/authcomment@": No such file or directory
LOG:  could not open secondary authentication file
"@remove-line-for-nolocal@" as
"/var/lib/pgsql/9.6/data/remove-line-for-nolocal@": No such file or
directory
LOG:  could not open secondary authentication file
"@remove-line-for-nolocal@local" as
"/var/lib/pgsql/9.6/data/remove-line-for-nolocal@local": No such file or
directory
LOG:  could not open secondary authentication file "@authmethodlocal@" as
"/var/lib/pgsql/9.6/data/authmethodlocal@": No such file or directory
LOG:  could not open secondary authentication file "@authmethodhost@" as
"/var/lib/pgsql/9.6/data/authmethodhost@": No such file or directory
LOG:  could not open secondary authentication file
"@remove-line-for-nolocal@" as
"/var/lib/pgsql/9.6/data/remove-line-for-nolocal@": No such file or
directory
LOG:  invalid connection type "all"
CONTEXT:  line 80 of configuration file
"/var/lib/pgsql/9.6/data/pg_hba.conf"
LOG:  end-of-line before authentication method
CONTEXT:  line 86 of configuration file
"/var/lib/pgsql/9.6/data/pg_hba.conf"
FATAL:  could not load pg_hba.conf
LOG:  database system is shut down

   Postgres runs on my desktop server/workstation and I'm the only user
loggin in. Here's pg_hba.conf (restored from 2-22-2017 backup) which still
does not work. I've read the pg_hba.conf section in the 9.6 docs without
seeing what's wrong with my file.

# PostgreSQL Client Authentication Configuration File
# ===================================================
# Put your actual configuration here
# ----------------------------------
@authcomment@

# TYPE  DATABASE        USER            ADDRESS                 METHOD

@remove-line-for-nolocal@# "local" is for Unix domain socket connections only
@remove-line-for-nolocal@local   all             all                                     @authmethodlocal@
# IPv4 local connections:
local    all        rshepard    trust
host    all             rshepard        127.0.0.1/32    trust
hostnossl all        rshepard    127.0.0.1/32    trust
# IPv6 local connections:
host    all             all             ::1/128                 @authmethodhost@
# Allow replication connections from localhost, by a user with the
# replication privilege.
@remove-line-for-nolocal@#local   replication     @default_username@                                @authmethodlocal@
#host    replication     @default_username@        127.0.0.1/32            @authmethodhost@
#host    replication     @default_username@        ::1/128                 @authmethodhost@

   What am I not seeing?

Rich


Re: [GENERAL] Upgradede -9.6.1 to -9.6.2; postmaster will not start

From
Tom Lane
Date:
Rich Shepard <rshepard@appl-ecosys.com> writes:
>    Postgres runs on my desktop server/workstation and I'm the only user
> loggin in. Here's pg_hba.conf (restored from 2-22-2017 backup) which still
> does not work. I've read the pg_hba.conf section in the 9.6 docs without
> seeing what's wrong with my file.

> # PostgreSQL Client Authentication Configuration File
> # ===================================================
> # Put your actual configuration here
> # ----------------------------------
> @authcomment@

> # TYPE  DATABASE        USER            ADDRESS                 METHOD

> @remove-line-for-nolocal@# "local" is for Unix domain socket connections only
> @remove-line-for-nolocal@local   all             all                                     @authmethodlocal@
> # IPv4 local connections:
> local    all        rshepard    trust
> host    all             rshepard        127.0.0.1/32    trust
> hostnossl all        rshepard    127.0.0.1/32    trust
> # IPv6 local connections:
> host    all             all             ::1/128                 @authmethodhost@
> # Allow replication connections from localhost, by a user with the
> # replication privilege.
> @remove-line-for-nolocal@#local   replication     @default_username@                                @authmethodlocal@
> #host    replication     @default_username@        127.0.0.1/32            @authmethodhost@
> #host    replication     @default_username@        ::1/128                 @authmethodhost@

>    What am I not seeing?

The "@remove-line-for-nolocal@" bits should not be there.
initdb would normally either delete those lines entirely, or
strip off "@remove-line-for-nolocal@", depending on the switches
it was given.  Likewise the various other @something@ bits should
have been changed to something else.

It certainly wasn't working before if it was like this, either.

            regards, tom lane


Re: [GENERAL] Upgradede -9.6.1 to -9.6.2; postmaster will not start

From
Adrian Klaver
Date:
On 03/10/2017 11:50 AM, Rich Shepard wrote:
> On Fri, 10 Mar 2017, Rich Shepard wrote:
>
>> Actually, it has the modifications I've made over the years. That's why I
>> could not see what changed.
>
>   Well, postgres was running yesterday and allowed me to access my
> bookkeeping software so obviously something did change without my manually
> editing pg_hba.conf. Here are the log records:
>
> LOG:  could not open secondary authentication file "@authcomment@" as
> "/var/lib/pgsql/9.6/data/authcomment@": No such file or directory
> LOG:  could not open secondary authentication file
> "@remove-line-for-nolocal@" as
> "/var/lib/pgsql/9.6/data/remove-line-for-nolocal@": No such file or
> directory
> LOG:  could not open secondary authentication file
> "@remove-line-for-nolocal@local" as
> "/var/lib/pgsql/9.6/data/remove-line-for-nolocal@local": No such file or
> directory
> LOG:  could not open secondary authentication file "@authmethodlocal@" as
> "/var/lib/pgsql/9.6/data/authmethodlocal@": No such file or directory
> LOG:  could not open secondary authentication file "@authmethodhost@" as
> "/var/lib/pgsql/9.6/data/authmethodhost@": No such file or directory
> LOG:  could not open secondary authentication file
> "@remove-line-for-nolocal@" as
> "/var/lib/pgsql/9.6/data/remove-line-for-nolocal@": No such file or
> directory
> LOG:  invalid connection type "all"
> CONTEXT:  line 80 of configuration file
> "/var/lib/pgsql/9.6/data/pg_hba.conf"
> LOG:  end-of-line before authentication method
> CONTEXT:  line 86 of configuration file
> "/var/lib/pgsql/9.6/data/pg_hba.conf"
> FATAL:  could not load pg_hba.conf
> LOG:  database system is shut down
>
>   Postgres runs on my desktop server/workstation and I'm the only user
> loggin in. Here's pg_hba.conf (restored from 2-22-2017 backup) which still
> does not work. I've read the pg_hba.conf section in the 9.6 docs without
> seeing what's wrong with my file.
>
> # PostgreSQL Client Authentication Configuration File
> # ===================================================
> # Put your actual configuration here
> # ----------------------------------
> @authcomment@
>
> # TYPE  DATABASE        USER            ADDRESS                 METHOD
>
> @remove-line-for-nolocal@# "local" is for Unix domain socket connections
> only
> @remove-line-for-nolocal@local   all
> all                                     @authmethodlocal@
> # IPv4 local connections:
> local    all        rshepard    trust
> host    all             rshepard        127.0.0.1/32    trust
> hostnossl all        rshepard    127.0.0.1/32    trust
> # IPv6 local connections:
> host    all             all             ::1/128
> @authmethodhost@
> # Allow replication connections from localhost, by a user with the
> # replication privilege.
> @remove-line-for-nolocal@#local   replication
> @default_username@                                @authmethodlocal@
> #host    replication     @default_username@
> 127.0.0.1/32            @authmethodhost@
> #host    replication     @default_username@
> ::1/128                 @authmethodhost@
>
>   What am I not seeing?

That this looks like a merge of the sample file:


https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/libpq/pg_hba.conf.sample;h=6b1778a72136edf52cea56f2ab088b9449df9a48;hb=HEAD

with your additions.

Seems you have not restarted Postgres since the last time you had a
valid pg_hba.conf file. The above is not one. Remove the @***@ strings
and you should be good.


>
> Rich
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com


Re: [GENERAL] Upgradede -9.6.1 to -9.6.2; postmaster will not start[FIXED]

From
Rich Shepard
Date:
On Fri, 10 Mar 2017, Tom Lane wrote:

> The "@remove-line-for-nolocal@" bits should not be there. initdb would
> normally either delete those lines entirely, or strip off
> "@remove-line-for-nolocal@", depending on the switches it was given.
> Likewise the various other @something@ bits should have been changed to
> something else.
>
> It certainly wasn't working before if it was like this, either.

Tom,

   I've no idea how the file was changed, but something certainly happened to
it. Restoring pg_hba.conf from 2016-09-17 (when pg-9.5.1 was installed)
certainly fixed the issue.

Many thanks,

Rich


Re: [GENERAL] Upgradede -9.6.1 to -9.6.2; postmaster will notstart

From
Rich Shepard
Date:
On Fri, 10 Mar 2017, Adrian Klaver wrote:

> That this looks like a merge of the sample file:
>
https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/libpq/pg_hba.conf.sample;h=6b1778a72136edf52cea56f2ab088b9449df9a48;hb=HEAD
> with your additions.

> Seems you have not restarted Postgres since the last time you had a valid
> pg_hba.conf file. The above is not one. Remove the @***@ strings and you
> should be good.

Adrian,

   I accessed a postgres database (my bookkeeping data) yesterday and the
only difference from then to now was upgrading postgres one third-digit
version.

   Prior to today my upgrades were generally one or two minor versions; e.g.,
from 9.3.x to 9.5.x, and done with pg_dumpall and restoration using psql.
Today I just used pg_ctl to stop the server, ran the normal Slackware
upgradepkg routines, and tried to restart the server. I do not see a
pg_hba.conf file anywhere in the source or build trees so I've no idea from
where this unworkable version came.

Thanks,

Rich