Thread: Trouble starting Postgresql after an upgrade

Trouble starting Postgresql after an upgrade

From
Kevin Brannen
Date:
I’m having a little trouble and I hope someone can point me towards a fix. ☺

We’ve been using Pg 9.3.4 and just upgraded to 9.5.1. I’ve installed it on 1 server and it works. ☺ However, when I
copiedthose binaries over to a 2nd server, I find that they won’t start normally. I can do “/etc/init.d/postgresql-9.5
start”from anywhere and it fails to start. Looking in the script, I believe the important line (expanded via bash -x)
is:

+ echo -n 'Starting postgresql-9.5 service: '
Starting postgresql-9.5 service: + runuser -l postgres -c '/opt/pgsql-9.5/bin/postmaster -p '\''5432'\'' -D
'\''/nms-db/9.5/data'\'' &'
 

In the pgstartup.log, I see:

LOG:  skipping missing configuration file "/home/postgres/postgresql.auto.conf"
2016-07-12 22:16:41 GMT [6066]: [2-1] user=,db=,app= FATAL:  "/home/postgres" is not a valid data directory
2016-07-12 22:16:41 GMT [6066]: [3-1] user=,db=,app= DETAIL:  File "/home/postgres/PG_VERSION" is missing.

Duh, there is no postgresql.auto.conf in the ~postgres, it's in the data dir "/nms-db/9.5/data", as is "PG_VERSION". I
alsodon’t see anything in the main log (in /logs/pg_log/post*”) that gives me any more info.
 

Since it all works on the original server, I must have done something wrong on this install, but I can’t figure out
what.Can someone see what I did wrong from the above or a pointer to a place to look with better error messages would
alsobe helpful.
 

To make the problem more interesting, I can bring the DB engine up if I use pg_ctl … but only if I’m in the data dir.

[root@nms59c ~]# pwd
/root
[root@nms59c ~]# sudo -u postgres "/opt/pgsql-9.5/bin/pg_ctl" -w -D "/nms-db/9.5/data" -o ""  start
could not change directory to "/root": Permission denied
waiting for server to start....LOG:  skipping missing configuration file "/opt/pgsql-9.5/bin/postgresql.auto.conf"
2016-07-12 22:41:30 GMT [5567]: [2-1] user=,db=,app= FATAL:  data directory "/opt/pgsql-9.5/bin" has wrong ownership
2016-07-12 22:41:30 GMT [5567]: [3-1] user=,db=,app= HINT:  The server must be started by the user that owns the data
directory.
stopped waiting
pg_ctl: could not start server
Examine the log output.
[root@nms59c ~]#
[root@nms59c ~]#
[root@nms59c ~]# cd $PGDATA
[root@nms59c data]# pwd
/nms-db/9.5/data
[root@nms59c data]# sudo -u postgres "/opt/pgsql-9.5/bin/pg_ctl" -w -D "/nms-db/9.5/data" -o ""  start
waiting for server to start....2016-07-12 22:41:38 GMT [5571]: [1-1] user=,db=,app= LOG:  redirecting log output to
loggingcollector process
 
2016-07-12 22:41:38 GMT [5571]: [2-1] user=,db=,app= HINT:  Future log output will appear in directory "/logs/pg_log".
done
server started
[root@nms59c data]#

That almost makes sense to me as it probably wants to write some file so perms matter, but yet I don’t understand why
itwouldn’t change to the data dir itself if that’s really required.
 

Given the above, I’m tempted to change /etc/init.d/postgresql-9.5 so that it does “cd $PGDATA” then replace the
“runuser-l postgres -c…” line to be what I have that works with pg_ctl, but I’m concerned that the error is an
indicationthat I’ve screwed up something important that will bite me later if I don’t figure it out now. This is on a
testsystem, so no real data is endangered; but I really need to figure this out as we automate the upgrade so it can go
toreal data servers.
 

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.
 


Re: Trouble starting Postgresql after an upgrade

From
Adrian Klaver
Date:
On 07/14/2016 06:58 AM, Kevin Brannen wrote:
> I’m having a little trouble and I hope someone can point me towards a fix. ☺
>
> We’ve been using Pg 9.3.4 and just upgraded to 9.5.1. I’ve installed it on 1 server and it works. ☺ However, when I
copiedthose binaries over to a 2nd server, I find that they won’t start normally. I can do “/etc/init.d/postgresql-9.5
start”from anywhere and it fails to start. Looking in the script, I believe the important line (expanded via bash -x)
is:

What OS, OS version and architecture for each machine?

How did you upgrade?

>
> + echo -n 'Starting postgresql-9.5 service: '
> Starting postgresql-9.5 service: + runuser -l postgres -c '/opt/pgsql-9.5/bin/postmaster -p '\''5432'\'' -D
'\''/nms-db/9.5/data'\'' &' 
>
> In the pgstartup.log, I see:
>
> LOG:  skipping missing configuration file "/home/postgres/postgresql.auto.conf"
> 2016-07-12 22:16:41 GMT [6066]: [2-1] user=,db=,app= FATAL:  "/home/postgres" is not a valid data directory
> 2016-07-12 22:16:41 GMT [6066]: [3-1] user=,db=,app= DETAIL:  File "/home/postgres/PG_VERSION" is missing.
>
> Duh, there is no postgresql.auto.conf in the ~postgres, it's in the data dir "/nms-db/9.5/data", as is "PG_VERSION".
Ialso don’t see anything in the main log (in /logs/pg_log/post*”) that gives me any more info. 
>
> Since it all works on the original server, I must have done something wrong on this install, but I can’t figure out
what.Can someone see what I did wrong from the above or a pointer to a place to look with better error messages would
alsobe helpful. 
>
> To make the problem more interesting, I can bring the DB engine up if I use pg_ctl … but only if I’m in the data dir.
>
> [root@nms59c ~]# pwd
> /root
> [root@nms59c ~]# sudo -u postgres "/opt/pgsql-9.5/bin/pg_ctl" -w -D "/nms-db/9.5/data" -o ""  start
> could not change directory to "/root": Permission denied
> waiting for server to start....LOG:  skipping missing configuration file "/opt/pgsql-9.5/bin/postgresql.auto.conf"
> 2016-07-12 22:41:30 GMT [5567]: [2-1] user=,db=,app= FATAL:  data directory "/opt/pgsql-9.5/bin" has wrong ownership
> 2016-07-12 22:41:30 GMT [5567]: [3-1] user=,db=,app= HINT:  The server must be started by the user that owns the data
directory.
> stopped waiting
> pg_ctl: could not start server
> Examine the log output.
> [root@nms59c ~]#
> [root@nms59c ~]#
> [root@nms59c ~]# cd $PGDATA
> [root@nms59c data]# pwd
> /nms-db/9.5/data
> [root@nms59c data]# sudo -u postgres "/opt/pgsql-9.5/bin/pg_ctl" -w -D "/nms-db/9.5/data" -o ""  start
> waiting for server to start....2016-07-12 22:41:38 GMT [5571]: [1-1] user=,db=,app= LOG:  redirecting log output to
loggingcollector process 
> 2016-07-12 22:41:38 GMT [5571]: [2-1] user=,db=,app= HINT:  Future log output will appear in directory
"/logs/pg_log".
> done
> server started
> [root@nms59c data]#
>
> That almost makes sense to me as it probably wants to write some file so perms matter, but yet I don’t understand why
itwouldn’t change to the data dir itself if that’s really required. 

I would say it is the difference between init/service scripts between
machines. Can't be more specific until the questions above are answered.

>
> Given the above, I’m tempted to change /etc/init.d/postgresql-9.5 so that it does “cd $PGDATA” then replace the
“runuser-l postgres -c…” line to be what I have that works with pg_ctl, but I’m concerned that the error is an
indicationthat I’ve screwed up something important that will bite me later if I don’t figure it out now. This is on a
testsystem, so no real data is endangered; but I really need to figure this out as we automate the upgrade so it can go
toreal data servers. 
>
> Kevin
>
> This e-mail transmission, and any documents, files or previous e-mail messages attached to it, may contain
confidentialinformation. If you are not the intended recipient, or a person responsible for delivering it to the
intendedrecipient, you are hereby notified that any disclosure, distribution, review, copy or use of any of the
informationcontained 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
readingthem or saving them to disk. Thank you. 
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com


Re: Trouble starting Postgresql after an upgrade

From
Tom Lane
Date:
Kevin Brannen <KBrannen@efji.com> writes:
> I’m having a little trouble and I hope someone can point me towards a fix. ☺
> We’ve been using Pg 9.3.4 and just upgraded to 9.5.1. I’ve installed it on 1 server and it works. ☺ However, when I
copiedthose binaries over to a 2nd server, I find that they won’t start normally. I can do “/etc/init.d/postgresql-9.5
start”from anywhere and it fails to start. Looking in the script, I believe the important line (expanded via bash -x)
is:

> + echo -n 'Starting postgresql-9.5 service: '
> Starting postgresql-9.5 service: + runuser -l postgres -c '/opt/pgsql-9.5/bin/postmaster -p '\''5432'\'' -D
'\''/nms-db/9.5/data'\'' &' 

> In the pgstartup.log, I see:

> LOG:  skipping missing configuration file "/home/postgres/postgresql.auto.conf"
> 2016-07-12 22:16:41 GMT [6066]: [2-1] user=,db=,app= FATAL:  "/home/postgres" is not a valid data directory
> 2016-07-12 22:16:41 GMT [6066]: [3-1] user=,db=,app= DETAIL:  File "/home/postgres/PG_VERSION" is missing.

The postmaster is evidently deciding that /home/postgres is supposed to be
the data directory.  The only theory I can think of to explain this is
that /nms-db/9.5/data contains a postgresql.conf file that sets
data_directory in a way that resolves as that.  It is definitely finding
a postgresql.conf somewhere, else you'd not have gotten this far.

> To make the problem more interesting, I can bring the DB engine up if I use pg_ctl … but only if I’m in the data dir.

Possibly you have "data_directory = ." or something like that in the
config file?

            regards, tom lane


Re: Trouble starting Postgresql after an upgrade

From
Kevin Brannen
Date:
-----Original Message-----
From: Adrian Klaver [mailto:adrian.klaver@aklaver.com]

> What OS, OS version and architecture for each machine?
>
> How did you upgrade?

CentOS 5.9 on both servers. (Yes, we know it's old, we're moving to 6.7, but we still have some older servers to
supportand we want to run the same Pg on the new and the old. :)
 
I used pg_upgrade, but the details don't matter now...


-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]

> The postmaster is evidently deciding that /home/postgres is supposed to be the data directory.  The only theory I can
thinkof to explain this is that /nms-db/9.5/data contains a postgresql.conf file that sets data_directory in a way that
resolvesas that.  It is definitely finding a postgresql.conf somewhere, else you'd not have gotten this far.
 

> Possibly you have "data_directory = ." or something like that in the config file?


Wow, you nailed it! For some reason, I read over:

data_directory = ''               # that's 2 single quotes and therefore the empty string

multiple times and missed it every time. I set that to the proper dir and life is good again. Obviously, that's
somethingthat must be changed in the automated upgrade script.
 

While it might be nice if the server warned of an "empty" dir for that config value, the problem was totally mine.

Thanks so very much to each of you!
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.