Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id 1FA001337C3A for ; Wed, 19 Jan 2011 15:17:44 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 13655-02-8 for ; Wed, 19 Jan 2011 19:17:35 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from web39704.mail.mud.yahoo.com (web39704.mail.mud.yahoo.com [209.191.106.50]) by mail.postgresql.org (Postfix) with SMTP id 145201337B69 for ; Wed, 19 Jan 2011 15:13:57 -0400 (AST) Received: (qmail 14400 invoked by uid 60001); 19 Jan 2011 19:13:56 -0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s1024; t=1295464436; bh=QWdB2N/4mSsbIYvkdBe6GYlYJqOXybLXKkB1ByDB76c=; h=Message-ID:X-YMail-OSG:Received:X-Mailer:Date:From:Subject:To:Cc:In-Reply-To:MIME-Version:Content-Type; b=ETj+5564aNImE6iX6i7BwRVicUZKLeo7OaesmJFh0nv45qLieCQ3RfN6vu/Hld3d4QbSC/XUNO4DRQufVKHOXr5jhJx/vozG6V2zaZ6BLht9MulW33ONAAQsTY4YVzCDdptQ+E3tJRLEo8RVJJ9vwiGyGAU89/8z8EYh6xWx7xM= DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=Message-ID:X-YMail-OSG:Received:X-Mailer:Date:From:Subject:To:Cc:In-Reply-To:MIME-Version:Content-Type; b=P/ZjvU1Zaf9JykDCl5nzDRhQcrnop0CkYTdPxgD4niDT04OdgDGO+ha5isrd3m4qeO83QHPclP2BtWCq3F2YZ6IDm1B62X8IZfmSqlwOC+SVTrVjG815hzqqoh98P/f40sXXxwDhY622hqzH2bkO6d8bk/lSq8P2/6LXiJZ5OTA=; Message-ID: <536560.14223.qm@web39704.mail.mud.yahoo.com> X-YMail-OSG: JKwLImYVM1nGp4jMlPITQ3bdkXPMAxt8ZeT3f0QWnb_laX7 DqE8_k3Vc.lVbSGYsV6djoi6ODFm8dVaCpS9nqatFWsPkOJfvKSHgWCv3WFm UR7Or1A8fZFDmA00lxPc28dAHejcj.0Bom9Y6lAb_9xbCqDddwl257UwFeDs 2XV4VtZ4N9CaWeG_FFKwcIQ.9j6yG9bZbvHNzOH6p6vCgT5BXh8Xh42RXKp9 GJdMfGRJ8SNNfzGBobPtkoL1n.399QraF4MhNeojPPwdXORAh00GkVqwmQkD GYDtxugygPdPdGTBezLWKHeEt2toXWwxBP9giiDoyKVZfQQu09GJrhSw- Received: from [64.126.29.122] by web39704.mail.mud.yahoo.com via HTTP; Wed, 19 Jan 2011 11:13:56 PST X-Mailer: YahooMailClassic/11.4.20 YahooMailWebService/0.8.107.285259 Date: Wed, 19 Jan 2011 11:13:56 -0800 (PST) From: Bob Lunney Subject: Re: Postgres Backup Utility To: Bradley Holbrook Cc: pgsql-admin@postgresql.org In-Reply-To: <81976671721DF04B9DCA6ECD87941A402640863E@roundway.Cromwell-tools.co.uk> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-2061493445-1295464436=:14223" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.907 tagged_above=-10 required=5 tests=BAYES_00=-1.9, FREEMAIL_FROM=0.001, HTML_MESSAGE=0.001, RCVD_IN_DNSWL_NONE=-0.0001, RFC_ABUSE_POST=0.001, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201101/116 X-Sequence-Number: 36556 --0-2061493445-1295464436=:14223 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable Brad, Google for "SQL Power Architect", download it, and try a schema comparison.= =C2=A0That might get you a ways down to road to what you want. Bob Lunney --- On Wed, 1/19/11, French, Martin wrote: From: French, Martin Subject: Re: [ADMIN] Postgres Backup Utility To: "Bradley Holbrook" Cc: pgsql-admin@postgresql.org Date: Wednesday, January 19, 2011, 2:12 AM =0A=0A=0A =0A =0A=0A=0A=0A=0A=0A=0A =0A=0A=0A=0AOk, you say that you cannot= drop=0Aand recreate, so you need to do this via alter statements only? Tha= t=E2=80=99s=0Aobviously going to complicate matters, as a straight dump, dr= op, recreate,=0Arestore would be the fastest and by far simplest method. = =0A=0A =C2=A0 =0A=0ASo, Ideally, you=E2=80=99ll need=0Ato do a table def co= mparison over the two databases, and generate the necessary=0Asql to amend = the tables in test accordingly? =0A=0A =C2=A0 =0A=0AQuerying the pg_catalo= g/information_schema=0Aover the two db=E2=80=99s should give you the table = ddl from which you can diff,=0Aand then generate the alter statements from = the results. =0A=0A =C2=A0 =0A=0ACheers =0A=0A =C2=A0 =0A=0AMartin =0A=0A= =C2=A0 =0A=0A=0A=0A=0A=0AFrom: Bradley Holbrook=0A[mailto:operations_bradl= ey@servillian.ca]=20 =0ASent: 18 January 2011 16:57 =0ATo: French, Martin =0ACc: pgsql-admin@postgresql.org =0ASubject: RE: [ADMIN] Postgres Backup Utility =0A=0A=0A=0A=0A=0A =C2=A0 = =0A=0AWell,=0AI can=E2=80=99t just go dropping and recreating tables=E2=80= =A6 it needs to create=0Athe correct alter statements if existing tables an= d or functions already exist. =0A=0A =C2=A0 =0A=0ASecondly,=0Awhen I=E2=80= =99m finished changing the structure, I need to be able to select the=0Alis= t of tables that will have content updates. =0A=0A =C2=A0 =0A=0AUsing=0Aa s= cript might be more work maintaining then it=E2=80=99s worth. I have a back= up=0Autility that can do the job, but 3 tedious steps per schema, that only= work=0Aabout 10% of the time (and no batching options so that I can create= a list of=0Aactions and run the list). =0A=0A =C2=A0 =0A=0A =C2=A0 =0A=0A= =0A=0A=0A=0AFrom: French, Martin=0A[mailto:frenchm@cromwell.co.uk]=20 =0ASent: January-18-11 5:47 AM =0ATo: Bradley Holbrook; pgsql-admin@postgresql.org =0ASubject: RE: [ADMIN] Postgres Backup Utility =0A=0A=0A=0A=0A=0A =C2=A0 = =0A=0AI=E2=80=99m assuming that this=0Aneeds to be tightly controlled and a= s such a replication tool is out of the=0Aquestion? =0A=0A =C2=A0 =0A=0AIn = that case; The first thing to=0Apop into my head here would be to use eithe= r use shell scripting, or to use the=0Apg API and write a c program to hand= le it. =0A=0A =C2=A0 =0A=0AI remember doing something very=0Asimilar with O= racle a few years back. =0A=0A =C2=A0 =0A=0ACheers =0A=0A =C2=A0 =0A=0AMart= in =0A=0A =C2=A0 =0A=0A =C2=A0 =0A=0A=0A=0A=0A=0AFrom: pgsql-admin-owner@po= stgresql.org=0A[mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Bradl= ey Holbrook =0ASent: 18 January 2011 00:08 =0ATo: pgsql-admin@postgresql.org =0ASubject: [ADMIN] Postgres Backup Utility =0A=0A=0A=0A=0A=0A =C2=A0 =0A= =0AHello! =0A=0A =C2=A0 =0A=0AFirst=0Aday on the new mailing list as I have= need of some expert=E2=80=99s advice. =0A=0A =C2=A0 =0A=0AI=0Aneed to be a= ble to quickly apply the structure updates from a development=0Adatabase to= a testing database, and do selective data updates (like on lookup=0Atables= , but not content tables). =0A=0A =C2=A0 =0A=0AAny=0Ahelp would be apprecia= ted! =0A=0A =C2=A0 =0A=0ABrad =0A=0A =0A___________________________________________________=20 =0A =0AThis email is intended for the named recipient. The information containe= d=20 =0Ain it is confidential. You should not copy it for any purposes, nor=20 =0Adisclose its contents to any other party. If you received this email=20 =0Ain error, please notify the sender immediately via email, and delete=20 =0Ait from your computer.=20 =0A =0AAny views or opinions presented are solely those of the author and do no= t=20 =0Anecessarily represent those of the company.=20 =0A =0APCI Compliancy: Please note, we do not send or wish to receive banking,= =20 =0Acredit or debit card information by email or any other form of=20 =0Acommunication.=20 =0A =0ACromwell Tools Limited, PO Box 14, 65 Chartwell Drive=20 =0AWigston, Leicester LE18 1AT. Tel 0116 2888000=20 =0ARegistered in England and Wales, Reg No 00986161=20 =0AVAT GB 115 5713 87 900=20 =0A__________________________________________________ =0A=0A=0A=0A =0A=0A ___________________________________________________ =0A =0A This email is intended for the named recipient. The information contained = =0A in it is confidential. You should not copy it for any purposes, nor =0A disclose its contents to any other party. If you received this email =0A in error, please notify the sender immediately via email, and delete=0A it from your computer. =0A =0A Any views or opinions presented are solely those of the author and do not = =0A necessarily represent those of the company. =0A =0A PCI Compliancy: Please note, we do not send or wish to receive banking,=0A credit or debit card information by email or any other form of =0A communication. =0A =0A Cromwell Tools Limited, PO Box 14, 65 Chartwell Drive=0A Wigston, Leicester LE18 1AT. Tel 0116 2888000=0A Registered in England and Wales, Reg No 00986161=0A VAT GB 115 5713 87 900=0A __________________________________________________=0A=0A=0A=0A=0A --0-2061493445-1295464436=:14223 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: quoted-printable
Brad,

Google for "SQL Powe= r Architect", download it, and try a schema comparison.  That might ge= t you a ways down to road to what you want.

Bob Lu= nney

--- On Wed, 1/19/11, French, Martin <frenchm@cromwell.= co.uk> wrote:

From: French, Mart= in <frenchm@cromwell.co.uk>
Subject: Re: [ADMIN] Postgres Backup U= tility
To: "Bradley Holbrook" <operations_bradley@servillian.ca>Cc: pgsql-admin@postgresql.org
Date: Wednesday, January 19, 2011, 2:12= AM

=0A=0A=0A =0A =0A=0A=0A=0A=0A =0A=0A
=0A=0A

Ok, you say that you cannot drop=0Aand recreate, so you= need to do this via alter statements only? That=E2=80=99s=0Aobviously goin= g to complicate matters, as a straight dump, drop, recreate,=0Arestore woul= d be the fastest and by far simplest method.

=0A=0A

 

= =0A=0A

So= , Ideally, you=E2=80=99ll need=0Ato do a table def comparison over the two = databases, and generate the necessary=0Asql to amend the tables in test acc= ordingly?

=0A=0A

 

=0A=0A

Querying the pg_catalog/information_sc= hema=0Aover the two db=E2=80=99s should give you the table ddl from which y= ou can diff,=0Aand then generate the alter statements from the results.

=0A=0A

 

=0A=0A

Cheers

=0A=0A

 

=0A=0A

Martin =0A=0A

=  

=0A=0A
=0A=0A
=0A=0A

From: Bradley Holbrook=0A[mailto:operations_bradley@servilli= an.ca]
=0ASent: 18 January 2011 16:57
=0ATo: French, M= artin
=0ACc: pgsql-admin@postgresql.org
=0ASubject: RE:= [ADMIN] Postgres Backup Utility

=0A=0A
=0A=0A
=0A=0A<= p class=3D"yiv1343573220MsoNormal">  

=0A=0A

Well,=0AI can=E2=80=99t just go dropping and recreating tables=E2=80=A6 it= needs to create=0Athe correct alter statements if existing tables and or f= unctions already exist.

=0A=0A

 

=0A=0A

Secondly,=0Awhen I=E2=80=99m finished = changing the structure, I need to be able to select the=0Alist of tables th= at will have content updates.

=0A=0A

&nbs= p;

=0A=0A

Using=0Aa script might be more w= ork maintaining then it=E2=80=99s worth. I have a backup=0Autility that can= do the job, but 3 tedious steps per schema, that only work=0Aabout 10% of = the time (and no batching options so that I can create a list of=0Aactions = and run the list).

=0A=0A

  =0A=0A

 

=0A=0A
=0A=0A
=0A=0A

From: French, Martin=0A[mail= to:frenchm@cromwell.co.uk]
=0ASent: January-18-11 5:47 AM
=0A= To: Bradley Holbrook; pgsql-admin@postgresql.org
=0ASubject: RE: [ADMIN] Postgres Backup Utility

=0A=0A
=0A=0A
= =0A=0A

 

=0A=0A

I=E2=80=99m assuming that this=0Aneeds to be tightly controlled and as= such a replication tool is out of the=0Aquestion?

=0A=0A

 <= /p> =0A=0A

In that case; The first thing to=0Apop into my head here would be to use = either use shell scripting, or to use the=0Apg API and write a c program to= handle it.

=0A=0A

 

=0A=0A

I remember doing something very=0Asim= ilar with Oracle a few years back.

=0A=0A

 

=0A=0A

Cheers<= /p> =0A=0A

 

=0A=0A

Martin

=0A=0A

 

=0A=0A

 

=0A= =0A
=0A=0A
=0A=0A

From:= p= gsql-admin-owner@postgresql.org=0A[mailto:pgsql-admin-owner@postgresql.org]= On Behalf Of Bradley Holbrook
=0ASent: 18 January 2011 00= :08
=0ATo: pgsql-admin@postgresql.org
=0ASubject: [ADMI= N] Postgres Backup Utility

=0A=0A
=0A=0A
=0A=0A

 

=0A=0A

Hello= !

=0A=0A

 

=0A=0A

First=0Aday on the new mailing list as I have need of some e= xpert=E2=80=99s advice.

=0A=0A

 

=0A=0A

I=0Aneed to be able to quickly apply t= he structure updates from a development=0Adatabase to a testing database, a= nd do selective data updates (like on lookup=0Atables, but not content tabl= es).

=0A=0A

 

=0A=0A

Any=0Ahelp would be appreciated!

=0A=0A

 

=0A=0A

Brad=

=0A=0A


=0A_______________= ____________________________________
=0A
=0AThis email is intended f= or the named recipient. The information contained
=0Ain it is confident= ial. You should not copy it for any purposes, nor
=0Adisclose its conte= nts to any other party. If you received this email
=0Ain error, please = notify the sender immediately via email, and delete
=0Ait from your com= puter.
=0A
=0AAny views or opinions presented are solely those of th= e author and do not
=0Anecessarily represent those of the company.
= =0A
=0APCI Compliancy: Please note, we do not send or wish to receive ba= nking,
=0Acredit or debit card information by email or any other form o= f
=0Acommunication.
=0A
=0ACromwell Tools Limited, PO Box 14, 65= Chartwell Drive
=0AWigston, Leicester LE18 1AT. Tel 0116 2888000
= =0ARegistered in England and Wales, Reg No 00986161
=0AVAT GB 115 5713 = 87 900
=0A__________________________________________________ =0A=0A

=0A=0A =0A=0A
____________________________________________= _______ =0A
=0A
This email is intended for the named recipient. The= information contained =0A
in it is confidential. You should not copy i= t for any purposes, nor =0A
disclose its contents to any other party. I= f you received this email =0A
in error, please notify the sender immedia= tely via email, and delete=0A
it from your computer. =0A
=0A
Any= views or opinions presented are solely those of the author and do not =0A<= br>necessarily represent those of the company. =0A
=0A
PCI Complian= cy: Please note, we do not send or wish to receive banking,=0A
credit or= debit card information by email or any other form of =0A
commu= nication. =0A
= =0ACromwell Tools Limited, PO Box 14, 65 Chartwell Drive=0A
Wigston, Leice= ster LE18 1AT. Tel 0116 2888000=0A
Registered in England and Wales, Reg = No 00986161=0A
VAT GB 115 5713 87 900=0A
____________________________= ______________________=0A=0A

= =0A=0A --0-2061493445-1295464436=:14223--