老陳獨白: Sequoia for EnterpriseDB Postgres Plus AS 8.3 資料庫集群簡明安裝指南

Sequoia下載地址
理論上這個Sequoia可以為所有提供JDBC驅動的資料庫實現集群，是C-JDBC項目的一個很好的擴展
https://forge.continuent.org/frs/?group_id=6

環境：
Red Hat Enterprise Linux 5 Update 2
EnterpriseDB Postgres Plus Advanced Server 8.3
Sequoia 2.10.10

兩台伺服器
controller1：192.168.100.21
controller2：192.168.100.22

gogogogogogogogogogogogogogogogogogogogogogo

一、安裝前準備(在兩台服務器中進行以下操作)
1. JDK安裝
2. 設置NTP時間服務，使兩台伺服器保持時間同步
3. 安裝EDB及Sequoia(將到/opt/sequoia-2.10.10xxxxx目錄)
4. 設置EDB網路連接關係
      修改 /opt/PostgresPlus/8.3AS/data/pg_hba.conf
      找到：
         host all       all       127.0.0.1/32       md5
      在這一行之前加入：
         host all       all       192.168.100.21/32    trust
         host all       all       192.168.100.22/32    trust
5. 設置環境變數
      修改 /etc/bashrc
         加入:
         JAVA_HOME=/usr/java /jdk1.5.0_13
         EDB_BIN=/opt/PostgresPlus/8.3AS/dbserver/bin
         SEQUOIA_HOME=/opt/sequoia-2.10.10-bin
         PATH=$PATH [點擊圖片可在新視窗打開] EDB_BIN
         export PATH JAVA_HOME SEQUOIA_HOME
6. reboot
7. 初始化EDB，在兩台controller上建立集群資料庫及其RecoveryLog庫
      # edb-psql edb enterprised b
      輸入密碼後執行以下SQL語句
         CREATE ROLE "edbCluster" LOGIN ENCRYPTED PASSWORD 'md5fe5cf0e29db4880fbb6e05cff4d17677'
            NOINHERIT CREATEDB
            VALID UNTIL 'infinity';
         \c edbCluster;
         CREATE DATABASE "edbCluster"
            WITH OWNER = "edbCluster"
               ENCODING = 'UTF8';
         CREATE SCHEMA "edbCluster"
            AUTHORIZATION "edbCluster";
         CREATE DATABASE "edbCluster_RecLog"
            WITH OWNER = "edbCluster"
               ENCODING = 'UTF8';
         \q;

二、配置Sequoia
1. 複製/opt/PostgresPlus/8.3AS/connectors/jdbc/edb-jdbc14.jar到/opt/sequoia-2.10.10-bin/drivers
2. 將/opt/sequoia-2.10.10-bin/lib下的sequoia-backupers.jar及jgroups-core.jar改名為*.bak
3. 將我修改好的

[點擊圖片可在新視窗打開] jgroups-core.jar
(2008-10-31 17:43:54, Size: 1.17 MB, Downloads: 0)
<附件:jgroups-core.jar>及

[點擊圖片可在新視窗打開] sequoia-backupers.jar
(2008-10-31 17:43:54, Size: 82.8 KB, Downloads: 0)
<附件:sequoia-backup.jar>到/opt/sequoia-2.10.10-bin/lib下
4. 複製/opt/sequoia-2.10.10-bin/config/virtualdatabase/hsqldb-raidb1-distribution-1.xml
      為/opt/sequoia-2.10.10-bin/config/virtualdatabase/edb-raidb1-distribution.xml
5. 修改/opt/sequoia-2.10.10-bin/config/virtualdatabase/edb-raidb1-distribution.xml
      ---------修改VDB名
      找到：
         <VirtualDatabase name="myDB">
      改為：
         <VirtualDatabase name="edbCluster">

      ---------指定資料庫Backuper，dumpServer為本機IP [兩台Controller各自修改]
      找到：
         <Backuper backuperName="Octopus"
            className="org.continuent.sequoia.controller.backup.backupers.OctopusBackuper"
            options="zip=true"/>
      改為：
         [controller1]：
            <Backuper backuperName="EdbBackuper1"
                  className="org.continuent.sequoia.controller.backup.backupers.EnterpriseDBBinaryBackuper"
                  options="dumpServer=192.168.100.21"/>
            [controller2]：
            <Backuper backuperName="EdbBackuper2"
                  className="org.continuent.sequoia.controller.backup.backupers.EnterpriseDBBinaryBackuper"
                  options="dumpServer=192.168.100.22"/>

      ---------指定Controller的Admin用戶名及密碼，及連接的User用戶名及密碼
      找到：
            <AuthenticationManager>
               <Admin>
                  <User username="admin" password=""/>
               </Admin>
               <VirtualUsers>
                  <VirtualLogin vLogin="user" vPassword=""/>
               </VirtualUsers>
            </AuthenticationManager>
      改為：
            <AuthenticationManager>
               <Admin>
                  <User username="admin" password="admin"/>
               </Admin>
               <VirtualUsers>
                  <VirtualLogin vLogin="user" vPassword="user"/>
               </VirtualUsers>
            </AuthenticationManager>

      ---------指定本地Backend資料庫，name及url為本地的資料庫參數 [兩台Controller各自修改]
      找到：
            <DatabaseBackend name="localhost1" driver="org.hsqldb.jdbcDriver" url="jdbc:hsqldb:hsql://localhost:9001" connecti>
               <ConnectionManager vLogin="user" rLogin="TEST" rPassword="">
                  <VariablePoolConnectionManager initPoolSize="10" minPoolSize="5" maxPoolSize="50" idleTimeout="30" waitTimeout="10"/>
               </ConnectionManager>
            </DatabaseBackend>
      改為：
         [controller1]：
            <DatabaseBackend name="edb1" driver="com.edb.Driver" url="jdbc:edb://192.168.100.21:5444/edbCluster" connecti>
               <ConnectionManager vLogin="user" rLogin="edbCluster" rPassword="edbCluster">
                  <VariablePoolConnectionManager initPoolSize="10" minPoolSize="5" maxPoolSize="50" idleTimeout="30" waitTimeout="10"/>
               </ConnectionManager>
            </DatabaseBackend>
         [controller2]：
            <DatabaseBackend name="edb2" driver="com.edb.Driver" url="jdbc:edb://192.168.100.22:5444/edbCluster" connecti>
               <ConnectionManager vLogin="user" rLogin="edbCluster" rPassword="edbCluster">
                  <VariablePoolConnectionManager initPoolSize="10" minPoolSize="5" maxPoolSize="50" idleTimeout="30" waitTimeout="10"/>
               </ConnectionManager>
            </DatabaseBackend>

      ---------注釋以下內容
      找到：
         <DatabaseBackend name="localhost2" driver="org.hsqldb.jdbcDriver" url="jdbc:hsqldb:hsql://localhost:9002" connecti>
               <ConnectionManager vLogin="user" rLogin="TEST" rPassword="">
                  <VariablePoolConnectionManager initPoolSize="10" minPoolSize="5" maxPoolSize="50" idleTimeout="30" waitTimeout="10"/>
               </ConnectionManager>
         </DatabaseBackend>
      改為：
         

      ---------各Controller的RecoveryLog所存放的位置 [兩台Controller各自修改]
      找到：
         <RecoveryLog driver="org.hsqldb.jdbcDriver"
            url="jdbc:hsqldb:hsql://localhost:9003" login="TEST" password="">
      改為：
         [controller1]：
            <RecoveryLog driver="com.edb.Driver"
                  url="jdbc:edb://192.168.100.21:5444/edbCluster_RecLog" login="edbCluster" password="edbCluster">
         [controller2]：
            <RecoveryLog driver="com.edb.Driver"
                  url="jdbc:edb://192.168.100.22:5444/edbCluster_RecLog" login="edbCluster" password="edbCluster">

6. 複製/opt/sequoia-2.10.10-bin/config/controller/controller-distributed-1.xml
      為/opt/sequoia-2.10.10-bin/config/controller/controller-edb.xml

7. 修改/opt/sequoia-2.10.10-bin/config/controller/controller-edb.xml
      ---------修改監聽的網路位址及埠
      找到：
         <Controller ipAddress="127.0.0.1" port="25322">
      改為：
         <Controller ipAddress="192.168.100.21" port="25322">
      ---------修改VDB所用的設定檔及名字
      找到：
         <VirtualDatabase c virtualDatabaseName="myDB" autoEnableBackends="true"/>
      改為：
         <VirtualDatabase c virtualDatabaseName="edbCluster" autoEnableBackends="true"/>

8. 修改/opt/sequoia-2.10.10-bin/config/sequencer.xml [兩台Controller各自修改]
      找到：
         <UDP mcast_port="45566"
      改為：
         [controller1]：
            <UDP bind_addr="192.168.100.21"
                     mcast_port="45566"
         [controller2]：
            <UDP bind_addr="192.168.100.22"
                     mcast_port="45566"


三、起動Sequoia
1. 在contoller1中運行
      # /opt/sequoia-2.10.10-bin/bin/controller.sh -f /opt/sequoia-2.10.10-bin/config/controller/controller-edb.xml
2. 在contoller2中運行
      # /opt/sequoia-2.10.10-bin/bin/controller.sh -f /opt/sequoia-2.10.10-bin/config/controller/controller-edb.xml
------這兩步沒有Error證明系統可以正常啟動了

四、初始化Sequoia
controller1：
      # /opt/sequoia-2.10.10-bin/bin/console.sh

         localhost:1090 > admin edbCluster
         Virtual database Administrator Login > admin
         Virtual database Administrator Password > admin
         Ready to administrate virtual database edbCluster

         edbCluster(admin) > initialize edb1
         Virtual Database edbCluster has been successfully initialized from backend edb1

         edbCluster(admin) > backup edb1 initial_dump EdbBackuper1 /tmp
         Backend login for backup process > enterprisedb
         Backend password for backup process > edb
         Backup backend edb1 in dump file initial_dump
         13:50:02,943 INFO  controller.RequestManager.edbCluster Starting backup of backend edb1
         13:50:03,046 INFO  backup.backupers.NativeCommandExec Starting execution of "pg_dump -h 192.168.100.21 -p 5444 -U enterprisedb  --format=c -f /tmp/initial_dump edbCluster"
         13:50:03,668 INFO  backup.backupers.NativeCommandExec Command "pg_dump -h 192.168.100.21 -p 5444 -U enterprisedb  --format=c -f /tmp/initial_dump edbCluster" logged 0 errors and terminated with exitcode 0
         13:50:03,764 INFO  controller.RequestManager.edbCluster Backup has completed.

         edbCluster(admin) > enable edb1
         Enabling backend edb1 from its last known checkpoint
         13:52:03,810 INFO  DatabaseBackend.edbCluster.edb1 Detected backend as: EnterpriseDB
         13:52:29,263 INFO  controller.RequestManager.edbCluster Setting new virtual database schema.
         13:52:29,370 INFO  controller.recoverylog.RecoverThread Starting recovery
         13:52:29,375 INFO  controller.RequestManager.edbCluster Suspending activity for edbCluster
         13:52:29,514 INFO  controller.RequestManager.edbCluster All activity is suspended for edbCluster
         13:52:29,555 INFO  controller.recoverylog.RecoverThread Recovery completed
         13:52:50,817 INFO  controller.RequestManager.edbCluster Resuming activity for edbCluster
         13:52:50,974 INFO  controller.RequestManager.edbCluster All activity is now resumed for edbCluster
         13:52:50,975 INFO  controller.recoverylog.RecoverThread Database backend edb1 is now enabled

         edbCluster(admin) > transfer dump initial_dump 192.168.100.22:1090
         Transferring dump initial_dump to controller 192.168.100.22:1090...
         13:58:25,689 INFO  controller.backup.BackupManager Using provided dump-server address: 192.168.100.21
         13:58:25,699 INFO  controller.backup.BackupManager Dump server started @ 0.0.0.0/0.0.0.0:46809
         13:58:25,963 INFO  controller.backup.BackupManager Client connected to dump server from /192.168.100.22:38963
         13:58:26,047 INFO  controller.backup.BackupManager Dump server terminated.
         Done

         edbCluster(admin) > expert on
         Expert mode on

         edbCluster(admin) > restore log initial_dump 192.168.100.21:1090
         A restore log command must be applied to a remote controller
         edbCluster(admin) > restore log initial_dump 192.168.100.22:1090
         14:01:09,632 INFO  controller.RequestManager.edbCluster Suspending activity for edbCluster
         14:01:09,880 INFO  controller.RequestManager.edbCluster All activity is suspended for edbCluster
         14:01:09,932 INFO  controller.virtualdatabase.edbCluster Storing checkpoint now-192.168.100.21:25322-20081021140109631+0800
         14:01:09,937 WARN  controller.virtualdatabase.edbCluster No information has been found for remote controller Member(address=/192.168.100.21:32787, uid=edbCluster)
         14:01:10,014 WARN  sequoia.controller.recoverylog Checkpoint now-192.168.100.21:25322-20081021140109631+0800 was stored
         14:01:10,260 INFO  controller.RequestManager.edbCluster Resuming activity for edbCluster
         14:01:10,390 INFO  controller.RequestManager.edbCluster All activity is now resumed for edbCluster
         Done

controller2：
      # /opt/sequoia-2.10.10-bin/bin/console.sh

         localhost:1090 > admin edbCluster
         Virtual database Administrator Login > admin
         Virtual database Administrator Password > admin
         Ready to administrate virtual database edbCluster

         edbCluster(admin) > show dumps
         +--------------+----------------------------+--------------------------+------+------------------+---------+--------+
         | Name       | Checkpoint                | Format                | Path | Date          | Backend | Tables |
         +--------------+----------------------------+--------------------------+------+------------------+---------+--------+
         | initial_dump | Initial_empty_recovery_log | EnterpriseDB Binary Dump | /tmp | 10/21/08 1:50 PM | edb1 | *    |
         +--------------+----------------------------+--------------------------+------+------------------+---------+--------+

         edbCluster(admin) > restore backend edb2 initial_dump
         Backend login for restore process > enterprisedb
         Backend password for restore process > edb
         Restoring backend edb2 with dump initial_dump

         edbCluster(admin) > enable edb2
         Enabling backend edb2 from its last known checkpoint

五、測試
      controller2:
         edbCluster(admin) > quit

         localhost:1090 > sql client jdbc:sequoia://192.168.100.21:25322,192.168.100.22:25322/edbCluster
         Login > user
         Password > user

         jdbc:sequoia://192.168.100.21:25322,192.168.100.22:25322/edbCluster (user) > create table test(i integer);
         Affected rows: 0
         Query executed in 7 s 737 ms .

         jdbc:sequoia://192.168.100.21:25322,192.168.100.22:25322/edbCluster (user) > select * from test;
         Query executed in 5 s 237 ms .

         jdbc:sequoia://192.168.100.21:25322,192.168.100.22:25322/edbCluster (user) > insert into test values(1);
         Affected rows: 1
         Query executed in 5 s 801 ms .

         jdbc:sequoia://192.168.100.21:25322,192.168.100.22:25322/edbCluster (user) > insert into test values(2);
         Affected rows: 1
         Query executed in 0 s 325 ms .

         jdbc:sequoia://192.168.100.21:25322,192.168.100.22:25322/edbCluster (user) > select * from test;
         +---+
         | i |
         +---+
         | 1 |
         | 2 |
         +---+

         Query executed in 0 s 114 ms .

此時可以通過Postgres Studio查看兩台伺服器上的edbCluster資料庫，我們所做的create table、insert等操作，在兩個資料庫中同時生效，由此證明並行資料庫已經生效

到些集群中的資料即時同步及負載均衡查詢已經實現

哪一但集群中的一個資料庫backend宕機，後應該如何回復？待續……    zhuangyu http://bbs.chinaunix.net/thread-1303431-1-1.html

老陳獨白

2011年12月6日星期二

Sequoia for EnterpriseDB Postgres Plus AS 8.3 資料庫集群簡明安裝指南

沒有留言:

2011年12月6日 星期二

Sequoia for EnterpriseDB Postgres Plus AS 8.3 資料庫集群簡明安裝指南

沒有留言:

2011年12月6日星期二