<html><body><div style="color:#000; background-color:#fff; font-family:times new roman, new york, times, serif;font-size:12pt"><div><span>You need to exclude the triggers</span></div><div><br><span></span></div><div><span>Make the dump using -Fc and not -Fp</span></div><div><br><span></span></div><div><span>pg_dump -N _</span><span> the_name_of_clustername .... </span><span>yourdump.dmp</span></div><div><span><br></span></div><div><span>First generate the TOC using -l<br></span></div><div><br><span></span></div><div><span>pg_dump -l yourdump.dmp | grep -v the_name_of_clustername > toc.safe</span></div><div><br><span></span></div><div><span>Then restore using the new the new toc</span></div><div><br><span></span></div><div><span>pg_restore -L </span><span> toc.safe </span><span>yourdump.dmp</span></div><div><br></div> <div style="font-family: times new roman, new york, times, serif; font-size: 12pt;"> <div style="font-family: times new roman, new
york, times, serif; font-size: 12pt;"> <font face="Arial" size="2"> <hr size="1"> <b><span style="font-weight:bold;">De :</span></b> Stuart Bishop <stuart@stuartbishop.net><br> <b><span style="font-weight: bold;">À :</span></b> slony <slony1-general@lists.slony.info> <br> <b><span style="font-weight: bold;">Envoyé le :</span></b> Mardi 29 Novembre 2011 13h33<br> <b><span style="font-weight: bold;">Objet :</span></b> [Slony1-general] dump, restore & --exclude-schema<br> </font> <br>Hi.<br><br>I was hoping to get 'clean' dumps from a slave now that I have<br>upgraded from the Slony-I 1.2 series using --exclude-schema. However,<br>the --exclude-schema option to pg_dump is still dumping all the slony<br>triggers attached to the tables:<br><br>CREATE TRIGGER _sl_denyaccess<br> BEFORE INSERT OR DELETE OR UPDATE ON accesspolicy<br> FOR EACH ROW<br> EXECUTE PROCEDURE
_sl.denyaccess('_sl');<br><br> This creates a dump that I cannot seem to restore error free -<br>pg_restore tries to create the triggers and fails, causing hundreds of<br>errors to be reported and, more importantly to my automation scripts,<br>return an error code.<br><br>Should I be doing something differently, or do I need to continue to<br>dump my database with the Slony-I schema and then 'repair' it using<br>"DROP SCHEMA _sl CASCADE;" as I have been in the past? I need<br>something that can be automated, as our production dumps are regularly<br>used to rebuild some staging servers (with the nice side effect of<br>testing our dumps and disaster recovery processes).<br><br>I'm considering creating a stub slony schema and denyaccess stored<br>procedure, but I'm not sure if that solution is better than the<br>mechanism I used with slony 1.2 (dump the whole database, including<br>the seemingly useless Slony schema, and repair it after
restoration).<br><br>-- <br>Stuart Bishop <<a ymailto="mailto:stuart@stuartbishop.net" href="mailto:stuart@stuartbishop.net">stuart@stuartbishop.net</a>><br><a href="http://www.stuartbishop.net/" target="_blank">http://www.stuartbishop.net/</a><br>_______________________________________________<br>Slony1-general mailing list<br><a ymailto="mailto:Slony1-general@lists.slony.info" href="mailto:Slony1-general@lists.slony.info">Slony1-general@lists.slony.info</a><br><a href="http://lists.slony.info/mailman/listinfo/slony1-general" target="_blank">http://lists.slony.info/mailman/listinfo/slony1-general</a><br><br><br> </div> </div> </div></body></html>