digoal
2023-01-16
PostgreSQL , PolarDB , babelfish , sql server 兼容
好消息: PolarDB 新版本也会支持babelfish插件.
本文测试使用了babelfish最新的stable分支, 如果又有了新的分支, 大家安装时请使用新分支.
babelfish包含了几个部分:
1、改版的postgresql, 主要增加协议层hook等, 如果postgresql 社区接收了这些改动, 以后就不需要用这个改版分支了.
2、babelfish插件, 其实是4个插件
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.
3、兼容性评估工具, 类似阿里云的adam
macOS docker的使用可以参考:
《如何用 PolarDB 证明巴菲特的投资理念 - 包括PolarDB on Docker简单部署》
docker pull centos:centos8.4.2105
docker run -d -it --cap-add=SYS_PTRACE --privileged=true --name centos8 centos:centos8.4.2105
docker exec -it centos8 /bin/bash
sed -i 's/mirrorlist/#mirrorlist/g' /etc/yum.repos.d/CentOS-*
sed -i 's|#baseurl=http://mirror.centos.org|baseurl=http://vault.centos.org|g' /etc/yum.repos.d/CentOS-*
dnf install 'dnf-command(config-manager)'
dnf install epel-release epel-next-release
dnf config-manager --set-enabled powertools
yum install -y git gcc readline-devel zlib-devel make bison libxslt flex man wget docbook-style-xsl libicu-devel libxml2-devel openssl-devel uuid-devel libuuid-devel gcc-c++ java sudo unzip which
useradd postgres
su - postgres
下载安装 postgresql babelfish改版分支
cd ~
git clone --depth 1 -b BABEL_3_X_DEV__PG_15_X https://github.com/babelfish-for-postgresql/postgresql_modified_for_babelfish.git
cd postgresql_modified_for_babelfish
./configure --prefix=/home/postgres/pg15.1 --with-icu --with-libxml --with-uuid=ossp
make world -j 8
make install-world
配置环境变量
cd ~
vi .bash_profile
# add
alias ll='ls -larht'
alias rm='rm -i'
export LANG=en_US.UTF8
export PGHOME=/home/postgres/pg15.1
export PATH="$PGHOME/bin:$PATH"
export MANPATH="$PGHOME/share/man:$MANPATH"
export PGLOCALEDIR=$PGHOME/share/locale
export PGDATA=/home/postgres/pgdata
export PGHOST=$PGDATA
export PGPORT=1921
export PGDATABASE=postgres
export PGUSER=postgres
export PGPASSWORD=pg123
. ./.bash_profile
初始化数据库
initdb -D $PGDATA -U postgres -E UTF8 --lc-collate=C --lc-ctype=en_US.UTF8 --locale-provider=icu --icu-locale=C
配置数据库参数和数据库防火墙
cd $PGDATA
vi postgresql.auto.conf
listen_addresses = '0.0.0.0'
port = 1921
max_connections = 100
unix_socket_directories = '/tmp,.'
unix_socket_permissions = 0700
tcp_keepalives_idle = 60
tcp_keepalives_interval = 60
tcp_keepalives_count = 10
tcp_user_timeout = 60
client_connection_check_interval = 60
password_encryption = scram-sha-256
shared_buffers = 128MB
dynamic_shared_memory_type = posix
vacuum_cost_delay = 0
bgwriter_delay = 10ms
bgwriter_lru_maxpages = 1000
bgwriter_lru_multiplier = 5.0
effective_io_concurrency = 0
max_parallel_workers_per_gather = 0
wal_level = replica
synchronous_commit = off
full_page_writes = on
wal_writer_delay = 10ms
checkpoint_timeout = 15min
max_wal_size = 1GB
min_wal_size = 80MB
random_page_cost = 1.1
log_destination = 'csvlog'
logging_collector = on
log_truncate_on_rotation = on
log_timezone = 'UTC'
autovacuum_vacuum_cost_delay = 0ms
vacuum_freeze_table_age = 1500000000
vacuum_freeze_min_age = 500000000
vacuum_multixact_freeze_table_age = 1500000000
vacuum_multixact_freeze_min_age = 50000000
datestyle = 'iso, mdy'
timezone = 'UTC'
lc_messages = 'C'
lc_monetary = 'C'
lc_numeric = 'C'
lc_time = 'C'
default_text_search_config = 'pg_catalog.english'
vi pg_hba.conf
# add
host all all 0.0.0.0/0 md5
1、先安装依赖
su - root
cd ~
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
-----------
Do you accept the license? [yn]:
y
By default the CMake will be installed in:
"/root/cmake-3.20.6-linux-x86_64"
Do you want to include the subdirectory cmake-3.20.6-linux-x86_64?
Saying no will install in: "/root" [Yn]:
Y
Using target directory: /root/cmake-3.20.6-linux-x86_64
Extracting, please wait...
Unpacking finished successfully
-----------
mkdir /usr/local/cmake
mv cmake-3.20.6-linux-x86_64/* /usr/local/cmake/
rm -rf cmake-3.20.6-linux-x86_64
2、配置环境变量
su - postgres
cd ~
vi .bash_profile
# add
export PATH=/usr/local/cmake/bin:$PATH
[postgres@faf9358f0e61 ~]$ . ./.bash_profile
[postgres@faf9358f0e61 ~]$ which cmake
/usr/local/cmake/bin/cmake
3、安装babelfish插件
下载插件
su - postgres
cd ~
git clone --depth 1 -b BABEL_3_X_DEV https://github.com/babelfish-for-postgresql/babelfish_extensions
安装antlr
su - root
cp /home/postgres/babelfish_extensions/contrib/babelfishpg_tsql/antlr/thirdparty/antlr/antlr-4.9.3-complete.jar /usr/local/lib
su - postgres
cd ~
wget --no-check-certificate 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 -j 4
cmake 如遇到如下报错, 修改antlr4/CMakeLists.txt解决:
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)
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.
su - root
[root@1829117d0f28 ~]# cd /home/postgres/antlr4/build
[root@1829117d0f28 build]# make install
检查一下antlr4的include目录, 后面安装时要配置一致
[root@1829117d0f28 ~]# find / -name antlr4-runtime
/usr/local/include/antlr4-runtime
su - postgres
cp /usr/local/lib/libantlr4-runtime.so.4.9.3 ~/pg15.1/lib/
接下来部署babelfish的几个插件, 因为使用了-Werror, 所有报警都会视为错误, 然后代码里面又有点脏, 所以需要修改一下.
先设置环境变量, 4个插件的安装都需要用到这些环境变量
export PG_CONFIG=~/pg15.1/bin/pg_config
export PG_SRC=~/postgresql_modified_for_babelfish
export cmake=/usr/local/cmake/bin/cmake
进入插件目录, 设置antlr4的include目录
cd ~/babelfish_extensions/
vi contrib/babelfishpg_tsql/antlr/CMakeLists.txt
设置:
SET (MYDIR /usr/local/include/antlr4-runtime/)
安装第一个插件babelfishpg_money
cd contrib/babelfishpg_money
make && make install
安装第二个插件babelfishpg_common
cd ../babelfishpg_common
make && make install
报错与fix如下:
报错:
src/collation.c: In function ‘BabelfishPreCreateCollation_hook’:
src/collation.c:1368:4: error: ‘strncat’ specified bound depends on the length of the source argument [-Werror=stringop-overflow=]
strncat(catcollcollate, collcollate, strlen(collcollate));
^~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
src/collation.c:1378:4: error: ‘strncat’ specified bound depends on the length of the source argument [-Werror=stringop-overflow=]
strncat(catcollctype, collcollate, strlen(collcollate));
^~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
cc1: all warnings being treated as errors
make: *** [<builtin>: src/collation.o] Error 1
fix:
vi src/collation.c
修改line 1368, 1378
// strncat(catcollcollate, collcollate, strlen(collcollate));
memcpy(strchr(catcollcollate, '\0'), collcollate, strlen(collcollate));
// strncat(catcollctype, collcollate, strlen(collcollate));
memcpy(strchr(catcollctype, '\0'), collcollate, strlen(collcollate));
安装第三个插件babelfishpg_tds
cd ../babelfishpg_tds
make && make install
报错与fix如下:
报错:
In function ‘check_version_number.part.2’,
inlined from ‘check_version_number’ at src/backend/tds/guc.c:117:1:
src/backend/tds/guc.c:127:2: error: ‘strncpy’ specified bound depends on the length of the source argument [-Werror=stringop-overflow=]
strncpy(copy_version_number,*newval,strlen(*newval) + 1);
^~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
src/backend/tds/guc.c: In function ‘check_version_number’:
src/backend/tds/guc.c:127:38: note: length computed here
strncpy(copy_version_number,*newval,strlen(*newval) + 1);
^~~~~~~~~~~~~~~
cc1: all warnings being treated as errors
src/backend/tds/tdslogin.c: In function ‘ProcessVersionNumber’:
src/backend/tds/tdslogin.c:400:2: error: ‘strncpy’ specified bound depends on the length of the source argument [-Werror=stringop-overflow=]
strncpy(copy_version_number,inputString,strlen(inputString) + 1);
^~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
src/backend/tds/tdslogin.c:400:42: note: length computed here
strncpy(copy_version_number,inputString,strlen(inputString) + 1);
^~~~~~~~~~~~~~~~~~~
src/backend/fault_injection/fault_injection_tests.c: In function ‘throw_error_buffer’:
src/backend/fault_injection/fault_injection_tests.c:241:2: error: ‘memcpy’ forming offset [4, 10] is out of the bounds [0, 3] of object ‘buffer’ with type ‘char[3]’ [-Werror=array-bounds]
memcpy(buffer,tem,10);
^~~~~~~~~~~~~~~~~~~~~
src/backend/fault_injection/fault_injection_tests.c:238:7: note: ‘buffer’ declared here
char buffer[3] = {'\0'};
^~~~~~
cc1: all warnings being treated as errors
fix:
vi src/backend/tds/guc.c
修改line 127
// strncpy(copy_version_number,*newval,strlen(*newval) + 1);
strcpy(copy_version_number,*newval);
vi src/backend/tds/tdslogin.c
修改line 400
// strncpy(copy_version_number,inputString,strlen(inputString) + 1);
strcpy(copy_version_number,inputString);
vi src/backend/fault_injection/fault_injection_tests.c
修改line 238
//char buffer[3] = {'\0'};
char buffer[10] = {'\0'};
安装第四个插件babelfishpg_tsql
cd ../babelfishpg_tsql
make && make install
报错与fix如下:
报错:
src/pl_handler.c: In function ‘pltsql_sequence_datatype_map’:
src/pl_handler.c:1783:3: error: ‘new_type_names’ may be used uninitialized in this function [-Werror=maybe-uninitialized]
list_free(new_type_names);
^~~~~~~~~~~~~~~~~~~~~~~~~
cc1: all warnings being treated as errors
make: *** [<builtin>: src/pl_handler.o] Error 1
src/pl_funcs.c: In function ‘pltsql_ns_additem’:
src/pl_funcs.c:109:2: error: ‘strncat’ specified bound depends on the length of the source argument [-Werror=stringop-overflow=]
strncat(nse->name, name, strlen(name));
^~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
cc1: all warnings being treated as errors
make: *** [<builtin>: src/pl_funcs.o] Error 1
fix:
vi Makefile
PG_CFLAGS += -g -Werror -Wno-error=maybe-uninitialized -Wno-error=stringop-overflow=
// 以上加了两个flag
-Wno-error=maybe-uninitialized -Wno-error=stringop-overflow=
4、配置postgresql配置文件, 并启动数据库
cd $PGDATA
vi postgresql.auto.conf
# add
shared_preload_libraries = 'babelfishpg_tds'
pg_ctl start
5、安装sqlserver 客户端sqlcmd
su - root
curl -o /etc/yum.repos.d/msprod.repo https://packages.microsoft.com/config/rhel/8/prod.repo
yum remove unixODBC-utf16 unixODBC-utf16-devel
yum install -y mssql-tools unixODBC-devel
6、将sql server客户端命令添加到PATH变量
su - postgres
vi .bash_profile
# add
export PATH=/opt/mssql-tools/bin:$PATH
[postgres@1829117d0f28 ~]$ . ./.bash_profile
[postgres@1829117d0f28 ~]$ which sqlcmd
/opt/mssql-tools/bin/sqlcmd
1、psql
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;
CREATE EXTENSION IF NOT EXISTS "babelfishpg_common" 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; -- 这个貌似不用设置了, 搜遍repo也没找到对应代码, 估计默认有了.
ALTER DATABASE babelfish_db SET babelfishpg_tsql.migration_mode = 'single-db';
SELECT pg_reload_conf();
CALL SYS.INITIALIZE_BABELFISH('babelfish_user');
2、sql server客户端sqlcmd连接postgresql with babelfish
sqlcmd -S localhost -U babelfish_user -P 12345678
1> create table tbl (id int, info text);
2> go
1> insert into tbl select id, md5(id::text) from generate_series(1,100) id;
2> go
(100 rows affected)
1> select count(*) from tbl;
2> go
count
-----------
100
(1 rows affected)
1> quit
3、psql 检查到通过sqlcmd写入到PG实例的数据正常
babelfish_db=# \c babelfish_db babelfish_user
You are now connected to database "babelfish_db" as user "babelfish_user".
babelfish_db=> \dt
Did not find any relations.
babelfish_db=> \dn
List of schemas
Name | Owner
-------------------------+-------------------
information_schema_tsql | postgres
master_dbo | master_db_owner
msdb_dbo | msdb_db_owner
public | pg_database_owner
sys | postgres
tempdb_dbo | tempdb_db_owner
(6 rows)
babelfish_db=> \dt master_dbo.tbl
List of relations
Schema | Name | Type | Owner
------------+------+-------+------------
master_dbo | tbl | table | master_dbo
(1 row)
babelfish_db=> select * from master_dbo.tbl limit 10;
id | info
----+----------------------------------
1 | c4ca4238a0b923820dcc509a6f75849b
2 | c81e728d9d4c2f636f067f89cc14862c
3 | eccbc87e4b5ce2fe28308fd9f2a7baf3
4 | a87ff679a2f3e71d9181a67b7542122c
5 | e4da3b7fbbce2345d7772b0674a318d5
6 | 1679091c5a880faf6fb5e6087eb1b2dc
7 | 8f14e45fceea167a5a36dedd4bea2543
8 | c9f0f895fb98ab9159f51fd0297e236d
9 | 45c48cce2e2d7fbdea1afc51c7c6ad26
10 | d3d9446802a44259755d38e6d163e820
(10 rows)
babelfishpg_tds.set_db_session_property
- This parameter specifies the database session property used for TDS connections. This means that babelfishpg_tsql.database_name is set during the login process. An error will occur if the database_name is not set.
https://babelfishpg.org/docs/installation/single-multiple/
