Re: pg_migrator to /contrib in a later 9.0 beta

There was talk of including pg_migrator in Postgres 9.0 in /contrib. Do
we still want to do that?

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

On Mon, Apr 26, 2010 at 9:26 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> There was talk of including pg_migrator in Postgres 9.0 in /contrib.  Do
> we still want to do that?

I think you articulated some pretty good reasons previously for
keeping it separate and, at any rate, I'm not eager to do it at the
11th hour without due consideration and adequate engineering time. So
I vote for holding off for this release and possibly revisiting at
some point down the road.

...Robert

Robert Haas wrote:
> On Mon, Apr 26, 2010 at 9:26 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> > There was talk of including pg_migrator in Postgres 9.0 in /contrib. ?Do
> > we still want to do that?
>
> I think you articulated some pretty good reasons previously for
> keeping it separate and, at any rate, I'm not eager to do it at the
> 11th hour without due consideration and adequate engineering time. So
> I vote for holding off for this release and possibly revisiting at
> some point down the road.

You might also remember I was outvoted. It will not be hard to put it
in /contrib as that is already a valid build option for pg_migrator.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

On Mon, Apr 26, 2010 at 9:46 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> Robert Haas wrote:
>> On Mon, Apr 26, 2010 at 9:26 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
>> > There was talk of including pg_migrator in Postgres 9.0 in /contrib. ?Do
>> > we still want to do that?
>>
>> I think you articulated some pretty good reasons previously for
>> keeping it separate and, at any rate, I'm not eager to do it at the
>> 11th hour without due consideration and adequate engineering time.  So
>> I vote for holding off for this release and possibly revisiting at
>> some point down the road.
>
> You might also remember I was outvoted.  It will not be hard to put it
> in /contrib as that is already a valid build option for pg_migrator.

[shrug...]

If that's the consensus I'll go along with it, but I'm not excited
about adding more things to our to-do list at this point, even
apparently simple ones.

...Robert

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> On Mon, Apr 26, 2010 at 9:26 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
>> There was talk of including pg_migrator in Postgres 9.0 in /contrib. Do
>> we still want to do that?

> I think you articulated some pretty good reasons previously for
> keeping it separate and, at any rate, I'm not eager to do it at the
> 11th hour without due consideration and adequate engineering time.

I concur; it's about a month too late to propose this.

regards, tom lane

Tom Lane wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> > On Mon, Apr 26, 2010 at 9:26 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> >> There was talk of including pg_migrator in Postgres 9.0 in /contrib. Do
> >> we still want to do that?
>
> > I think you articulated some pretty good reasons previously for
> > keeping it separate and, at any rate, I'm not eager to do it at the
> > 11th hour without due consideration and adequate engineering time.
>
> I concur; it's about a month too late to propose this.

I am confused why it is late. We add to /contrib even during beta, and
I didn't bring it up earlier because I didn't want to be pushing my own
software. Was someone else supposed to suggest it a month ago?

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

Bruce Momjian wrote:
>> I concur; it's about a month too late to propose this.
>>
>
> I am confused why it is late. We add to /contrib even during beta, and
> I didn't bring it up earlier because I didn't want to be pushing my own
> software. Was someone else supposed to suggest it a month ago?
>
>

Bruce,

you're not usually such a shrinking violet. If you don't push your
project it's less likely others will, IMNSHO.

cheers

andrew

Andrew Dunstan wrote:
>
>
> Bruce Momjian wrote:
> >> I concur; it's about a month too late to propose this.
> >>
> >
> > I am confused why it is late. We add to /contrib even during beta, and
> > I didn't bring it up earlier because I didn't want to be pushing my own
> > software. Was someone else supposed to suggest it a month ago?
> >
> >
>
> Bruce,
>
> you're not usually such a shrinking violet. If you don't push your
> project it's less likely others will, IMNSHO.

Well, I am sensitive to be pushing my external project into /contrib when
I am someone who puts other stuff into contrib, and I have been working
on pg_migrator for only one year.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

Tom Lane wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> > On Mon, Apr 26, 2010 at 9:26 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> >> There was talk of including pg_migrator in Postgres 9.0 in /contrib. Do
> >> we still want to do that?
>
> > I think you articulated some pretty good reasons previously for
> > keeping it separate and, at any rate, I'm not eager to do it at the
> > 11th hour without due consideration and adequate engineering time.
>
> I concur; it's about a month too late to propose this.

I talked to a few people personally about this, and it seems there was a
misunderstanding. I was not asking if pg_migrator should be in 9.0
beta1. I was asking if we should think about putting it into a later
9.0 beta, like 9.0 beta3. It would be another major 9.0 feature.

Because pg_migrator is an external project, it seemed best to do it
after beta1, while we are just waiting for bug reports, and not during
our main push to beta1.

FYI, here was the last discussion about having pg_migrator in /contrib
in December 2009:

http://archives.postgresql.org/pgsql-hackers/2009-12/msg01787.php

and most of the limitations of pg_migrator are gone when migrating to
9.0, even from Postgres 8.3. This could help beta testers move their
data to 9.0 as well.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

Bruce Momjian wrote:
>
> and most of the limitations of pg_migrator are gone when migrating to
> 9.0, even from Postgres 8.3. This could help beta testers move their
> data to 9.0 as well.
>
>
Wouldn't this help even for beta1?

Cheers

Mark

Mark Kirkwood <mark(dot)kirkwood(at)catalyst(dot)net(dot)nz> writes:
> Bruce Momjian wrote:
>> and most of the limitations of pg_migrator are gone when migrating to
>> 9.0, even from Postgres 8.3. This could help beta testers move their
>> data to 9.0 as well.

> Wouldn't this help even for beta1?

It's too late for beta1. It probably should have been proposed when
there was still time ... but it wasn't.

I'm not necessarily averse to shoving it in as of beta2 or beta3 or
so; we've always been laxer about contrib than the core server.

regards, tom lane

Mark Kirkwood wrote:
> Bruce Momjian wrote:
> >
> > and most of the limitations of pg_migrator are gone when migrating to
> > 9.0, even from Postgres 8.3. This could help beta testers move their
> > data to 9.0 as well.
> >
> >
> Wouldn't this help even for beta1?

It would, but there is so much work going into getting other features
done that there just isn't enough time. We don't want pg_migrator
delaying beta1.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

On tor, 2010-04-29 at 17:34 -0400, Bruce Momjian wrote:
> I talked to a few people personally about this, and it seems there was a
> misunderstanding. I was not asking if pg_migrator should be in 9.0
> beta1. I was asking if we should think about putting it into a later
> 9.0 beta, like 9.0 beta3. It would be another major 9.0 feature.

If it's supposed to be a major feature, then it should be in src/bin,
and fully integrated in the install, the documentation, etc.

If you want to put it in contrib because the standards are more lax
there, then by definition it's not really a major feature, it's just a
random feature that was snuck in. You can't have it both ways.

My personal feeling is that pg_migrator should be fully integrated, but
it's too late for that, obviously. Let's do it for 9.1.

I also think that the standards for contrib should not be so lax that a
completely new module can be added after beta. (This is mostly informed
by the feeling that contrib should go away entirely.)

Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> My personal feeling is that pg_migrator should be fully integrated, but
> it's too late for that, obviously. Let's do it for 9.1.

+1

> I also think that the standards for contrib should not be so lax that a
> completely new module can be added after beta. (This is mostly informed
> by the feeling that contrib should go away entirely.)

+1

For the record, the contrib replacement would look like proper Extension
handling in dump&restore, PGXS support for windows, and PGAN for source
level archive distribution. We'd still rely on distributions support for
binaries.

Those are the technical layers we need, then we'd have a PGAN entry for
replacing contrib, and a host of other ones. The contrib Archive Network
would contain -core reviewed (and maintained?) extensions, the other
ones are on their own. Maybe the main other one would be (could be?) a
new component of pgfoundry.

Regards,
--
dim

Dimitri Fontaine <dfontaine(at)hi-media(dot)com> writes:
> Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
>> I also think that the standards for contrib should not be so lax that a
>> completely new module can be added after beta. (This is mostly informed
>> by the feeling that contrib should go away entirely.)

> +1

> For the record, the contrib replacement would look like proper Extension
> handling in dump&restore, PGXS support for windows, and PGAN for source
> level archive distribution. We'd still rely on distributions support for
> binaries.

Both of you are living in some fantasy land. The reason contrib is held
to a lower standard than core is that nobody is willing to put the same
level of effort into contrib. There are modules in there (most of them,
in fact) that haven't been touched for years, other than as part of
system-wide search-and-replace patches. Extension support is not going
to magically fix that and cause maintenance effort to appear from
nowhere.

In the end, the main useful function that contrib serves is to provide
examples of how to write Postgres extensions. Because of that, removing
it as Peter suggests doesn't seem like a good idea to me.

regards, tom lane

Tom Lane wrote:
> Dimitri Fontaine <dfontaine(at)hi-media(dot)com> writes:
>
>> Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
>>
>>> I also think that the standards for contrib should not be so lax that a
>>> completely new module can be added after beta. (This is mostly informed
>>> by the feeling that contrib should go away entirely.)
>>>
>
>
>> +1
>>
>
>
>> For the record, the contrib replacement would look like proper Extension
>> handling in dump&restore, PGXS support for windows, and PGAN for source
>> level archive distribution. We'd still rely on distributions support for
>> binaries.
>>
>
> Both of you are living in some fantasy land. The reason contrib is held
> to a lower standard than core is that nobody is willing to put the same
> level of effort into contrib. There are modules in there (most of them,
> in fact) that haven't been touched for years, other than as part of
> system-wide search-and-replace patches. Extension support is not going
> to magically fix that and cause maintenance effort to appear from
> nowhere.
>
> In the end, the main useful function that contrib serves is to provide
> examples of how to write Postgres extensions. Because of that, removing
> it as Peter suggests doesn't seem like a good idea to me.
>
>

Quite so. Getting a better extensions mechanism doesn't mean we should
abandon what we currently have, IMNSHO.

cheers

andrew

On fre, 2010-04-30 at 10:45 -0400, Tom Lane wrote:
> In the end, the main useful function that contrib serves is to provide
> examples of how to write Postgres extensions.

Maybe, but pg_migrator surely doesn't fit that. And neither does about
a third of the other contrib modules, IMO.

> Because of that, removing
> it as Peter suggests doesn't seem like a good idea to me.

contrib means many things to many people, and that's exactly the problem
in my mind: It doesn't mean anything in particular. If we were to
separate it into

- examples

- production-quality add-ons with small user base

- production-quality add-ons that everyone wants, but we keep them as
plugins because plugins are cool

- experimental code that we wanted to ship anyway

- (historically) differently licensed code

then these discussions would be much simpler.

Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
> Quite so. Getting a better extensions mechanism doesn't mean we should
> abandon what we currently have, IMNSHO.

Yeah, agreed. Exactly what I proposed. The only change is the
distribution mean. Either we keep things as they are now exactly, or we
use the new Archive Network facilities, in the spirit of being sure they
get exercised, requiring that commiters gets to use them and maybe use a
separate code repository for contribs. Or simply an adjusted Makefile.

Summary: the only proposed change is how to do it, not what we do.

Regards,
--
dim

Tom Lane wrote:
> Dimitri Fontaine <dfontaine(at)hi-media(dot)com> writes:
> > Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> >> I also think that the standards for contrib should not be so lax that a
> >> completely new module can be added after beta. (This is mostly informed
> >> by the feeling that contrib should go away entirely.)
>
> > +1
>
> > For the record, the contrib replacement would look like proper Extension
> > handling in dump&restore, PGXS support for windows, and PGAN for source
> > level archive distribution. We'd still rely on distributions support for
> > binaries.
>
> Both of you are living in some fantasy land. The reason contrib is held
> to a lower standard than core is that nobody is willing to put the same
> level of effort into contrib. There are modules in there (most of them,
> in fact) that haven't been touched for years, other than as part of
> system-wide search-and-replace patches. Extension support is not going
> to magically fix that and cause maintenance effort to appear from
> nowhere.
>
> In the end, the main useful function that contrib serves is to provide
> examples of how to write Postgres extensions. Because of that, removing
> it as Peter suggests doesn't seem like a good idea to me.

So what do people want to do with pg_migrator? I don't think calling
pg_migrator a major features requires it to be in /bin. We added full
text search to /contrib years ago and that was a major feature.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

On Sat, May 1, 2010 at 02:23, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> Tom Lane wrote:
>> Dimitri Fontaine <dfontaine(at)hi-media(dot)com> writes:
>> > Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
>> >> I also think that the standards for contrib should not be so lax that a
>> >> completely new module can be added after beta.  (This is mostly informed
>> >> by the feeling that contrib should go away entirely.)
>>
>> > +1
>>
>> > For the record, the contrib replacement would look like proper Extension
>> > handling in dump&restore, PGXS support for windows, and PGAN for source
>> > level archive distribution. We'd still rely on distributions support for
>> > binaries.
>>
>> Both of you are living in some fantasy land.  The reason contrib is held
>> to a lower standard than core is that nobody is willing to put the same
>> level of effort into contrib.  There are modules in there (most of them,
>> in fact) that haven't been touched for years, other than as part of
>> system-wide search-and-replace patches.  Extension support is not going
>> to magically fix that and cause maintenance effort to appear from
>> nowhere.
>>
>> In the end, the main useful function that contrib serves is to provide
>> examples of how to write Postgres extensions.  Because of that, removing
>> it as Peter suggests doesn't seem like a good idea to me.
>
> So what do people want to do with pg_migrator?  I don't think calling
> pg_migrator a major features requires it to be in /bin.  We added full
> text search to /contrib years ago and that was a major feature.

There is a reason people said that "8.3 comes with full text search" -
that's when it really became a major feature to the outside world.
Before that, it wasn't included in most comparisons. It was a PITA to
install (well, not install, but upgrading when you had it, etc). (once
you knew the insides, it was a major feature yes, but people didn't
know about that)

A lot of people are not willing to put stuff labeled "contrib" on
their production boxes.

And as Tom says, even we *ourselves* acknowledge that things in
/contrib are held to a lower standard. If we put that label on
pg_migrator, it doesn't exactly signal people that this is something
they should use on their critical database.

--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/

On Sat, May 1, 2010 at 3:32 AM, Magnus Hagander <magnus(at)hagander(dot)net> wrote:
> On Sat, May 1, 2010 at 02:23, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
>> Tom Lane wrote:
>>> Dimitri Fontaine <dfontaine(at)hi-media(dot)com> writes:
>>> > Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
>>> >> I also think that the standards for contrib should not be so lax that a
>>> >> completely new module can be added after beta.  (This is mostly informed
>>> >> by the feeling that contrib should go away entirely.)
>>>
>>> > +1
>>>
>>> > For the record, the contrib replacement would look like proper Extension
>>> > handling in dump&restore, PGXS support for windows, and PGAN for source
>>> > level archive distribution. We'd still rely on distributions support for
>>> > binaries.
>>>
>>> Both of you are living in some fantasy land.  The reason contrib is held
>>> to a lower standard than core is that nobody is willing to put the same
>>> level of effort into contrib.  There are modules in there (most of them,
>>> in fact) that haven't been touched for years, other than as part of
>>> system-wide search-and-replace patches.  Extension support is not going
>>> to magically fix that and cause maintenance effort to appear from
>>> nowhere.
>>>
>>> In the end, the main useful function that contrib serves is to provide
>>> examples of how to write Postgres extensions.  Because of that, removing
>>> it as Peter suggests doesn't seem like a good idea to me.
>>
>> So what do people want to do with pg_migrator?  I don't think calling
>> pg_migrator a major features requires it to be in /bin.  We added full
>> text search to /contrib years ago and that was a major feature.
>
>
> There is a reason people said that "8.3 comes with full text search" -
> that's when it really  became a major feature to the outside world.
> Before that, it wasn't included in most comparisons. It was a PITA to
> install (well, not install, but upgrading when you had it, etc). (once
> you knew the insides, it was a major feature yes, but people didn't
> know about that)
>
> A lot of people are not willing to put stuff labeled "contrib" on
> their production boxes.
>
> And as Tom says, even we *ourselves* acknowledge that things in
> /contrib are held to a lower standard. If we put that label on
> pg_migrator, it doesn't exactly signal people that this is something
> they should use on their critical database.

Well, I guess that begs the question... IS this something they should
use on their critical database?

...Robert

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> On Sat, May 1, 2010 at 3:32 AM, Magnus Hagander <magnus(at)hagander(dot)net> wrote:
>> A lot of people are not willing to put stuff labeled "contrib" on
>> their production boxes.
>>
>> And as Tom says, even we *ourselves* acknowledge that things in
>> /contrib are held to a lower standard. If we put that label on
>> pg_migrator, it doesn't exactly signal people that this is something
>> they should use on their critical database.

> Well, I guess that begs the question... IS this something they should
> use on their critical database?

Not unless it gets some serious testing during the 9.0 beta cycle.
Which it surely won't get if it's not included in the core tarball.

I think that having it in contrib for a release cycle or so would be
exactly the right approach, actually. Peter's position that it should
be in /bin is fine *once the bugs are out*. Just dropping it there
doesn't make the bugs go away.

regards, tom lane

Tom Lane wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> > On Sat, May 1, 2010 at 3:32 AM, Magnus Hagander <magnus(at)hagander(dot)net> wrote:
> >> A lot of people are not willing to put stuff labeled "contrib" on
> >> their production boxes.
> >>
> >> And as Tom says, even we *ourselves* acknowledge that things in
> >> /contrib are held to a lower standard. If we put that label on
> >> pg_migrator, it doesn't exactly signal people that this is something
> >> they should use on their critical database.
>
> > Well, I guess that begs the question... IS this something they should
> > use on their critical database?
>
> Not unless it gets some serious testing during the 9.0 beta cycle.
> Which it surely won't get if it's not included in the core tarball.
>
> I think that having it in contrib for a release cycle or so would be
> exactly the right approach, actually. Peter's position that it should
> be in /bin is fine *once the bugs are out*. Just dropping it there
> doesn't make the bugs go away.

I think one aspect we might be missing is that /contrib has uses beyond
experimental stuff. For example, I don't believe anyone thinks
/contrib/pgcrypto is going to get more stable than it is now, but it is
in /contrib because it has functionality that is useful to a limited
number of users. I think these /contrib modules fall into a similar
category:

auto_explain/
fuzzystrmatch/
hstore/
isn/
oid2name/
pageinspect/
pg_buffercache/
pg_freespacemap/
pg_standby/
pg_stat_statements/
pgbench/
pgrowlocks/
pgstattuple/
sslinfo/
unaccent/

That is over a third of the /contrib modules. I think pg_migrator falls
into that category too --- it is only of use to people wanting to do a
binary upgrade, and even then, they only run it once. And it is not
something you are going to just fire up like psql. Here is the
pg_migrator README:

http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/~checkout~/pg-migrator/pg_migrator/README?rev=1.72&content-type=text/plain

and the 15-step INSTALL file:

http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/~checkout~/pg-migrator/pg_migrator/INSTALL?rev=1.56&content-type=text/plain

While most of the limitations in previous versions of pg_migrator are
gone, there are still issues with migrating /contrib modules, and there
are many steps to its use.

I think to attain mass usage of pg_migrator, some type of one-click
installer has to be built that can access the operating system and make
the migration process simple, though that is probably beyond what we as
a community are going to do.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

On Sat, May 1, 2010 at 11:42 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> On Sat, May 1, 2010 at 3:32 AM, Magnus Hagander <magnus(at)hagander(dot)net> wrote:
>>> A lot of people are not willing to put stuff labeled "contrib" on
>>> their production boxes.
>>>
>>> And as Tom says, even we *ourselves* acknowledge that things in
>>> /contrib are held to a lower standard. If we put that label on
>>> pg_migrator, it doesn't exactly signal people that this is something
>>> they should use on their critical database.
>
>> Well, I guess that begs the question...  IS this something they should
>> use on their critical database?
>
> Not unless it gets some serious testing during the 9.0 beta cycle.
> Which it surely won't get if it's not included in the core tarball.
>
> I think that having it in contrib for a release cycle or so would be
> exactly the right approach, actually.  Peter's position that it should
> be in /bin is fine *once the bugs are out*.  Just dropping it there
> doesn't make the bugs go away.

I think in the previous iteration of this discussion I had the
impression that you felt that it wasn't really to the point where it
even met our standards for /contrib (although, admittedly, it seems
those are pretty darn low, at least as far as the stuff that's already
in there goes). If I misunderstood or if that that's no longer your
feeling then maybe it makes sense. But I don't think we should do it
at this point unless it's as simple as "check it in and ship it". If
doing this seems likely to make 9.0 take longer to get out the door,
then I think we should wait and do it in 9.1 instead.

...Robert

Robert Haas wrote:
> On Sat, May 1, 2010 at 11:42 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> >> On Sat, May 1, 2010 at 3:32 AM, Magnus Hagander <magnus(at)hagander(dot)net> wrote:
> >>> A lot of people are not willing to put stuff labeled "contrib" on
> >>> their production boxes.
> >>>
> >>> And as Tom says, even we *ourselves* acknowledge that things in
> >>> /contrib are held to a lower standard. If we put that label on
> >>> pg_migrator, it doesn't exactly signal people that this is something
> >>> they should use on their critical database.
> >
> >> Well, I guess that begs the question... ?IS this something they should
> >> use on their critical database?
> >
> > Not unless it gets some serious testing during the 9.0 beta cycle.
> > Which it surely won't get if it's not included in the core tarball.
> >
> > I think that having it in contrib for a release cycle or so would be
> > exactly the right approach, actually. ?Peter's position that it should
> > be in /bin is fine *once the bugs are out*. ?Just dropping it there
> > doesn't make the bugs go away.
>
> I think in the previous iteration of this discussion I had the
> impression that you felt that it wasn't really to the point where it
> even met our standards for /contrib (although, admittedly, it seems
> those are pretty darn low, at least as far as the stuff that's already
> in there goes). If I misunderstood or if that that's no longer your
> feeling then maybe it makes sense. But I don't think we should do it

Well, the original suggestion to move pg_migrator to /contrib was that
it would be easier to do per-major-version distributions of pg_migrator.
However, I have code that is pretty clear about what version it is
dealing with, so I am not worried about that. One concern I had that it
would be harder to make fixes to pg_migrator if it was tied to Postgres
releases. However, I am no longer worried about that because I have not
made changes to pg_migrator for months. (Six months ago I was making
major changes to pg_migrator regularly.)

> at this point unless it's as simple as "check it in and ship it". If
> doing this seems likely to make 9.0 take longer to get out the door,
> then I think we should wait and do it in 9.1 instead.

I can't imagine why pg_migrator would ever delay 9.0 -- it is in
/contrib and everything it needs is already in the backend code. We
could always rip it back out of /contrib if we wanted to, or disable it.
That's the beauty of /contrib --- it is isolated from the rest of the
system.

I think the big question is whether we want to provide a binary upgrade
facility for Postgres. If so, pg_migrator is the only facility
currently available, and I can't imagine another option appearing. I
would love for pg_migrator to be easier to use, but I can't figure out
how that can happen without an external OS-specific installer.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

Bruce Momjian <bruce(at)momjian(dot)us> writes:
> While most of the limitations in previous versions of pg_migrator are
> gone, there are still issues with migrating /contrib modules, and there
> are many steps to its use.

> I think to attain mass usage of pg_migrator, some type of one-click
> installer has to be built that can access the operating system and make
> the migration process simple, though that is probably beyond what we as
> a community are going to do.

While the above are true statements, IMO the real gating factor right now
for pg_migrator is that people don't know whether they can trust it.
It won't get over that basic hump without a lot more real-world testing;
and as long as it's a separately distributed project I don't think it'll
get the necessary level of testing. That's why I feel it needs some
time in contrib --- and why I don't have a warm fuzzy feeling about
Peter's proposal to push it directly into the core project.

As for the "one click" aspect, I think that that's largely on the heads
of packagers to provide some convenient method of using it. For the RPM
packages, I envision eventually having a "postgresql-migrate" package
that contains pg_migrator, a copy of the version-N-minus-1 postmaster,
and some sort of frontend script. Install, run the script, remove.
But we're a long way from that yet.

regards, tom lane

> I think the big question is whether we want to provide a binary upgrade
> facility for Postgres. If so, pg_migrator is the only facility
> currently available, and I can't imagine another option appearing. I
> would love for pg_migrator to be easier to use, but I can't figure out
> how that can happen without an external OS-specific installer.

FYI, the length of this thread is kind of why I waited for beta to talk
about pg_migrator. pg_migrator is really external to the backend code
and now we have the leisure time to talk about it, understand what
/contrib is useful for, and make a decision.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> On Sat, May 1, 2010 at 11:42 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> I think that having it in contrib for a release cycle or so would be
>> exactly the right approach, actually. Peter's position that it should
>> be in /bin is fine *once the bugs are out*. Just dropping it there
>> doesn't make the bugs go away.

> I think in the previous iteration of this discussion I had the
> impression that you felt that it wasn't really to the point where it
> even met our standards for /contrib (although, admittedly, it seems
> those are pretty darn low, at least as far as the stuff that's already
> in there goes). If I misunderstood or if that that's no longer your
> feeling then maybe it makes sense.

In the 8.3->8.4 cycle I did think it was pretty half-baked. We've
stomped many of the worst limitations since then, so I think that for
8.4->9.0 it might be a credible solution. We won't really know unless
we try.

> But I don't think we should do it
> at this point unless it's as simple as "check it in and ship it". If
> doing this seems likely to make 9.0 take longer to get out the door,
> then I think we should wait and do it in 9.1 instead.

Agreed, we're not holding up 9.0 for it. I think the main bit of work
that would be needed to put it into contrib would be to SGML-ize the
docs. Don't know if Bruce has got the time to get that done.

regards, tom lane

Tom Lane wrote:
> Bruce Momjian <bruce(at)momjian(dot)us> writes:
> > While most of the limitations in previous versions of pg_migrator are
> > gone, there are still issues with migrating /contrib modules, and there
> > are many steps to its use.
>
> > I think to attain mass usage of pg_migrator, some type of one-click
> > installer has to be built that can access the operating system and make
> > the migration process simple, though that is probably beyond what we as
> > a community are going to do.
>
> While the above are true statements, IMO the real gating factor right now
> for pg_migrator is that people don't know whether they can trust it.
> It won't get over that basic hump without a lot more real-world testing;
> and as long as it's a separately distributed project I don't think it'll
> get the necessary level of testing. That's why I feel it needs some

Agreed.

> time in contrib --- and why I don't have a warm fuzzy feeling about
> Peter's proposal to push it directly into the core project.

I am unclear why it would be in /bin if it requires 15 steps to run and
is run only once by only some users. It seems natural for /contrib,
like pgcrypto.

> As for the "one click" aspect, I think that that's largely on the heads
> of packagers to provide some convenient method of using it. For the RPM
> packages, I envision eventually having a "postgresql-migrate" package
> that contains pg_migrator, a copy of the version-N-minus-1 postmaster,
> and some sort of frontend script. Install, run the script, remove.
> But we're a long way from that yet.

Yes, that is what is needed eventually.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

Tom Lane wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> > On Sat, May 1, 2010 at 11:42 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> >> I think that having it in contrib for a release cycle or so would be
> >> exactly the right approach, actually. Peter's position that it should
> >> be in /bin is fine *once the bugs are out*. Just dropping it there
> >> doesn't make the bugs go away.
>
> > I think in the previous iteration of this discussion I had the
> > impression that you felt that it wasn't really to the point where it
> > even met our standards for /contrib (although, admittedly, it seems
> > those are pretty darn low, at least as far as the stuff that's already
> > in there goes). If I misunderstood or if that that's no longer your
> > feeling then maybe it makes sense.
>
> In the 8.3->8.4 cycle I did think it was pretty half-baked. We've
> stomped many of the worst limitations since then, so I think that for
> 8.4->9.0 it might be a credible solution. We won't really know unless
> we try.

True. I do see this as a much-requested feature (like built-in
replication).

> > But I don't think we should do it
> > at this point unless it's as simple as "check it in and ship it". If
> > doing this seems likely to make 9.0 take longer to get out the door,
> > then I think we should wait and do it in 9.1 instead.
>
> Agreed, we're not holding up 9.0 for it. I think the main bit of work
> that would be needed to put it into contrib would be to SGML-ize the
> docs. Don't know if Bruce has got the time to get that done.

Creating the SGML docs is trivial, especially compared to the 9.0
release notes SGML. ;-) It will take only an hour --- I am basically
going to merge the README and the INSTALL file, remove mentions about
migrating to < 9.0, and add SGML markup. I labored on README and the
INSTALL files for a long time and can't figure out how to improve them.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

> > Agreed, we're not holding up 9.0 for it. I think the main bit of work
> > that would be needed to put it into contrib would be to SGML-ize the
> > docs. Don't know if Bruce has got the time to get that done.
>
> Creating the SGML docs is trivial, especially compared to the 9.0
> release notes SGML. ;-) It will take only an hour --- I am basically
> going to merge the README and the INSTALL file, remove mentions about
> migrating to < 9.0, and add SGML markup. I labored on README and the
> INSTALL files for a long time and can't figure out how to improve them.

Oh, and I will remove the C code that was used to migrate _to_ pre-9.0
databases. People can use the pgfoundry version for such cases. I have
specifically not created a pgfoundry release of pg_migrator that
migrates to 9.0. (It worked for the 8.5 numbering.)

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

2010/5/1 Bruce Momjian <bruce(at)momjian(dot)us>:
> Tom Lane wrote:
>> Bruce Momjian <bruce(at)momjian(dot)us> writes:
>> > While most of the limitations in previous versions of pg_migrator are
>> > gone, there are still issues with migrating /contrib modules, and there
>> > are many steps to its use.
>>
>> > I think to attain mass usage of pg_migrator, some type of one-click
>> > installer has to be built that can access the operating system and make
>> > the migration process simple, though that is probably beyond what we as
>> > a community are going to do.
>>
>> While the above are true statements, IMO the real gating factor right now
>> for pg_migrator is that people don't know whether they can trust it.
>> It won't get over that basic hump without a lot more real-world testing;
>> and as long as it's a separately distributed project I don't think it'll
>> get the necessary level of testing.  That's why I feel it needs some
>
> Agreed.
>
>> time in contrib --- and why I don't have a warm fuzzy feeling about
>> Peter's proposal to push it directly into the core project.
>
> I am unclear why it would be in /bin if it requires 15 steps to run and
> is run only once by only some users.  It seems natural for /contrib,
> like pgcrypto.

Do we already have a process to upgrade postgres + HotStandby
(9.0->9.1 for example) ?

>
>> As for the "one click" aspect, I think that that's largely on the heads
>> of packagers to provide some convenient method of using it.  For the RPM
>> packages, I envision eventually having a "postgresql-migrate" package
>> that contains pg_migrator, a copy of the version-N-minus-1 postmaster,
>> and some sort of frontend script.  Install, run the script, remove.
>> But we're a long way from that yet.
>
> Yes, that is what is needed eventually.
>
> --
>  Bruce Momjian  <bruce(at)momjian(dot)us>        http://momjian.us
>  EnterpriseDB                             http://enterprisedb.com
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

--
Cédric Villemain

Cdric Villemain wrote:
> 2010/5/1 Bruce Momjian <bruce(at)momjian(dot)us>:
> > Tom Lane wrote:
> >> Bruce Momjian <bruce(at)momjian(dot)us> writes:
> >> > While most of the limitations in previous versions of pg_migrator are
> >> > gone, there are still issues with migrating /contrib modules, and there
> >> > are many steps to its use.
> >>
> >> > I think to attain mass usage of pg_migrator, some type of one-click
> >> > installer has to be built that can access the operating system and make
> >> > the migration process simple, though that is probably beyond what we as
> >> > a community are going to do.
> >>
> >> While the above are true statements, IMO the real gating factor right now
> >> for pg_migrator is that people don't know whether they can trust it.
> >> It won't get over that basic hump without a lot more real-world testing;
> >> and as long as it's a separately distributed project I don't think it'll
> >> get the necessary level of testing. ?That's why I feel it needs some
> >
> > Agreed.
> >
> >> time in contrib --- and why I don't have a warm fuzzy feeling about
> >> Peter's proposal to push it directly into the core project.
> >
> > I am unclear why it would be in /bin if it requires 15 steps to run and
> > is run only once by only some users. ?It seems natural for /contrib,
> > like pgcrypto.
>
> Do we already have a process to upgrade postgres + HotStandby
> (9.0->9.1 for example) ?

No because when you create the file system backup and restore it to the
new server, it is still running the same Postgres major version. There
is no facility to handle major-version-changed master/slave setups.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

On Sat, May 1, 2010 at 5:46 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
>> > Agreed, we're not holding up 9.0 for it.  I think the main bit of work
>> > that would be needed to put it into contrib would be to SGML-ize the
>> > docs.  Don't know if Bruce has got the time to get that done.
>>
>> Creating the SGML docs is trivial, especially compared to the 9.0
>> release notes SGML.  ;-)  It will take only an hour --- I am basically
>> going to merge the README and the INSTALL file, remove mentions about
>> migrating to < 9.0, and add SGML markup.  I labored on README and the
>> INSTALL files for a long time and can't figure out how to improve them.
>
> Oh, and I will remove the C code that was used to migrate _to_ pre-9.0
> databases.  People can use the pgfoundry version for such cases. I have
> specifically not created a pgfoundry release of pg_migrator that
> migrates to 9.0.  (It worked for the 8.5 numbering.)

I wonder if this is just going to lead to us maintaining two versions
of pg_migrator, which wouldn't be awesome. I don't think it's going
to be practical to retain all the migration code for every pair of
versions forever, but I'm reluctant to start changing things just
because we're sucking the thing into our main tree. Especially things
that sound suspiciously like features.

...Robert

Robert Haas wrote:
> I don't think it's going
> to be practical to retain all the migration code for every pair of
> versions forever,
>

I thought the idea was just to support migration from version N to
version N+1.

cheers

andrew

On Sat, May 1, 2010 at 11:31 PM, Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:
> Robert Haas wrote:
>>
>>  I don't think it's going
>> to be practical to retain all the migration code for every pair of
>> versions forever,
>
> I thought the idea was just to support migration from version N to version
> N+1.

I think that would be "good enough". But right now we can do better.
No reason to rip it out, is there?

...Robert

Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
> Robert Haas wrote:
>> I don't think it's going
>> to be practical to retain all the migration code for every pair of
>> versions forever,

> I thought the idea was just to support migration from version N to
> version N+1.

Yeah. I think trying to do more than that is just going to make things
messy. For example, we added features to pg_dump and the core server
since 8.4 to help pg_migrator do its thing. Trying to make the same
pg_migrator code support cases with and without those features available
is going to complicate the code, not to mention the documentation,
enormously.

To the extent that future bug fixes are relevant to multiple versions
of pg_migrator, we could just apply them to multiple branches, same as
we manage such fixes for the core code. I don't see that trying to
have a single version of pg_migrator is going to make things easier
anywhere.

regards, tom lane

On Sat, May 1, 2010 at 11:38 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> To the extent that future bug fixes are relevant to multiple versions
> of pg_migrator, we could just apply them to multiple branches, same as
> we manage such fixes for the core code.  I don't see that trying to
> have a single version of pg_migrator is going to make things easier
> anywhere.

That might be OK if by "multiple versions" we mean "multiple branches
within the repository". But I'm not so sure about having a version in
the core repository and another version on pgfoundry, or something of
that sort.

...Robert

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> On Sat, May 1, 2010 at 11:38 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> To the extent that future bug fixes are relevant to multiple versions
>> of pg_migrator, we could just apply them to multiple branches, same as
>> we manage such fixes for the core code. I don't see that trying to
>> have a single version of pg_migrator is going to make things easier
>> anywhere.

> That might be OK if by "multiple versions" we mean "multiple branches
> within the repository". But I'm not so sure about having a version in
> the core repository and another version on pgfoundry, or something of
> that sort.

True. If we push it into contrib as of 9.0, I think this won't be too
bad, because the pgfoundry version will only be covering 8.3->8.4.
Given all the limitations in that version, it's not going to be too
interesting and will probably die on the vine pretty quickly. If we
hold off merging it till 9.1, though, the pgfoundry version probably
will be useful and in need of maintenance for a while to come.

regards, tom lane

On lör, 2010-05-01 at 17:26 -0400, Bruce Momjian wrote:
> I am unclear why it would be in /bin if it requires 15 steps to run
> and is run only once by only some users. It seems natural
> for /contrib, like pgcrypto.

Well, pg_resetxlog is also rarely run by most users. It started in
contrib but was later moved to bin in order to show that it is fully
supported.

Also, I think the 15 steps are a bit inflated. Several of those steps
are about building and installing various pieces of software. If you
count that way, using PostgreSQL itself might also require about 12
steps. In a packaged environment that allows side-by-side installation
of major versions (such as Debian or Windows), you need about 4 or 5
manual steps, and with a small script layer you need only 1 or 0.

Robert Haas wrote:
> On Sat, May 1, 2010 at 5:46 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> >> > Agreed, we're not holding up 9.0 for it. ?I think the main bit of work
> >> > that would be needed to put it into contrib would be to SGML-ize the
> >> > docs. ?Don't know if Bruce has got the time to get that done.
> >>
> >> Creating the SGML docs is trivial, especially compared to the 9.0
> >> release notes SGML. ?;-) ?It will take only an hour --- I am basically
> >> going to merge the README and the INSTALL file, remove mentions about
> >> migrating to < 9.0, and add SGML markup. ?I labored on README and the
> >> INSTALL files for a long time and can't figure out how to improve them.
> >
> > Oh, and I will remove the C code that was used to migrate _to_ pre-9.0
> > databases. ?People can use the pgfoundry version for such cases. I have
> > specifically not created a pgfoundry release of pg_migrator that
> > migrates to 9.0. ?(It worked for the 8.5 numbering.)
>
> I wonder if this is just going to lead to us maintaining two versions
> of pg_migrator, which wouldn't be awesome. I don't think it's going
> to be practical to retain all the migration code for every pair of
> versions forever, but I'm reluctant to start changing things just
> because we're sucking the thing into our main tree. Especially things
> that sound suspiciously like features.

Tom's idea basically was that each version of pg_migrator would only
support it current major version as a _target_. We would have to
backpatch fixes to pg_migrator to previous major versions, and to
pgfoundry if necessary.

However, there isn't much code churn in pg_migrator anymore (as there
was months before), so we should be OK. We already do such backpatching
for all our other core code.

I can easily keep all the code in each version of pg_migrator. However,
pg_dump only supports loads into the _target_ major version, just like
pg_migrator would do.

I am unclear on which direction to take, but both are easy for me.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

Andrew Dunstan wrote:
>
>
> Robert Haas wrote:
> > I don't think it's going
> > to be practical to retain all the migration code for every pair of
> > versions forever,
> >
>
> I thought the idea was just to support migration from version N to
> version N+1.

Oh, I will also support many older _source_ versions, like 8.3 and 8.4.
The question is whether a pg_migrator in /contrib should support
multiple _target_ versions.

(Again, I assumed this discussion would be necessary, and is best done
during beta.)

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

Bruce Momjian <bruce(at)momjian(dot)us> writes:
> Andrew Dunstan wrote:
>> I thought the idea was just to support migration from version N to
>> version N+1.

> Oh, I will also support many older _source_ versions, like 8.3 and 8.4.

Really? Nobody else has bought into that, and it's not only pg_migrator
that would have to go out of its way to support such cases. You're
talking about cross-multi-version compatibility of on-disk formats too.

regards, tom lane

Robert Haas wrote:
> On Sat, May 1, 2010 at 11:31 PM, Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:
> > Robert Haas wrote:
> >>
> >> ?I don't think it's going
> >> to be practical to retain all the migration code for every pair of
> >> versions forever,
> >
> > I thought the idea was just to support migration from version N to version
> > N+1.
>
> I think that would be "good enough". But right now we can do better.
> No reason to rip it out, is there?

Again, we are talking about removing _target_ support for 8.4 in
pg_migrator 9.0. It is hard to see why someone would use 9.0 /contrib
pg_migrator to migrate from 8.3 to 8.4.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

Tom Lane wrote:
> Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
> > Robert Haas wrote:
> >> I don't think it's going
> >> to be practical to retain all the migration code for every pair of
> >> versions forever,
>
> > I thought the idea was just to support migration from version N to
> > version N+1.
>
> Yeah. I think trying to do more than that is just going to make things
> messy. For example, we added features to pg_dump and the core server
> since 8.4 to help pg_migrator do its thing. Trying to make the same
> pg_migrator code support cases with and without those features available
> is going to complicate the code, not to mention the documentation,
> enormously.

As a summary, let me list the migrations pg_migrator supports:

8.3 -> 8.4
8.4 -> 9.0
8.3 -> 9.0

Surprisingly, it is 8.3 -> 8.4 that has the most restrictions because it
doesn't have access to the features we added in Postgres 9.0.

Tom is right that the code could be cleaned up if we removed 8.3 -> 8.4,
but more importantly the documentation would be clearer.

> To the extent that future bug fixes are relevant to multiple versions
> of pg_migrator, we could just apply them to multiple branches, same as
> we manage such fixes for the core code. I don't see that trying to
> have a single version of pg_migrator is going to make things easier
> anywhere.

Yea, that is probably right. We can enforce in pg_migrator that you can
only migrate to the matching major version you are using in /contrib.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

Tom Lane wrote:
> Bruce Momjian <bruce(at)momjian(dot)us> writes:
> > Andrew Dunstan wrote:
> >> I thought the idea was just to support migration from version N to
> >> version N+1.
>
> > Oh, I will also support many older _source_ versions, like 8.3 and 8.4.
>
> Really? Nobody else has bought into that, and it's not only pg_migrator
> that would have to go out of its way to support such cases. You're
> talking about cross-multi-version compatibility of on-disk formats too.

Well, it works. I have a test suite that I run regularly. Because of
the way pg_migrator works it is pretty painless to support multiple
_source_ major versions.

The binary format issue is relevant, but until we have a way to remove
the old binary format, I don't see much value in supporting just one
source version. For example, we don't have any system now to remove the
HEAP_MOVED_OFF and HEAP_MOVED_IN heap bits so effectively major versions
have to support them forever. Now, if we develop a system where a
version would _remove_ the old data format, we would then specify that
pg_migrator can only migrate _from_ one major version, and you would
have to run a script to remove the old data format. For example,
migrating from 9.0 to 9.2 would requiring migrating from 9.0 to 9.1 with
pg_migrator, updating the data pages to 9.1 format, then using
pg_migrator again to migrate from 9.1 to 9.2, but of course, we are not
there yet.

My guess is that when that happens we would just document/enforce it in
pg_migrator, but I don't see why we would arbitrarily restrict
pg_migrator at this time.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

On May 2, 2010, at 12:01 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> Tom Lane wrote:
>> Bruce Momjian <bruce(at)momjian(dot)us> writes:
>>> Andrew Dunstan wrote:
>>>> I thought the idea was just to support migration from version N to
>>>> version N+1.
>>
>>> Oh, I will also support many older _source_ versions, like 8.3 and
>>> 8.4.
>>
>> Really? Nobody else has bought into that, and it's not only
>> pg_migrator
>> that would have to go out of its way to support such cases. You're
>> talking about cross-multi-version compatibility of on-disk formats
>> too.
>
> Well, it works. I have a test suite that I run regularly. Because of
> the way pg_migrator works it is pretty painless to support multiple
> _source_ major versions.
>
> The binary format issue is relevant, but until we have a way to remove
> the old binary format, I don't see much value in supporting just one
> source version. For example, we don't have any system now to remove
> the
> HEAP_MOVED_OFF and HEAP_MOVED_IN heap bits so effectively major
> versions
> have to support them forever. Now, if we develop a system where a
> version would _remove_ the old data format, we would then specify that
> pg_migrator can only migrate _from_ one major version, and you would
> have to run a script to remove the old data format. For example,
> migrating from 9.0 to 9.2 would requiring migrating from 9.0 to 9.1
> with
> pg_migrator, updating the data pages to 9.1 format, then using
> pg_migrator again to migrate from 9.1 to 9.2, but of course, we are
> not
> there yet.
>
> My guess is that when that happens we would just document/enforce it
> in
> pg_migrator, but I don't see why we would arbitrarily restrict
> pg_migrator at this time.

Yeah. It's not uncommon to want to upgrade by more than one release at
a time, and I haven't heard any reason why we should arbitrarily
refuse to support that. Of course we may need to do that eventually
for some specific reason, but it seems like we should only consider
imposing such a restriction in the face of a tangible need.

...Robert

Robert Haas wrote:
> > My guess is that when that happens we would just document/enforce it
> > in
> > pg_migrator, but I don't see why we would arbitrarily restrict
> > pg_migrator at this time.
>
> Yeah. It's not uncommon to want to upgrade by more than one release at
> a time, and I haven't heard any reason why we should arbitrarily
> refuse to support that. Of course we may need to do that eventually
> for some specific reason, but it seems like we should only consider
> imposing such a restriction in the face of a tangible need.

Yea, we will need it one day, and if pg_migrator was more tied to system
table changes and such, it would be smart to do it now, but if you look
at the C code you will see that most of the effort is related to
compatibility with the _target_ major version, not the _source_ major
version, and by definition, the source major version never changes as we
release new major versions. (Remember, pg_dump already does the heavy
lifting of moving our database schema to the new major version.)

A lot of understanding pg_migrator is understanding the source/target
matrix of compatibility --- something we as a community have not thought
about very much at this level.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> Yeah. It's not uncommon to want to upgrade by more than one release at
> a time, and I haven't heard any reason why we should arbitrarily
> refuse to support that. Of course we may need to do that eventually
> for some specific reason, but it seems like we should only consider
> imposing such a restriction in the face of a tangible need.

I wasn't suggesting that we should arbitrarily refuse to support cases
that would work otherwise. What I *was* saying is that the community
has not bought into doing any extra work to support
cross-multiple-version migrations, and I don't think it will do so.
It would be a mistake to give users the impression that such cases can
be expected to work in future. In particular we should not provide a
documentation table that is set up to give the impression that
multi-version upgrades are going to be supported. The docs should be
written to make it clear that one-version-at-a-time is the normal case,
and any cases where you can take a shortcut should be documented as
exceptions.

regards, tom lane

Tom Lane wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> > Yeah. It's not uncommon to want to upgrade by more than one release at
> > a time, and I haven't heard any reason why we should arbitrarily
> > refuse to support that. Of course we may need to do that eventually
> > for some specific reason, but it seems like we should only consider
> > imposing such a restriction in the face of a tangible need.
>
> I wasn't suggesting that we should arbitrarily refuse to support cases
> that would work otherwise. What I *was* saying is that the community
> has not bought into doing any extra work to support
> cross-multiple-version migrations, and I don't think it will do so.
> It would be a mistake to give users the impression that such cases can
> be expected to work in future. In particular we should not provide a
> documentation table that is set up to give the impression that
> multi-version upgrades are going to be supported. The docs should be
> written to make it clear that one-version-at-a-time is the normal case,
> and any cases where you can take a shortcut should be documented as
> exceptions.

Well, that is going to make the documentation more complicated than it
already is. Why mention a process in 9.0 that no one needs to use? I
am not writing the docs for some hypothetical release, but for 9.0.
When we have some restriction, we can document that.

My guess is that 9.2 will be able to upgrade from 8.3, 8.4, 9.0, and
9.1, but that going to 9.5 will require you to go to 9.2 first, run some
script, then upgrade to 9.5; again all hypothetical. I think we can
require people using pg_migrator to read matching documentation for that
major version of pg_migrator, and pg_migrator will enforce any
restrictions we come up with in the future. For example, I assume there
will be some major version of Postgres where pg_migrator will not work
at all.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

Peter Eisentraut wrote:
> On l?r, 2010-05-01 at 17:26 -0400, Bruce Momjian wrote:
> > I am unclear why it would be in /bin if it requires 15 steps to run
> > and is run only once by only some users. It seems natural
> > for /contrib, like pgcrypto.
>
> Well, pg_resetxlog is also rarely run by most users. It started in
> contrib but was later moved to bin in order to show that it is fully
> supported.

Yea, it is like pg_resetxlog. The only counter-argument I can think of
is that we decided that everyone should have the pg_resetxlog binary
available, and not have to scramble around to install it in case of a
problem, but I will admit that is a thin argument.

> Also, I think the 15 steps are a bit inflated. Several of those steps
> are about building and installing various pieces of software. If you
> count that way, using PostgreSQL itself might also require about 12
> steps. In a packaged environment that allows side-by-side installation
> of major versions (such as Debian or Windows), you need about 4 or 5
> manual steps, and with a small script layer you need only 1 or 0.

Well, that is good news.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

Bruce Momjian wrote:
> Well, that is going to make the documentation more complicated than it
> already is. Why mention a process in 9.0 that no one needs to use? I
> am not writing the docs for some hypothetical release, but for 9.0.
> When we have some restriction, we can document that.
>
> My guess is that 9.2 will be able to upgrade from 8.3, 8.4, 9.0, and
> 9.1, but that going to 9.5 will require you to go to 9.2 first, run some
> script, then upgrade to 9.5; again all hypothetical. I think we can
> require people using pg_migrator to read matching documentation for that
> major version of pg_migrator, and pg_migrator will enforce any
> restrictions we come up with in the future. For example, I assume there
> will be some major version of Postgres where pg_migrator will not work
> at all.

I do think we are going to have to mention new pg_migrator restrictions
in the major release notes for future versions.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

Bruce Momjian wrote:
> For example, I assume there
> will be some major version of Postgres where pg_migrator will not work
> at all.
>
>

We need to be thinking more now about such a contingency. Postgres use
in very large installations is now at such a level that requiring a
pg_dump/pg_restore is really not an option for many users. If
pg_migrator is not always going to work then we need to be addressing
that now, or else it is at best a stop-gap. ISTM some sort of page
layout versioning scheme might be at least part of what we'll need in
the medium term.

cheers

andrew

Andrew Dunstan wrote:
>
>
> Bruce Momjian wrote:
> > For example, I assume there
> > will be some major version of Postgres where pg_migrator will not work
> > at all.
> >
> >
>
> We need to be thinking more now about such a contingency. Postgres use
> in very large installations is now at such a level that requiring a
> pg_dump/pg_restore is really not an option for many users. If
> pg_migrator is not always going to work then we need to be addressing
> that now, or else it is at best a stop-gap. ISTM some sort of page
> layout versioning scheme might be at least part of what we'll need in
> the medium term.

Yes, my bet is that when we do need to change the page format, we will
either use a conversion-on-read process or some external tool to do the
conversion. pg_migrator does have code to do page conversions but it
has never been used. If you are using pg_migrator in copy mode, we
might be able to do page conversion during the copy, but for link mode,
that is not possible.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

<crazy hat on --- but do I ever quit it?>

Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
> We need to be thinking more now about such a contingency. Postgres use in
> very large installations is now at such a level that requiring a
> pg_dump/pg_restore is really not an option for many users. If pg_migrator is
> not always going to work then we need to be addressing that now, or else it
> is at best a stop-gap. ISTM some sort of page layout versioning scheme might
> be at least part of what we'll need in the medium term.

Would it be on the same complexity level to support recovering WALs from
previous version? On the code maintenance alone it sounds bad enough,
but apart from that?

The idea of course would be then to add an Hot-Standby server running
next PostgreSQL version and fed from current production server. The base
backup would have to either be processed by pg_migrator or we'd have to
open the possibility of starting a slave from a pg_dump, which has been
talked about already. The change here would be that this initial step
would not be done as part of the maintenance window.

Now you tell me how awful this idea really is :)

Regards,
--
dim

On Sun, May 2, 2010 at 3:45 PM, Dimitri Fontaine <dfontaine(at)hi-media(dot)com> wrote:
> Now you tell me how awful this idea really is :)

I'm not sure I can count that high. :-)

...Robert

Bruce Momjian wrote:
> As a summary, let me list the migrations pg_migrator supports:
> 8.3 -> 8.4
> 8.4 -> 9.0
> 8.3 -> 9.0
> Surprisingly, it is 8.3 -> 8.4 that has the most restrictions because it
> doesn't have access to the features we added in Postgres 9.0.
> Tom is right that the code could be cleaned up if we removed 8.3 -> 8.4,
> but more importantly the documentation would be clearer.
>

I think it's extremely valuable that either 8.3 or 8.4 can be upgraded
to 9.0. But let's face it: in the long run, the number of people who
are going to use pg_migrator for a 8.3->8.4 migration, but that's
haven't already done so, is a small user base. The feature set
improvement in 8.4 had a lot of great stuff, but few that were
compelling from a "now I can do something completely impossible before!"
standpoint. As was noted recently during the "Native DB replication for
PG" discussion over on pgsql-general last week, there are plenty of
people happily running a stable 8.3 who just ignore 8.4 altogether for
that reason.

The replication features in 9.0 are compelling in that way though, and I
expect to see plenty of upgrades to that version from both 8.3 and 8.4
installs. If that works fine right now, I would prefer to see that
documented as a special case two-versions at once situation that people
shouldn't necessarily expect in the future, but certainly valuable to
keep going if the maintenance burden isn't so bad.

Balancing out development reality with the ideal situation from the
perspective of [potential|current] customers that I deal with every day,
what I would prefer to see here is:

1) Commit a streamlined pg_migrator that only handles conversions with
9.0 as a target into contrib, and ship it with 9.0. Like Bruce, I had
presumed that the discussion about whether that was going to happen
would happen in parallel with beta (read: right now), rather than its
already being too late to even consider. I think it's completely
bizarre from an advocacy standpoint to even consider that you wouldn't
ship such a tool with the core database, now that it's been around for
long enough to have a positive track record.

2) Deprecate the pg_migrator hosted on pg_foundry as only being
recommended for limited 8.3->8.4 upgrades. Essentially stop active
development on the version there, and focus on the one in contrib/
instead. People who want an improved 8.3->8.4 tool can always contract
with someone to backport fixes needed for their particular use case. I
think we're past the point where the community at large (meaning:
mainly Bruce right now) should be expected to do that, now that 9.0 is
coming out, so long as 8.3 to 9.0 conversions are available too. I
can't imagine suggesting to anyone that they upgrade in-place from 8.3
to 8.4 right now. Everybody I talk to who isn't already on 8.4 is
delaying upgrades in anticipation of 9.0 later this year or early next.

My main issues with this project continuing to be hosted in pgfoundry are:

1) Perceived lack of confidence and/or legitimacy for it as an in-place
upgrade solution, which would be a terrible PR move. When people ask
about in-place upgrades and I tell them "there's a tool you can download
for that", they look at me in terror and ask if I'm serious that it
isn't just included in the core code. The improvement between answering
that way and saying "yes, the tool for 8.3 and 8.4 is included with the
core distribution", from the perspective of selling people on adopting
PostgreSQL, cannot be overstated.

2) Anyone who looks at pgfoundry for more than a few minutes walks away
covered with the scent of dead projects. One reason for that is that
related to how painful it is to develop there. I don't want to reignite
a full anti-pgfoundry discussion here. Suffice it to say that there are
many of us who just can't bear working with CVS anymore who have just
given up on doing anything useful to projects hosted there. Something
that's in core (and therefore included in the git conversion already
being published) is much easier to work with and submit patches
against. I'm already dumping git clones of the PG repo on every system
I do serious work on. If each of those were then capable of generating
pg_migrator patches I could submit, I would actually do that each time I
use the tool for an upgrade and notice how it could be improved.

--
Greg Smith 2ndQuadrant US Baltimore, MD
PostgreSQL Training, Services and Support
greg(at)2ndQuadrant(dot)com www.2ndQuadrant.us

On Mon, 2010-05-03 at 16:12 -0400, Robert Haas wrote:
> On Sun, May 2, 2010 at 3:45 PM, Dimitri Fontaine <dfontaine(at)hi-media(dot)com> wrote:
> > Now you tell me how awful this idea really is :)
>
> I'm not sure I can count that high. :-)

You don't have to...

NaN

Joshua D. Drake

>
> ...Robert
>

--
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564
Consulting, Training, Support, Custom Development, Engineering

Robert Haas wrote:
> On Sun, May 2, 2010 at 3:45 PM, Dimitri Fontaine <dfontaine(at)hi-media(dot)com> wrote:
> > Now you tell me how awful this idea really is :)
>
> I'm not sure I can count that high. :-)

While I can't improve on Robert's reply, I can supply a PDF about how
pg_migrator works:

http://momjian.us/main/presentations/technical.html#pg_migrator

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

Greg Smith wrote:
> Bruce Momjian wrote:
> > As a summary, let me list the migrations pg_migrator supports:
> > 8.3 -> 8.4
> > 8.4 -> 9.0
> > 8.3 -> 9.0
> > Surprisingly, it is 8.3 -> 8.4 that has the most restrictions because it
> > doesn't have access to the features we added in Postgres 9.0.
> > Tom is right that the code could be cleaned up if we removed 8.3 -> 8.4,
> > but more importantly the documentation would be clearer.
> >
>
> I think it's extremely valuable that either 8.3 or 8.4 can be upgraded
> to 9.0. But let's face it: in the long run, the number of people who
> are going to use pg_migrator for a 8.3->8.4 migration, but that's
> haven't already done so, is a small user base. The feature set
> improvement in 8.4 had a lot of great stuff, but few that were
> compelling from a "now I can do something completely impossible before!"
> standpoint. As was noted recently during the "Native DB replication for
> PG" discussion over on pgsql-general last week, there are plenty of
> people happily running a stable 8.3 who just ignore 8.4 altogether for
> that reason.
>
> The replication features in 9.0 are compelling in that way though, and I
> expect to see plenty of upgrades to that version from both 8.3 and 8.4
> installs. If that works fine right now, I would prefer to see that
> documented as a special case two-versions at once situation that people
> shouldn't necessarily expect in the future, but certainly valuable to
> keep going if the maintenance burden isn't so bad.

Until we have some kind of "delete the incompatibile format" step in
major releases, my bet is that pg_migrator will support many previous
major versions, or not work at all. It is hard to see why it would work
for some major versions and not others given our current code.

> 2) Deprecate the pg_migrator hosted on pg_foundry as only being
> recommended for limited 8.3->8.4 upgrades. Essentially stop active
> development on the version there, and focus on the one in contrib/
> instead. People who want an improved 8.3->8.4 tool can always contract
> with someone to backport fixes needed for their particular use case. I
> think we're past the point where the community at large (meaning:
> mainly Bruce right now) should be expected to do that, now that 9.0 is
> coming out, so long as 8.3 to 9.0 conversions are available too. I
> can't imagine suggesting to anyone that they upgrade in-place from 8.3
> to 8.4 right now. Everybody I talk to who isn't already on 8.4 is
> delaying upgrades in anticipation of 9.0 later this year or early next.

Assuming pg_migrator moves to /contrib, I don't plan on doing much to
improve the pgfoundry version unless people find specific bugs. I have
not released a 9.0-compatible version there for that reason.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

So what was the conclusion here? Is pg_migrator going to be in contrib
for beta2 or 3, after cleaning it up?

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

On 2010-05-03 23:09, Bruce Momjian wrote:
> Robert Haas wrote:
>
>> On Sun, May 2, 2010 at 3:45 PM, Dimitri Fontaine<dfontaine(at)hi-media(dot)com> wrote:
>>
>>> Now you tell me how awful this idea really is :)
>>>
>> I'm not sure I can count that high. :-)
>>
> While I can't improve on Robert's reply, I can supply a PDF about how
> pg_migrator works:
>
> http://momjian.us/main/presentations/technical.html#pg_migrator
>
>
There is a huge amount of users to whom pg_migrator is "at least"
a big a feature as HS+SR is.

Last dump/restore was a 24 hours process in one of our installations.
I think it was due to in-efficiency in handling BYTEA types in the
process (but not sure).

But I'm one of the few guys who seem to have an infinite amount of
time for reading on mailing lists, but without my knowledge from
reading this list I would never have run pg_migrator on my production
data if I had to pick it from pg_foundry.

Just my 0.25€

Jesper
--
Jesper

Alvaro Herrera wrote:
>
> So what was the conclusion here? Is pg_migrator going to be in contrib
> for beta2 or 3, after cleaning it up?

Thanks for asking. :-) I can add pg_migrator to contrib by the end of
next week, so it will be in beta2. I will remove 8.4 as a migration
target, which will allow the removal of some C code and documentation
warnings. Unless I hear otherwise, I will start on it in the next few
days. Total work will be < 8 hours, including testing.

One outstanding question is whether we want to rename pg_migrator to
something clearer, like pg_upgrade or pg_binary_upgrade. (pg_upgrade
was the original name for this migration method in the 1998.) I am
slightly concerned that the "migration" word is too associated with
cross-database-product migration. (There are no mentions of
"pg_migrator" in our CVS now, except for an 8.4 release note item
mention when pg_dump --binary-upgrade was added.)

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

Jesper Krogh wrote:
> On 2010-05-03 23:09, Bruce Momjian wrote:
> > Robert Haas wrote:
> >
> >> On Sun, May 2, 2010 at 3:45 PM, Dimitri Fontaine<dfontaine(at)hi-media(dot)com> wrote:
> >>
> >>> Now you tell me how awful this idea really is :)
> >>>
> >> I'm not sure I can count that high. :-)
> >>
> > While I can't improve on Robert's reply, I can supply a PDF about how
> > pg_migrator works:
> >
> > http://momjian.us/main/presentations/technical.html#pg_migrator
> >
> >
> There is a huge amount of users to whom pg_migrator is "at least"
> a big a feature as HS+SR is.
>
> Last dump/restore was a 24 hours process in one of our installations.
> I think it was due to in-efficiency in handling BYTEA types in the
> process (but not sure).
>
> But I'm one of the few guys who seem to have an infinite amount of
> time for reading on mailing lists, but without my knowledge from
> reading this list I would never have run pg_migrator on my production
> data if I had to pick it from pg_foundry.

So, did you use "copy" or "link" mode, and how fast was the pg_migrator
upgrade?

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

On Wed, May 5, 2010 at 7:44 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> Alvaro Herrera wrote:
>>
>> So what was the conclusion here?  Is pg_migrator going to be in contrib
>> for beta2 or 3, after cleaning it up?
>
> Thanks for asking.  :-)  I can add pg_migrator to contrib by the end of
> next week, so it will be in beta2.  I will remove 8.4 as a migration
> target, which will allow the removal of some C code and documentation
> warnings.  Unless I hear otherwise, I will start on it in the next few
> days.  Total work will be < 8 hours, including testing.
>
> One outstanding question is whether we want to rename pg_migrator to
> something clearer, like pg_upgrade or pg_binary_upgrade.  (pg_upgrade
> was the original name for this migration method in the 1998.)  I am
> slightly concerned that the "migration" word is too associated with
> cross-database-product migration.  (There are no mentions of
> "pg_migrator" in our CVS now, except for an 8.4 release note item
> mention when pg_dump --binary-upgrade was added.)

I think it will be confusing if we change the name, so I vote to not
change the name.

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

On Wed, 2010-05-05 at 20:24 -0400, Robert Haas wrote:
> On Wed, May 5, 2010 at 7:44 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> > Alvaro Herrera wrote:
> >>
> >> So what was the conclusion here? Is pg_migrator going to be in contrib
> >> for beta2 or 3, after cleaning it up?
> >
> > Thanks for asking. :-) I can add pg_migrator to contrib by the end of
> > next week, so it will be in beta2. I will remove 8.4 as a migration
> > target, which will allow the removal of some C code and documentation
> > warnings. Unless I hear otherwise, I will start on it in the next few
> > days. Total work will be < 8 hours, including testing.
> >
> > One outstanding question is whether we want to rename pg_migrator to
> > something clearer, like pg_upgrade or pg_binary_upgrade. (pg_upgrade
> > was the original name for this migration method in the 1998.) I am
> > slightly concerned that the "migration" word is too associated with
> > cross-database-product migration. (There are no mentions of
> > "pg_migrator" in our CVS now, except for an 8.4 release note item
> > mention when pg_dump --binary-upgrade was added.)
>
> I think it will be confusing if we change the name, so I vote to not
> change the name.

Actually, I would vote yes to change the name. Once its in contrib, we
likely never will and this isn't really a migration tool. It is an
upgrade tool.

Joshua D. Drake

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

--
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564
Consulting, Training, Support, Custom Development, Engineering

"Joshua D. Drake" <jd(at)commandprompt(dot)com> writes:
> On Wed, 2010-05-05 at 20:24 -0400, Robert Haas wrote:
>> I think it will be confusing if we change the name, so I vote to not
>> change the name.

> Actually, I would vote yes to change the name.

I lean that way too. If there were no history involved, we'd certainly
prefer pg_upgrade to pg_migrator.

regards, tom lane

Tom Lane wrote:
> "Joshua D. Drake" <jd(at)commandprompt(dot)com> writes:
> > On Wed, 2010-05-05 at 20:24 -0400, Robert Haas wrote:
> >> I think it will be confusing if we change the name, so I vote to not
> >> change the name.
>
> > Actually, I would vote yes to change the name.
>
> I lean that way too. If there were no history involved, we'd certainly
> prefer pg_upgrade to pg_migrator.

Yeah, that was my feeling too. People like "pg_upgrade", or something
else? I will add some text like "pg_upgrade (formerly pg_migrator)" in
the docs.

I will also add something about the fact that there is no guarantee that
pg_upgrade will work with all future major Postgres releases, per Tom's
concern.

FYI, I specifically labeled backend changes as "binary upgrade" because
I wanted to make sure those changes were useful for other binary upgrade
tools, in case someone wanted to create another one.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

On 2010-05-06 01:45, Bruce Momjian wrote:
> Jesper Krogh wrote:
>
>> On 2010-05-03 23:09, Bruce Momjian wrote:
>>
>>> Robert Haas wrote:
>>>
>>>
>>>> On Sun, May 2, 2010 at 3:45 PM, Dimitri Fontaine<dfontaine(at)hi-media(dot)com> wrote:
>>>>
>>>>
>>>>> Now you tell me how awful this idea really is :)
>>>>>
>>>>>
>>>> I'm not sure I can count that high. :-)
>>>>
>>>>
>>> While I can't improve on Robert's reply, I can supply a PDF about how
>>> pg_migrator works:
>>>
>>> http://momjian.us/main/presentations/technical.html#pg_migrator
>>>
>>>
>>>
>> There is a huge amount of users to whom pg_migrator is "at least"
>> a big a feature as HS+SR is.
>>
>> Last dump/restore was a 24 hours process in one of our installations.
>> I think it was due to in-efficiency in handling BYTEA types in the
>> process (but not sure).
>>
>> But I'm one of the few guys who seem to have an infinite amount of
>> time for reading on mailing lists, but without my knowledge from
>> reading this list I would never have run pg_migrator on my production
>> data if I had to pick it from pg_foundry.
>>
> So, did you use "copy" or "link" mode, and how fast was the pg_migrator
> upgrade?
>
>
I did go the painful way (dump+restore) at that point in time.
It was an 8.1 - 8.3 migration. Since then data has grown and the dump
restore is even less favorable on the 8.3 -> 9.0 migration.

So in general the pg_migrator way seems to be the only way to aviod
the slony way which is orders of magnitude more complicated.

Q: I read you pdf, why isn't statistics copied over? It seems to be the last
part missing from doing an upgrade in a few minutes.

Jesper
--
Jesper

Excerpts from Jesper Krogh's message of jue may 06 00:32:09 -0400 2010:

> Q: I read you pdf, why isn't statistics copied over? It seems to be the last
> part missing from doing an upgrade in a few minutes.

Seems fraught with peril, and a bit pointless. What's so bad about having to
run ANALYZE afterwards?
--

On 2010-05-06 06:41, Alvaro Herrera wrote:
> Excerpts from Jesper Krogh's message of jue may 06 00:32:09 -0400 2010:
>
>
>> Q: I read you pdf, why isn't statistics copied over? It seems to be the last
>> part missing from doing an upgrade in a few minutes.
>>
> Seems fraught with peril, and a bit pointless. What's so bad about having to
> run ANALYZE afterwards?
>

There is nothing directly "bad" about it.. but:

It's just "an extra step, that might be overseen and is absolutely
required".

I should have written:
Why isn't statistics copied over or why doesnt pg_migrator run analyze by
itself?

The database (of a reasonable size) is useless until statistics is
available.

I guess it is because pg_dump/restore doesn't do it either.

Jesper
--
Jesper

Jesper Krogh <jesper(at)krogh(dot)cc> writes:
> I did go the painful way (dump+restore) at that point in time.
> It was an 8.1 - 8.3 migration. Since then data has grown and the dump
> restore is even less favorable on the 8.3 -> 9.0 migration.
>
> So in general the pg_migrator way seems to be the only way to aviod
> the slony way which is orders of magnitude more complicated.

Right in the middle there's the Londiste way. It works like Slony but
the setup is much easier.

Regards,
--
Dimitri Fontaine
PostgreSQL DBA, Architecte

Jesper Krogh wrote:
> On 2010-05-06 06:41, Alvaro Herrera wrote:
> > Excerpts from Jesper Krogh's message of jue may 06 00:32:09 -0400 2010:
> >
> >
> >> Q: I read you pdf, why isn't statistics copied over? It seems to be the last
> >> part missing from doing an upgrade in a few minutes.
> >>
> > Seems fraught with peril, and a bit pointless. What's so bad about having to
> > run ANALYZE afterwards?
> >
>
> There is nothing directly "bad" about it.. but:
>
> It's just "an extra step, that might be overseen and is absolutely
> required".
>
> I should have written:
> Why isn't statistics copied over or why doesnt pg_migrator run analyze by
> itself?
>
> The database (of a reasonable size) is useless until statistics is
> available.
>
> I guess it is because pg_dump/restore doesn't do it either.

Yeah, the statistics are part of the system tables, and system tables
are fully handled by pg_dumpall --schema-only (except for statistics).
There might be changes in the system table statistics format that would
break if pg_migrator tried to migrate the statistics. Right now
pg_migrator is immune from any system table changes, and I would like to
keep it that way.

And if pg_migrator ran analyze itself, it would greatly increase its
great migration times!

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

Bruce Momjian wrote:
> > The database (of a reasonable size) is useless until statistics is
> > available.
> >
> > I guess it is because pg_dump/restore doesn't do it either.
>
> Yeah, the statistics are part of the system tables, and system tables
> are fully handled by pg_dumpall --schema-only (except for statistics).
> There might be changes in the system table statistics format that would
> break if pg_migrator tried to migrate the statistics. Right now
> pg_migrator is immune from any system table changes, and I would like to
> keep it that way.
>
> And if pg_migrator ran analyze itself, it would greatly increase its
> great migration times!

Forgot the :-).

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

Bruce Momjian <bruce(at)momjian(dot)us> writes:
> Jesper Krogh wrote:
>> I should have written:
>> Why isn't statistics copied over or why doesnt pg_migrator run analyze by
>> itself?

> Yeah, the statistics are part of the system tables, and system tables
> are fully handled by pg_dumpall --schema-only (except for statistics).
> There might be changes in the system table statistics format that would
> break if pg_migrator tried to migrate the statistics.

Right, it's not really practical for pg_migrator to just copy over the
statistics without any intelligence. We might at some time choose to
teach it which stats could be copied safely, but that hardly seems like
something to do in version 1.0 --- and anyway it could not be a 100%
solution.

> And if pg_migrator ran analyze itself, it would greatly increase its
> great migration times!

Exactly. It's not a win for pg_migrator to not give you back control of
the database until everything has been ANALYZEd. That's a task that can
likely be done in background.

regards, tom lane

Tom Lane wrote:
> Bruce Momjian <bruce(at)momjian(dot)us> writes:
> > Jesper Krogh wrote:
> >> I should have written:
> >> Why isn't statistics copied over or why doesnt pg_migrator run analyze by
> >> itself?
>
> > Yeah, the statistics are part of the system tables, and system tables
> > are fully handled by pg_dumpall --schema-only (except for statistics).
> > There might be changes in the system table statistics format that would
> > break if pg_migrator tried to migrate the statistics.
>
> Right, it's not really practical for pg_migrator to just copy over the
> statistics without any intelligence. We might at some time choose to
> teach it which stats could be copied safely, but that hardly seems like
> something to do in version 1.0 --- and anyway it could not be a 100%
> solution.
>
> > And if pg_migrator ran analyze itself, it would greatly increase its
> > great migration times!
>
> Exactly. It's not a win for pg_migrator to not give you back control of
> the database until everything has been ANALYZEd. That's a task that can
> likely be done in background.

I have to keep those sub-minute migration times for bragging rights. :-)

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

Tom Lane wrote:
> Bruce Momjian <bruce(at)momjian(dot)us> writes:
> > Jesper Krogh wrote:
> >> I should have written:
> >> Why isn't statistics copied over or why doesnt pg_migrator run analyze by
> >> itself?
>
> > Yeah, the statistics are part of the system tables, and system tables
> > are fully handled by pg_dumpall --schema-only (except for statistics).
> > There might be changes in the system table statistics format that would
> > break if pg_migrator tried to migrate the statistics.
>
> Right, it's not really practical for pg_migrator to just copy over the
> statistics without any intelligence. We might at some time choose to
> teach it which stats could be copied safely, but that hardly seems like
> something to do in version 1.0 --- and anyway it could not be a 100%
> solution.

It seems copying over pg_statistic would require preservation of
pg_class.oid. Right now we only preserve pg_class.relfilenode.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

Excerpts from Bruce Momjian's message of jue may 06 11:19:27 -0400 2010:

> It seems copying over pg_statistic would require preservation of
> pg_class.oid. Right now we only preserve pg_class.relfilenode.

That could be fixed easily by creating a throwaway table which included the
qualified table name instead of the OID, and reloading it into pg_statistic
after the migration.

Perhaps this could be done as a regular task in the old database before
bringing the server down.
--

Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org> writes:
> Excerpts from Bruce Momjian's message of jue may 06 11:19:27 -0400 2010:
>> It seems copying over pg_statistic would require preservation of
>> pg_class.oid. Right now we only preserve pg_class.relfilenode.

> That could be fixed easily by creating a throwaway table which included the
> qualified table name instead of the OID, and reloading it into pg_statistic
> after the migration.

Yeah. Casting to and from regclass would do the trick too.

regards, tom lane

Tom Lane wrote:
> Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org> writes:
> > Excerpts from Bruce Momjian's message of jue may 06 11:19:27 -0400 2010:
> >> It seems copying over pg_statistic would require preservation of
> >> pg_class.oid. Right now we only preserve pg_class.relfilenode.
>
> > That could be fixed easily by creating a throwaway table which included the
> > qualified table name instead of the OID, and reloading it into pg_statistic
> > after the migration.
>
> Yeah. Casting to and from regclass would do the trick too.

I will add this idea to the pg_migrator TODO file. I didn't bother with
this in the 8.3 -> 8.4 migration because we changed the default number
of statistics collected in 8.4.

This would be a 9.1 item.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

Bruce Momjian wrote:
> Tom Lane wrote:
> > "Joshua D. Drake" <jd(at)commandprompt(dot)com> writes:
> > > On Wed, 2010-05-05 at 20:24 -0400, Robert Haas wrote:
> > >> I think it will be confusing if we change the name, so I vote to not
> > >> change the name.
> >
> > > Actually, I would vote yes to change the name.
> >
> > I lean that way too. If there were no history involved, we'd certainly
> > prefer pg_upgrade to pg_migrator.
>
> Yeah, that was my feeling too. People like "pg_upgrade", or something
> else? I will add some text like "pg_upgrade (formerly pg_migrator)" in
> the docs.

OK, seems people like pg_upgrade, but do we call it "pgupgrade" or
"pg_upgrade"? I don't see consistent naming in /contrib:

pg_buffercache/
pg_freespacemap/
pg_standby/
pg_stat_statements/
pg_trgm/
pgbench/
pgcrypto/
pgrowlocks/
pgstattuple/

The original 7.2 name was "pg_upgrade":

http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/contrib/pg_upgrade/Attic/

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

On 6 May 2010 20:55, Bruce Momjian <bruce(at)momjian(dot)us> wrote:

> Bruce Momjian wrote:
> > Tom Lane wrote:
> > > "Joshua D. Drake" <jd(at)commandprompt(dot)com> writes:
> > > > On Wed, 2010-05-05 at 20:24 -0400, Robert Haas wrote:
> > > >> I think it will be confusing if we change the name, so I vote to not
> > > >> change the name.
> > >
> > > > Actually, I would vote yes to change the name.
> > >
> > > I lean that way too. If there were no history involved, we'd certainly
> > > prefer pg_upgrade to pg_migrator.
> >
> > Yeah, that was my feeling too. People like "pg_upgrade", or something
> > else? I will add some text like "pg_upgrade (formerly pg_migrator)" in
> > the docs.
>
> OK, seems people like pg_upgrade, but do we call it "pgupgrade" or
> "pg_upgrade"? I don't see consistent naming in /contrib:
>
> pg_buffercache/
> pg_freespacemap/
> pg_standby/
> pg_stat_statements/
> pg_trgm/
> pgbench/
> pgcrypto/
> pgrowlocks/
> pgstattuple/
>
> The original 7.2 name was "pg_upgrade":
>
>
> http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/contrib/pg_upgrade/Attic/
>
> --
>
>
You will call it pg_upgrade. I have spoken.

Thom

Bruce Momjian <bruce(at)momjian(dot)us> writes:
> OK, seems people like pg_upgrade, but do we call it "pgupgrade" or
> "pg_upgrade"?

The latter. The former is unreadable.

regards, tom lane

On Thu, May 6, 2010 at 8:10 PM, Thom Brown <thombrown(at)gmail(dot)com> wrote:

> You will call it pg_upgrade.  I have spoken.
> Thom

LOL.

--
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise Postgres Company

2010/5/6 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> Bruce Momjian <bruce(at)momjian(dot)us> writes:
>> OK, seems people like pg_upgrade, but do we call it "pgupgrade" or
>> "pg_upgrade"?
>

pg_upgrade sounds good. I just bet that some users will want it to
upgrade their postgresql from 9.0.0 to 9.0.1..

> The latter.  The former is unreadable.
>
>                        regards, tom lane
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

--
Cédric Villemain

Bruce Momjian wrote:
> Bruce Momjian wrote:
> > Tom Lane wrote:
> > > "Joshua D. Drake" <jd(at)commandprompt(dot)com> writes:
> > > > On Wed, 2010-05-05 at 20:24 -0400, Robert Haas wrote:
> > > >> I think it will be confusing if we change the name, so I vote to not
> > > >> change the name.
> > >
> > > > Actually, I would vote yes to change the name.
> > >
> > > I lean that way too. If there were no history involved, we'd certainly
> > > prefer pg_upgrade to pg_migrator.
> >
> > Yeah, that was my feeling too. People like "pg_upgrade", or something
> > else? I will add some text like "pg_upgrade (formerly pg_migrator)" in
> > the docs.
>
> OK, seems people like pg_upgrade, but do we call it "pgupgrade" or
> "pg_upgrade"? I don't see consistent naming in /contrib:
>
> pg_buffercache/
> pg_freespacemap/
> pg_standby/
> pg_stat_statements/
> pg_trgm/
> pgbench/
> pgcrypto/
> pgrowlocks/
> pgstattuple/
>
> The original 7.2 name was "pg_upgrade":
>
> http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/contrib/pg_upgrade/Attic/

Added to /contrib as 'pg_upgrade'.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com