Re: Tracking structural changes from psql

Mike McGavin <jester(at)NOSPAM(dot)mcsnospam(dot)vuw(dot)acNOSPAM(dot)nz> writes:

> Hi everyone.
>
> I'm searching for a quick and dirty way to have psql record the SQL
> statements that I enter, especially those related to the database structure.

Well the server logs all that information.
log_statement = true

IIRC in CVS this has even been separated into two options for ddl and dml.

> It also occurs to me that an even more useful utility might be one that stores
> the structural state of the database at a particular time (such as when I last
> updated the production server), and then generate a diff of SQL statements to
> update it to the current structural state. I don't suppose this already exists
> anywhere, does it?

You can pg_dump -s the two and diff them. You'll find the OIDs in SQL comments
which throw off the diff. I have a little sed line that strips them out.

And the objects are printed in creation order, so if you created the objects
in different orders on the two servers you'll get spurious differences.

I believe both of these issues are improved in the current CVS tree. You
should be able to build from CVS and use that pg_dump against your current
server though.

Alternatively you could look at Alzabo which I understand can do schema diffs
and generate scripts to sync schemas. Haven't tried it though.

--
greg