Thread: Tracking structural changes from psql

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.

My main motivation is to help keep what will probably be a production
server up-to-date with my development server.  I've thought a little
about replication, but the current options for that seem like overkill
for the relatively small database that I have.  I'm particularly
interested in tracking data-definition related statements, which I
mostly tend to run through psql.  (eg. Creating and altering objects,
plus the occasional insert and update thrown in.)


psql supports a couple of output-to-file options, but apparently not
really for what I want.  eg.  \o will output query results to a file,
and \w will save the current query buffer to a file.

What I'd really like is to have the commands that I execute logged to a
file semi-automatically as I execute them, without having to remember to
save it afterwards prior to closing psql.  The occasional inconsistency
won't be too important because I'll probably review it before actually
using it, but simply having an output file that contains a history of
sql statements, perhaps with commented datestamps, would be quite useful.

If anyone with some experience could point me to a simple way to do
this, I'd appreciate it.  Have I overlooked anything in particular?


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?


Thanks for any help.
Mike.

Mike McGavin <jester@NOSPAM.mcsnospam.vuw.acNOSPAM.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

Check out your .psql_history file and \s <filename> from within psql.

Robert Treat

On Sat, 2004-05-01 at 23:53, Mike McGavin wrote:
> 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.
>
> My main motivation is to help keep what will probably be a production
> server up-to-date with my development server.  I've thought a little
> about replication, but the current options for that seem like overkill
> for the relatively small database that I have.  I'm particularly
> interested in tracking data-definition related statements, which I
> mostly tend to run through psql.  (eg. Creating and altering objects,
> plus the occasional insert and update thrown in.)
>
>
> psql supports a couple of output-to-file options, but apparently not
> really for what I want.  eg.  \o will output query results to a file,
> and \w will save the current query buffer to a file.
>
> What I'd really like is to have the commands that I execute logged to a
> file semi-automatically as I execute them, without having to remember to
> save it afterwards prior to closing psql.  The occasional inconsistency
> won't be too important because I'll probably review it before actually
> using it, but simply having an output file that contains a history of
> sql statements, perhaps with commented datestamps, would be quite useful.
>
> If anyone with some experience could point me to a simple way to do
> this, I'd appreciate it.  Have I overlooked anything in particular?
>
>
> 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?
>
>
> Thanks for any help.
> Mike.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL

Mike McGavin <jester@NOSPAM.mcsnospam.vuw.acNOSPAM.nz> writes:

> 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 might want to try:

http://zongle.sourceforge.net/
http://pgdiff.sourceforge.net/
http://gborg.postgresql.org/project/pgdiff/projdisplay.php

--
 Daniel Vérité - daniel at manitou dash mail dot org