kylin-v10sp2-Babelfish for PostgreSQL

环境准备

x86_linux_kylin v10 sp2

1、依赖

yum makecache
yum install -y uuid-devel

2、源代码

下载支持babelfish的pg，也是babelfish社区维护更新

cd /opt#pg源码，支持babelfish版
git clone https://github.com/babelfish-for-postgresql/postgresql_modified_for_babelfish.git#babelfish扩展
git clone https://github.com/babelfish-for-postgresql/babelfish_extensions.git

git clone https://github.com/babelfish-for-postgresql/postgresql_modified_for_babelfish.git

git clone https://github.com/babelfish-for-postgresql/babelfish_extensions.git

 3、安装cmake和antlr

babelfishpg_tsql需要antlr，建议安装和babelfish编译相同的版本，antlr-4.9.2

3.1、utfcpp（antlr依赖）可以连接外网忽略此步

cd /usr/src/
wget https://github.com/nemtrif/utfcpp/archive/refs/tags/v3.1.1.tar.gz
tar -xvf v3.1.1.tar.gz
cd utfcpp-3.1.1
cmake -B bin -DUTF8_TESTS=OFF
cmake --build bin --config Debug
cmake --install bin --config Debug#环境变量  暂时未测试
export UTFCPP_HOME=/usr/local/utfcpp-3.1.1
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/local/lib
export CPLUS_INCLUDE_PATH=$CPLUS_INCLUDE_PATH:/usr/local/include

3.2、安装antlr

cd /opt
wget https://codeload.github.com/antlr/antlr4/zip/refs/tags/4.9.3
unzip 4.9.3
cd antlr4-4.9.3/runtime/Cpp
mkdir build && cd build
EXTENSIONS_SOURCE_CODE_PATH=/opt/babelfish_extensions
cmake .. -DCMAKE_INSTALL_PREFIX=/usr/local/antlr-4.9.3 \
-DANTLR_JAR_LOCATION=/opt/babelfish_extensions/contrib/babelfishpg_tsql/antlr/thirdparty/antlr/antlr-4.9.3-complete.jar#修改git源  https://github.com/antlr/antlr4/pull/3192
#git config --global url.https://github.com/.insteadOf git://github.com/
make -j4
make install

4、编译babelfish的pg

#创建安装目录
INSTALLATION_PATH=/usr/local/pgsql 
mkdir $INSTALLATION_PATH#构建配置
cd /opt/postgresql_modified_for_babelfish./configure CFLAGS="-ggdb" \
--prefix=$INSTALLATION_PATH \
--enable-debug \
--with-libxml \
--with-uuid=ossp \
--with-icu \
--with-extra-version="Babelfish for PostgreSQL"#编译pg
make -j4 && make install   #编译pg扩展
cd contrib 
make -j4 && make install#copy antlr
cp /usr/local/antlr-4.9.3/lib/libantlr4-runtime.so /usr/local/pgsql/lib/
cp /usr/local/antlr-4.9.3/lib/libantlr4-runtime.so.4.9.3 /usr/local/pgsql/lib/

5、构建安装babelfish扩展

设置临时环境变量 

export PG_CONFIG=$INSTALLATION_PATH/bin/pg_config
export PG_SRC=/opt/postgresql_modified_for_babelfish
export cmake=/usr/local/cmake-3.21.1/bin/cmake#antlr
export CPLUS_INCLUDE_PATH=$CPLUS_INCLUDE_PATH:/usr/local/antlr-4.9.3/include/antlr4-runtime
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/local/antlr-4.9.3/lib

编译

#babelfishpg_money
cd /opt/babelfish_extensions/contrib/babelfishpg_money
make
make install#babelfishpg_common
cd /opt/babelfish_extensions/contrib/babelfishpg_common
make 
make install#babelfishpg_tds
cd /opt/babelfish_extensions/contrib/babelfishpg_tds
make 
make install#babelfishpg_tsql
cd /opt/babelfish_extensions/contrib/babelfishpg_tsql
make
make install

编辑babelfishpg_tsql可能出现的错误：

/usr/bin/ld: src/pl_comp-2.o:/opt/babelfish_extensions/contrib/babelfishpg_tsql/src/pl_comp-2.c:27: multiple definition of `pltsql_curr_compile_body_lineno'; src/pl_comp.o:/opt/babelfish_extensions/contrib/babelfishpg_tsql/src/pl_comp.c:65: first defined here
/usr/bin/ld: src/backend_parser/gram-backend.o:(.bss+0x20): multiple definition of `pgtsql_base_yydebug'; src/backend_parser/parser.o:(.bss+0x0): first defined here

参考官方PR修改记录Could not compile extensions with LLVM · Issue #2423 · babelfish-for-postgresql/babelfish_extensions (github.com)

不知道什么原因代码没有合并到分支，修改后即可通过编译。

6、创建postgres用户，修改pg配置

6.1、创建用户

useradd postgres -m

6.2、环境变量

vi /home/postgres/.bash_profile
export INSTALLATION_PATH=/usr/local/pgsql
export PGHOME=$INSTALLATION_PATH
export PGDATA=$PGHOME/data
export PATH=$PGHOME/bin:$PATH
source /home/postgres/.bash_profile

6.3、修改配置

初始化 

initdb

修改auto.conf 

vi /usr/local/pgsql/data/postgresql.auto.conf
listen_addresses = '*'
port = 5432
shared_preload_libraries = 'babelfishpg_tds'

修改hba.conf 

vi data/pg_hba.confhost    all             all             0.0.0.0/0                md5

启动 

pg_ctl start[postgres@baidu pgsql]$ psql
psql (16.3Babelfish for PostgreSQL)
Type "help" for help.postgres=# select version();version
----------------------------------------------------------------------------------------------------------------------------------PostgreSQL 16.3Babelfish for PostgreSQL on x86_64-pc-linux-gnu, compiled by gcc (GCC) 10.3.1 20210422 (Red Hat 10.3.1-1), 64-bit
(1 row)

7、创建扩展

migration_mode有2种模式，在single-db的情况下，MS SQL数据库被映射到PostgreSQL中的模式。在multi-db情况下，可使用多个数据库。

--创建用户
create user testuser superuser password 'testuser';
--创建目标库
create database testdb owner testuser;
--切换到新库
\c testdb--创建 Babelfish 扩展
create extension if not exists "babelfishpg_tds" cascade;--设置 Babelfish 数据库名称
alter system set babelfishpg_tsql.database_name = 'testdb';--设置数据库迁移模式---支持多数据库模式
alter database testdb set babelfishpg_tsql.migration_mode = 'multi-db';--重新加载 PostgreSQL 配置，使之前的配置生效
select pg_reload_conf();--初始化 Babelfish，设置必要的系统对象和配置
call sys.initialize_babelfish('testuser');

注意：一旦初始化成功，比如想删掉用户之类的，需要tsql 或 sqlcmd，通过发送sql的方式连接pg删除。

8、使用tsql测试

#安装tsql
yum install freetds -y[root@baidu /]# tsql -S localhost -U testuser -P testuser;
locale is "en_US.UTF-8"
locale charset is "UTF-8"
using default charset "UTF-8"
Changed database context to 'master'.
1> select version();
2> go
version
Babelfish for PostgreSQL with SQL Server Compatibility - 12.0.2000.8
Jun 19 2024 15:51:16
Copyright (c) Amazon Web Services
PostgreSQL 16.3Babelfish for PostgreSQL on x86_64-pc-linux-gnu (Babelfish 4.3.0)
(1 row affected)
1> exit