pg_migrator issues
in [PgSql]
|
From: Robert Haas on 4 Jan 2010 17:04 On Mon, Jan 4, 2010 at 4:53 PM, Bruce Momjian <bruce(a)momjian.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 -- 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 17:14 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(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 22:28 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(a)momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
From: Bruce Momjian on 4 Jan 2010 22:30 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(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: Tom Lane on 5 Jan 2010 00:20
Bruce Momjian <bruce(a)momjian.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers |