pg_upgrade --logfile option documentation

[Peter Eisentraut-2]

The documentation of the pg_upgrade -l/--logfile option never made much
sense to me:

  -l, --logfile=FILENAME        log session activity to file

I don't know what "session" means for pg_upgrade, so I never used it.

What it actually does is log the output of all the programs that
pg_upgrade calls internally, such as pg_ctl, psql, vacuumdb,
pg_resetxlog, to the specified file, which is quite useful for analyzing
errors such as

unable to connect to new postmaster started with the command: "/usr/lib/postgresql/9.1/bin/pg_ctl" -w -l "/dev/null" -D "/var/lib/postgresql/9.1/main" -o "-p 5433 -b" start >> "/dev/null" 2>&1

where -l would have put something in the place of /dev/null.

So what might be a better wording for this option?  Something like "log
output of internally called programs to file"?



--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[Robert Haas]

On Sun, Feb 19, 2012 at 6:13 AM, Peter Eisentraut <[hidden email]> wrote:

> The documentation of the pg_upgrade -l/--logfile option never made much
> sense to me:
>
>  -l, --logfile=FILENAME        log session activity to file
>
> I don't know what "session" means for pg_upgrade, so I never used it.
>
> What it actually does is log the output of all the programs that
> pg_upgrade calls internally, such as pg_ctl, psql, vacuumdb,
> pg_resetxlog, to the specified file, which is quite useful for analyzing
> errors such as
>
> unable to connect to new postmaster started with the command: "/usr/lib/postgresql/9.1/bin/pg_ctl" -w -l "/dev/null" -D "/var/lib/postgresql/9.1/main" -o "-p 5433 -b" start >> "/dev/null" 2>&1
>
> where -l would have put something in the place of /dev/null.
>
> So what might be a better wording for this option?  Something like "log
> output of internally called programs to file"?

I don't think we should be that specific, because we might someday
want pg_upgrade itself to write messages to that file as well, even if
it doesn't today.  I agree that the phrase "session activity" is a bit
misleading.

As a more general comment, I think that the way pg_upgrade does
logging right now is absolutely terrible.  IME, it is utterly
impossible to understand what has gone wrong with pg_upgrade without
looking at the log file.  And by default, no log file is created.  So
typically what happens is:

- I run pg_upgrade.  It fails.
- I rename the control file from the old cluster back to its original name.
- I rerun pg_upgrade, this time with -l.  It fails again.
- I read the log file, figure out what the problem is, and correct it.
- I rename the control file from the old cluster back to its original
name, again.
- I run pg_upgrade a third time.
- On a good day, it works, else go to step 5.

One pretty obvious improvement would be: if pg_upgrade fails after
renaming the control file for the old cluster out of the way - say,
while loading the schema dump into the new cluster - have it RENAME
THE OLD CONTROL FILE BACK before exiting.  But I also think the
logging needs improvement.  Right now, we studiously redirect both
stdout and stderr to /dev/null; maybe it would be better to redirect
stdout to /dev/null and NOT redirect stderr.  If that generates too
much chatter in non-failure cases, then let's adjust the output of the
commands pg_upgrade is invoking until it doesn't.  The actual cause of
the failure, rather than pg_upgrade's fairly-useless gloss on it,
ought to be visible right away, at least IMHO.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[Bruce Momjian]

On Sun, Feb 19, 2012 at 01:13:10PM +0200, Peter Eisentraut wrote:

> The documentation of the pg_upgrade -l/--logfile option never made much
> sense to me:
>
>   -l, --logfile=FILENAME        log session activity to file
>
> I don't know what "session" means for pg_upgrade, so I never used it.
>
> What it actually does is log the output of all the programs that
> pg_upgrade calls internally, such as pg_ctl, psql, vacuumdb,
> pg_resetxlog, to the specified file, which is quite useful for analyzing
> errors such as
>
> unable to connect to new postmaster started with the command: "/usr/lib/postgresql/9.1/bin/pg_ctl" -w -l "/dev/null" -D "/var/lib/postgresql/9.1/main" -o "-p 5433 -b" start >> "/dev/null" 2>&1
>
> where -l would have put something in the place of /dev/null.
>
> So what might be a better wording for this option?  Something like "log
> output of internally called programs to file"?

How about?

        -l, --logfile=FILENAME        log internal activity to file

--
  Bruce Momjian  <[hidden email]>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +

--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[Bruce Momjian]

On Sun, Feb 19, 2012 at 01:24:34PM -0500, Robert Haas wrote:

> As a more general comment, I think that the way pg_upgrade does
> logging right now is absolutely terrible.  IME, it is utterly
> impossible to understand what has gone wrong with pg_upgrade without
> looking at the log file.  And by default, no log file is created.  So
> typically what happens is:
>
> - I run pg_upgrade.  It fails.
> - I rename the control file from the old cluster back to its original name.
> - I rerun pg_upgrade, this time with -l.  It fails again.
> - I read the log file, figure out what the problem is, and correct it.
> - I rename the control file from the old cluster back to its original
> name, again.
> - I run pg_upgrade a third time.
> - On a good day, it works, else go to step 5.
>
> One pretty obvious improvement would be: if pg_upgrade fails after
> renaming the control file for the old cluster out of the way - say,
> while loading the schema dump into the new cluster - have it RENAME
> THE OLD CONTROL FILE BACK before exiting.  But I also think the

The behavior you are seeing now is the paranoia inherent in pg_upgrade's
design.  Now that pg_upgrade is being used more, perhaps that needs to
be relaxed.  

However, remember we rename that control file to prevent the old cluster
from being run accidentally, which is particular important in link mode.
There might be some error cases that still would not restore the
location of that file if we have a revert behavior on error.  A more
normal behavior would be for pg_upgrade to rename the control file only
when the upgrade completes successfully.  

> logging needs improvement.  Right now, we studiously redirect both
> stdout and stderr to /dev/null; maybe it would be better to redirect
> stdout to /dev/null and NOT redirect stderr.  If that generates too
> much chatter in non-failure cases, then let's adjust the output of the
> commands pg_upgrade is invoking until it doesn't.  The actual cause of
> the failure, rather than pg_upgrade's fairly-useless gloss on it,
> ought to be visible right away, at least IMHO.

Well, we have a -d option for debug;  we could modify that to have debug
levels.  Also, from the command line, it is difficult to have multiple
process write into a single file, so that isn't going work to have
pg_upgrade and the server logging to the same file on Windows.

--
  Bruce Momjian  <[hidden email]>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +

--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[Alvaro Herrera-7]

Excerpts from Bruce Momjian's message of mié feb 22 17:01:10 -0300 2012:
> On Sun, Feb 19, 2012 at 01:24:34PM -0500, Robert Haas wrote:

> > One pretty obvious improvement would be: if pg_upgrade fails after
> > renaming the control file for the old cluster out of the way - say,
> > while loading the schema dump into the new cluster - have it RENAME
> > THE OLD CONTROL FILE BACK before exiting.  But I also think the
>
> The behavior you are seeing now is the paranoia inherent in pg_upgrade's
> design.  Now that pg_upgrade is being used more, perhaps that needs to
> be relaxed.  
>
> However, remember we rename that control file to prevent the old cluster
> from being run accidentally, which is particular important in link mode.

... but if the upgrade failed, clearly this shouldn't be a problem.  I
agree with Robert, and was bit by this last week -- in case of any error
during the procedure, the control file should be renamed back to its
original name.

> There might be some error cases that still would not restore the
> location of that file if we have a revert behavior on error.  A more
> normal behavior would be for pg_upgrade to rename the control file only
> when the upgrade completes successfully.  

Not sure about this.  If the upgrades completes successfully and the
file is not renamed at the last minute due to some error, that would be
a problem as well, because now the old cluster would happily run and
perhaps corrupt the data files from under the new cluster.

--
Álvaro Herrera <[hidden email]>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[Bruce Momjian]

On Wed, Feb 22, 2012 at 05:22:29PM -0300, Alvaro Herrera wrote:

>
> Excerpts from Bruce Momjian's message of mié feb 22 17:01:10 -0300 2012:
> > On Sun, Feb 19, 2012 at 01:24:34PM -0500, Robert Haas wrote:
>
> > > One pretty obvious improvement would be: if pg_upgrade fails after
> > > renaming the control file for the old cluster out of the way - say,
> > > while loading the schema dump into the new cluster - have it RENAME
> > > THE OLD CONTROL FILE BACK before exiting.  But I also think the
> >
> > The behavior you are seeing now is the paranoia inherent in pg_upgrade's
> > design.  Now that pg_upgrade is being used more, perhaps that needs to
> > be relaxed.  
> >
> > However, remember we rename that control file to prevent the old cluster
> > from being run accidentally, which is particular important in link mode.
>
> ... but if the upgrade failed, clearly this shouldn't be a problem.  I
> agree with Robert, and was bit by this last week -- in case of any error
> during the procedure, the control file should be renamed back to its
> original name.
>
> > There might be some error cases that still would not restore the
> > location of that file if we have a revert behavior on error.  A more
> > normal behavior would be for pg_upgrade to rename the control file only
> > when the upgrade completes successfully.  
>
> Not sure about this.  If the upgrades completes successfully and the
> file is not renamed at the last minute due to some error, that would be
> a problem as well, because now the old cluster would happily run and
> perhaps corrupt the data files from under the new cluster.

Well, the basic problem is that the user, before pg_upgrade started,
installed a new cluster that works.  If we rename the old control, but
rename it back on failure, there are cases we will miss, kill like -9 or
a server crash, and it will not be obvious to them that the control file
was renamed.

Of course, if we only rename on success, and there is kill -9 or server
crash, the old cluster is still start-able, like the new one.

One good argument for the rename early is that on a server crash, the
system is probably going to restart the database automatically, and that
means the old server.

Right now we have a clear message that they need to rename the control
file to start the old server.  Not sure what the new wording would look
like --- let me try.

--
  Bruce Momjian  <[hidden email]>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +

--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[Peter Eisentraut-2]

On ons, 2012-02-22 at 14:38 -0500, Bruce Momjian wrote:
> How about?
>
> -l, --logfile=FILENAME        log internal activity to file

That sounds better.


--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[Peter Eisentraut-2]

On sön, 2012-02-19 at 13:24 -0500, Robert Haas wrote:
> But I also think the
> logging needs improvement.  Right now, we studiously redirect both
> stdout and stderr to /dev/null; maybe it would be better to redirect
> stdout to /dev/null and NOT redirect stderr.  If that generates too
> much chatter in non-failure cases, then let's adjust the output of the
> commands pg_upgrade is invoking until it doesn't.

That should be achievable for calls to psql and vacuumdb, say, but what
would you do with the server logs?


--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[Robert Haas]

On Wed, Feb 22, 2012 at 3:51 PM, Peter Eisentraut <[hidden email]> wrote:

> On sön, 2012-02-19 at 13:24 -0500, Robert Haas wrote:
>> But I also think the
>> logging needs improvement.  Right now, we studiously redirect both
>> stdout and stderr to /dev/null; maybe it would be better to redirect
>> stdout to /dev/null and NOT redirect stderr.  If that generates too
>> much chatter in non-failure cases, then let's adjust the output of the
>> commands pg_upgrade is invoking until it doesn't.
>
> That should be achievable for calls to psql and vacuumdb, say, but what
> would you do with the server logs?

I don't know.  It might be less of an issue, though.  I mean, IME,
what typically happens is that psql fails to restore the dump, either
because it can't connect to the new database or because it's confused
by some stupid case that isn't handled well.  So even if we could just
improve the error handling to report those types of failures more
transparently, I think it would be a big improvement.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[Bruce Momjian]

On Wed, Feb 22, 2012 at 05:49:26PM -0500, Robert Haas wrote:

> On Wed, Feb 22, 2012 at 3:51 PM, Peter Eisentraut <[hidden email]> wrote:
> > On sön, 2012-02-19 at 13:24 -0500, Robert Haas wrote:
> >> But I also think the
> >> logging needs improvement.  Right now, we studiously redirect both
> >> stdout and stderr to /dev/null; maybe it would be better to redirect
> >> stdout to /dev/null and NOT redirect stderr.  If that generates too
> >> much chatter in non-failure cases, then let's adjust the output of the
> >> commands pg_upgrade is invoking until it doesn't.
> >
> > That should be achievable for calls to psql and vacuumdb, say, but what
> > would you do with the server logs?
>
> I don't know.  It might be less of an issue, though.  I mean, IME,
> what typically happens is that psql fails to restore the dump, either
> because it can't connect to the new database or because it's confused
> by some stupid case that isn't handled well.  So even if we could just
> improve the error handling to report those types of failures more
> transparently, I think it would be a big improvement.

Well, on Unix, it is easy to redirect the server logs to the same place
as the pg_upgrade logs.  That doesn't help?  How would we improve the
reporting of SQL restore failures?

--
  Bruce Momjian  <[hidden email]>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +

--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[Bruce Momjian]

On Wed, Feb 22, 2012 at 10:50:07PM +0200, Peter Eisentraut wrote:
> On ons, 2012-02-22 at 14:38 -0500, Bruce Momjian wrote:
> > How about?
> >
> > -l, --logfile=FILENAME        log internal activity to file
>
> That sounds better.

Done.

--
  Bruce Momjian  <[hidden email]>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +

--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[Bruce Momjian]

On Wed, Feb 22, 2012 at 03:37:29PM -0500, Bruce Momjian wrote:

> On Wed, Feb 22, 2012 at 05:22:29PM -0300, Alvaro Herrera wrote:
> > Not sure about this.  If the upgrades completes successfully and the
> > file is not renamed at the last minute due to some error, that would be
> > a problem as well, because now the old cluster would happily run and
> > perhaps corrupt the data files from under the new cluster.
>
> Well, the basic problem is that the user, before pg_upgrade started,
> installed a new cluster that works.  If we rename the old control, but
> rename it back on failure, there are cases we will miss, kill like -9 or
> a server crash, and it will not be obvious to them that the control file
> was renamed.
>
> Of course, if we only rename on success, and there is kill -9 or server
> crash, the old cluster is still start-able, like the new one.
>
> One good argument for the rename early is that on a server crash, the
> system is probably going to restart the database automatically, and that
> means the old server.
>
> Right now we have a clear message that they need to rename the control
> file to start the old server.  Not sure what the new wording would look
> like --- let me try.

I have thought about this, and feel that it would be odd to lock the old
cluster at the start of the upgrade, and then unlock it on a failure,
particularly because we can't always unlock it, e.g. operating system
crash.

A cleaner solution would be to lock it when we complete the upgrade,
which I have done in the attached patch.  I have also added a warning
about restarting the old server when link mode is used, and updated the
documentation to match the new behavior.

Patch attached.  I would like to apply this to 9.2/HEAD.

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

Performing Consistency Checks
-----------------------------
Checking current, bin, and data directories                 ok
Checking cluster versions                                   ok
Checking database user is a superuser                       ok
Checking for prepared transactions                          ok
Checking for reg* system OID user data types                ok
Checking for contrib/isn with bigint-passing mismatch       ok
Creating catalog dump                                       ok
Checking for prepared transactions                          ok
Checking for presence of required libraries                 ok

If pg_upgrade fails after this point, you must re-initdb the
new cluster before continuing.

Performing Upgrade
------------------
Analyzing all rows in the new cluster                       ok
Freezing all rows on the new cluster                        ok
Deleting new commit clogs                                   ok
Copying old commit clogs to new server                      ok
Setting next transaction ID for new cluster                 ok
Resetting WAL archives                                      ok
Setting frozenxid counters in new cluster                   ok
Creating databases in the new cluster                       ok
Adding support functions to new cluster                     ok
Restoring database schema to new cluster                    ok
Removing support functions from new cluster                 ok
Linking user relation files
                                                            ok
Setting next OID for new cluster                            ok
Creating script to delete old cluster                       ok
Adding ".old" suffix to old global/pg_control               ok

If you want to start the old cluster, you will need to remove
the ".old" suffix from /u/pgsql.old/data/global/pg_control.old.
Because "link" mode was used, the old cluster cannot be safely
started once the new cluster has been started.

Upgrade complete
----------------
Optimizer statistics are not transferred by pg_upgrade so
consider running:
    vacuumdb --all --analyze-only
on the newly-upgraded cluster.

Running this script will delete the old cluster's data files:
    /usr/local/pgdev/pg_upgrade/delete_old_cluster.sh

--
  Bruce Momjian  <[hidden email]>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +


--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[Robert Haas]

On Tue, Feb 28, 2012 at 11:21 AM, Bruce Momjian <[hidden email]> wrote:

> On Wed, Feb 22, 2012 at 03:37:29PM -0500, Bruce Momjian wrote:
>> On Wed, Feb 22, 2012 at 05:22:29PM -0300, Alvaro Herrera wrote:
>> > Not sure about this.  If the upgrades completes successfully and the
>> > file is not renamed at the last minute due to some error, that would be
>> > a problem as well, because now the old cluster would happily run and
>> > perhaps corrupt the data files from under the new cluster.
>>
>> Well, the basic problem is that the user, before pg_upgrade started,
>> installed a new cluster that works.  If we rename the old control, but
>> rename it back on failure, there are cases we will miss, kill like -9 or
>> a server crash, and it will not be obvious to them that the control file
>> was renamed.
>>
>> Of course, if we only rename on success, and there is kill -9 or server
>> crash, the old cluster is still start-able, like the new one.
>>
>> One good argument for the rename early is that on a server crash, the
>> system is probably going to restart the database automatically, and that
>> means the old server.
>>
>> Right now we have a clear message that they need to rename the control
>> file to start the old server.  Not sure what the new wording would look
>> like --- let me try.
>
> I have thought about this, and feel that it would be odd to lock the old
> cluster at the start of the upgrade, and then unlock it on a failure,
> particularly because we can't always unlock it, e.g. operating system
> crash.
>
> A cleaner solution would be to lock it when we complete the upgrade,
> which I have done in the attached patch.  I have also added a warning
> about restarting the old server when link mode is used, and updated the
> documentation to match the new behavior.
>
> Patch attached.  I would like to apply this to 9.2/HEAD.
>
> ---------------------------------------------------------------------------
>
> Performing Consistency Checks
> -----------------------------
> Checking current, bin, and data directories                 ok
> Checking cluster versions                                   ok
> Checking database user is a superuser                       ok
> Checking for prepared transactions                          ok
> Checking for reg* system OID user data types                ok
> Checking for contrib/isn with bigint-passing mismatch       ok
> Creating catalog dump                                       ok
> Checking for prepared transactions                          ok
> Checking for presence of required libraries                 ok
>
> If pg_upgrade fails after this point, you must re-initdb the
> new cluster before continuing.
>
> Performing Upgrade
> ------------------
> Analyzing all rows in the new cluster                       ok
> Freezing all rows on the new cluster                        ok
> Deleting new commit clogs                                   ok
> Copying old commit clogs to new server                      ok
> Setting next transaction ID for new cluster                 ok
> Resetting WAL archives                                      ok
> Setting frozenxid counters in new cluster                   ok
> Creating databases in the new cluster                       ok
> Adding support functions to new cluster                     ok
> Restoring database schema to new cluster                    ok
> Removing support functions from new cluster                 ok
> Linking user relation files
>                                                            ok
> Setting next OID for new cluster                            ok
> Creating script to delete old cluster                       ok
> Adding ".old" suffix to old global/pg_control               ok
>
> If you want to start the old cluster, you will need to remove
> the ".old" suffix from /u/pgsql.old/data/global/pg_control.old.
> Because "link" mode was used, the old cluster cannot be safely
> started once the new cluster has been started.
>
> Upgrade complete
> ----------------
> Optimizer statistics are not transferred by pg_upgrade so
> consider running:
>    vacuumdb --all --analyze-only
> on the newly-upgraded cluster.
>
> Running this script will delete the old cluster's data files:
>    /usr/local/pgdev/pg_upgrade/delete_old_cluster.sh

I think you should rename the old control file just before the step
that says "linking user relation files".  That's the point after which
it becomes unsafe to start the old cluster, right?

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[Alvaro Herrera-7]

Excerpts from Robert Haas's message of mar feb 28 15:24:45 -0300 2012:

> I think you should rename the old control file just before the step
> that says "linking user relation files".  That's the point after which
> it becomes unsafe to start the old cluster, right?

Also, if it's not using link mode, what is the point in doing the rename
in the first place?  I was using copy mode when I did my tests and yet
it got renamed (unless link mode is now the default, which I doubt?)

--
Álvaro Herrera <[hidden email]>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[Bruce Momjian]

On Tue, Feb 28, 2012 at 01:24:45PM -0500, Robert Haas wrote:
> > Running this script will delete the old cluster's data files:
> >    /usr/local/pgdev/pg_upgrade/delete_old_cluster.sh
>
> I think you should rename the old control file just before the step
> that says "linking user relation files".  That's the point after which
> it becomes unsafe to start the old cluster, right?

Yes, it is true that that is the danger point, and also it is much less
likely to fail at that point --- it usually happens during the schema
creation.  I would have to add some more conditional wording without
clearly stating if the old suffix is present.

--
  Bruce Momjian  <[hidden email]>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +

--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[Bruce Momjian]

On Tue, Feb 28, 2012 at 03:48:03PM -0300, Alvaro Herrera wrote:

>
> Excerpts from Robert Haas's message of mar feb 28 15:24:45 -0300 2012:
>
> > I think you should rename the old control file just before the step
> > that says "linking user relation files".  That's the point after which
> > it becomes unsafe to start the old cluster, right?
>
> Also, if it's not using link mode, what is the point in doing the rename
> in the first place?  I was using copy mode when I did my tests and yet
> it got renamed (unless link mode is now the default, which I doubt?)

You are right that we lock unconditionally.  We can certainly only lock
in link mode.

--
  Bruce Momjian  <[hidden email]>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +

--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[Bruce Momjian]

On Tue, Feb 28, 2012 at 02:15:30PM -0500, Bruce Momjian wrote:

> On Tue, Feb 28, 2012 at 01:24:45PM -0500, Robert Haas wrote:
> > > Running this script will delete the old cluster's data files:
> > >    /usr/local/pgdev/pg_upgrade/delete_old_cluster.sh
> >
> > I think you should rename the old control file just before the step
> > that says "linking user relation files".  That's the point after which
> > it becomes unsafe to start the old cluster, right?
>
> Yes, it is true that that is the danger point, and also it is much less
> likely to fail at that point --- it usually happens during the schema
> creation.  I would have to add some more conditional wording without
> clearly stating if the old suffix is present.

OK, I have implemented both Roberts and Àlvaro's ideas in my patch.
I only add the .old suffix to pg_controldata when link mode is used, and
I now do it after the schema has been created (the most common failure
case for pg_upgrade), and just before we actually link files --- both
very good ideas.

Patch attached;  new pg_upgrade output with link mode below.

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

Performing Consistency Checks
-----------------------------
Checking current, bin, and data directories                 ok
Checking cluster versions                                   ok
Checking database user is a superuser                       ok
Checking for prepared transactions                          ok
Checking for reg* system OID user data types                ok
Checking for contrib/isn with bigint-passing mismatch       ok
Creating catalog dump                                       ok
Checking for prepared transactions                          ok
Checking for presence of required libraries                 ok

If pg_upgrade fails after this point, you must re-initdb the
new cluster before continuing.

Performing Upgrade
------------------
Analyzing all rows in the new cluster                       ok
Freezing all rows on the new cluster                        ok
Deleting new commit clogs                                   ok
Copying old commit clogs to new server                      ok
Setting next transaction ID for new cluster                 ok
Resetting WAL archives                                      ok
Setting frozenxid counters in new cluster                   ok
Creating databases in the new cluster                       ok
Adding support functions to new cluster                     ok
Restoring database schema to new cluster                    ok
Removing support functions from new cluster                 ok
Adding ".old" suffix to old global/pg_control               ok

If you want to start the old cluster, you will need to remove
the ".old" suffix from /u/pgsql.old/data/global/pg_control.old.
Because "link" mode was used, the old cluster cannot be safely
started once the new cluster has been started.

Linking user relation files
                                                            ok
Setting next OID for new cluster                            ok
Creating script to delete old cluster                       ok

Upgrade complete
----------------
Optimizer statistics are not transferred by pg_upgrade so
consider running:
    vacuumdb --all --analyze-only
on the newly-upgraded cluster.

Running this script will delete the old cluster's data files:
    /usr/local/pgdev/pg_upgrade/delete_old_cluster.sh


--
  Bruce Momjian  <[hidden email]>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +


--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[Robert Haas]

On Tue, Feb 28, 2012 at 9:45 PM, Bruce Momjian <[hidden email]> wrote:
> OK, I have implemented both Roberts and Àlvaro's ideas in my patch.
> I only add the .old suffix to pg_controldata when link mode is used, and
> I now do it after the schema has been created (the most common failure
> case for pg_upgrade), and just before we actually link files --- both
> very good ideas.

Thanks for working on this.  I think this will be a significant
usability improvement.

Any ideas about improving the error reporting more generally, so that
when reloading the dump fails, the user can easily see what went
belly-up, even if they didn't use -l?

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[Bruce Momjian]

On Wed, Feb 29, 2012 at 04:34:24PM -0500, Robert Haas wrote:
> On Tue, Feb 28, 2012 at 9:45 PM, Bruce Momjian <[hidden email]> wrote:
> > OK, I have implemented both Roberts and Àlvaro's ideas in my patch.
> > I only add the .old suffix to pg_controldata when link mode is used, and
> > I now do it after the schema has been created (the most common failure
> > case for pg_upgrade), and just before we actually link files --- both
> > very good ideas.
>
> Thanks for working on this.  I think this will be a significant
> usability improvement.

Glad I got such good feedback and ideas.

> Any ideas about improving the error reporting more generally, so that
> when reloading the dump fails, the user can easily see what went
> belly-up, even if they didn't use -l?

The only idea I have is to write the psql log to a temporary file and
report the last X lines from the file in case of failure.  Does that
help?

--
  Bruce Momjian  <[hidden email]>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +

--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[AM]

On Feb 29, 2012, at 6:02 PM, Bruce Momjian wrote:

> On Wed, Feb 29, 2012 at 04:34:24PM -0500, Robert Haas wrote:
>> On Tue, Feb 28, 2012 at 9:45 PM, Bruce Momjian <[hidden email]> wrote:
>>> OK, I have implemented both Roberts and Àlvaro's ideas in my patch.
>>> I only add the .old suffix to pg_controldata when link mode is used, and
>>> I now do it after the schema has been created (the most common failure
>>> case for pg_upgrade), and just before we actually link files --- both
>>> very good ideas.
>>
>> Thanks for working on this.  I think this will be a significant
>> usability improvement.
>
> Glad I got such good feedback and ideas.
>
>> Any ideas about improving the error reporting more generally, so that
>> when reloading the dump fails, the user can easily see what went
>> belly-up, even if they didn't use -l?
>
> The only idea I have is to write the psql log to a temporary file and
> report the last X lines from the file in case of failure.  Does that
> help?
>

Perhaps pg_upgrade can print the path to the temp file containing the log and instruct the user to look there for more detail.

Cheers,
M




--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers