Re: pg_migrator issues

pg_migrator has become more popular recently, so it seems time to look
at some enhancements that would improve pg_migrator. None of these are
required, but rather changes that would be nice to have:

1) Right now pg_migrator preserves relfilenodes for TOAST files because
this is required for proper migration. Now that we have shown that
strategically-placed global variables with a server-side function to set
them is a viable solution, it would be nice to preserve all relfilenodes
from the old server. This would simplify pg_migrator by no long
requiring place-holder relfilenodes or the renaming of TOAST files. A
simpler solution would just be to allow TOAST table creation to
automatically remove placeholder files and create specified relfilenodes
via global variables.

2) Right now pg_migrator renames old tablespaces to .old, which fails
if the tablespaces are on mount points. I have already received a
report of such a failure. $PGDATA also has that issue, but that
renaming has to be done by the user before pg_migrator is run, and only
if they want to keep the same $PGDATA value after migration, i.e. no
version-specific directory path. One idea we floated around was to have
tablespaces use major version directory names under the tablespace
directory so renaming would not be necessary. I could implement a
pg_migrator --delete-old flag to cleanly delete the old 8.4 server files
which are not in a version-specific subdirectory.

3) There is no easy way to analyze all databases. vacuumdb --analyze
does analyze _and_ vacuum, which for an 8.4 to 8.5 migration does an
unnecessary vacuum. Right now I recommend ANALYZE in every database,
but it would be nice if there were a single command which did this.

4) I have implemented the ability to run pg_migrator --check on a live
old server. However, pg_migrator uses information from controldata to
check things, and it also needs xid information that is only available
via pg_resetxlog -n(no update) to perform the migration. Unfortunately,
pg_resetxlog -n cannot be run on a live server, so pg_migrator runs
pg_controldata for --check and pg_resetxlog -n for real upgrades. It
would simplify pg_migrator if I would run pg_resetxlog -n on a live
server, but I can understand if people don't want to do that because the
xid information reported on a live server is inaccurate.

Comments?

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

+ If your life is a hard drive, Christ can be your backup. +

Bruce Momjian wrote:
> pg_migrator has become more popular recently, so it seems time to look
> at some enhancements that would improve pg_migrator. None of these are
> required, but rather changes that would be nice to have:
>
> 1) Right now pg_migrator preserves relfilenodes for TOAST files because
> this is required for proper migration. Now that we have shown that
> strategically-placed global variables with a server-side function to set
> them is a viable solution, it would be nice to preserve all relfilenodes
> from the old server. This would simplify pg_migrator by no long
> requiring place-holder relfilenodes or the renaming of TOAST files. A
> simpler solution would just be to allow TOAST table creation to
> automatically remove placeholder files and create specified relfilenodes
> via global variables.
>
> 2) Right now pg_migrator renames old tablespaces to .old, which fails
> if the tablespaces are on mount points. I have already received a
> report of such a failure. $PGDATA also has that issue, but that
> renaming has to be done by the user before pg_migrator is run, and only
> if they want to keep the same $PGDATA value after migration, i.e. no
> version-specific directory path. One idea we floated around was to have
> tablespaces use major version directory names under the tablespace
> directory so renaming would not be necessary. I could implement a
> pg_migrator --delete-old flag to cleanly delete the old 8.4 server files
> which are not in a version-specific subdirectory.
>
> 3) There is no easy way to analyze all databases. vacuumdb --analyze
> does analyze _and_ vacuum, which for an 8.4 to 8.5 migration does an
> unnecessary vacuum. Right now I recommend ANALYZE in every database,
> but it would be nice if there were a single command which did this.
>
> 4) I have implemented the ability to run pg_migrator --check on a live
> old server. However, pg_migrator uses information from controldata to
> check things, and it also needs xid information that is only available
> via pg_resetxlog -n(no update) to perform the migration. Unfortunately,
> pg_resetxlog -n cannot be run on a live server, so pg_migrator runs
> pg_controldata for --check and pg_resetxlog -n for real upgrades. It
> would simplify pg_migrator if I would run pg_resetxlog -n on a live
> server, but I can understand if people don't want to do that because the
> xid information reported on a live server is inaccurate.
>
> Comments?

Having received no replies to my email above, I assume the community
would like to review and perhaps approve patches to implement all of
these items. I will start working on the patches.

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

+ If your life is a hard drive, Christ can be your backup. +

Bruce,

Sorry for not having replied sooner...

On Sun, Jan 3, 2010 at 5:43 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
>> 1)  Right now pg_migrator preserves relfilenodes for TOAST files because
>> this is required for proper migration.  Now that we have shown that
>> strategically-placed global variables with a server-side function to set
>> them is a viable solution, it would be nice to preserve all relfilenodes
>> from the old server.  This would simplify pg_migrator by no long
>> requiring place-holder relfilenodes or the renaming of TOAST files.  A
>> simpler solution would just be to allow TOAST table creation to
>> automatically remove placeholder files and create specified relfilenodes
>> via global variables.

I have no opinion on this one way or the other.

>> 2)  Right now pg_migrator renames old tablespaces to .old, which fails
>> if the tablespaces are on mount points.  I have already received a
>> report of such a failure.  $PGDATA also has that issue, but that
>> renaming has to be done by the user before pg_migrator is run, and only
>> if they want to keep the same $PGDATA value after migration, i.e. no
>> version-specific directory path.  One idea we floated around was to have
>> tablespaces use major version directory names under the tablespace
>> directory so renaming would not be necessary.  I could implement a
>> pg_migrator --delete-old flag to cleanly delete the old 8.4 server files
>> which are not in a version-specific subdirectory.

I don't really like this. It seems klunky, and it seems like there
ought to be a way to avoid needing to rename the tablespace
directories at all.

>> 3)  There is no easy way to analyze all databases.  vacuumdb --analyze
>> does analyze _and_ vacuum, which for an 8.4 to 8.5 migration does an
>> unnecessary vacuum.  Right now I recommend ANALYZE in every database,
>> but it would be nice if there were a single command which did this.

Something like vacuumdb --analyze-only? It seems like overkill to
create a whole new command for this, even though vacuumdb doesn't
quite make sense.

>> 4)  I have implemented the ability to run pg_migrator --check on a live
>> old server.  However, pg_migrator uses information from controldata to
>> check things, and it also needs xid information that is only available
>> via pg_resetxlog -n(no update) to perform the migration.  Unfortunately,
>> pg_resetxlog -n cannot be run on a live server, so pg_migrator runs
>> pg_controldata for --check and pg_resetxlog -n for real upgrades.  It
>> would simplify pg_migrator if I would run pg_resetxlog -n on a live
>> server, but I can understand if people don't want to do that because the
>> xid information reported on a live server is inaccurate.

I don't really have a specific thought on this issue, except that it
sounds like you're launching a lot of shell commands, and I wonder
whether it would be better to try to do this through either C code or
by exposing the appropriate stuff at the SQL level.

...Robert

Bruce Momjian wrote:
> pg_migrator has become more popular recently, so it seems time to look
> at some enhancements that would improve pg_migrator. None of these are
> required, but rather changes that would be nice to have:
>
> 1) Right now pg_migrator preserves relfilenodes for TOAST files because
> this is required for proper migration. Now that we have shown that
> strategically-placed global variables with a server-side function to set
> them is a viable solution, it would be nice to preserve all relfilenodes
> from the old server. This would simplify pg_migrator by no long
> requiring place-holder relfilenodes or the renaming of TOAST files. A
> simpler solution would just be to allow TOAST table creation to
> automatically remove placeholder files and create specified relfilenodes
> via global variables.

Getting rid of the need for placeholders is a good idea. +1 on getting
TOAST tables created with the correct relfilenode from the start. I
don't know that preserving any other relfilenode is useful; however if
it means you no longer have to rename the files underlying each table,
it would probably also be a good idea. (I don't know how does
pg_migrator deal with such things currently -- does it keep a map of
table name to relfilenode?)

> 2) Right now pg_migrator renames old tablespaces to .old, which fails
> if the tablespaces are on mount points. I have already received a
> report of such a failure.

I thought it was impossible to use bare mountpoints as tablespaces due
to ownership problems ... Is that not the case? -1 for special hacks
that work around bogus setups, if that means intrusive changes to the
core code.

> 3) There is no easy way to analyze all databases. vacuumdb --analyze
> does analyze _and_ vacuum, which for an 8.4 to 8.5 migration does an
> unnecessary vacuum. Right now I recommend ANALYZE in every database,
> but it would be nice if there were a single command which did this.

+1 for vacuumdb --analyze-only

> 4) I have implemented the ability to run pg_migrator --check on a live
> old server. However, pg_migrator uses information from controldata to
> check things, and it also needs xid information that is only available
> via pg_resetxlog -n(no update) to perform the migration. Unfortunately,
> pg_resetxlog -n cannot be run on a live server, so pg_migrator runs
> pg_controldata for --check and pg_resetxlog -n for real upgrades. It
> would simplify pg_migrator if I would run pg_resetxlog -n on a live
> server, but I can understand if people don't want to do that because the
> xid information reported on a live server is inaccurate.

What xid info does it need? Would it be good enough to use the "next
XID" from most recent checkpoint from pg_controldata? It is a bit
outdated, but can't you simply add some value to it to have a safety margin?

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

Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
> Getting rid of the need for placeholders is a good idea. +1 on getting
> TOAST tables created with the correct relfilenode from the start. I
> don't know that preserving any other relfilenode is useful; however if
> it means you no longer have to rename the files underlying each table,
> it would probably also be a good idea.

I think this is an all-or-nothing proposition: if you try to preserve
only some relfilenodes, you risk collisions with automatically assigned
ones. It's just like the situation with pg_type OIDs.

I concur that trying to preserve them looks like it would be less work
than the current method.

regards, tom lane

Alvaro Herrera wrote:
> > 3) There is no easy way to analyze all databases. vacuumdb --analyze
> > does analyze _and_ vacuum, which for an 8.4 to 8.5 migration does an
> > unnecessary vacuum. Right now I recommend ANALYZE in every database,
> > but it would be nice if there were a single command which did this.
>
> +1 for vacuumdb --analyze-only

OK, I have implemented this using --only-analyze to avoid having the
'--anal' option spelling be ambiguous, which might confuse/frustrate
users.

I also moved the --freeze option documention mention into a more logical
place.

Patch attached.

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

+ If your life is a hard drive, Christ can be your backup. +

Robert Haas wrote:
> >> 3) ?There is no easy way to analyze all databases. ?vacuumdb --analyze
> >> does analyze _and_ vacuum, which for an 8.4 to 8.5 migration does an
> >> unnecessary vacuum. ?Right now I recommend ANALYZE in every database,
> >> but it would be nice if there were a single command which did this.
>
> Something like vacuumdb --analyze-only? It seems like overkill to
> create a whole new command for this, even though vacuumdb doesn't
> quite make sense.

Yea, I am not excited about having vacuumdb do only analyze, but it
seems the most minimal solution. I spelled it --only-analyze and just
posted the reason and patch.

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

+ If your life is a hard drive, Christ can be your backup. +

Robert Haas wrote:
> >> 4) ?I have implemented the ability to run pg_migrator --check on a live
> >> old server. ?However, pg_migrator uses information from controldata to
> >> check things, and it also needs xid information that is only available
> >> via pg_resetxlog -n(no update) to perform the migration. ?Unfortunately,
> >> pg_resetxlog -n cannot be run on a live server, so pg_migrator runs
> >> pg_controldata for --check and pg_resetxlog -n for real upgrades. ?It
> >> would simplify pg_migrator if I would run pg_resetxlog -n on a live
> >> server, but I can understand if people don't want to do that because the
> >> xid information reported on a live server is inaccurate.
>
> I don't really have a specific thought on this issue, except that it
> sounds like you're launching a lot of shell commands, and I wonder
> whether it would be better to try to do this through either C code or
> by exposing the appropriate stuff at the SQL level.

I considered that but realize that pg_migrator has to read
pg_controldata in both the old and new servers, meaning it would need
access to both C structures, and considering they both have the same
structure names, that would require some odd C tricks. Add to that you
don't know which version of Postgres you are migrating from/to during
compile and the idea of using C becomes even less attractive.

Doing this in C would require pg_migrator to track all changes in the
pg_controldata structure layout, which seems excessively
complex/error-prone. Right now I only have to track changes to the
naming of the output fields.

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

+ If your life is a hard drive, Christ can be your backup. +

Bruce Momjian escribió:

> I considered that but realize that pg_migrator has to read
> pg_controldata in both the old and new servers, meaning it would need
> access to both C structures, and considering they both have the same
> structure names, that would require some odd C tricks. Add to that you
> don't know which version of Postgres you are migrating from/to during
> compile and the idea of using C becomes even less attractive.

However, keep in mind that this might not be the last time on which we
will want to read something from a C struct, so perhaps it would be good
to bite the bullet and write the odd tricks. Does it already have
access (at compile time) to the old and new source trees?

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Alvaro Herrera wrote:
> Bruce Momjian wrote:
> > pg_migrator has become more popular recently, so it seems time to look
> > at some enhancements that would improve pg_migrator. None of these are
> > required, but rather changes that would be nice to have:
> >
> > 1) Right now pg_migrator preserves relfilenodes for TOAST files because
> > this is required for proper migration. Now that we have shown that
> > strategically-placed global variables with a server-side function to set
> > them is a viable solution, it would be nice to preserve all relfilenodes
> > from the old server. This would simplify pg_migrator by no long
> > requiring place-holder relfilenodes or the renaming of TOAST files. A
> > simpler solution would just be to allow TOAST table creation to
> > automatically remove placeholder files and create specified relfilenodes
> > via global variables.
>
> Getting rid of the need for placeholders is a good idea. +1 on getting
> TOAST tables created with the correct relfilenode from the start. I
> don't know that preserving any other relfilenode is useful; however if
> it means you no longer have to rename the files underlying each table,
> it would probably also be a good idea. (I don't know how does
> pg_migrator deal with such things currently -- does it keep a map of
> table name to relfilenode?)

Yea, as Tom said later, there are two options. Either we create
placeholder files and then remove the place-holders when we create the
toast tables or we just preserve all relfilenodes --- I think the later
is easier.

> > 4) I have implemented the ability to run pg_migrator --check on a live
> > old server. However, pg_migrator uses information from controldata to
> > check things, and it also needs xid information that is only available
> > via pg_resetxlog -n(no update) to perform the migration. Unfortunately,
> > pg_resetxlog -n cannot be run on a live server, so pg_migrator runs
> > pg_controldata for --check and pg_resetxlog -n for real upgrades. It
> > would simplify pg_migrator if I would run pg_resetxlog -n on a live
> > server, but I can understand if people don't want to do that because the
> > xid information reported on a live server is inaccurate.
>
> What xid info does it need? Would it be good enough to use the "next
> XID" from most recent checkpoint from pg_controldata? It is a bit
> outdated, but can't you simply add some value to it to have a safety margin?

Well, I am not much into 'safety margins' with pg_migrator, meaning I
want to get the most reliable value I can --- I have no idea what that
safety margin would be. Right now pg_migrator works fine by calling
pg_controldata or pg_resetxlog as appropriate. I was hoping to allow
pg_resetxlog -n on a live server. Is that something we should avoid?
I really don't need the change --- it would just simplify pg_migrator.

I was just really asking if disallowing pg_resetxlog -n on a live server
is planned behavior or an oversight. I can see the logic that it should
be disallowed but I am just looking for confirmation from someone and I
can then drop the issue.

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

+ If your life is a hard drive, Christ can be your backup. +

Alvaro Herrera wrote:
> Bruce Momjian escribi?:
>
> > I considered that but realize that pg_migrator has to read
> > pg_controldata in both the old and new servers, meaning it would need
> > access to both C structures, and considering they both have the same
> > structure names, that would require some odd C tricks. Add to that you
> > don't know which version of Postgres you are migrating from/to during
> > compile and the idea of using C becomes even less attractive.
>
> However, keep in mind that this might not be the last time on which we
> will want to read something from a C struct, so perhaps it would be good
> to bite the bullet and write the odd tricks. Does it already have
> access (at compile time) to the old and new source trees?

No, only the new soure tree, or actually any source tree, but ideally
the new one. Remember we have Win32 binaries being built, and right now
there is limited linkage between pg_migrator and the backend code.

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

+ If your life is a hard drive, Christ can be your backup. +

Alvaro Herrera wrote:
> > 2) Right now pg_migrator renames old tablespaces to .old, which fails
> > if the tablespaces are on mount points. I have already received a
> > report of such a failure.
>
> I thought it was impossible to use bare mountpoints as tablespaces due
> to ownership problems ... Is that not the case? -1 for special hacks
> that work around bogus setups, if that means intrusive changes to the
> core code.

I talked to the person who reported the problem and he and I confirmed
that it is quite easy to make the mount point be owned by the postgres
user and have that function as a tablespace. Is that not a supported
setup? There is probably a larger problem that the tablespace must be
located in a directory that has directory rename permission for
postgres. I have updated the pg_migrator INSTALL file to mention this
issue.

As far as .old, we could create the tablespaces as *.new, but that kind
of defeats the existing recommended pg_migrator usage where we tell the
user to rename PGDATA to .old before running pg_migrator.

It was actually Tom's idea months ago to put a version-specific
directory in the tablespace. I don't think it is necessary, and we can
live with the mount point limitation.

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

+ If your life is a hard drive, Christ can be your backup. +

Bruce Momjian <bruce(at)momjian(dot)us> writes:
> I was just really asking if disallowing pg_resetxlog -n on a live server
> is planned behavior or an oversight. I can see the logic that it should
> be disallowed but I am just looking for confirmation from someone and I
> can then drop the issue.

Well, it's not only a matter of "are we going to clobber live state",
it's also "is the state that we are looking at changing under us?".
The -n switch only covers the first point. I think it would require
some careful analysis, and testing that's never been done, before having
any confidence in the results of pg_resetxlog on a live server.

Why should you need this anyway? pg_migrator should not be having to
run pg_resetxlog on the old installation, I would think.

regards, tom lane

On Mon, Jan 4, 2010 at 2:11 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> Alvaro Herrera wrote:
>> > 2)  Right now pg_migrator renames old tablespaces to .old, which fails
>> > if the tablespaces are on mount points.  I have already received a
>> > report of such a failure.
>>
>> I thought it was impossible to use bare mountpoints as tablespaces due
>> to ownership problems ... Is that not the case?  -1 for special hacks
>> that work around bogus setups, if that means intrusive changes to the
>> core code.
>
> I talked to the person who reported the problem and he and I confirmed
> that it is quite easy to make the mount point be owned by the postgres
> user and have that function as a tablespace.  Is that not a supported
> setup?  There is probably a larger problem that the tablespace must be
> located in a directory that has directory rename permission for
> postgres.  I have updated the pg_migrator INSTALL file to mention this
> issue.
>
> As far as .old, we could create the tablespaces as *.new, but that kind
> of defeats the existing recommended pg_migrator usage where we tell the
> user to rename PGDATA to .old before running pg_migrator.
>
> It was actually Tom's idea months ago to put a version-specific
> directory in the tablespace.  I don't think it is necessary, and we can
> live with the mount point limitation.

What doesn't work if we just don't rename the tablespace at all? And
can't we put some smarts into the backend to handle that thing?

...Robert

Tom Lane wrote:
> Bruce Momjian <bruce(at)momjian(dot)us> writes:
> > I was just really asking if disallowing pg_resetxlog -n on a live server
> > is planned behavior or an oversight. I can see the logic that it should
> > be disallowed but I am just looking for confirmation from someone and I
> > can then drop the issue.
>
> Well, it's not only a matter of "are we going to clobber live state",
> it's also "is the state that we are looking at changing under us?".
> The -n switch only covers the first point. I think it would require
> some careful analysis, and testing that's never been done, before having
> any confidence in the results of pg_resetxlog on a live server.

Yea, that was my analysis too. I will discard the idea and just keep
the pg_migrator code that does either.

> Why should you need this anyway? pg_migrator should not be having to
> run pg_resetxlog on the old installation, I would think.

Well, the same code is run on the new and old server. The complex issue
is that the same code that checks for matching controldata settings
(check mode) is the same that pulls the xid from the old server to set
it on the new one.

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

+ If your life is a hard drive, Christ can be your backup. +

Robert Haas wrote:
> On Mon, Jan 4, 2010 at 2:11 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> > Alvaro Herrera wrote:
> >> > 2) ?Right now pg_migrator renames old tablespaces to .old, which fails
> >> > if the tablespaces are on mount points. ?I have already received a
> >> > report of such a failure.
> >>
> >> I thought it was impossible to use bare mountpoints as tablespaces due
> >> to ownership problems ... Is that not the case? ?-1 for special hacks
> >> that work around bogus setups, if that means intrusive changes to the
> >> core code.
> >
> > I talked to the person who reported the problem and he and I confirmed
> > that it is quite easy to make the mount point be owned by the postgres
> > user and have that function as a tablespace. ?Is that not a supported
> > setup? ?There is probably a larger problem that the tablespace must be
> > located in a directory that has directory rename permission for
> > postgres. ?I have updated the pg_migrator INSTALL file to mention this
> > issue.
> >
> > As far as .old, we could create the tablespaces as *.new, but that kind
> > of defeats the existing recommended pg_migrator usage where we tell the
> > user to rename PGDATA to .old before running pg_migrator.
> >
> > It was actually Tom's idea months ago to put a version-specific
> > directory in the tablespace. ?I don't think it is necessary, and we can
> > live with the mount point limitation.
>
> What doesn't work if we just don't rename the tablespace at all? And
> can't we put some smarts into the backend to handle that thing?

Well, when you restore the old dump's schema into the new server, the
tablespace directory path will be the same, so we had better not have
any directory there.

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

+ If your life is a hard drive, Christ can be your backup. +

On Mon, Jan 4, 2010 at 2:52 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> Robert Haas wrote:
>> On Mon, Jan 4, 2010 at 2:11 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
>> > Alvaro Herrera wrote:
>> >> > 2) ?Right now pg_migrator renames old tablespaces to .old, which fails
>> >> > if the tablespaces are on mount points. ?I have already received a
>> >> > report of such a failure.
>> >>
>> >> I thought it was impossible to use bare mountpoints as tablespaces due
>> >> to ownership problems ... Is that not the case? ?-1 for special hacks
>> >> that work around bogus setups, if that means intrusive changes to the
>> >> core code.
>> >
>> > I talked to the person who reported the problem and he and I confirmed
>> > that it is quite easy to make the mount point be owned by the postgres
>> > user and have that function as a tablespace. ?Is that not a supported
>> > setup? ?There is probably a larger problem that the tablespace must be
>> > located in a directory that has directory rename permission for
>> > postgres. ?I have updated the pg_migrator INSTALL file to mention this
>> > issue.
>> >
>> > As far as .old, we could create the tablespaces as *.new, but that kind
>> > of defeats the existing recommended pg_migrator usage where we tell the
>> > user to rename PGDATA to .old before running pg_migrator.
>> >
>> > It was actually Tom's idea months ago to put a version-specific
>> > directory in the tablespace. ?I don't think it is necessary, and we can
>> > live with the mount point limitation.
>>
>> What doesn't work if we just don't rename the tablespace at all?  And
>> can't we put some smarts into the backend to handle that thing?
>
> Well, when you restore the old dump's schema into the new server, the
> tablespace directory path will be the same, so we had better not have
> any directory there.

Well that seems like something you could work around by hacking the
contents of the dump...

...Robert

Robert Haas wrote:
> On Mon, Jan 4, 2010 at 2:52 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> > Robert Haas wrote:
> >> On Mon, Jan 4, 2010 at 2:11 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> >> > Alvaro Herrera wrote:
> >> >> > 2) ?Right now pg_migrator renames old tablespaces to .old, which fails
> >> >> > if the tablespaces are on mount points. ?I have already received a
> >> >> > report of such a failure.
> >> >>
> >> >> I thought it was impossible to use bare mountpoints as tablespaces due
> >> >> to ownership problems ... Is that not the case? ?-1 for special hacks
> >> >> that work around bogus setups, if that means intrusive changes to the
> >> >> core code.
> >> >
> >> > I talked to the person who reported the problem and he and I confirmed
> >> > that it is quite easy to make the mount point be owned by the postgres
> >> > user and have that function as a tablespace. ?Is that not a supported
> >> > setup? ?There is probably a larger problem that the tablespace must be
> >> > located in a directory that has directory rename permission for
> >> > postgres. ?I have updated the pg_migrator INSTALL file to mention this
> >> > issue.
> >> >
> >> > As far as .old, we could create the tablespaces as *.new, but that kind
> >> > of defeats the existing recommended pg_migrator usage where we tell the
> >> > user to rename PGDATA to .old before running pg_migrator.
> >> >
> >> > It was actually Tom's idea months ago to put a version-specific
> >> > directory in the tablespace. ?I don't think it is necessary, and we can
> >> > live with the mount point limitation.
> >>
> >> What doesn't work if we just don't rename the tablespace at all? ?And
> >> can't we put some smarts into the backend to handle that thing?
> >
> > Well, when you restore the old dump's schema into the new server, the
> > tablespace directory path will be the same, so we had better not have
> > any directory there.
>
> Well that seems like something you could work around by hacking the
> contents of the dump...

True, in --binary-upgrade mode, but what do we make it? *.new? What if
they want to have the same tablespace names after the upgrade? It
really gets ugly if we are on a mount point because the tablespaces will
be in different file systems, which makes --link mode impossible, and
might create files in a filesystem that doesn't have enough space.

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

+ If your life is a hard drive, Christ can be your backup. +

Bruce Momjian wrote:
> Alvaro Herrera wrote:
> > > 2) Right now pg_migrator renames old tablespaces to .old, which fails
> > > if the tablespaces are on mount points. I have already received a
> > > report of such a failure.
> >
> > I thought it was impossible to use bare mountpoints as tablespaces due
> > to ownership problems ... Is that not the case? -1 for special hacks
> > that work around bogus setups, if that means intrusive changes to the
> > core code.
>
> I talked to the person who reported the problem and he and I confirmed
> that it is quite easy to make the mount point be owned by the postgres
> user and have that function as a tablespace. Is that not a supported
> setup? There is probably a larger problem that the tablespace must be
> located in a directory that has directory rename permission for
> postgres. I have updated the pg_migrator INSTALL file to mention this
> issue.

Oh, the actual INSTALL warning is:

If you are using tablespaces, there must be sufficient directory
permissions to allow each tablespace directory to be renamed with a
".old" suffix.

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

+ If your life is a hard drive, Christ can be your backup. +

On Mon, Jan 4, 2010 at 3:33 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> Robert Haas wrote:
>> On Mon, Jan 4, 2010 at 2:52 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
>> > Robert Haas wrote:
>> >> On Mon, Jan 4, 2010 at 2:11 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
>> >> > Alvaro Herrera wrote:
>> >> >> > 2) ?Right now pg_migrator renames old tablespaces to .old, which fails
>> >> >> > if the tablespaces are on mount points. ?I have already received a
>> >> >> > report of such a failure.
>> >> >>
>> >> >> I thought it was impossible to use bare mountpoints as tablespaces due
>> >> >> to ownership problems ... Is that not the case? ?-1 for special hacks
>> >> >> that work around bogus setups, if that means intrusive changes to the
>> >> >> core code.
>> >> >
>> >> > I talked to the person who reported the problem and he and I confirmed
>> >> > that it is quite easy to make the mount point be owned by the postgres
>> >> > user and have that function as a tablespace. ?Is that not a supported
>> >> > setup? ?There is probably a larger problem that the tablespace must be
>> >> > located in a directory that has directory rename permission for
>> >> > postgres. ?I have updated the pg_migrator INSTALL file to mention this
>> >> > issue.
>> >> >
>> >> > As far as .old, we could create the tablespaces as *.new, but that kind
>> >> > of defeats the existing recommended pg_migrator usage where we tell the
>> >> > user to rename PGDATA to .old before running pg_migrator.
>> >> >
>> >> > It was actually Tom's idea months ago to put a version-specific
>> >> > directory in the tablespace. ?I don't think it is necessary, and we can
>> >> > live with the mount point limitation.
>> >>
>> >> What doesn't work if we just don't rename the tablespace at all? ?And
>> >> can't we put some smarts into the backend to handle that thing?
>> >
>> > Well, when you restore the old dump's schema into the new server, the
>> > tablespace directory path will be the same, so we had better not have
>> > any directory there.
>>
>> Well that seems like something you could work around by hacking the
>> contents of the dump...
>
> True, in --binary-upgrade mode, but what do we make it?  *.new?  What if
> they want to have the same tablespace names after the upgrade?  It
> really gets ugly if we are on a mount point because the tablespaces will
> be in different file systems, which makes --link mode impossible, and
> might create files in a filesystem that doesn't have enough space.

But can't we just call a special function first before running the
CREATE TABLESPACE, like:

pg_tablespace_dont_really_create_this_tablespace_because_we_are_in_pg_migrator()?

Sorta like what you did to preserve ENUM OIDs, etc.?

...Robert

Robert Haas wrote:
> >> >> What doesn't work if we just don't rename the tablespace at all? ?And
> >> >> can't we put some smarts into the backend to handle that thing?
> >> >
> >> > Well, when you restore the old dump's schema into the new server, the
> >> > tablespace directory path will be the same, so we had better not have
> >> > any directory there.
> >>
> >> Well that seems like something you could work around by hacking the
> >> contents of the dump...
> >
> > True, in --binary-upgrade mode, but what do we make it? ?*.new? ?What if
> > they want to have the same tablespace names after the upgrade? ?It
> > really gets ugly if we are on a mount point because the tablespaces will
> > be in different file systems, which makes --link mode impossible, and
> > might create files in a filesystem that doesn't have enough space.
>
> But can't we just call a special function first before running the
> CREATE TABLESPACE, like:
>
> pg_tablespace_dont_really_create_this_tablespace_because_we_are_in_pg_migrator()?
>
> Sorta like what you did to preserve ENUM OIDs, etc.?

Well, the problem is that we are creating something in a file system,
and the old and new contents of the tablespace directories must exist
after the migration (in case the migration is reverted). We were able
to get away with this for enum because we were only creating this in the
_new_ database. With the file system, we have a resource/namespace
shared between the old and new server.

What were you thinking this function call would do?

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

+ If your life is a hard drive, Christ can be your backup. +

On Mon, Jan 4, 2010 at 4:53 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> Robert Haas wrote:
>> >> >> What doesn't work if we just don't rename the tablespace at all? ?And
>> >> >> can't we put some smarts into the backend to handle that thing?
>> >> >
>> >> > Well, when you restore the old dump's schema into the new server, the
>> >> > tablespace directory path will be the same, so we had better not have
>> >> > any directory there.
>> >>
>> >> Well that seems like something you could work around by hacking the
>> >> contents of the dump...
>> >
>> > True, in --binary-upgrade mode, but what do we make it? ?*.new? ?What if
>> > they want to have the same tablespace names after the upgrade? ?It
>> > really gets ugly if we are on a mount point because the tablespaces will
>> > be in different file systems, which makes --link mode impossible, and
>> > might create files in a filesystem that doesn't have enough space.
>>
>> But can't we just call a special function first before running the
>> CREATE TABLESPACE, like:
>>
>> pg_tablespace_dont_really_create_this_tablespace_because_we_are_in_pg_migrator()?
>>
>> Sorta like what you did to preserve ENUM OIDs, etc.?
>
> Well, the problem is that we are creating something in a file system,
> and the old and new contents of the tablespace directories must exist
> after the migration (in case the migration is reverted).  We were able
> to get away with this for enum because we were only creating this in the
> _new_ database.  With the file system, we have a resource/namespace
> shared between the old and new server.

Oh, I thought you were hard-linking the files, not copying them, so
the old directory would be destroyed anyway.

> What were you thinking this function call would do?

Basically, make PostgreSQL not reinitialize the directory as it
normally would when a new tablespace is created. Or make it ignore
the existence of the directory until told to stop ignoring it. Or
whatever is needed to avoid having to move the thing around. Sorry,
I'm hand-waving wildly here; I haven't read the code. Maybe I should
shut up.

...Robert

Robert Haas wrote:
> >> But can't we just call a special function first before running the
> >> CREATE TABLESPACE, like:
> >>
> >> pg_tablespace_dont_really_create_this_tablespace_because_we_are_in_pg_migrator()?
> >>
> >> Sorta like what you did to preserve ENUM OIDs, etc.?
> >
> > Well, the problem is that we are creating something in a file system,
> > and the old and new contents of the tablespace directories must exist
> > after the migration (in case the migration is reverted). ?We were able
> > to get away with this for enum because we were only creating this in the
> > _new_ database. ?With the file system, we have a resource/namespace
> > shared between the old and new server.
>
> Oh, I thought you were hard-linking the files, not copying them, so
> the old directory would be destroyed anyway.

The default mode is COPY but there is a --link option. You are right
that if we only did linking things would be much simpler.

> > What were you thinking this function call would do?
>
> Basically, make PostgreSQL not reinitialize the directory as it
> normally would when a new tablespace is created. Or make it ignore
> the existence of the directory until told to stop ignoring it. Or
> whatever is needed to avoid having to move the thing around. Sorry,
> I'm hand-waving wildly here; I haven't read the code. Maybe I should
> shut up.

Sorry, I can't figure out how that would work.

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

+ If your life is a hard drive, Christ can be your backup. +

Bruce Momjian wrote:
> pg_migrator has become more popular recently, so it seems time to look
> at some enhancements that would improve pg_migrator. None of these are
> required, but rather changes that would be nice to have:
>
> 1) Right now pg_migrator preserves relfilenodes for TOAST files because
> this is required for proper migration. Now that we have shown that
> strategically-placed global variables with a server-side function to set
> them is a viable solution, it would be nice to preserve all relfilenodes
> from the old server. This would simplify pg_migrator by no long
> requiring place-holder relfilenodes or the renaming of TOAST files. A
> simpler solution would just be to allow TOAST table creation to
> automatically remove placeholder files and create specified relfilenodes
> via global variables.

Attached is a patch that implements #1 above by preserving all
relfilenodes, with pg_dump support. It uses the same method I used for
preserving pg_type/pg_enum. I have tested this on the regression
database and it successfully preserved all relfilenodes.

This patch also removes the 'force' parameter in toast functions that
Tom added for 8.4 --- it is no longer needed.

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

+ If your life is a hard drive, Christ can be your backup. +

Bruce Momjian wrote:
> pg_migrator has become more popular recently, so it seems time to look
> at some enhancements that would improve pg_migrator. None of these are
> required, but rather changes that would be nice to have:
>
> 1) Right now pg_migrator preserves relfilenodes for TOAST files because
> this is required for proper migration. Now that we have shown that
> strategically-placed global variables with a server-side function to set
> them is a viable solution, it would be nice to preserve all relfilenodes
> from the old server. This would simplify pg_migrator by no long
> requiring place-holder relfilenodes or the renaming of TOAST files. A
> simpler solution would just be to allow TOAST table creation to
> automatically remove placeholder files and create specified relfilenodes
> via global variables.
>
> 2) Right now pg_migrator renames old tablespaces to .old, which fails
> if the tablespaces are on mount points. I have already received a
> report of such a failure. $PGDATA also has that issue, but that
> renaming has to be done by the user before pg_migrator is run, and only
> if they want to keep the same $PGDATA value after migration, i.e. no
> version-specific directory path. One idea we floated around was to have
> tablespaces use major version directory names under the tablespace
> directory so renaming would not be necessary. I could implement a
> pg_migrator --delete-old flag to cleanly delete the old 8.4 server files
> which are not in a version-specific subdirectory.
>
> 3) There is no easy way to analyze all databases. vacuumdb --analyze
> does analyze _and_ vacuum, which for an 8.4 to 8.5 migration does an
> unnecessary vacuum. Right now I recommend ANALYZE in every database,
> but it would be nice if there were a single command which did this.
>
> 4) I have implemented the ability to run pg_migrator --check on a live
> old server. However, pg_migrator uses information from controldata to
> check things, and it also needs xid information that is only available
> via pg_resetxlog -n(no update) to perform the migration. Unfortunately,
> pg_resetxlog -n cannot be run on a live server, so pg_migrator runs
> pg_controldata for --check and pg_resetxlog -n for real upgrades. It
> would simplify pg_migrator if I would run pg_resetxlog -n on a live
> server, but I can understand if people don't want to do that because the
> xid information reported on a live server is inaccurate.

FYI, for those keeping score, I have posted patches for #1 and #3, and
we have decided not to implement #2 and #4.

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

+ If your life is a hard drive, Christ can be your backup. +

Bruce Momjian <bruce(at)momjian(dot)us> writes:
> Alvaro Herrera wrote:
>> I thought it was impossible to use bare mountpoints as tablespaces due
>> to ownership problems ... Is that not the case? -1 for special hacks
>> that work around bogus setups, if that means intrusive changes to the
>> core code.

> I talked to the person who reported the problem and he and I confirmed
> that it is quite easy to make the mount point be owned by the postgres
> user and have that function as a tablespace. Is that not a supported
> setup?

It might be *possible*, but that doesn't make it a good idea. The
traditional sysadmin advice in this area is that mount points should
be owned by root. I don't really remember the reasoning but I'm pretty
sure I remember the principle.

> It was actually Tom's idea months ago to put a version-specific
> directory in the tablespace.

I was just about to re-suggest that. Why do you think it's such a
bad idea?

regards, tom lane

Bruce Momjian <bruce(at)momjian(dot)us> writes:
> Well, when you restore the old dump's schema into the new server, the
> tablespace directory path will be the same, so we had better not have
> any directory there.

If we implicitly added "/8.5" etc at the end of the specified tablespace
path, the problems go away.

regards, tom lane

Tom Lane wrote:
> Bruce Momjian <bruce(at)momjian(dot)us> writes:
> > Alvaro Herrera wrote:
> >> I thought it was impossible to use bare mountpoints as tablespaces due
> >> to ownership problems ... Is that not the case? -1 for special hacks
> >> that work around bogus setups, if that means intrusive changes to the
> >> core code.
>
> > I talked to the person who reported the problem and he and I confirmed
> > that it is quite easy to make the mount point be owned by the postgres
> > user and have that function as a tablespace. Is that not a supported
> > setup?
>
> It might be *possible*, but that doesn't make it a good idea. The
> traditional sysadmin advice in this area is that mount points should
> be owned by root. I don't really remember the reasoning but I'm pretty
> sure I remember the principle.

Yea, I think the logic is that files under that directory disappear
after the mount, so you don't want users putting things in there
accidentally. In fact, the user said they mount a pg_xlog directory
under the $PGDATA directory (rather than use a symlink), which I also
thought was an odd approach and prone to problems if the mount failed
intermittently.

> > It was actually Tom's idea months ago to put a version-specific
> > directory in the tablespace.
>
> I was just about to re-suggest that. Why do you think it's such a
> bad idea?

I liked the idea, but I listed it as item #2 and no one else said they
liked it. The only complexity I can see with the idea is that doing an
upgrade from one alpha to another would have the same major version
number and would therefore not be possible, so maybe we have to use the
catalog version number in there.

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

+ If your life is a hard drive, Christ can be your backup. +

Bruce Momjian <bruce(at)momjian(dot)us> writes:
> I liked the idea, but I listed it as item #2 and no one else said they
> liked it. The only complexity I can see with the idea is that doing an
> upgrade from one alpha to another would have the same major version
> number and would therefore not be possible, so maybe we have to use the
> catalog version number in there.

Good point. Using catversion for the purpose seems a bit ugly but
I have no better ideas.

regards, tom lane

Tom Lane wrote:
> Bruce Momjian <bruce(at)momjian(dot)us> writes:
> > I liked the idea, but I listed it as item #2 and no one else said they
> > liked it. The only complexity I can see with the idea is that doing an
> > upgrade from one alpha to another would have the same major version
> > number and would therefore not be possible, so maybe we have to use the
> > catalog version number in there.
>
> Good point. Using catversion for the purpose seems a bit ugly but
> I have no better ideas.

I thought we had rejected the idea of being able to migrate between
alphas. Is migrating between major versions not difficult enough?

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

On Tue, Jan 5, 2010 at 11:06 AM, Alvaro Herrera
<alvherre(at)commandprompt(dot)com> wrote:
> Tom Lane wrote:
>> Bruce Momjian <bruce(at)momjian(dot)us> writes:
>> > I liked the idea, but I listed it as item #2 and no one else said they
>> > liked it.  The only complexity I can see with the idea is that doing an
>> > upgrade from one alpha to another would have the same major version
>> > number and would therefore not be possible, so maybe we have to use the
>> > catalog version number in there.
>>
>> Good point.  Using catversion for the purpose seems a bit ugly but
>> I have no better ideas.
>
> I thought we had rejected the idea of being able to migrate between
> alphas.  Is migrating between major versions not difficult enough?

We like a challenge.

...Robert

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> On Tue, Jan 5, 2010 at 11:06 AM, Alvaro Herrera
> <alvherre(at)commandprompt(dot)com> wrote:
>> Tom Lane wrote:
>>> Good point. Using catversion for the purpose seems a bit ugly but
>>> I have no better ideas.
>>
>> I thought we had rejected the idea of being able to migrate between
>> alphas. Is migrating between major versions not difficult enough?

> We like a challenge.

The problem with using just major version there is that then we are
*wiring into the on-disk representation* the assumption that pg_migrator
only goes from one major version to the next. I agree that we're not
likely to start supporting cross-alpha-version migration any time soon,
but I don't think it's wise to foreclose the possibility of ever doing
it.

regards, tom lane

Tom Lane wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> > On Tue, Jan 5, 2010 at 11:06 AM, Alvaro Herrera
> > <alvherre(at)commandprompt(dot)com> wrote:
> >> Tom Lane wrote:
> >>> Good point. Using catversion for the purpose seems a bit ugly but
> >>> I have no better ideas.
> >>
> >> I thought we had rejected the idea of being able to migrate between
> >> alphas. Is migrating between major versions not difficult enough?
>
> > We like a challenge.
>
> The problem with using just major version there is that then we are
> *wiring into the on-disk representation* the assumption that pg_migrator
> only goes from one major version to the next. I agree that we're not
> likely to start supporting cross-alpha-version migration any time soon,
> but I don't think it's wise to foreclose the possibility of ever doing
> it.

I know people are trying to make things easier on pg_migrator, but
frankly going from alpha to alpha does not require any new code in
pg_migrator. And pg_migrator already supports cross-alpha-version
migration just using the existing code --- no new code was added to
enable this.

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

+ If your life is a hard drive, Christ can be your backup. +

Alvaro Herrera wrote:
> Bruce Momjian wrote:
> > pg_migrator has become more popular recently, so it seems time to look
> > at some enhancements that would improve pg_migrator. None of these are
> > required, but rather changes that would be nice to have:
> >
> > 1) Right now pg_migrator preserves relfilenodes for TOAST files because
> > this is required for proper migration. Now that we have shown that
> > strategically-placed global variables with a server-side function to set
> > them is a viable solution, it would be nice to preserve all relfilenodes
> > from the old server. This would simplify pg_migrator by no long
> > requiring place-holder relfilenodes or the renaming of TOAST files. A
> > simpler solution would just be to allow TOAST table creation to
> > automatically remove placeholder files and create specified relfilenodes
> > via global variables.
>
> Getting rid of the need for placeholders is a good idea. +1 on getting
> TOAST tables created with the correct relfilenode from the start. I
> don't know that preserving any other relfilenode is useful; however if
> it means you no longer have to rename the files underlying each table,

--> > it would probably also be a good idea. (I don't know how does
--> > pg_migrator deal with such things currently -- does it keep a map of
--> > table name to relfilenode?)

Yes, and it will still need that because we don't want to transfer over
any of the system tables or pg_catalog files.

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

+ If your life is a hard drive, Christ can be your backup. +

Bruce Momjian wrote:
> Bruce Momjian wrote:
> > pg_migrator has become more popular recently, so it seems time to look
> > at some enhancements that would improve pg_migrator. None of these are
> > required, but rather changes that would be nice to have:
> >
> > 1) Right now pg_migrator preserves relfilenodes for TOAST files because
> > this is required for proper migration. Now that we have shown that
> > strategically-placed global variables with a server-side function to set
> > them is a viable solution, it would be nice to preserve all relfilenodes
> > from the old server. This would simplify pg_migrator by no long
> > requiring place-holder relfilenodes or the renaming of TOAST files. A
> > simpler solution would just be to allow TOAST table creation to
> > automatically remove placeholder files and create specified relfilenodes
> > via global variables.
>
> Attached is a patch that implements #1 above by preserving all
> relfilenodes, with pg_dump support. It uses the same method I used for
> preserving pg_type/pg_enum. I have tested this on the regression
> database and it successfully preserved all relfilenodes.
>
> This patch also removes the 'force' parameter in toast functions that
> Tom added for 8.4 --- it is no longer needed.

Applied.

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

+ If your life is a hard drive, Christ can be your backup. +

Bruce Momjian wrote:
> Alvaro Herrera wrote:
> > > 3) There is no easy way to analyze all databases. vacuumdb --analyze
> > > does analyze _and_ vacuum, which for an 8.4 to 8.5 migration does an
> > > unnecessary vacuum. Right now I recommend ANALYZE in every database,
> > > but it would be nice if there were a single command which did this.
> >
> > +1 for vacuumdb --analyze-only
>
> OK, I have implemented this using --only-analyze to avoid having the
> '--anal' option spelling be ambiguous, which might confuse/frustrate
> users.
>
> I also moved the --freeze option documention mention into a more logical
> place.
>
> Patch attached.

Applied.

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

+ If your life is a hard drive, Christ can be your backup. +

On Dec 30, 2009, at 9:50 PM, Bruce Momjian wrote:
> 3) There is no easy way to analyze all databases. vacuumdb --analyze
> does analyze _and_ vacuum, which for an 8.4 to 8.5 migration does an
> unnecessary vacuum. Right now I recommend ANALYZE in every database,
> but it would be nice if there were a single command which did this.

I actually started on a patch for this (http://lnk.nu/archives.postgresql.org/14rm.php). IIRC it's pretty close, I just haven't had time to come back to it for final cleanup and changing the docs as needed.
--
Jim C. Nasby, Database Architect jim(at)nasby(dot)net
512.569.9461 (cell) http://jim.nasby.net

On Jan 6, 2010, at 1:52 AM, decibel wrote:
> On Dec 30, 2009, at 9:50 PM, Bruce Momjian wrote:
>> 3) There is no easy way to analyze all databases. vacuumdb --analyze
>> does analyze _and_ vacuum, which for an 8.4 to 8.5 migration does an
>> unnecessary vacuum. Right now I recommend ANALYZE in every database,
>> but it would be nice if there were a single command which did this.
>
> I actually started on a patch for this (http://lnk.nu/archives.postgresql.org/14rm.php). IIRC it's pretty close, I just haven't had time to come back to it for final cleanup and changing the docs as needed.

Crap, I see I should have read the whole thread before posting. Sorry for the noise (and not getting the patch completed). :(
--
Jim C. Nasby, Database Architect jim(at)nasby(dot)net
512.569.9461 (cell) http://jim.nasby.net

Dne 4.01.10 19:28, Alvaro Herrera napsal(a):
> Bruce Momjian escribió:
>
>> I considered that but realize that pg_migrator has to read
>> pg_controldata in both the old and new servers, meaning it would need
>> access to both C structures, and considering they both have the same
>> structure names, that would require some odd C tricks. Add to that you
>> don't know which version of Postgres you are migrating from/to during
>> compile and the idea of using C becomes even less attractive.
>
> However, keep in mind that this might not be the last time on which we
> will want to read something from a C struct, so perhaps it would be good
> to bite the bullet and write the odd tricks. Does it already have
> access (at compile time) to the old and new source trees?

I have some proof of concept when each control data struct version
version have one header file like pg_control_843.h and structure like
ControlFileData has name ControlFileData_843. The main pg_control.h
defines types without version like

typedef ControlFileData_843 ControlFileData;

I planed to do it for 8.5 but unfortunately no time :( commit fest is
too close.

Zdenek

Zdenek Kotala wrote:
> Dne 4.01.10 19:28, Alvaro Herrera napsal(a):
> > Bruce Momjian escribi?:
> >
> >> I considered that but realize that pg_migrator has to read
> >> pg_controldata in both the old and new servers, meaning it would need
> >> access to both C structures, and considering they both have the same
> >> structure names, that would require some odd C tricks. Add to that you
> >> don't know which version of Postgres you are migrating from/to during
> >> compile and the idea of using C becomes even less attractive.
> >
> > However, keep in mind that this might not be the last time on which we
> > will want to read something from a C struct, so perhaps it would be good
> > to bite the bullet and write the odd tricks. Does it already have
> > access (at compile time) to the old and new source trees?
>
> I have some proof of concept when each control data struct version
> version have one header file like pg_control_843.h and structure like
> ControlFileData has name ControlFileData_843. The main pg_control.h
> defines types without version like
>
> typedef ControlFileData_843 ControlFileData;
>
> I planed to do it for 8.5 but unfortunately no time :( commit fest is
> too close.

Yea, I think the confusion of doing it isn't worth it.

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

+ If your life is a hard drive, Christ can be your backup. +

Bruce Momjian wrote:
> 2) Right now pg_migrator renames old tablespaces to .old, which fails
> if the tablespaces are on mount points. I have already received a
> report of such a failure. $PGDATA also has that issue, but that
> renaming has to be done by the user before pg_migrator is run, and only
> if they want to keep the same $PGDATA value after migration, i.e. no
> version-specific directory path. One idea we floated around was to have
> tablespaces use major version directory names under the tablespace
> directory so renaming would not be necessary. I could implement a
> pg_migrator --delete-old flag to cleanly delete the old 8.4 server files
> which are not in a version-specific subdirectory.

FYI, pg_migrator CVS now uses the relfilenode preservation ability in
8.5 to avoid the creation of placeholder relfilenodes and renaming. It
also recommends using vacuumdb --only-analyze.

To simplify pg_migrator, you can now only migrate _to_ 8.5 as of today's
CVS, not earlier 8.5 CVS trees. One interesting aspect of pg_migrator
is that while it has to carry around support for upgrading _from_ many
old releases, the target/to version support can stay limited, i.e. I
doubt anyone is going to want to use pg_migrator to migrate to 8.4 in a
year or two --- they will be migrating to 8.5. We can also consider
removing 8.4 target migration support in pg_migrator 8.5 and just tell
people they have to use pg_migrator 8.4.X for a migration to PG 8.4.X.

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

+ If your life is a hard drive, Christ can be your backup. +

On mån, 2010-01-04 at 13:07 -0500, Bruce Momjian wrote:
> Yea, I am not excited about having vacuumdb do only analyze, but it
> seems the most minimal solution. I spelled it --only-analyze and just
> posted the reason and patch.

I can't find the patch and the reason, but note that we already have
other options like --data-only, --schema-only, --tuples-only. I
personally don't like the spelling of --only-analyze.

Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> On mn, 2010-01-04 at 13:07 -0500, Bruce Momjian wrote:
>> Yea, I am not excited about having vacuumdb do only analyze, but it
>> seems the most minimal solution. I spelled it --only-analyze and just
>> posted the reason and patch.

> I can't find the patch and the reason, but note that we already have
> other options like --data-only, --schema-only, --tuples-only. I
> personally don't like the spelling of --only-analyze.

In particular note that pg_dump has options --schema and --schema-only,
and nobody has complained about that. I concur with Peter that this
spelling is gratuitously unlike everyplace else.

Perhaps we could compromise by calling it "--no-vacuum", instead?

regards, tom lane

Tom Lane wrote:
> Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> > On mn, 2010-01-04 at 13:07 -0500, Bruce Momjian wrote:
> >> Yea, I am not excited about having vacuumdb do only analyze, but it
> >> seems the most minimal solution. I spelled it --only-analyze and just
> >> posted the reason and patch.
>
> > I can't find the patch and the reason, but note that we already have
> > other options like --data-only, --schema-only, --tuples-only. I
> > personally don't like the spelling of --only-analyze.
>
> In particular note that pg_dump has options --schema and --schema-only,
> and nobody has complained about that. I concur with Peter that this
> spelling is gratuitously unlike everyplace else.

Oh, interesting about pg_dump. Let's just go with --analyze-only.
--only-analyze is feeling odd to me too.

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

+ If your life is a hard drive, Christ can be your backup. +

> Tom Lane wrote:
> > Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> > > On m<C3><A5>n, 2010-01-04 at 13:07 -0500, Bruce Momjian wrote:
> > >> Yea, I am not excited about having vacuumdb do only analyze, but it
> > >> seems the most minimal solution. I spelled it --only-analyze and just
> > >> posted the reason and patch.
> >
> > > I can't find the patch and the reason, but note that we already have
> > > other options like --data-only, --schema-only, --tuples-only. I
> > > personally don't like the spelling of --only-analyze.
> >
> > In particular note that pg_dump has options --schema and --schema-only,
> > and nobody has complained about that. I concur with Peter that this
> > spelling is gratuitously unlike everyplace else.
>
> Oh, interesting about pg_dump. Let's just go with --analyze-only.
> --only-analyze is feeling odd to me too.

Done, attached and applied.

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

+ If your life is a hard drive, Christ can be your backup. +

Bruce Momjian escribió:

> > Oh, interesting about pg_dump. Let's just go with --analyze-only.
> > --only-analyze is feeling odd to me too.
>
> Done, attached and applied.

<bikeshedding>
Why -o and not, say, -Z? I imagine you picked -o for "only" but it
seems strange.
</>

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

Alvaro Herrera wrote:
> Bruce Momjian escribi?:
>
> > > Oh, interesting about pg_dump. Let's just go with --analyze-only.
> > > --only-analyze is feeling odd to me too.
> >
> > Done, attached and applied.
>
> <bikeshedding>
> Why -o and not, say, -Z? I imagine you picked -o for "only" but it
> seems strange.
> </>

Hmmm, sure -Z makes sense. Change applied with attached patch. Thanks.

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

+ If your life is a hard drive, Christ can be your backup. +

Bruce Momjian wrote:
> 2) Right now pg_migrator renames old tablespaces to .old, which fails
> if the tablespaces are on mount points. I have already received a
> report of such a failure. $PGDATA also has that issue, but that
> renaming has to be done by the user before pg_migrator is run, and only
> if they want to keep the same $PGDATA value after migration, i.e. no
> version-specific directory path. One idea we floated around was to have
> tablespaces use major version directory names under the tablespace
> directory so renaming would not be necessary. I could implement a
> pg_migrator --delete-old flag to cleanly delete the old 8.4 server files
> which are not in a version-specific subdirectory.

I have created a patch to implement per-cluster directories in
tablespaces. This is for use by pg_migrator so it doesn't have to
rename the tablespaces during the migration. Users still need to remove
the old cluster's tablespace subdirectory, and I can add a --delete-old
option to pg_migrator to do that.

The old code used a symlink from pg_tblspc/#### to the location
directory specified in CREATE TABLESPACE. During CREATE TABLESPACE, a
PG_VERSION file is created containing the major version number. Anytime
a database object is created in the tablespace, a per-database directory
is created.

With the new code in this patch, pg_tblspc/#### points to the CREATE
TABLESPACE directory just like before, but a new directory, PG_ +
major_version + catalog_version, e.g. PG_8.5_201001061, is created and
all per-database directories are created under that directory. This
directory has the same purpose as the old PG_VERSION file. One
disadvantage of this approach is that functions that need to look inside
tablespaces must now also specify the version directory, e.g.
pg_tablespace_databases().

An alternative approach would be for the pg_tblspc/#### symbolic link to
point to the new version directory, PG_*, but that makes removal of the
version directory complicated, particularly during WAL replay where we
don't have access to the system catalogs, and readlink() to read the
symbolic link target is not supported on all operating systems
(particularly Win32).

I used the version directory pattern "PG_8.5_201001061" because "PG_"
helps people realize the directory is for the use of Postgres
(PG_VERSION is gone in tablespaces), and the catalog version number
enables alpha migrations. The major version number is not necessary but
probably useful for administrators.

pg_migrator is going to need to know about the version directory too,
and it can't use the C macro --- it has to construct the directory
pattern based on the contents of pg_control from the old and new
servers. And, it is going to be difficult to run pg_control on the old
server for pg_migrator --delete-old after migration because it is
renamed to pg_control.old --- I will need to create a symbolic link
during the time I run pg_controldata. Also, the contents of the
tablespace directory for an 8.4 to 8.5 migration is going to be ugly
because there will be many numeric directories (for databases), and
PG_VERSION (for 8.4), and the PG_8.5_201001061 directory which should
not be touched.

Can someone explain why TablespaceCreateDbspace() creates a non-symlink
directory during recovery if the symlink is missing? Is it just for
robustness? I would like to document that more clearly.

Comments?

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

+ If your life is a hard drive, Christ can be your backup. +

Bruce Momjian wrote:
> Bruce Momjian wrote:
> > 2) Right now pg_migrator renames old tablespaces to .old, which fails
> > if the tablespaces are on mount points. I have already received a
> > report of such a failure. $PGDATA also has that issue, but that
> > renaming has to be done by the user before pg_migrator is run, and only
> > if they want to keep the same $PGDATA value after migration, i.e. no
> > version-specific directory path. One idea we floated around was to have
> > tablespaces use major version directory names under the tablespace
> > directory so renaming would not be necessary. I could implement a
> > pg_migrator --delete-old flag to cleanly delete the old 8.4 server files
> > which are not in a version-specific subdirectory.
>
> I have created a patch to implement per-cluster directories in
> tablespaces. This is for use by pg_migrator so it doesn't have to
> rename the tablespaces during the migration. Users still need to remove
> the old cluster's tablespace subdirectory, and I can add a --delete-old
> option to pg_migrator to do that.
>
> The old code used a symlink from pg_tblspc/#### to the location
> directory specified in CREATE TABLESPACE. During CREATE TABLESPACE, a
> PG_VERSION file is created containing the major version number. Anytime
> a database object is created in the tablespace, a per-database directory
> is created.
>
> With the new code in this patch, pg_tblspc/#### points to the CREATE
> TABLESPACE directory just like before, but a new directory, PG_ +
> major_version + catalog_version, e.g. PG_8.5_201001061, is created and
> all per-database directories are created under that directory. This
> directory has the same purpose as the old PG_VERSION file. One
> disadvantage of this approach is that functions that need to look inside
> tablespaces must now also specify the version directory, e.g.
> pg_tablespace_databases().
>
> An alternative approach would be for the pg_tblspc/#### symbolic link to
> point to the new version directory, PG_*, but that makes removal of the
> version directory complicated, particularly during WAL replay where we
> don't have access to the system catalogs, and readlink() to read the
> symbolic link target is not supported on all operating systems
> (particularly Win32).
>
> I used the version directory pattern "PG_8.5_201001061" because "PG_"
> helps people realize the directory is for the use of Postgres
> (PG_VERSION is gone in tablespaces), and the catalog version number
> enables alpha migrations. The major version number is not necessary but
> probably useful for administrators.
>
> pg_migrator is going to need to know about the version directory too,
> and it can't use the C macro --- it has to construct the directory
> pattern based on the contents of pg_control from the old and new
> servers. And, it is going to be difficult to run pg_control on the old
> server for pg_migrator --delete-old after migration because it is
> renamed to pg_control.old --- I will need to create a symbolic link
> during the time I run pg_controldata. Also, the contents of the
> tablespace directory for an 8.4 to 8.5 migration is going to be ugly
> because there will be many numeric directories (for databases), and
> PG_VERSION (for 8.4), and the PG_8.5_201001061 directory which should
> not be touched.
>
> Can someone explain why TablespaceCreateDbspace() creates a non-symlink
> directory during recovery if the symlink is missing? Is it just for
> robustness? I would like to document that more clearly.

Applied.

FYI, I decide to create a pg_migrator_remove_old_cluster.sh/.bat file
that can be run by the user after the upgrade, instead of adding a
--delete-old-cluster option to pg_migrator.

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

+ If your life is a hard drive, Christ can be your backup. +

FYI, I consider all the issues below to be addressed (we did all but
#4), and pg_migrator will take advantage of these new facilities for 8.5.

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

Bruce Momjian wrote:
> pg_migrator has become more popular recently, so it seems time to look
> at some enhancements that would improve pg_migrator. None of these are
> required, but rather changes that would be nice to have:
>
> 1) Right now pg_migrator preserves relfilenodes for TOAST files because
> this is required for proper migration. Now that we have shown that
> strategically-placed global variables with a server-side function to set
> them is a viable solution, it would be nice to preserve all relfilenodes
> from the old server. This would simplify pg_migrator by no long
> requiring place-holder relfilenodes or the renaming of TOAST files. A
> simpler solution would just be to allow TOAST table creation to
> automatically remove placeholder files and create specified relfilenodes
> via global variables.
>
> 2) Right now pg_migrator renames old tablespaces to .old, which fails
> if the tablespaces are on mount points. I have already received a
> report of such a failure. $PGDATA also has that issue, but that
> renaming has to be done by the user before pg_migrator is run, and only
> if they want to keep the same $PGDATA value after migration, i.e. no
> version-specific directory path. One idea we floated around was to have
> tablespaces use major version directory names under the tablespace
> directory so renaming would not be necessary. I could implement a
> pg_migrator --delete-old flag to cleanly delete the old 8.4 server files
> which are not in a version-specific subdirectory.
>
> 3) There is no easy way to analyze all databases. vacuumdb --analyze
> does analyze _and_ vacuum, which for an 8.4 to 8.5 migration does an
> unnecessary vacuum. Right now I recommend ANALYZE in every database,
> but it would be nice if there were a single command which did this.
>
> 4) I have implemented the ability to run pg_migrator --check on a live
> old server. However, pg_migrator uses information from controldata to
> check things, and it also needs xid information that is only available
> via pg_resetxlog -n(no update) to perform the migration. Unfortunately,
> pg_resetxlog -n cannot be run on a live server, so pg_migrator runs
> pg_controldata for --check and pg_resetxlog -n for real upgrades. It
> would simplify pg_migrator if I would run pg_resetxlog -n on a live
> server, but I can understand if people don't want to do that because the
> xid information reported on a live server is inaccurate.
>
> Comments?
>
> --
> Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
> EnterpriseDB http://enterprisedb.com
>
> + If your life is a hard drive, Christ can be your backup. +
>
> --
> 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

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

+ If your life is a hard drive, Christ can be your backup. +