Copy Page

You are not logged in. Any changes you make will be marked as anonymous. You may want to Log In if you already have an account. You can also Sign Up for a new account.

<p>This document describes how to add a table to an existing <a href="http://bruce.codehaus.org/">Bruce</a> replication cluster<sup>1</sup>. An familiarity with administering <a href="http://www.postgresql.org/">PostgreSQL</a> is presumed of the reader.</p>

<h4>The simple case: A brand new table</h4>

<ul>
	<li>Create the table on the master database, using psql and 'create table' statements, including indexes, constraints, primary key, etc.</li>
	<li>Create the table on all the slaves. One way to do this is<sup>2</sup>:</li>
</ul>

<ul>
	<li>Create slave triggers on all slaves:</li>
</ul>

<table class="wysiwyg-macro" data-macro-name="noformat" style="background-image: url(/plugins/servlet/confluence/placeholder/macro-heading?definition=e25vZm9ybWF0fQ&amp;locale=en_GB&amp;version=2); background-repeat: no-repeat;" data-macro-body-type="PLAIN_TEXT"><tr><td class="wysiwyg-macro-body"><pre>
psql &lt;slave database> 
create trigger &lt;table name>_deny 
BEFORE INSERT OR DELETE OR UPDATE ON &lt;fully qualified table name> 
FOR EACH ROW EXECUTE PROCEDURE bruce.denyaccesstrigger();
\q
</pre></td></tr></table>

<ul>
	<li>Create master triggers:</li>
</ul>

<table class="wysiwyg-macro" data-macro-name="noformat" style="background-image: url(/plugins/servlet/confluence/placeholder/macro-heading?definition=e25vZm9ybWF0fQ&amp;locale=en_GB&amp;version=2); background-repeat: no-repeat;" data-macro-body-type="PLAIN_TEXT"><tr><td class="wysiwyg-macro-body"><pre>
psql &lt;master database>
create trigger &lt;table name>_sn 
BEFORE INSERT OR DELETE OR UPDATE ON &lt;fully qualified table name> 
FOR EACH STATEMENT EXECUTE PROCEDURE bruce.logsnapshottrigger();
create trigger &lt;table name>_tx 
AFTER INSERT OR DELETE OR UPDATE ON &lt;fully qualified tables name> 
FOR EACH ROW EXECUTE PROCEDURE bruce.logtransactiontrigger();
\q 
</pre></td></tr></table>

<ul>
	<li>Table is now being replicated, no daemon restart is necessary.</li>
</ul>

<h4>The not so simple case: An already populated table</h4>

<ul>
	<li>Lock the cluster.</li>
</ul>

<ul>
	<li>In another window: create table on all slaves, including table data:</li>
</ul>

<ul>
	<li>Create slave triggers on all slaves:</li>
</ul>

<ul>
	<li>Back to the open transaction on the master, create master triggers:</li>
</ul>

<table class="wysiwyg-macro" data-macro-name="noformat" style="background-image: url(/plugins/servlet/confluence/placeholder/macro-heading?definition=e25vZm9ybWF0fQ&amp;locale=en_GB&amp;version=2); background-repeat: no-repeat;" data-macro-body-type="PLAIN_TEXT"><tr><td class="wysiwyg-macro-body"><pre>
create trigger &lt;table name>_sn 
BEFORE INSERT OR DELETE OR UPDATE ON &lt;fully qualified table name> 
FOR EACH STATEMENT EXECUTE PROCEDURE bruce.logsnapshottrigger();
create trigger &lt;table name>_tx 
AFTER INSERT OR DELETE OR UPDATE ON &lt;fully qualified tables name> 
FOR EACH ROW EXECUTE PROCEDURE bruce.logtransactiontrigger();
</pre></td></tr></table>

<ul>
	<li>Close transaction:</li>
</ul>

<ul>
	<li>Table is being replicated. No daemon restart is nessasary</li>
</ul>

<h5>Footnotes</h5>

<p><sup>1</sup>. For now. In a future version of <a href="http://bruce.codehaus.org/">Bruce</a>, a method will be provided to ease this process.<br />
<sup>2</sup>. Replace text within &lt;&gt; with appropriate values.</p>