Re: Postgres Backup Utility - Mailing list pgsql-admin

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?

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
not go trawling through what can only be described as hundreds of
thousands of lines of PostgreSQL log to find THE RIGHT DDL statements.
To me that's surely a recipe for disaster, and certainly not a role I
would expect to complete as a DBA. Yes the Developers should've kept the
DDL for the changes, however; I know from experience that this isn't
always the case, and have found it necessary to "Diff" databases on a
regular basis for one reason or another.

Each time I've done something like this I have resulted to coding shell
scripts, or C programs that are bespoke to accomplish this, as I've yet
to find a tool that does this to my satisfaction levels.

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

On Wed, Jan 19, 2011 at 12:12 AM, French, Martin
<frenchm@cromwell.co.uk> wrote:
> Ok, you say that you cannot drop and recreate, so you need to do this
via
> alter statements only? That's obviously going to complicate matters,
as a
> straight dump, drop, recreate, restore would be the fastest and by far
> simplest method.
>
>
>
> So, Ideally, you'll need to do a table def comparison over the two
> databases, and generate the necessary sql to amend the tables in test
> accordingly?

Sorry but this is exactly backwards from good procedure.  What you do
it have your developers checkin the SQL code they used to alter the
tables / add rows in the test database, and you apply that to testing
/ QA / staging / production.  Any other way is a recipe for both
disaster and DBA burnout.

To make it easier, you can always turn on ddl logging on the
developer's databases and then just trawl the logs for what they did.
Still easier than trying to compare schemas and figure out what's
different and how to write the SQL to make it happen.

___________________________________________________

This email is intended for the named recipient. The information contained
in it is confidential.  You should not copy it for any purposes, nor
disclose its contents to any other party.  If you received this email
in error, please notify the sender immediately via email, and delete it from
your computer.

Any views or opinions presented are solely those of the author and do not
necessarily represent those of the company.

PCI Compliancy: Please note, we do not send or wish to receive banking, credit
or debit card information by email or any other form of communication.

Cromwell Tools Limited, PO Box 14, 65 Chartwell Drive
Wigston, Leicester LE18 1AT. Tel 0116 2888000
Registered in England and Wales, Reg No 00986161
VAT GB 115 5713 87 900
__________________________________________________