Re: Database updates

[John Locke <..hidden..>]

On 07/14/2011 02:42 PM, Chris Travers wrote:
> On Thu, Jul 14, 2011 at 2:31 PM, John Locke <..hidden..> wrote:
>> Chris Travers wrote:
>>> Secondly, a tool to compare schemas will be helpful, and I would like
>>> to create one in the near future.  in the mean time, as a simple
>>> discussion draft, let me post the following bash code.
>>>
>>> rm ../testdump1.sql ../testdump2.sql
>>> for line in `grep -i "^create table" sql/Pg-database.sql | sed -e
>>> 's/(.*$//' | awk '{ print $3 }'`
>>>     do pg_dump -U postgres -s -t $line database1 | grep -v GRANT |
>>> grep -v REVOKE >> ../testdump1.sql
>>> done
>>>
>>> for line in `grep -i "^create table" sql/Pg-database.sql | sed -e
>>> 's/(.*$//' | awk '{ print $3 }'`
>>>     do pg_dump -U postgres -s -t $line database2 | grep -v GRANT |
>>> grep -v REVOKE >> ../testdump2.sql
>>> done
>>>
>>> diff ../testdump1.sql ../testdump2.sql
>>>
>>> If this sort of result is found to be acceptable, I could probably
>>> port the first two parts to Perl pretty easily.  Not sure about diffs
>>> though.
>> That sounds like a good start.
>>
>> I'm thinking, though, that Pg-database.sql is still too big and
>> monolithic for this -- and the resulting dump file ought to be where you
>> do the initial schema load, don't you think?
> Ok, so if we are dealing with a single database server, we could I
> suppose do a load, and then dump schema dump
> and then compare against a schema dump from production (filtering out
> set commands and comments)

Is that a built-in PG command?

> That won't give you everything....  For example menu changes...... But
> with menu changes, if you have any custom entries (which are often
> created by addons), you can't simply import the different records, so
> the update scripts have to be relied on there.  And typically the way
> this works is to run the update scripts ona new database, then dump
> the tables and copy them back into the Pg-database.
>

Right. We still need the update scripts...
>
>> Maybe have a separate file, sort of like an ini file or parsable
>> "loadorder" that can identify the type of relation and the name. e.g.
> I would REALLY like to avoid having multiple authoritative sources of
> the database schema if possible.  It's very easy for those to get out
> of sync.

I'm not suggesting storing the schema in such a file, just a list of
tables in an order they can be created that will load successfully
(taking into account foreign keys, types, etc).
>> table acc_trans
>> table account
>> type xxxx_xx_xx
>> view chart
>>
>> ... etc.
>>
>> This could then be fed to the dump script, instead of parsing
>> Pg-database.sql...
> If that's what we want., I think we'd want to automatically generate
> it from the SQL files in question rather than maintain it separately.
>

Yes, that's the whole point of having a separate file listing this stuff
-- so the base schema can be generated automatically. And re-generated
to easily compare against what it should be.

Essentially that file replaces Pg-database.sql. But we need to keep the
data out of it, because that data may get changed after it's already in
the database, making the schema dump inconsistent/not-comparable.

So I'm suggesting:

1. Schema index file, parsed by Perl/Bash/whatever
2. base schema sql file, auto-generated from a clean install by a script
that parses #1
3. base sql data file, to populate menu tables, etc.
>> Then we can have the tables ordered in a way that will actually load
>> with foreign keys to tables previously defined, etc.
>>
>> And as long as that's consistent, then I think diff is fine for now for
>> seeing what's changed... can just run the dump script, direct the output
>> to a tmp file, and compare against the one in the code tree.
> Also there is a second issue here......
>
> Column ordering is not guaranteed when alter table commands are used.
> So no matter how we do it, there will be false positives that have to
> be reviewed and dismissed.

Hmm. Is there any way in Postgres to define column ordering via an alter
statement? This is something MySQL supports ;-)

This might be an issue for developers, but for regular users, I think we
just need to make sure we don't change the update scripts after creating
them -- as long as people follow the upgrade procedures, wouldn't new
columns get added to the end of the table? Or does this vary by Pg version?

Cheers,
John Locke
http://www.freelock.com