SQL server 迁移至GBase 8c操作指南

SQL server 迁移至GBase 8c操作指南

迁移数据库SQL server GBase 8c

---

首先下载依赖包perl 5.0以上版本，kettle linux
环境可以部署在gbase环境上

使用手册：

1、导出需要迁移数据库及表对象、视图、存储过程、自定义函数

登陆sqlserver management studio 选择数据库—右击--任务--生成脚本

选择对应的对象后，进行下一步----下一步---完成（生成.sql文件）

2、装载sqlserver2pg

可以装在windows上，也可以装在linux下，我下面测试是在linux
该工具依赖的插件：perl 可以通过yum install -y perl下载
需要数据迁移依赖工具：kettle java1.8

操作步骤

• 1）建议部署再gbase 用户下，支持gsql
  下载路径：链接:
  https://pan.baidu.com/s/10kBPAhfqHSsnu5nM7UuSng?pwd=3dkn
  解压sqlserver2pgsql-master.zip文件 ：unzip sqlserver2pgsql-master.zip
  将生成的sqlserver脚本传至sqlserver2pgsql-master路径下

• 2）装在kettle路径

下载路径：链接: https://pan.baidu.com/s/1l_NLxSjd3Np1Xh0jeZPYdg?pwd=h7n7
使用unzip 进行解压
解压后移至 cp -r data-integration/ /home/gbase/sqlserver2pgsql-master/
依赖：yum intsall -y webkitgtk3.x86_64 下载完依旧无法使用：wget http://ftp.pbone.net/mirror/ftp5.gwdg.de/pub/opensuse/repositories/home:/matthewdva:/build:/EPEL:/el7/RHEL_7/x86_64/webkitgtk-2.4.9-1.el7.x86_64.rpm

    chmod +x -R *.sh
   ./kitchen.sh
   ./spoon.sh &  能弹出界面

3、执行迁移

--1、文件转换测试

./sqlserver2pgsql.pl -f sqlserver_sql_dump    -b output_before_script -a output_after_script   -u output_unsure_script

或者使用配置文件
./sqlserver2pgsql.pl -conf example_conf_file -f mydatabase_dump.sql

--2、生成kettle文件

./sqlserver2pgsql.pl -b output_before_script -a output_after_script -u output_unsure_script -k data-integration/ -sd NIS -sh 172.16.162.11 -sp 1433 -su sa -sw gbase;123 -pd test2 -ph 172.16.5.102 -pp 5432 -pu regress -pw Gbase_1234 -f NIS.sql

--3、执行表结构

gsql -U regress -W Gbase_1234 test2 -f output_before_script

--4、适用kettle迁移数据

1）使用界面化显示

export DISPLAY=172.16.162.11:0.0
./spoon.sh &

自动会打开3.2生成的kettle文件(migration.kjb)，直接执行即可。

2)使用命令行的方式

注意：jtds-1.3.1-dist的jdbc驱动需要存放到lib下

./kitchen.sh -file=../data-integration_record/migration.kjb -level=detailed

使用sql执行：修改表约束及索引等

gsql -d test2 -h 172.16.5.102 -p 5432 -U regress -W Gbase_1234 -f output_after_script

3）拷贝文件windows kettle 进行数据数据传输^[1]

修改

这个两文件中的路径为当前windows路径

以上是全量迁移（migration.kjb）与增量迁移(incremental.kjb)的方案。

#!/usr/bin/env python 
# -*- coding: utf-8 -*-
# @Time    : 2023/6/9 18:12
# @Author  : zhaodongz
# @Site    : 
# @File    : sqlserver_8c.py
# @Software: PyCharm


import psycopg2
import pymssql
import time

class CompareDataBase():
    Suc = 0
    Fal = 0
    faillist=[]
    def __init__(self):

        self.pgconn = psycopg2.connect(database="***", host="***", port=5432, user="***",
                                            password="***")

        self.msconn = pymssql.connect(host="***", user="sa", password="***", database="***")

    def commit(self):
        self.pgconn.commit()

    def close(self):
        self.pgconn.close()
        self.msconn.close()

    def rollback(self):
        self.pgconn.rollback()

    def exesyncdb(self):
        mscursor = self.msconn.cursor()
        sql = (
            "SELECT COUNT(COLUMNNAME) AS CT,TABLENAME FROM (SELECT A.NAME AS COLUMNNAME,B.NAME AS TABLENAME FROM SYSCOLUMNS A RIGHT JOIN SYSOBJECTS B ON A.ID=B.ID WHERE B.TYPE='U' AND B.NAME NOT IN ('dtproperties','0626')) A GROUP BY TABLENAME ")
        mscursor.execute(sql)
        table = mscursor.fetchall()
        print("total table %d" % len(table))
        if (table is None or len(table) <= 0):
            return
        else:
            for row in table:
                self.executeTable(row[1], row[0])
                print("%s is execute success" % row[1])

    def comparedb(self):
        mscursor = self.msconn.cursor()
        sql = (
            "SELECT COUNT(COLUMNNAME) AS CT,TABLENAME FROM (SELECT A.NAME AS COLUMNNAME,B.NAME AS TABLENAME FROM SYSCOLUMNS A RIGHT JOIN SYSOBJECTS B ON A.ID=B.ID WHERE B.TYPE='U' AND B.NAME NOT IN ('dtproperties','0626')) A GROUP BY TABLENAME ")
        mscursor.execute(sql)
        table = mscursor.fetchall()
        print("total table %d" % len(table))
        if (table is None or len(table) <= 0):
            return
        else:
            for row in table:
                self.compareTable(row[1])

    def executeTable(self, tablename, count):
        # print tablename
        sql1 = "SELECT * FROM %s" % tablename
        print(sql1)
        mscursor = self.msconn.cursor()
        mscursor.execute(sql1)
        table = mscursor.fetchall()
        if (table is None or len(table) <= 0):
            mscursor.close()
            return
        lst_result = self.initColumn(table)
        # print "column"
        mscursor.close()
        print("execute sync  %s data to postgresql" % tablename)
        sql2 = self.initPgSql(tablename, count)
        pgcursor = self.pgconn.cursor()
        pgcursor.executemany(sql2, lst_result)
        pgcursor.close()

    def compareTable(self, tablename):
        # print tablename
        sql1 = "SELECT count(*) FROM %s" % tablename
        mscursor = self.msconn.cursor()
        mscursor.execute(sql1)
        ms_res = mscursor.fetchall()
        mscursor.close()
        pgcursor = self.pgconn.cursor()
        pgcursor.execute(sql1)
        pg_res = pgcursor.fetchall()
        pgcursor.close()
        res = ""
        if ms_res[0][0] == pg_res[0][0]:
            res = "ok"
            self.Suc += 1
        else:
            res = "fail"
            self.Fal += 1
            self.faillist.append(tablename)
        print("execute compare  table  %s data  GBase8c: %s  sql server:%s result: %s" % (
        tablename, pg_res[0][0], ms_res[0][0], res))

if __name__ == "__main__":
    sdb = CompareDataBase()
    start_time = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
    print("task start time %s" % start_time)
    try:
        sdb.comparedb()
    except Exception as e:
        print(e)
        sdb.rollback()
    else:
        sdb.commit()
    sdb.close()
    end_time = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
    print("task end time %s" % end_time)
    print("total table conut:Success: %s; Fail: %s" % (sdb.Suc,sdb.Fal))
    if sdb.Fal > 0:
        print("Fail table %s" % sdb.faillist)
0.1'

enter description here

---

1. ---推荐使用 ↩