[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

Re: upgrades and database integrity.

Hi John;

I had a slightly different approach but conceptually similar.

On Fri, Jan 8, 2010 at 2:31 PM, John Locke <..hidden..> wrote:

I'm with Alvin on the upgrade--upgrading in place gives me the
willies... would prefer to migrate my data into a clean install.

What I am thinking is ALTER SCHEMA PUBLIC RENAME TO lsmb_pre_1_3;

(load all the schema scripts)

INSERT INTO FOO select * from foo (where the table is unchanged)....

This approach is nice because you can, within the same db session, check both post- and pre-migration data and decide how many past versions you want to keep around.  This is also the approach I have taken with the sl 2.8 migration scripts which are partially complete at this point as well.

Have the dump script do some integrity checking, and log any problems it
finds with duplicated transaction ids and other collisions--after
running each dump, group by something that should be unique and log any
ids that show up more than once.

Then the user can edit the raw data in a spreadsheet, guided by problems
found in the log. Don't try to automatically repair any problems--just
identify them where we can.

What's the row limit of a typical spreadsheet program?  Keep in mind that there are several instances of LSMB installed where acc_trans has millions of rows....

And then we can write an import script/stored functions to store these
rows in the right tables.

If I get a chance to work on any of these soon, I'll push them up to our
public git repo and let the list know... And yes, some funding might
help that happen quicker ;-)

If anyone wants to fund this, I suggest funding with John as I have a full load at the moment :-).

Best Wishes,
Chris Travers