Thread: [GENERAL] Upgradede -9.6.1 to -9.6.2; postmaster will not start
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
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
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
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
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
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
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
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