pg_migrator issues
in [PgSql]
|
From: Bruce Momjian on 4 Jan 2010 13:07 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(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: Bruce Momjian on 4 Jan 2010 13:12 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(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: Alvaro Herrera on 4 Jan 2010 13:28 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 -- 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:51 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(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: Bruce Momjian on 4 Jan 2010 13:53
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(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 |