Tracking structural changes from psql

From: Mike McGavin <jester(at)NOSPAM(dot)mcsnospam(dot)vuw(dot)acNOSPAM(dot)nz>
To: pgsql-general(at)postgresql(dot)org
Subject: Tracking structural changes from psql
Date: 2004-05-02 03:53:36
Message-ID: 1083470016.571556@bats.mcs.vuw.ac.nz
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2004-05-02 05:43:26 Anyone interested in a Pittsburgh-area Postgres users' group?
Previous Message Bruno Wolff III 2004-05-02 01:39:55 Re: 1 foreign key to 2 different tables?