Fixed Pg-tables.sql, added scripts to configure replication

git-svn-id: https://ledger-smb.svn.sourceforge.net/svnroot/ledger-smb/trunk@174 4979c152-3d1c-0410-bac9-87ea11338e46

5 files changed, 323 insertions, 8 deletions

diff --git a/CONTRIBUTORS b/CONTRIBUTORS
index 92c426a2..b6214f3c 100644
--- a/CONTRIBUTORS
+++ b/CONTRIBUTORS
@@ -29,7 +29,7 @@ Jason Rodriguez <jasonjayr+ledgersmb @ gmail.com> provided the logic to force
 the password change in the admin.pl
 
 Christopher Browne <cbrowne @ acm.org> provided some corrections to the 
-documentation.
+documentation, the Pg-tables.sql, and a setup file for replication
 
 
 Original Authors of SQL-Ledger:
diff --git a/Changelog b/Changelog
index fa1e5905..9243af65 100644
--- a/Changelog
+++ b/Changelog
@@ -1,5 +1,6 @@
 Changelog for LedgerSMB 1.2.0
 
+* Added script to configure Slony replication (Chris Browne)
 * Audited OE.pm and AM.pm for SQL injection problems. (Chris Travers)
 * Forced edited files to have whitelisted extensions and no .. strings (Chris T)
 * Added $form->callproc($procname, @args) returns @hashrefs (Chris Travers)
diff --git a/sql/Pg-tables.sql b/sql/Pg-tables.sql
index 896dae28..9cd3f939 100755
--- a/sql/Pg-tables.sql
+++ b/sql/Pg-tables.sql
@@ -24,7 +24,7 @@ CREATE TABLE makemodel (
 );
 --
 CREATE TABLE gl (
-  id int DEFAULT nextval ( 'id' ) PRIMAY KEY,
+  id int DEFAULT nextval ( 'id' ) PRIMARY KEY,
   reference text,
   description text,
   transdate date DEFAULT current_date,
@@ -41,8 +41,7 @@ CREATE TABLE chart (
   category char(1),
   link text,
   gifi_accno text,
-  contra bool DEFAULT 'f',
-  PRIMARY KEY (id)
+  contra bool DEFAULT 'f'
 );
 --
 CREATE TABLE gifi (
@@ -261,7 +260,7 @@ CREATE TABLE customertax (
 CREATE TABLE vendortax (
   vendor_id int,
   chart_id int,
-  PRIMARKY KEY (vendor_id, chart_id)
+  PRIMARY KEY (vendor_id, chart_id)
 );
 --
 CREATE TABLE oe (
@@ -424,7 +423,7 @@ CREATE TABLE department (
 -- department transaction table
 CREATE TABLE dpt_trans (
   trans_id int PRIMARY KEY,
-  department_id int,
+  department_id int
 );
 --
 -- business table
@@ -454,7 +453,7 @@ CREATE TABLE inventory (
   qty numeric,
   shippingdate date,
   employee_id int,
-  entry_id SERIAL PRIMARY KEY,
+  entry_id SERIAL PRIMARY KEY
 );
 --
 CREATE TABLE yearend (
@@ -490,7 +489,7 @@ CREATE TABLE partscustomer (
 );
 --
 CREATE TABLE language (
-  code varchar(6) SERIAL PRIMARY KEY,
+  code varchar(6) PRIMARY KEY,
   description text
 );
 --
diff --git a/sql/README b/sql/README
new file mode 100644
index 00000000..4deb8a78
--- /dev/null
+++ b/sql/README
@@ -0,0 +1,129 @@
+README
+------------
+$Id$
+Christopher Browne
+cbbrowne@gmail.com
+2006-09-29
+------------
+
+The script configure-replication.sh is intended to allow the gentle user
+to readily configure replication of the LedgerSMB database schema
+using the Slony-I replication system for PostgreSQL.
+
+For more general details about Slony-I, see <http://slony.info/>
+
+This script uses a number of environment variables to determine the
+shape of the configuration.  In many cases, the defaults should be at
+least nearly OK...
+
+Global:
+  CLUSTER - Name of Slony-I cluster
+  NUMNODES - Number of nodes to set up
+
+  PGUSER - name of PostgreSQL superuser controlling replication
+  PGPORT - default port number
+  PGDATABASE - default database name
+
+For each node, there are also four parameters; for node 1:
+  DB1 - database to connect to
+  USER1 - superuser to connect as
+  PORT1 - port
+  HOST1 - host
+
+It is quite likely that DB*, USER*, and PORT* should be drawn from the
+default PGDATABASE, PGUSER, and PGPORT values above; that sort of
+uniformity is usually a good thing.
+
+In contrast, HOST* values should be set explicitly for HOST1, HOST2,
+..., as you don't get much benefit from the redundancy replication
+provides if all your databases are on the same server!
+
+slonik config files are generated in a temp directory under /tmp.  The
+usage is thus:
+
+1.  preamble.slonik is a "preamble" containing connection info used by
+    the other scripts.
+
+   Verify the info in this one closely; you may want to keep this
+   permanently to use with other maintenance you may want to do on the
+   cluster.
+
+2.  create_set.slonik
+
+    This is the first script to run; it sets up the requested nodes as
+    being Slony-I nodes, adding in some Slony-I-specific config tables
+    and such.
+
+You can/should start slon processes any time after this step has run.
+
+3.  store_paths.slonik
+
+    This is the second script to run; it indicates how the slons
+    should intercommunicate.  It assumes that all slons can talk to
+    all nodes, which may not be a valid assumption in a
+    complexly-firewalled environment.
+
+4.  create_set.slonik
+
+    This sets up the replication set consisting of the whole bunch of
+    tables and sequences that make up the LedgerSMB database schema.
+
+    When you run this script, all that happens is that triggers are
+    added on the origin node (node #1) that start collecting updates;
+    replication won't start until #5...
+
+    There are two assumptions in this script that could be invalidated
+    by circumstances:
+
+     1.  That all of the LedgerSMB tables and sequences have been
+         included.
+
+         This becomes invalid if new tables get added to LedgerSMB and
+         don't get added to the TABLES list in the generator script.
+
+     2.  That all tables have been defined with primary keys.
+
+          This *should* be the case soon if not already.
+
+5.  subscribe_set_2.slonik
+
+     And 3, and 4, and 5, if you set the number of nodes higher...
+
+     This is the step that "fires up" replication.  
+
+     The assumption that the script generator makes is that all the
+     subscriber nodes will want to subscribe directly to the origin
+     node.  If you plan to have "sub-clusters", perhaps where there is
+     something of a "master" location at each data centre, you may
+     need to revise that.
+
+     The slon processes really ought to be running by the time you
+     attempt running this step.  To do otherwise would be rather
+     foolish.
+
+Once all of these slonik scripts have been run, replication may be
+expected to continue to run as long as slons stay running.
+
+If you have an outage, where a database server or a server hosting
+slon processes falls over, and it's not so serious that a database
+gets mangled, then no big deal: Just restart the postmaster and
+restart slon processes, and replication should pick up.
+
+If something does get mangled, then actions get more complicated:
+
+1 - If the failure was of the "origin" database, then you probably want
+   to use FAIL OVER to shift the "master" role to another system.
+
+2 - If a subscriber failed, and other nodes were drawing data from it,
+   then you could submit SUBSCRIBE SET requests to point those other
+   nodes to some node that is "less mangled."  That is not a real big
+   deal; note that this does NOT require that they get re-subscribed
+   from scratch; they can pick up (hopefully) whatever data they
+   missed and simply catch up by using a different data source.
+
+Once you have reacted to the loss by reconfiguring the surviving nodes
+to satisfy your needs, you may want to recreate the mangled node.  See
+the Slony-I Administrative Guide for more details on how to do that.
+It is not overly profound; you need to drop out the mangled node, and
+recreate it anew, which is not all that different from setting up
+another subscriber.
diff --git a/sql/configure-replication.sh b/sql/configure-replication.sh
new file mode 100644
index 00000000..9edad3a9
--- /dev/null
+++ b/sql/configure-replication.sh
@@ -0,0 +1,186 @@
+#!/bin/bash
+# $Id$
+
+# Global defaults
+CLUSTER=${CLUSTER:-"LedgerSMB"}
+NUMNODES=${NUMNODES:-"2"}
+
+# Defaults - origin node
+DB1=${DB1:-${PGDATABASE:-"ledgersmb"}}
+HOST1=${HOST1:-`hostname`}
+USER1=${USER1:-${PGUSER:-"slony"}}
+PORT1=${PORT1:-${PGPORT:-"5432"}}
+
+# Defaults - node 2
+DB2=${DB2:-${PGDATABASE:-"ledgersmb"}}
+HOST2=${HOST2:-"backup.example.info"}
+USER2=${USER2:-${PGUSER:-"slony"}}
+PORT2=${PORT2:-${PGPORT:-"5432"}}
+
+# Defaults - node 3
+DB3=${DB3:-${PGDATABASE:-"ledgersmb"}}
+HOST3=${HOST3:-"backup3.example.info"}
+USER3=${USER3:-${PGUSER:-"slony"}}
+PORT3=${PORT3:-${PGPORT:-"5432"}}
+
+# Defaults - node 4
+DB4=${DB4:-${PGDATABASE:-"ledgersmb"}}
+HOST4=${HOST4:-"backup4.example.info"}
+USER4=${USER4:-${PGUSER:-"slony"}}
+PORT4=${PORT4:-${PGPORT:-"5432"}}
+
+# Defaults - node 5
+DB5=${DB5:-${PGDATABASE:-"ledgersmb"}}
+HOST5=${HOST5:-"backup5.example.info"}
+USER5=${USER5:-${PGUSER:-"slony"}}
+PORT5=${PORT5:-${PGPORT:-"5432"}}
+
+store_path()
+{
+
+echo "include <${PREAMBLE}>;" > $mktmp/store_paths.slonik
+  i=1
+  while : ; do
+    eval db=\$DB${i}
+    eval host=\$HOST${i}
+    eval user=\$USER${i}
+    eval port=\$PORT${i}
+
+    if [ -n "${db}" -a "${host}" -a "${user}" -a "${port}" ]; then
+      j=1
+      while : ; do
+        if [ ${i} -ne ${j} ]; then
+          eval bdb=\$DB${j}
+          eval bhost=\$HOST${j}
+          eval buser=\$USER${j}
+          eval bport=\$PORT${j}
+          if [ -n "${bdb}" -a "${bhost}" -a "${buser}" -a "${bport}" ]; then
+	    echo "STORE PATH (SERVER=${i}, CLIENT=${j}, CONNINFO='dbname=${db} host=${host} user=${user} port=${port}');" >> $mktmp/store_paths.slonik
+          else
+            err 3 "No conninfo"
+          fi
+        fi
+        if [ ${j} -ge ${NUMNODES} ]; then
+          break;
+        else
+          j=$((${j} + 1))
+        fi
+      done
+      if [ ${i} -ge ${NUMNODES} ]; then
+        break;
+      else
+        i=$((${i} +1))
+      fi
+    else
+      err 3 "no DB"
+    fi
+  done
+}
+
+mktmp=`mktemp -d -t ledgersmb-temp.XXXXXX`
+if [ $MY_MKTEMP_IS_DECREPIT ] ; then
+       mktmp=`mktemp -d /tmp/ledgersmb-temp.XXXXXX`
+fi
+
+PREAMBLE=${mktmp}/preamble.slonik
+
+echo "cluster name=${CLUSTER};" > $PREAMBLE
+
+alias=1
+
+while : ; do
+  eval db=\$DB${alias}
+  eval host=\$HOST${alias}
+  eval user=\$USER${alias}
+  eval port=\$PORT${alias}
+
+  if [ -n "${db}" -a "${host}" -a "${user}" -a "${port}" ]; then
+    conninfo="dbname=${db} host=${host} user=${user} port=${port}"
+    echo "NODE ${alias} ADMIN CONNINFO = '${conninfo}';" >> $PREAMBLE
+    if [ ${alias} -ge ${NUMNODES} ]; then
+      break;
+    else
+      alias=`expr ${alias} + 1`
+    fi   
+  else
+    break;
+  fi
+done
+
+
+SEQUENCES=" acc_trans_entry_id_seq audittrail_entry_id_seq
+ custom_field_catalog_field_id_seq custom_table_catalog_table_id_seq
+ id inventory_entry_id_seq invoiceid jcitemsid orderitemsid
+ partscustomer_entry_id_seq partsvendor_entry_id_seq
+ session_session_id_seq shipto_entry_id_seq "
+
+TABLES=" acc_trans ap ar assembly audittrail business chart
+ custom_field_catalog custom_table_catalog customer customertax
+ defaults department dpt_trans employee exchangerate gifi gl inventory
+ invoice jcitems language makemodel oe orderitems parts partscustomer
+ partsgroup partstax partsvendor pricegroup project recurring
+ recurringemail recurringprint session shipto sic status tax
+ transactions translation vendor vendortax warehouse yearend"
+
+SETUPSET=${mktmp}/create_set.slonik
+
+echo "include <${PREAMBLE}>;" > $SETUPSET
+echo "create set (id=1, origin=1, comment='${CLUSTER} Tables and Sequences');" >> $SETUPSET
+
+tnum=1
+
+for table in `echo $TABLES`; do
+    echo "set add table (id=${tnum}, set id=1, origin=1, fully qualified name='public.${table}', comment='${CLUSTER} table ${table}');" >> $SETUPSET
+    tnum=`expr ${tnum} + 1`
+done
+
+snum=1
+for seq in `echo $SEQUENCES`; do
+    echo "set add sequence (id=${snum}, set id=1, origin=1, fully qualified name='public.${seq}', comment='${CLUSTER} sequence ${seq}');" >> $SETUPSET
+    snum=`expr ${snum} + 1`
+done
+
+NODEINIT=$mktmp/create_nodes.slonik
+echo "include <${PREAMBLE}>;" > $NODEINIT
+echo "init cluster (id=1, comment='${CLUSTER} node 1');" >> $NODEINIT
+
+node=2
+while : ; do
+    SUBFILE=$mktmp/subscribe_set_${node}.slonik
+    echo "include <${PREAMBLE}>;" > $SUBFILE
+    echo "store node (id=${node}, comment='${CLUSTER} subscriber node ${node}');" >> $NODEINIT
+    echo "subscribe set (id=1, provider=1, receiver=${node}, forward=yes);" >> $SUBFILE
+    if [ ${node} -ge ${NUMNODES} ]; then
+      break;
+    else
+      node=`expr ${node} + 1`
+    fi   
+done
+
+store_path
+
+echo "
+$0 has generated Slony-I slonik scripts to initialize replication for LedgerSMB.
+
+Cluster name: ${CLUSTER}
+Number of nodes: ${NUMNODES}
+Scripts are in ${mktmp}
+=====================
+"
+ls -l $mktmp
+
+echo "
+=====================
+Be sure to verify that the contents of $PREAMBLE very carefully, as
+the configuration there is used widely in the other scripts.
+=====================
+====================="
+
+
+
+
+
+
+
+
+