Karl Lehenbauer wrote:
> So we follow the best practice of testing DDL by wrapping it in 
> a BEGIN...ROLLBACK and making sure it "works" on the master 
> before pushing it out using slonik_execute_script.
>
> Yesterday we got burned.  We had some DDL that included a 
> DROP FUNCTION.  Problem was the function existed on the master but not on
> the slaves.  (We should have used DROP FUNCTION IF EXISTS, but we didn't.)
>
> So we got into the thing where the slon daemon on the slaves would 
> die and then get respawned and then try to sync a ton of stuff and 
> die whenever it hit the PGRES_FATAL_ERROR with the messed up DDL. 
>  Finally we created the missing function on the slaves and then the 
> DDL could complete and after a few minutes, everything was back to normal.
>
> I propose that a better best practice is to test the DDL on the master 
> *and every slave* in a BEGIN...ROLLBACK to make sure the DDL 
> will succeed everywhere.  We've built a program to do this, 
> try_slony_ddl, but it's written in Tcl.  Perhaps someone with better 
> Perl skills than I could make a slonik_test_script that does the same 
> thing but uses the slon_tools.conf.  (My stuff currently gets the db 
> connect strings out of sl_path).
>
> I share this in the hope that it might be useful to someone.  We 
> feel like after something's been shown by try_slony_ddl to work on the 
> master and all the slaves, it's a pretty dead certainty that it's 
> going to work when pushed out with slonik_execute_script.
I'm getting keener on having "comparator tools" to look for variances 
between schemas, and depending on that as a mechanism.

Our DBAs are, I believe, looking at DBSolo for this purpose.

I think it would make me feel more confident to say:
  "I ran a comparison of the schemas, and {Tool X} found that they were 
the same (modulo reconcilable differences)"
than to say:
  "I did a dry run of the upgrade and it seemed to run OK."

The former seems like a rather better test.

-- 
"cbbrowne","@","ca.afilias.info"
<http://dba2.int.libertyrms.com/>
Christopher Browne
(416) 673-4124 (land)
"Bother,"  said Pooh,  "Eeyore, ready  two photon  torpedoes  and lock
phasers on the Heffalump, Piglet, meet me in transporter room three"