Обсуждение: BUG #15376: Postgres sql 9.4.19 pg_upgrade stops with error Thesource cluster was not shut down cleanly.

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.

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 +

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 +

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 +

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 +

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 +

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 +

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 +

On Wed, Sep 12, 2018 at 09:48:04AM -0400, Bruce Momjian wrote:
> 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.

OK, so I looked into this and "wait/-w" has been the default action for
pg_ctl stop since at least PG 9.1, which is what the person was
reporting:

    if (!wait_set)
    {
        switch (ctl_command)
        {
            case RESTART_COMMAND:
            case START_COMMAND:
                do_wait = false;
                break;
            case STOP_COMMAND:
                do_wait = true;
                break;
            default:
                break;
        }
    }

Are they using pg_ctl -W earlier?  I don't know because the reporter is
not providing answers to my questions.  I don't think it makes sense for
me to keep trying to guess the cause of 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 +

On Wed, Sep 12, 2018 at 12:41:26PM -0400, Bruce Momjian wrote:
> Are they using pg_ctl -W earlier?  I don't know because the reporter is
> not providing answers to my questions.  I don't think it makes sense for
> me to keep trying to guess the cause of this.

Neither does it for me.  One thing which would be nice to check is if
they are using --mode=smart.
--
Michael