From: "Bradley Holbrook" <operations_bradley@servillian.ca>
To: "'Scott Marlowe'" <scott.marlowe@gmail.com>,
	"'French, Martin'" <frenchm@cromwell.co.uk>
Cc: <pgsql-admin@postgresql.org>
References: <036101cbb6a3$baa178e0$2fe46aa0$@servillian.ca>
	<81976671721DF04B9DCA6ECD87941A402640812A@roundway.Cromwell-tools.co.uk>
	<004e01cbb730$bdb21930$39164b90$@servillian.ca>
	<81976671721DF04B9DCA6ECD87941A402640863E@roundway.Cromwell-tools.co.uk>
	<AANLkTi=E0EjWKUuxiOyOOmemKYga9Z=5a4iJbsbKX=hE@mail.gmail.com>
	<81976671721DF04B9DCA6ECD87941A40264B2BB5@roundway.Cromwell-tools.co.uk>
	<AANLkTimKC442BHxR3OgMVw=30L-qwTx+vc+SoTdwF6s7@mail.gmail.com>
In-Reply-To: <AANLkTimKC442BHxR3OgMVw=30L-qwTx+vc+SoTdwF6s7@mail.gmail.com>
Subject: Re: Postgres Backup Utility
Date: Thu, 20 Jan 2011 09:42:02 -0800
Message-ID: <01af01cbb8c9$58eaa0e0$0abfe2a0$@servillian.ca>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit
Thread-Index: 
 AQHHkvKLrosR4aSyAm2SjCI6Zq933AO9xqP7Akk6VIIDAixhoAH4cdVIAfRS2jAC6GVMfpNjcQpw
Content-Language: en-ca

Thanks Scott... a couple comments.

Our developers never decide what goes to where... they just happily plumb
away on the development db until we're ready to take our product to testing
(at regular intervals), once QA is passed, we wish to apply these to live.
We have several diff tools and sync tools, but they take forever (especially
the ones that only go one schema at a time).

The DDL Logging sounds like a sufficient solution, can it be configured to
only record create and alter commands (or create or replace commands on
functions or updates on sequences, etc)? I'd likely write a script to have
this emailed to me at the end of every day. I'm going to google DDL logging
(never heard of it), but any good resources off the top of your head?

Martin French is right though, ask your developers to write down all their
SQL struct changes and they look at you funny... and being a developer
myself I'd look at me funny. If you forget just once you're screwed into a
day sifting through tables and code.


-----Original Message-----
From: Scott Marlowe [mailto:scott.marlowe@gmail.com] 
Sent: January-20-11 7:40 AM
To: French, Martin
Cc: Bradley Holbrook; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Postgres Backup Utility

On Thu, Jan 20, 2011 at 5:00 AM, French, Martin <frenchm@cromwell.co.uk>
wrote:
> Having been a C/C++ developer many years before being a DBA, and 
> having written ITIL software; How is migrating structure from a 
> Development database to a test database whilst maintaining test data
backwards?

It's not.  doing by running diffs on ddl / schema is.

This is what the OP asked for:

"I need to be able to quickly apply the structure updates from a development
database to a testing database, and do selective data updates (like on
lookup tables, but not content tables)."

>
> Besides, the OP was asking how to diff to databases and create ddl, 
> not asking for us to comment on why he's doing it. Personally, I'd 
> rather

Re-read what he asked for.  He did not ask how to run diffs of schema, he
asked how to update it with changes from a test database.  The best way to
do this is to check in changes to the database and lookup tables so the DBA
can then apply them when needed to the various other databases.

But hey, feel free to answer a question the OP didn't ask with an answer he
doesn't need.