sql-server 迁移数据库到postresql 方案Babelfish for PostgreSQL14.2 Ubuntu 20.4

sql-server 迁移数据库到postresql 方案 数据库 Postgresql 14.2

参考：
https://github.com/babelfish-for-postgresql/babelfish_extensions/blob/BABEL_1_X_DEV/contrib/README.md

https://mp.weixin.qq.com/s/-Rwf9R5utg_wYi91ZcUsFA
https://www.modb.pro/db/181528

https://www.modb.pro/db/237005
https://www.modb.pro/db/402380

This package includes 4 extensions:

    babelfishpg_tsql
        Supports the tsql language.
    babelfishpg_tds
        Supports the tds connection.
    babelfishpg_common
        Supports the various datatypes in MSSQL.
    babelfishpg_money
        supports the money type in MSSQL. This is a variation of the opensource fixeddecimal extension.

before setup config env

postgres@iZbp179u0bgzhoi4l5mu3lZ:~$ more /etc/issue
Ubuntu 20.04.4 LTS

postgres@iZbp179u0bgzhoi4l5mu3lZ:~$ cmake -version
cmake version 3.20.6

CMake suite maintained and supported by Kitware (kitware.com/cmake).

vi  .bashrc
export PATH="$PATH:/opt/mssql-tools/bin"

export EXTENSIONS_SOURCE_CODE_PATH="/opt/babelfish_extensions"

export INSTALLATION_PATH=/opt/postgres

export PG_CONFIG=$INSTALLATION_PATH/bin/pg_config
export PG_SRC=/opt/postgresql_modified_for_babelfish
export cmake=/usr/local/bin/cmake
~                                     

How do I build the extensions?

The following build instructions comply with Ubuntu 20.04 and Amazon Linux 2 environment.
Build the Postgres engine

1 First install dependent tools and libraries

Install postgresql-devel, libicu, libxml2, openssl and uuid-devel packages in order to build Babelfish.

You'll also need to install gcc, gcc-c++, java and bison.

    sudo apt-get install uuid-dev openjdk-8-jre \
                        libicu-dev libxml2-dev openssl libssl-dev python-dev \
                        libossp-uuid-dev libpq-dev pkg-config g++ build-essential bison 

For RHEL-flavoured distributions, this is the command for dealing with the required dependencies:

    sudo yum install libicu-devel libxml2-devel \
                     openssl-devel uuid-devel postgresql-devel gcc gcc-c++ java

2 Now it’s time to build the Postgres engine.

 In the Postgres engine directory, run these sequence of commands to build the PG engine modified for Babelfish (Github repo: https://github.com/babelfish-for-postgresql/postgresql_modified_for_babelfish):

    ./configure --prefix=$HOME/postgres/ --without-readline --without-zlib --enable-debug CFLAGS="-ggdb" --with-libxml --with-uuid=ossp --with-icu
    make -j 4 2>error.txt
    make install
    make check

3 Also build and install the extensions because uuid-ossp.so is a runtime dependency for babelfish:

    cd contrib && make && sudo make install

4 Install & build dependencies

4.1 Get the latest version of cmake (version 3+ is required)

To get and use the latest version of cmake:

    wget https://github.com/Kitware/CMake/releases/download/v3.20.6/cmake-3.20.6-linux-x86_64.sh
    sh cmake-3.20.6-linux-x86_64.sh

4.2 Install ANTLR

The babelfishpg_tsql contrib has a plug-in parser generated by ANTLR, which depends on cmake and antlr4-cpp-runtime-4.9.3. Unfortunately, there aren't binaries for C++ targets available. You'll also need have uuid-devel installed in order to install antlr4-cpp-runtime-4.9.3.

First copy the jar file in contrib/babelfishpg_tsql/antlr/thirdparty/antlr/ to another location:

cd babelfish_extensions/contrib/babelfishpg_tsql/antlr/thirdparty/antlr/
sudo cp antlr-4.9.3-complete.jar /usr/local/lib

Compile antlr4:

    wget http://www.antlr.org/download/antlr4-cpp-runtime-4.9.3-source.zip
    unzip -d antlr4 antlr4-cpp-runtime-4.9.3-source.zip 
    cd antlr4
    mkdir build && cd build 
    cmake .. -DANTLR_JAR_LOCATION=/usr/local/lib/antlr-4.9.3-complete.jar -DCMAKE_INSTALL_PREFIX=/usr/local -DWITH_DEMO=True
    make
    sudo make install

  Copy libantlr4-runtime to postgres/lib

    cp /usr/local/lib/libantlr4-runtime.so.4.9.3 ~/postgres/lib/

If you come across the error

-- Checking for module 'uuid'
--   No package 'uuid' found
CMake Error at /usr/local/share/cmake-3.21/Modules/FindPkgConfig.cmake:554 (message):
    A required package was not found
Call Stack (most recent call first):
    /usr/local/share/cmake-3.21/Modules/FindPkgConfig.cmake:776 (_pkg_check_modules_internal)
    CMakeLists.txt:44 (pkg_check_modules)

4.3 Check that you have uuid-devel installed. If so, go to antlr4/CMakeLists.txt and comment out the line pkg_check_modules(UUID REQUIRED uuid) by adding a # to the beginning of the line.

More information about installing ANTLR4 can be found at this link.

Set environment variables and paths
    Set the PG_CONFIG environment variable to where you installed the DB engine postgresql_modified_for_babelfish. For example, I installed it under ~/postgres (the default location), so I set PG_CONFIG as follows:

export PG_CONFIG=~/postgres/bin/pg_config

    Set PG_SRC to where the DB engine source package is as we need access to a few engine source files in order to build the Babelfish extensions. For example:

export PG_SRC=~/postgresql_modified_for_babelfish

    Set the cmake environment variable to where you installed cmake (as defined above in step 2). For example:

export cmake=/usr/local/bin/cmake

    Update the file contrib/babelfishpg_tsql/antlr/CMakeLists.txt with the correct antlr4-runtime path (if not there already). For example:

SET (MYDIR /usr/local/include/antlr4-runtime/)

Build the extensions

make and make install each extension within the extension project’s contrib directory:

cd contrib/babelfishpg_money
make && make install
cd ../babelfishpg_common
make && make install
cd ../babelfishpg_tds
make && make install
cd ../babelfishpg_tsql
make && make install

How to install the extensions and how to connect via SQLCMD?

Install the SQL server command line tools by following steps 1 - 3 under Install the SQL Server command-line tools. Use the RHEL7 URL in step 1.
    For convenience, add /opt/mssql-tools/bin/ to your PATH environment variable. This enables you to run the tools without specifying the full path. For Z shell (the default shell for Cloud Desktop), add the following line to your ~/.zshrc file:

export PATH=/opt/mssql-tools/bin:$PATH

From where you installed postgres in the ./configure step adjust the paths of these commands and run them to start the Postgres server:

    ~/postgres/bin/initdb -D ~/postgres/data/
    ~/postgres/bin/pg_ctl -D ~/postgres/data/ -l logfile start

Modify ~/postgres/data/postgresql.conf by uncommenting and adjusting the following 2 properties:

    listen_addresses = '*'
    shared_preload_libraries = 'babelfishpg_tds'

Modify ~/postgres/data/pg_hba.conf to allow connections from allowed IP addresses, replacing 10.x.y.z with your IP address. E.g.

      host    all             all     0.0.0.0/0            md5

Now run this to apply the changes:

    ~/postgres/bin/pg_ctl -D ~/postgres/data/ -l logfile restart

Connect via psql using the command ~/postgres/bin/psql -U your_user_name. Create the extension and set up essential parameters. Please be aware you need to choose either ‘single-db’ or ‘multi-db’ mode during this provisioning step and you CAN NOT change it later. Refer to our documentation page for more information on ‘single-db’ vs ‘multi-db’ mode.

select * from pg_available_extensions WHERE name like 'postgis%'
          babelfishpg_money
 
     babelfishpg_common
 
    babelfishpg_tds
 
      babelfishpg_tsql

    CREATE USER babelfish_user WITH CREATEDB CREATEROLE PASSWORD '12345678' INHERIT;
    DROP DATABASE IF EXISTS babelfish_db;
    CREATE DATABASE babelfish_db OWNER babelfish_user;
    \c babelfish_db
    CREATE EXTENSION IF NOT EXISTS "babelfishpg_tds" CASCADE;
    GRANT ALL ON SCHEMA sys to babelfish_user;
    ALTER SYSTEM SET babelfishpg_tsql.database_name = 'babelfish_db';
    ALTER SYSTEM SET babelfishpg_tds.set_db_session_property = true;
    ALTER DATABASE babelfish_db SET babelfishpg_tsql.migration_mode = 'single-db'|'multi-db';
    SELECT pg_reload_conf();
    CALL SYS.INITIALIZE_BABELFISH('babelfish_user');

    If you run into errors connecting to psql such as psql: error: could not connect to server: No such file or directory try giving permissions by using this command instead:

    sudo ~/postgres/bin/psql -d postgres -U your_user_name

Try connecting to Babelfish via SQLCMD

    sqlcmd -S localhost -U babelfish_user -P 12345678

验证数据库引擎

https://www.modb.pro/db/237005

SELECT @@version,@@servername ;

SELECT * from sysdatabases s;

SELECT * from pg_user;

SELECT * from pg_database;

create database abc

重新连接到 abc库
在 abc库 做如下操作:

create table xxx (id int);
insert into xxx values(1),(2),(3);

SELECT * from xxx;