pg_migrator issues
in [PgSql]
|
From: Bruce Momjian on 3 Jan 2010 17:43 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(a)momjian.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(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Robert Haas on 3 Jan 2010 22:17 Bruce, Sorry for not having replied sooner... On Sun, Jan 3, 2010 at 5:43 PM, Bruce Momjian <bruce(a)momjian.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Alvaro Herrera on 4 Jan 2010 10:14 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. -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Tom Lane on 4 Jan 2010 10:18 Alvaro Herrera <alvherre(a)commandprompt.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Bruce Momjian on 4 Jan 2010 13:05
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(a)momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + |