BUG #15376: Postgres sql 9.4.19 pg_upgrade stops with error The source cluster was not shut down cleanly.

On Sun, Sep 9, 2018 at 04:14:45PM +0000, PG Bug reporting form wrote:

The following bug has been logged on the website:

Bug reference: 15376
Logged by: kalyani kaniganti
Email address: kalyanithumati@gmail.com
PostgreSQL version: 9.4.19
Operating system: SUSE LINUX
Description:

Currently, we are using 9.4.17 version of Postgre SQL and due to the
security vulnerabilities reported(CVE-2018-10925,
CVE-2018-10915) we are upgrading the Postgres SQL to 9.4.19 version as the
fixes are available.
The application started up successfully with 9.4.19 version and DB
transactions are working fine, But we are facing issues when upgrading the
database from 9.1 version to latest version 9.4.19.
pg_upgrade is failing and throwing the below error.
The source cluster was not shut down cleanly.
Failure, exiting
ERROR: DB upgrade failed!
From some of the sites and Postgre SQL.org, we have noticed that this error
occurs when an old server doesn't shut down properly and pg_upgarde is
failing to check that and in 9.4.19 version release notes mentioned that
this issue is fixed.
But still we are facing the error, Could anyone of you can share the details
that issue is fixed in 9.4.19 and suggest how we can proceed further to
resolve this issue.

What 9.4.19 fixed is that users were doing a pg_ctl -m immediate, and
then running pg_upgrade, and getting _silently_ corrupted upgrades.
9.4.19 fixes this so pg_upgrade properly complains when this happens.

You should only see that error if you are doing "immediate" shutdowns of
your server, and this is not supported, and throws ane error. Are you
seeing the pg_upgrade error message when you do a _clean_ shutdown of
your servers?

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +

On Sun, Sep 9, 2018 at 06:22:24PM -0400, Bruce Momjian wrote:

On Sun, Sep 9, 2018 at 04:14:45PM +0000, PG Bug reporting form wrote:
What 9.4.19 fixed is that users were doing a pg_ctl -m immediate, and
then running pg_upgrade, and getting _silently_ corrupted upgrades.
9.4.19 fixes this so pg_upgrade properly complains when this happens.

You should only see that error if you are doing "immediate" shutdowns of
your server, and this is not supported, and throws ane error. Are you
seeing the pg_upgrade error message when you do a _clean_ shutdown of
your servers?

Also, you can't run pg_upgrade directly on standby servers. You have to
either make it a primary server or use the rsync instructions in the
pg_upgrade docs. The next minor release will have a better error
message on this.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +

Hi Bruce,
Thanks for the immediate reply.
We are using pg_ctl command to shutdown the old server with no option.In
this case the pg_ctl will consider as smart shutdown if no option is
specified as per documentation .
please find bellow command.
<new path for data dir>/bin/pg_ctl -D <olddata dir path> stop
We are using the same script for multiple releases in Postgres sql to
upgrade the database from 9.1 to 9.4 .
Same script is working fine in 9.4.17 version, while upgrading DB from 9.1
to 9.4.17.
From the release notes of 9.4.19 version we did not find any changes done
on pg_upgrade except one bug fix related to pg_upgrade has been fixed.
Do we have to add in our code to check for graceful shutdown or we have to
wait for the next release?
Please let us know if you have any workaround till the next minor patch is
released.

BR,
Kalyani

On Mon, Sep 10, 2018, 7:40 AM Bruce Momjian <bruce@momjian.us> wrote:

On Mon, Sep 10, 2018 at 09:30:28AM +0530, kalyani kaniganti wrote:

Hi Bruce,
Thanks for the immediate reply.
We are using pg_ctl command to shutdown the old server with no option.In this
case the pg_ctl will consider as smart shutdown if no option is specified as
per documentation .
please find bellow command.
<new path for data dir>/bin/pg_ctl -D <olddata dir path> stop�

Yes, this should be fine.

We are using the same script for multiple releases in Postgres sql�to upgrade
the database from 9.1 to 9.4 .
Same script is working fine in 9.4.17 version,�while upgrading DB from 9.1 to
9.4.17.

OK.

From the release notes of 9.4.19 version we did not find any changes done on
pg_upgrade except one bug fix related to pg_upgrade has been fixed.
Do we have to add in our code to check for graceful shutdown or we have to wait
for the next release?
Please let us know if you have any workaround till the next minor patch is
released.

Well, there is nothing pending in the next beta except a better error
message text. Can you show me the commands and the error you are
getting. The system is looking at the output from:

$ pg_controldata $PGDATA

The "Database cluster state:" must be "shut down" before running
pg_upgrade.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +

HI Bruce,

We are using below command for an upgrade and after executing the below
command the status is returning with the cluster error.
/opt/postgres/9.4/bin/pg_upgrade -b /usr/bin -B /opt/postgres/9.4/bin -d
<old data path> -D <new data path>
rc=$?
Rc returns the mentioned error.

When we check with pg_ctl status command output is showing as server not
running.

How can we check the database cluster is shut down using which command.Can
you share us the details other than the pg_ctl.bR
kalyani

On Mon, Sep 10, 2018 at 9:40 AM Bruce Momjian <bruce@momjian.us> wrote:

Hi Bruce,

We have checked cluster status on both the server's data and we noticed
source cluster state is in production and destination cluster state in
shutdown.
Database cluster state: in production
Database cluster state: shut down

How can we shut down the Database cluster?

BR
Kalyani.k

On Mon, Sep 10, 2018 at 3:45 PM kalyani kaniganti <kalyanithumati@gmail.com>
wrote:

On Mon, Sep 10, 2018 at 03:45:15PM +0530, kalyani kaniganti wrote:

HI Bruce,

We are using below command for an upgrade and after executing the below command
the status is returning with the cluster error.
�/opt/postgres/9.4/bin/pg_upgrade -b /usr/bin -B /opt/postgres/9.4/bin -d <old
data path> -D <new data path>
�rc=$?
Rc returns the mentioned error.

When we check with pg_ctl status command output is showing as�server�not
running.

How can we check the database cluster is shut down using which command.Can you
share us the details other than the pg_ctl.bR�

Sure. A running server shows this:

$ pg_controldata /u/pg/data
pg_control version number: 1100
Catalog version number: 201809052
Database system identifier: 6598932306319366658
--> Database cluster state: in production
pg_control last modified: Sat 08 Sep 2018 03:12:38 PM EDT

A shut down server looks like this:

$ pg_controldata /u/pg/data
pg_control version number: 1100
Catalog version number: 201809052
Database system identifier: 6598932306319366658
--> Database cluster state: shut down
pg_control last modified: Mon 10 Sep 2018 12:09:29 PM EDT

If is says "shut down in recovery" then it is in standby mode and cannot
be used by pg_upgrade directly, only via rsync.

---------------------------------------------------------------------------

kalyani

On Mon, Sep 10, 2018 at 9:40 AM Bruce Momjian <bruce@momjian.us> wrote:

On Mon, Sep 10, 2018 at 09:30:28AM +0530, kalyani kaniganti wrote:

Hi Bruce,
Thanks for the immediate reply.
We are using pg_ctl command to shutdown the old server with no option.In

this

case the pg_ctl will consider as smart shutdown if no option is specified

as

per documentation .
please find bellow command.
<new path for data dir>/bin/pg_ctl -D <olddata dir path> stop�

Yes, this should be fine.

We are using the same script for multiple releases in Postgres sql�to

upgrade

the database from 9.1 to 9.4 .
Same script is working fine in 9.4.17 version,�while upgrading DB from

9.1 to

9.4.17.

OK.

From the release notes of 9.4.19 version we did not find any changes done

on

pg_upgrade except one bug fix related to pg_upgrade has been fixed.
Do we have to add in our code to check for graceful shutdown or we have

to wait

for the next release?
Please let us know if you have any workaround till the next minor patch

is

released.

Well, there is nothing pending in the next beta except a better error
message text.� Can you show me the commands and the error you are
getting.� The system is looking at the output from:

� � � � $ pg_controldata $PGDATA

The "Database cluster state:" must be "shut down" before running
pg_upgrade.

--
� Bruce Momjian� <bruce@momjian.us>� � � � http://momjian.us
� EnterpriseDB� � � � � � � � � � � � � � �http://enterprisedb.com

+ As you are, so once was I.� As I am, so you will be. +
+� � � � � � � � � � � Ancient Roman grave inscription +

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +

On Mon, Sep 10, 2018 at 05:34:14PM +0530, kalyani kaniganti wrote:

Hi Bruce,

We have checked cluster status on both the server's data and we noticed source
cluster state is in production and destination cluster state in shutdown.
Database cluster state:� � � � � � � �in production
Database cluster state:� � � � � � � �shut down

How can we shut down the Database cluster?

Uh, pg_ctl and point to the running data directory.

---------------------------------------------------------------------------

BR
Kalyani.k

On Mon, Sep 10, 2018 at 3:45 PM kalyani kaniganti <kalyanithumati@gmail.com>
wrote:

HI Bruce,

We are using below command for an upgrade and after executing the below
command the status is returning with the cluster error.
�/opt/postgres/9.4/bin/pg_upgrade -b /usr/bin -B /opt/postgres/9.4/bin -d
<old data path> -D <new data path>
�rc=$?
Rc returns the mentioned error.

When we check with pg_ctl status command output is showing as�server�not
running.

How can we check the database cluster is shut down using which command.Can
you share us the details other than the pg_ctl.bR�
kalyani

On Mon, Sep 10, 2018 at 9:40 AM Bruce Momjian <bruce@momjian.us> wrote:

On Mon, Sep 10, 2018 at 09:30:28AM +0530, kalyani kaniganti wrote:

Hi Bruce,
Thanks for the immediate reply.
We are using pg_ctl command to shutdown the old server with no

option.In this

case the pg_ctl will consider as smart shutdown if no option is

specified as

per documentation .
please find bellow command.
<new path for data dir>/bin/pg_ctl -D <olddata dir path> stop�

Yes, this should be fine.

We are using the same script for multiple releases in Postgres sql�to

upgrade

the database from 9.1 to 9.4 .
Same script is working fine in 9.4.17 version,�while upgrading DB

from 9.1 to

9.4.17.

OK.

From the release notes of 9.4.19 version we did not find any changes

done on

pg_upgrade except one bug fix related to pg_upgrade has been fixed.
Do we have to add in our code to check for graceful shutdown or we

have to wait

for the next release?
Please let us know if you have any workaround till the next minor

patch is

released.

Well, there is nothing pending in the next beta except a better error
message text.� Can you show me the commands and the error you are
getting.� The system is looking at the output from:

� � � � $ pg_controldata $PGDATA

The "Database cluster state:" must be "shut down" before running
pg_upgrade.

--
� Bruce Momjian� <bruce@momjian.us>� � � � http://momjian.us
� EnterpriseDB� � � � � � � � � � � � � � �http://enterprisedb.com

+ As you are, so once was I.� As I am, so you will be. +
+� � � � � � � � � � � Ancient Roman grave inscription +

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +

On Mon, Sep 10, 2018 at 12:14:43PM -0400, Bruce Momjian wrote:

On Mon, Sep 10, 2018 at 05:34:14PM +0530, kalyani kaniganti wrote:

Hi Bruce,

We have checked cluster status on both the server's data and we noticed source
cluster state is in production and destination cluster state in shutdown.
Database cluster state:� � � � � � � �in production
Database cluster state:� � � � � � � �shut down

How can we shut down the Database cluster?

Uh, pg_ctl and point to the running data directory.

Let me be more specific:

$ pg_ctl -D path-to-old-cluster stop

e.g.,

$ pg_ctl -D /u/pg-old/data stop
waiting for server to shut down.... done
server stopped

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +

Hi Bruce,
We have performed the same steps.
But pg_ctl is throwing error as postmaster.pid is not found in old path and
displays as is sever running ?
pg_ctl status display output as server not running.
May I know it's an bug in 9.4.19 .Any other suggestions ?
BR,
Kalyani

On Mon, Sep 10, 2018, 10:17 PM Bruce Momjian <bruce@momjian.us> wrote:

On Mon, Sep 10, 2018 at 10:29:05PM +0530, kalyani kaniganti wrote:

Hi Bruce,
We have performed the same steps.
But pg_ctl is throwing error as postmaster.pid is not found in old path and
displays as is sever running ?
pg_ctl status display output as server not running.
May I know it's an bug in 9.4.19 .Any other suggestions ?

I suggest you show us exactly what you are doing and what you are
seeing. There is no known bug here yet.

---------------------------------------------------------------------------

BR,
Kalyani

On Mon, Sep 10, 2018, 10:17 PM Bruce Momjian <bruce@momjian.us> wrote:

On Mon, Sep 10, 2018 at 12:14:43PM -0400, Bruce Momjian wrote:

On Mon, Sep 10, 2018 at 05:34:14PM +0530, kalyani kaniganti wrote:

Hi Bruce,

We have checked cluster status on both the server's data and we noticed

source

cluster state is in production and destination cluster state in

shutdown.

Database cluster state:� � � � � � � �in production
Database cluster state:� � � � � � � �shut down

How can we shut down the Database cluster?

Uh, pg_ctl and point to the running data directory.

Let me be more specific:

� � � � $ pg_ctl -D path-to-old-cluster stop

e.g.,

� � � � $ pg_ctl -D /u/pg-old/data stop
� � � � waiting for server to shut down.... done
� � � � server stopped

--
� Bruce Momjian� <bruce@momjian.us>� � � � http://momjian.us
� EnterpriseDB� � � � � � � � � � � � � � �http://enterprisedb.com

+ As you are, so once was I.� As I am, so you will be. +
+� � � � � � � � � � � Ancient Roman grave inscription +

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +

Hi Bruce.

Below commands are executed.

pg_ctl -D <path to old data> stop

output
pg_ctl: PID file "/tmp/folder/postgresql/postmaster.pid" does not exist
Is server running?

pg_controldata <path to old data> output displayed as Database cluster :
in production

pg_ctl status displays server not running

We have performed same steps and executed this script with 9.4.17 version
of postgreSQL and its working fine.
We can get the upgarded DB and we have compared the pg_control data in
9.4.17 version we can see the DB cluster status is in shut down state for
NEW DB data and OLD DB data.

We are not understanding what has changed from 9.4.17 to 9.4.19.

BR
Kalyani

On Mon, Sep 10, 2018 at 11:18 PM Bruce Momjian <bruce@momjian.us> wrote:

On Tue, Sep 11, 2018 at 07:20:47AM +0530, kalyani kaniganti wrote:

Hi Bruce.

Below commands are executed.

pg_ctl -D <path to old data> stop

output
pg_ctl: PID file "/tmp/folder/postgresql/postmaster.pid" does not exist
Is server running?

pg_controldata <path to old data>� output displayed as Database cluster : in
production

pg_ctl status displays server not running

We have performed same steps and executed this script with 9.4.17 version of�
postgreSQL and its working fine.
We can get the upgarded�DB and we have compared the pg_control data in 9.4.17
version we can see the DB cluster status is in shut down state for NEW DB data
and OLD DB data.

We are not understanding what has changed from 9.4.17 to 9.4.19.

My guess is that Postgres was improperly shut down before you tried
using pg_ctl stop. Of course, you didn't show me anything exact, so I
can only guess.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +

Hi Bruce,
Issue is solved after performing below steps

pgctl start -D <old data folder>
pgctl stop -D <old data folder>

But we tried to reinstall the postgressql and checked again. by default the
satndby cluster is going to in production state and while executing the
above commands sometimes the upgarde is working fine and issue is
inconsistent.

Do you have any suggestions.

BR,
Kalyani.k

On Tue, Sep 11, 2018, 8:04 AM Bruce Momjian <bruce@momjian.us> wrote:

On Tue, Sep 11, 2018 at 08:57:26PM +0530, kalyani kaniganti wrote:

Hi Bruce,
Issue is solved after performing below steps�

pgctl� start -D <old data folder>
pgctl stop -D <old data folder>

Yes, I suspected this would fix it. You were shutdown, so pg_ctl stop
had no effect. Starting it and then shutting it down properly fixed it.

To summarize, you should never have been able to do upgrades without a
clean shutdown. 9.4.17 allowed it, but shouldn't have. This was fixed
in 9.4.19 throwing an error in such cases.

When the server is not cleanly shut down, there are potentially changes
in the WAL that were not replayed on startup into the shared buffers and
disk storage, so you could lose transaction or have an inconsistent
database. This was prevented in 9.4.19.

But we tried to reinstall the postgressql and checked again. by default the
satndby cluster is going to in production state and while executing the above
commands sometimes the upgarde is working fine and issue is inconsistent.

Do you have any suggestions.

You have still not provided any exact details of how the server was shut
down _before_ you ran pg_ctl stop, so I am not to going to bother
guessing any further.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +

Hi Bruce,
Thanks for the info.
But the problem ,if we repeat the same steps some time old cluster isgoing
in to in production state.Which is not recoverable after start and stop
using pg_ctl command.
How can we can check this case before upgrading to the latest release.

Any suggestions ?

BR,
Kalyani.k

On Tue, Sep 11, 2018, 9:48 PM Bruce Momjian <bruce@momjian.us> wrote:

Hi Bruce,
I have a query,in our case old db is not getting shutdown cleanly .May I
know why the DB is unable to shutdown cleanly and this happens repeatedly
.How we can recover the cluster state going to in production state after
stopping the cluster.

BR,a
Kalyani.k

On Tue, Sep 11, 2018, 10:39 PM kalyani kaniganti <kalyanithumati@gmail.com>
wrote:

On Wed, Sep 12, 2018 at 06:51:44AM +0530, kalyani kaniganti wrote:

I have a query,in our case old db is not getting shutdown cleanly .May I
know why the DB is unable to shutdown cleanly and this happens repeatedly
.How we can recover the cluster state going to in production state after
stopping the cluster.

If one of your PostgreSQL instances is not able to stop, its logs would
tell you what it did not have time to finish. You may perhaps blame the
time it takes for the shutdown checkpoint to finish. You should also
try to use the fast mode of "pg_ctl stop", which makes sure that all
sessions connected are forcibly finished cleanly, allowing Postgres to
not need crash recovery the next time it starts.
--
Michael

On Wed, Sep 12, 2018 at 11:27:44AM +0900, Michael Paquier wrote:

On Wed, Sep 12, 2018 at 06:51:44AM +0530, kalyani kaniganti wrote:

I have a query,in our case old db is not getting shutdown cleanly .May I
know why the DB is unable to shutdown cleanly and this happens repeatedly
.How we can recover the cluster state going to in production state after
stopping the cluster.

If one of your PostgreSQL instances is not able to stop, its logs would
tell you what it did not have time to finish. You may perhaps blame the
time it takes for the shutdown checkpoint to finish. You should also
try to use the fast mode of "pg_ctl stop", which makes sure that all
sessions connected are forcibly finished cleanly, allowing Postgres to
not need crash recovery the next time it starts.

Oh, I never suspected it was a timing issue. Use pg_ctl -w to wait for
the shutdown. The default for wait has changed over time. I will add
-w to the pg_upgrade manual page example using pg_ctl for stopping the
server.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +