Sequoia

Sequoia is a very powerful clustering system for Application servers that can be used with any database backend that supports jdbc

In my case it was the chosen one for clustering Postgres 8.2 due to the lack funcionality or extreme complexity and overload of other solutions like pg_cluster or slony-I

Example 1

1 sequoia controller (localhost), 1 hsql database (localhost) for the recovery log, and 2 postgres backends.

Enable NTP in all servers involved.

Copy the Postgres jdbc driver

cp /opt/libs/postgresql-8.2-504.jdbc3.jar /usr/local/sequoia/drivers/
cd /usr/local/sequoia/drivers
unzip postgresql-8.2-504.jdbc3.jar
mkdir -p /usr/local/sequoia/3rdparty/postgres/lib/
ln -s /usr/local/sequoia/drivers/postgresql-8.2-504.jdbc3.jar /usr/local/sequoia/3rdparty/postgres/lib/

By default every controller uses 512Mb, this can be changed in bin/controller.sh

/usr/local/sequoia/config/controller/controller-raidb1.xml

<?xml version="1.0" encoding="ISO-8859-1" ?>
<!DOCTYPE SEQUOIA-CONTROLLER PUBLIC "-//Continuent//DTD SEQUOIA-CONTROLLER 2.10.9//EN"  "http://sequoia.continuent.org/dtds/sequoia-controller-2.10.9.dtd">
<SEQUOIA-CONTROLLER>
  <Controller port="25322">
    <Report/>
    <JmxSettings>
      <RmiJmxAdaptor port="1090"/>
    </JmxSettings>
    <VirtualDatabase configFile="hsqldb-raidb1.xml" virtualDatabaseName="vdb" autoEnableBackends="true" checkpointName="Initial_empty_recovery_log"/>
  </Controller>
</SEQUOIA-CONTROLLER>

If there are 2 backends that we want to enable at the same time, we should use autoEnableBackends="force"

If there is already 1 backend and we want to add another one we should use autoEnableBackends="true"

/usr/local/sequoia/config/virtualdatabase/hsqldb-raidb1.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE SEQUOIA PUBLIC "-//Continuent//DTD SEQUOIA 2.10.9//EN" "http://sequoia.continuent.org/dtds/sequoia-2.10.9.dtd">

<SEQUOIA>

  <VirtualDatabase name="vdb">

    <Monitoring>
      <SQLMonitoring defaultMonitoring="off">
         <SQLMonitoringRule queryPattern="^select" caseSensitive="false" applyToSkeleton ="false" monitoring="on"/>
      </SQLMonitoring>
    </Monitoring>

<!-- Any of the following backupers could be used -->

    <Backup>
      <Backuper backuperName="PostgreSQLPlainTextBackuper"
        className="org.continuent.sequoia.controller.backup.backupers.PostgreSQLPlainTextBackuper"/>
    </Backup>

<!--
    <Backup>
      <Backuper backuperName="PostgreSQLBinaryBackuper"
        className="org.continuent.sequoia.controller.backup.backupers.PostgreSQLBinaryBackuper"/>
    </Backup>

    <Backup>
      <Backuper backuperName="PostgreSQLSplitPlainTextBackuper"
        className="org.continuent.sequoia.controller.backup.backupers.PostgreSQLSplitPlainTextBackuper"/>
    </Backup>

    <Backup>
      <Backuper backuperName="PostgreSQLTarBackuper"
        className="org.continuent.sequoia.controller.backup.backupers.PostgreSQLTarBackuper"/>
    </Backup>
-->

    <AuthenticationManager>
      <Admin>
        <User username="admin" password=""/>
      </Admin>
      <VirtualUsers>
        <VirtualLogin vLogin="DatabaseUserName" vPassword=""/>
      </VirtualUsers>
    </AuthenticationManager>

    <DatabaseBackend name="database1" driver="org.postgresql.Driver"
      url="jdbc:postgresql://192.168.2.130:5432/vdb"
      connectionTestStatement="select now()">
      <ConnectionManager vLogin="DatabaseUserName" rLogin="DatabaseUserName" rPassword="">
        <VariablePoolConnectionManager initPoolSize="10" minPoolSize="5"
          maxPoolSize="50" idleTimeout="30" waitTimeout="10"/>
      </ConnectionManager>
    </DatabaseBackend>

    <DatabaseBackend name="database2" driver="org.postgresql.Driver"
      url="jdbc:postgresql://192.168.2.131:5432/vdb"
      connectionTestStatement="select now()">
      <ConnectionManager vLogin="DatabaseUserName" rLogin="DatabaseUserName" rPassword="">
        <VariablePoolConnectionManager initPoolSize="10" minPoolSize="5"
          maxPoolSize="50" idleTimeout="30" waitTimeout="10"/>
      </ConnectionManager>
    </DatabaseBackend>

    <RequestManager>
      <RequestScheduler>
         <RAIDb-1Scheduler level="passThrough"/>
      </RequestScheduler>

      <RequestCache>
         <MetadataCache/>
         <ParsingCache/>
<!--       <ResultCache granularity="table"/> -->
      </RequestCache>

      <LoadBalancer>
         <RAIDb-1>
            <WaitForCompletion policy="first"/>
            <RAIDb-1-LeastPendingRequestsFirst/>
         </RAIDb-1>
      </LoadBalancer>

      <RecoveryLog driver="org.hsqldb.jdbcDriver"
        url="jdbc:hsqldb:hsql://localhost:9003" login="TEST" password="">
        <RecoveryLogTable tableName="RECOVERY" logIdColumnType="BIGINT NOT NULL"
          vloginColumnType="VARCHAR NOT NULL" sqlColumnType="VARCHAR NOT NULL"
          extraStatementDefinition=",PRIMARY KEY (log_id)"/>
        <CheckpointTable tableName="CHECKPOINT"
          checkpointNameColumnType="VARCHAR NOT NULL"/>
        <BackendTable tableName="BACKEND"
          databaseNameColumnType="VARCHAR NOT NULL"
          backendNameColumnType="VARCHAR NOT NULL"
          checkpointNameColumnType="VARCHAR NOT NULL"/>
        <DumpTable tableName="DUMP" dumpNameColumnType="VARCHAR NOT NULL"
          dumpDateColumnType="TIMESTAMP"
          dumpPathColumnType="VARCHAR NOT NULL"
          dumpFormatColumnType="VARCHAR NOT NULL"
          checkpointNameColumnType="VARCHAR NOT NULL"
          backendNameColumnType="VARCHAR NOT NULL"
          tablesColumnType="VARCHAR NOT NULL"/>
      </RecoveryLog>
    </RequestManager>

  </VirtualDatabase>

</SEQUOIA>

Set the following environment variables in /etc/profile

export JAVA_HOME=/opt/jdk
export SEQUOIA_HOME=/usr/local/sequoia

Script to initialize the whole thing: /usr/local/sequoia/demo/demo-raidb1.sh

#!/bin/sh

if [ -z "$SEQUOIA_HOME" ] ; then
  echo "Cannot find Sequoia home directory. Please set the SEQUOIA_HOME environment variable."
  exit 1
fi

# Add hsqldb.jar in the classpath for Octopus backup/restore
export CLASSPATH_XTRA=$SEQUOIA_HOME/drivers/hsqldb.jar

cd $SEQUOIA_HOME/bin

echo "Starting hsqldb on port 9003 (recovery)"
./hsqldb.sh -port 9003 &

echo "Waiting for hsqldb servers to finish start up"
sleep 10

echo "Starting Controller with Raidb1 Configuration"
./controller.sh -f ../config/controller/controller-raidb1.xml &

echo "Waiting for controller to initialize"
sleep 5

#http://osdir.com/ml/db.sequoia.general/2006-02/msg00100.html -> backup

echo "Initializing cluster"
./console.sh -p 1090 -f ../demo/init_raidb1.script

#echo "Executing queries against the cluster"
#./console.sh -p 1090 -f ../demo/sql_test.script

/usr/local/sequoia/demo/init_raidb1.script

admin vdb
admin

expert on
initialize database1
backup database1 vdb.servername.dump PostgreSQLPlainTextBackuper /home/bckusr/bak/dbprod
DatabaseUserName

show dumps
enable database1
restore backend database2 vdb.servername.dump
DatabaseUserName

enable database2
show backend *
quit
quit

Important If using SEQUIA 2.x, after restoring and before enabling the second backend a manual vacuum analyze should be performed on it.

In SEQUOIA 3 this can be done automatically. See https://forge.continuent.org/jira/browse/SEQUOIA-659

Test the cluster

./console.sh -p 1090
admin vdb
admin

sql client jdbc:sequoia://127.0.0.1:25322,127.0.0.1:25323/vdb
DatabaseUserName

create table test(i integer);
insert into test values(1);
select * from test;

Copy the Sequoia driver to coldfusion libs

cp $SEQUOIA_HOME/drivers/sequoia-driver.jar /opt/libs
/etc/init.d/coldfusion restart

Create a data source in coldfusion administration with the following parameters:

Sequoia-DS
jdbc:sequoia://dbserver/vdb
org.continuent.sequoia.driver.Driver
Sequoia
DatabaseUserName
DatabasePassword
Sequoia jdbc driver

Example 2

2 sequoia controllers (localhost), 2 hsql database (localhost) for the recovery log, and 2 postgres backends.

Script to initialize the whole thing with 2 controllers:

#!/bin/sh

if [ -z "$SEQUOIA_HOME" ] ; then
  echo "Cannot find Sequoia home directory. Please set the SEQUOIA_HOME environment variable."
  exit 1
fi

# Add hsqldb.jar in the classpath for Octopus backup/restore
export CLASSPATH_XTRA=$SEQUOIA_HOME/drivers/hsqldb.jar

cd $SEQUOIA_HOME/bin

echo "Starting hsqldb on port 9003 for controller1(recovery)"
./hsqldb.sh -port 9003 &

echo "Waiting for hsqldb servers1 to finish start up"
sleep 10

echo "Starting hsqldb on port 9004 for controller2(recovery)"
./hsqldb.sh -port 9004 &

echo "Waiting for hsqldb servers2 to finish start up"
sleep 10

echo "Starting Controller1 with Raidb1 Configuration"
./controller.sh -f ../config/controller/controller-raidb1-distributed1.xml &

echo "Waiting for controller to initialize"
sleep 5

echo "Starting Controller2 with Raidb1 Configuration"
./controller.sh -f ../config/controller/controller-raidb1-distributed2.xml &

echo "Waiting for controller to initialize"
sleep 5

#http://osdir.com/ml/db.sequoia.general/2006-02/msg00100.html -> backup

echo "Initializing cluster"
./console.sh -p 1090 -f ../demo/init_raidb1.script

echo "Initializing cluster"
./console.sh -p 1091 -f ../demo/init_raidb1.script

#echo "Executing queries against the cluster"
#./console.sh -p 1090 -f ../demo/sql_test.script

/usr/local/sequoia/config/controller/controller-raidb1-distributed1.xml

<?xml version="1.0" encoding="ISO-8859-1" ?>
<!DOCTYPE SEQUOIA-CONTROLLER PUBLIC "-//Continuent//DTD SEQUOIA-CONTROLLER 2.10.9//EN"  "http://sequoia.continuent.org/dtds/sequoia-controller-2.10.9.dtd">
<SEQUOIA-CONTROLLER>
  <Controller port="25322">
    <Report/>
    <JmxSettings>
      <RmiJmxAdaptor port="1090"/>
    </JmxSettings>
    <VirtualDatabase configFile="hsqldb-raidb1-distributed1.xml" virtualDatabaseName="vdb" autoEnableBackends="true" checkpointName="Initial_empty_recovery_log"/>
  </Controller>
</SEQUOIA-CONTROLLER>

/usr/local/sequoia/config/controller/controller-raidb1-distributed2.xml

<?xml version="1.0" encoding="ISO-8859-1" ?>
<!DOCTYPE SEQUOIA-CONTROLLER PUBLIC "-//Continuent//DTD SEQUOIA-CONTROLLER 2.10.9//EN"  "http://sequoia.continuent.org/dtds/sequoia-controller-2.10.9.dtd">
<SEQUOIA-CONTROLLER>
  <Controller port="25323">
    <Report/>
    <JmxSettings>
      <RmiJmxAdaptor port="1091"/>
    </JmxSettings>
    <VirtualDatabase configFile="hsqldb-raidb1-distributed2.xml" virtualDatabaseName="vdb" autoEnableBackends="true" checkpointName="Initial_empty_recovery_log"/>
  </Controller>
</SEQUOIA-CONTROLLER>

/usr/local/sequoia/config/virtualdatabase/hsqldb-raidb1-distributed1.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE SEQUOIA PUBLIC "-//Continuent//DTD SEQUOIA 2.10.9//EN" "http://sequoia.continuent.org/dtds/sequoia-2.10.9.dtd">

<SEQUOIA>

  <VirtualDatabase name="vdb">

    <Distribution>
      <MessageTimeouts/>
    </Distribution>

<!--
    <Monitoring>
      <SQLMonitoring defaultMonitoring="off">
         <SQLMonitoringRule queryPattern="^select" caseSensitive="false" applyToSkeleton ="false" monitoring="on"/>
      </SQLMonitoring>
    </Monitoring>
-->

    <Backup>
      <Backuper backuperName="PostgreSQLPlainTextBackuper"
        className="org.continuent.sequoia.controller.backup.backupers.PostgreSQLPlainTextBackuper"/>
    </Backup>
<!--
    <Backup>
      <Backuper backuperName="PostgreSQLBinaryBackuper"
        className="org.continuent.sequoia.controller.backup.backupers.PostgreSQLBinaryBackuper"/>
    </Backup>

    <Backup>
      <Backuper backuperName="PostgreSQLSplitPlainTextBackuper"
        className="org.continuent.sequoia.controller.backup.backupers.PostgreSQLSplitPlainTextBackuper"/>
    </Backup>

    <Backup>
      <Backuper backuperName="PostgreSQLTarBackuper"
        className="org.continuent.sequoia.controller.backup.backupers.PostgreSQLTarBackuper"/>
    </Backup>
-->

    <AuthenticationManager>
      <Admin>
        <User username="admin" password=""/>
      </Admin>
      <VirtualUsers>
        <VirtualLogin vLogin="dbuser" vPassword=""/>
      </VirtualUsers>
    </AuthenticationManager>

    <DatabaseBackend name="database1" driver="org.postgresql.Driver"
      url="jdbc:postgresql://192.168.2.130:5432/vdb"
      connectionTestStatement="select now()">
      <ConnectionManager vLogin="dbuser" rLogin="dbuser" rPassword="">
        <VariablePoolConnectionManager initPoolSize="10" minPoolSize="5"
          maxPoolSize="50" idleTimeout="30" waitTimeout="10"/>
      </ConnectionManager>
    </DatabaseBackend>

<!--
    <DatabaseBackend name="database2" driver="org.postgresql.Driver"
      url="jdbc:postgresql://192.168.2.131:5432/vdb"
      connectionTestStatement="select now()">
      <ConnectionManager vLogin="dbuser" rLogin="dbuser" rPassword="">
        <VariablePoolConnectionManager initPoolSize="10" minPoolSize="5"
          maxPoolSize="50" idleTimeout="30" waitTimeout="10"/>
      </ConnectionManager>
    </DatabaseBackend>
-->

    <RequestManager>
      <RequestScheduler>
         <RAIDb-1Scheduler level="passThrough"/>
      </RequestScheduler>

      <RequestCache>
         <MetadataCache/>
         <ParsingCache/>                
<!--       <ResultCache granularity="table"/> -->
      </RequestCache>

      <LoadBalancer>
         <RAIDb-1>
            <WaitForCompletion policy="first"/>
            <RAIDb-1-LeastPendingRequestsFirst/>
         </RAIDb-1>
      </LoadBalancer>

      <RecoveryLog driver="org.hsqldb.jdbcDriver"
        url="jdbc:hsqldb:hsql://localhost:9003" login="TEST" password="">
        <RecoveryLogTable tableName="RECOVERY" logIdColumnType="BIGINT NOT NULL"
          vloginColumnType="VARCHAR NOT NULL" sqlColumnType="VARCHAR NOT NULL"
          extraStatementDefinition=",PRIMARY KEY (log_id)"/>
        <CheckpointTable tableName="CHECKPOINT"
          checkpointNameColumnType="VARCHAR NOT NULL"/>
        <BackendTable tableName="BACKEND"
          databaseNameColumnType="VARCHAR NOT NULL"
          backendNameColumnType="VARCHAR NOT NULL"
          checkpointNameColumnType="VARCHAR NOT NULL"/>
        <DumpTable tableName="DUMP" dumpNameColumnType="VARCHAR NOT NULL"
          dumpDateColumnType="TIMESTAMP"
          dumpPathColumnType="VARCHAR NOT NULL"
          dumpFormatColumnType="VARCHAR NOT NULL"
          checkpointNameColumnType="VARCHAR NOT NULL"
          backendNameColumnType="VARCHAR NOT NULL"
          tablesColumnType="VARCHAR NOT NULL"/>
      </RecoveryLog>
    </RequestManager>

  </VirtualDatabase>

</SEQUOIA>

/usr/local/sequoia/config/virtualdatabase/hsqldb-raidb1-distributed2.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE SEQUOIA PUBLIC "-//Continuent//DTD SEQUOIA 2.10.9//EN" "http://sequoia.continuent.org/dtds/sequoia-2.10.9.dtd">

<SEQUOIA>

  <VirtualDatabase name="vdb">

        <Distribution>
        <MessageTimeouts/>
        </Distribution>

<!--
    <Monitoring>
      <SQLMonitoring defaultMonitoring="off">
         <SQLMonitoringRule queryPattern="^select" caseSensitive="false" applyToSkeleton ="false" monitoring="on"/>
      </SQLMonitoring>
    </Monitoring>
-->

    <Backup>
      <Backuper backuperName="PostgreSQLPlainTextBackuper"
        className="org.continuent.sequoia.controller.backup.backupers.PostgreSQLPlainTextBackuper"/>
    </Backup>
<!--
    <Backup>
      <Backuper backuperName="PostgreSQLBinaryBackuper"
        className="org.continuent.sequoia.controller.backup.backupers.PostgreSQLBinaryBackuper"/>
    </Backup>

    <Backup>
      <Backuper backuperName="PostgreSQLSplitPlainTextBackuper"
        className="org.continuent.sequoia.controller.backup.backupers.PostgreSQLSplitPlainTextBackuper"/>
    </Backup>

    <Backup>
      <Backuper backuperName="PostgreSQLTarBackuper"
        className="org.continuent.sequoia.controller.backup.backupers.PostgreSQLTarBackuper"/>
    </Backup>
-->

    <AuthenticationManager>
      <Admin>
        <User username="admin" password=""/>
      </Admin>
      <VirtualUsers>
        <VirtualLogin vLogin="dbuser" vPassword=""/>
      </VirtualUsers>
    </AuthenticationManager>

<!--
    <DatabaseBackend name="database1" driver="org.postgresql.Driver"
      url="jdbc:postgresql://192.168.2.130:5432/vdb"
      connectionTestStatement="select now()">
      <ConnectionManager vLogin="dbuser" rLogin="dbuser" rPassword="">
        <VariablePoolConnectionManager initPoolSize="10" minPoolSize="5"
          maxPoolSize="50" idleTimeout="30" waitTimeout="10"/>
      </ConnectionManager>
    </DatabaseBackend>
-->

    <DatabaseBackend name="database2" driver="org.postgresql.Driver"
      url="jdbc:postgresql://192.168.2.131:5432/vdb"
      connectionTestStatement="select now()">
      <ConnectionManager vLogin="dbuser" rLogin="dbuser" rPassword="">
        <VariablePoolConnectionManager initPoolSize="10" minPoolSize="5"
          maxPoolSize="50" idleTimeout="30" waitTimeout="10"/>
      </ConnectionManager>
    </DatabaseBackend>

    <RequestManager>
      <RequestScheduler>
         <RAIDb-1Scheduler level="passThrough"/>
      </RequestScheduler>

      <RequestCache>
         <MetadataCache/>
         <ParsingCache/>                
<!--       <ResultCache granularity="table"/> -->
      </RequestCache>

      <LoadBalancer>
         <RAIDb-1>
            <WaitForCompletion policy="first"/>
            <RAIDb-1-LeastPendingRequestsFirst/>
         </RAIDb-1>
      </LoadBalancer>

      <RecoveryLog driver="org.hsqldb.jdbcDriver"
        url="jdbc:hsqldb:hsql://localhost:9004" login="TEST" password="">
        <RecoveryLogTable tableName="RECOVERY" logIdColumnType="BIGINT NOT NULL"
          vloginColumnType="VARCHAR NOT NULL" sqlColumnType="VARCHAR NOT NULL"
          extraStatementDefinition=",PRIMARY KEY (log_id)"/>
        <CheckpointTable tableName="CHECKPOINT"
          checkpointNameColumnType="VARCHAR NOT NULL"/>
        <BackendTable tableName="BACKEND"
          databaseNameColumnType="VARCHAR NOT NULL"
          backendNameColumnType="VARCHAR NOT NULL"
          checkpointNameColumnType="VARCHAR NOT NULL"/>
        <DumpTable tableName="DUMP" dumpNameColumnType="VARCHAR NOT NULL"
          dumpDateColumnType="TIMESTAMP"
          dumpPathColumnType="VARCHAR NOT NULL"
          dumpFormatColumnType="VARCHAR NOT NULL"
          checkpointNameColumnType="VARCHAR NOT NULL"
          backendNameColumnType="VARCHAR NOT NULL"
          tablesColumnType="VARCHAR NOT NULL"/>
      </RecoveryLog>
    </RequestManager>

  </VirtualDatabase>

</SEQUOIA>

- How to modify logging through $SEQUOIA_HOME/config/log4j.properties

[root@server sequoia]# diff config/log4j.properties config/log4j.properties.debugEnabled
131c131
< log4j.logger.org.continuent.sequoia.controller.backend.DatabaseBackend=INFO, Console,Filetrace
---
> log4j.logger.org.continuent.sequoia.controller.backend.DatabaseBackend=DEBUG, Console,Filetrace

First I will give you some links where you can find documentation (This documentation is also available in the docs directory of the tarball).

User guide

Admin guide

Postgres backuper problems
| Write the RequestManager element definition

[]