469,898 Members | 1,605 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,898 developers. It's quick & easy.

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.
Nov 23 '05 #1
7 1623

Mike McGavin <je****@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
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 23 '05 #2

Mike McGavin <je****@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
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 23 '05 #3
Mike McGavin <je****@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/...rojdisplay.php

--
Daniel Vérité - daniel at manitou dash mail dot org
Nov 23 '05 #4
Mike McGavin <je****@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/...rojdisplay.php

--
Daniel Vérité - daniel at manitou dash mail dot org
Nov 23 '05 #5
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 ma*******@postgresql.org)


--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 23 '05 #6
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 ma*******@postgresql.org)


--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 23 '05 #7
Hi everyone.

Mike McGavin wrote:
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.


I just wanted to say thanks for the responses that I had to this query,
both in the newsgroup and email. They've been very helpful.

Mike.
Nov 23 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by johnny boy | last post: by
1 post views Thread by Muddassir | last post: by
4 posts views Thread by Glenn Owens | last post: by
1 post views Thread by fred tate via .NET 247 | last post: by
reply views Thread by Mike McGavin | last post: by
4 posts views Thread by tagg3rx | last post: by
1 post views Thread by Waqarahmed | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.