Community

Blog Events Webinars Tutorials Forum
  1. Blog
  2. Events
  3. Webinars
  4. Tutorials
  5. Forum
Create Account
×
Community Blog PostgreSQL SQL Server Compatibility with Babelfish: Babelfish Deployment on MSSQL 2019 Docker on Ubuntu

PostgreSQL SQL Server Compatibility with Babelfish: Babelfish Deployment on MSSQL 2019 Docker on Ubuntu

This article introduces the deployment of revised PostgreSQL on MSSQL2019 Docker.

By digoal

Background

This article introduces the deployment revision PostgreSQL on MSSQL2019 Docker. (MSSQL2019 Docker is based on Ubuntu OS.)

This article test uses BabelFish's latest stable branch. If there is a new branch, please use the new branch when installing.

BabelFish contains several parts:

1.  The revised PostgreSQL mainly adds protocol layer hooks. If the PostgreSQL community receives these changes, it will not need to use this revised branch in the future.

2.  The BabelFish plug-in is 4 plug-ins.

babelfishpg_tsql

  • It supports the tsql language.

babelfishpg_tds

  • It supports the tds connection.

babelfishpg_common

  • It supports the various datatypes in MSSQL.

babelfishpg_money

  • It supports the money type in MSSQL. This is a variation of the open-source fixeddecimal extension.

3.  The compatibility evaluation tool is similar to Alibaba Cloud's ADAM.

Installation and Deployment

1. Deploy PostgreSQL on MSSQL 2019 Docker

docker pull mcr.microsoft.com/mssql/server:2019-latest
  
docker run -d -it --cap-add=SYS_PTRACE --privileged=true --name mssql2019 -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=Mssql2o2o-" -e "MSSQL_PID=EnterpriseCore" -e "CONFIG_EDGE_BUILD=0" -p 1433:1433 mcr.microsoft.com/mssql/server:2019-latest
  
docker exec -it mssql2019 /bin/bash
  
/opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P Mssql2o2o-
  
mssql@109c95b407d8:/$ pwd
/
mssql@109c95b407d8:/$ whoami
mssql

Log in to Docker with root (switch to /root directory after -w /root entering the container):

IT-C02YW2EFLVDL:~ digoal$ docker exec -it -u 0 -w /root mssql2019 /bin/bash
root@109c95b407d8:/# whoami
root
root@109c95b407d8:/# cat /etc/passwd
root:x:0:0:root:/root:/bin/bash
daemon:x:1:1:daemon:/usr/sbin:/usr/sbin/nologin
bin:x:2:2:bin:/bin:/usr/sbin/nologin
sys:x:3:3:sys:/dev:/usr/sbin/nologin
sync:x:4:65534:sync:/bin:/bin/sync
games:x:5:60:games:/usr/games:/usr/sbin/nologin
man:x:6:12:man:/var/cache/man:/usr/sbin/nologin
lp:x:7:7:lp:/var/spool/lpd:/usr/sbin/nologin
mail:x:8:8:mail:/var/mail:/usr/sbin/nologin
news:x:9:9:news:/var/spool/news:/usr/sbin/nologin
uucp:x:10:10:uucp:/var/spool/uucp:/usr/sbin/nologin
proxy:x:13:13:proxy:/bin:/usr/sbin/nologin
www-data:x:33:33:www-data:/var/www:/usr/sbin/nologin
backup:x:34:34:backup:/var/backups:/usr/sbin/nologin
list:x:38:38:Mailing List Manager:/var/list:/usr/sbin/nologin
irc:x:39:39:ircd:/var/run/ircd:/usr/sbin/nologin
gnats:x:41:41:Gnats Bug-Reporting System (admin):/var/lib/gnats:/usr/sbin/nologin
nobody:x:65534:65534:nobody:/nonexistent:/usr/sbin/nologin
_apt:x:100:65534::/nonexistent:/usr/sbin/nologin
mssql:x:10001:0::/home/mssql:/bin/bash
  
  
  
root@109c95b407d8:/# mkdir /home/mssql
  
root@109c95b407d8:/# id mssql
uid=10001(mssql) gid=0(root) groups=0(root)
root@109c95b407d8:/# chown mssql:root /home/mssql

SQL server2019 Docker uses Ubuntu 20.04:

root@109c95b407d8:~# lsb_release -a
No LSB modules are available.
Distributor ID:    Ubuntu
Description:    Ubuntu 20.04.5 LTS
Release:    20.04
Codename:    focal

2. Deploy BabelFish with Modified PostgreSQL 15.1

1.  Install the PostgteSQL dependency:

apt-get update
  
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 unzip man wget flex make git gcc libreadline-dev vim zlib1g-dev libxml2-utils xsltproc docbook

2.  Add a Postgres user:

useradd postgres
mkdir /home/postgres
chown postgres:postgres /home/postgres

3.  Some Convenient Configurations

3.1 Modify the common user default shell type, and the default is sh:

vi /etc/passwd
  
postgres:x:10002:10002::/home/postgres:/bin/bash

3.2 Configure automatic command completion:

Open the /etc/inputrc file, search for the keyword history-search, delete the # in front of these two lines, exit the save, and log in to the terminal again.

3.3 Up and down keys support historical commands:

sudo vi /etc/bash.bashrc  
  
1.  #enable bash completion in interactive shells  
if ! shopt -oq posix; then  
      if [-f  /usr/share/bash-completion/bash_completion ]; then  
          . /usr/share/bash-completion/bash_completion  
      elif [ -f /etc/bash_completion]; then  
           . /etc/bash_completion  
      fi  
fi

4.  Install BabelFish modified PG 15.1

su - postgres
  
cd ~
git clone --depth 1 -b BABEL_3_0_STABLE__PG_15_1 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

5.  Configure environment variables:

cd ~
vi .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
  
. ./.profile

6.  Initialize the database:

initdb -D $PGDATA -U postgres -E UTF8 --lc-collate=C --lc-ctype=en_US.UTF8 --locale-provider=icu --icu-locale=C

7.  Configure database parameters and database firewall:

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

3. Install and Deploy the BabelFish Plug-in

1.  Install the dependencies:

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.  Configure environment variables:

su - postgres  
cd ~  
vi .profile  
# add  
export PATH=/usr/local/cmake/bin:$PATH  
  
  
[postgres@faf9358f0e61 ~]$ . ./.profile  
[postgres@faf9358f0e61 ~]$ which cmake  
/usr/local/cmake/bin/cmake

3.  Install the BabelFish plug-in

Download the plug-in:

su - postgres
cd ~
  
git clone --depth 1 -b BABEL_3_0_STABLE https://github.com/babelfish-for-postgresql/babelfish_extensions

Install 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

If cmake encounters the following error, modify antlr4/CMakeLists.txt to solve it (this error is not encountered on MSSQL2019 Docker).

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  
Check the include directory of antlr4. The configuration should be consistent during later installation.
  
[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/

Next, several plug-ins of BabelFish will be deployed. As a result of the use of -Werror, all alarms will be regarded as errors. Then, the code needs to be modified.

First, set the environment variables, which are required for the installation of the four plug-ins.

export PG_CONFIG=~/pg15.1/bin/pg_config
export PG_SRC=~/postgresql_modified_for_babelfish
export cmake=/usr/local/cmake/bin/cmake

Go to the plug-in directory and set the include directory of ANTLR4, which must be the same as what was installed earlier.

cd ~/babelfish_extensions/
vi contrib/babelfishpg_tsql/antlr/CMakeLists.txt
  
Settings (If it is correct, do not change).
SET (MYDIR /usr/local/include/antlr4-runtime/)

Install the first plug-in babelfishpg_money.

cd contrib/babelfishpg_money
make && make install

Install the second plug-in babelfishpg_common:

cd ../babelfishpg_common
make && make install

The error and fix are listed below:

Error:
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  
  
Modify 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));  
  
----------------------------------------------  
  
Alternatively, you can modify the Makefile to ignore alarms:
vi Makefile
  
PG_CFLAGS += -g -Werror
Modify to:
PG_CFLAGS += -g -Werror -Wno-error=stringop-overflow=

Install the third plug-in babelfishpg_tds:

cd ../babelfishpg_tds
make && make install

The error and fix are listed below:

Error:
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  
  
Modify line 127
// strncpy(copy_version_number,*newval,strlen(*newval) + 1);
strcpy(copy_version_number,*newval);
  
vi src/backend/tds/tdslogin.c
  
Modify line 400
// strncpy(copy_version_number,inputString,strlen(inputString) + 1);
strcpy(copy_version_number,inputString);
  
vi src/backend/fault_injection/fault_injection_tests.c
  
Modify line 238
//char buffer[3] = {'\0'};
char buffer[10] = {'\0'};
  
----------------------------------------------
  
Alternatively, you can modify the Makefile to ignore alarms.
vi Makefile
  
PG_CFLAGS += -Werror
Modify to:
PG_CFLAGS += -Werror -Wno-error=array-bounds

Install the fourth plug-in babelfishpg_tsql:

cd ../babelfishpg_tsql
make && make install

The error and fix are listed below:

Error:
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:  
Modify the Makefile to ignore alarms.
vi Makefile
  
PG_CFLAGS += -g -Werror
Modify to:
PG_CFLAGS += -g -Werror -Wno-error=maybe-uninitialized -Wno-error=stringop-overflow= -Wno-error=array-bounds

4.  Configure the PostgreSQL configuration file and start the database.

BabelFish will listen to 2 ports (one pgSQL protocol and one TDS protocol), so it can be used for both purposes. Please read BabelFish internal for details: https://babelfishpg.org/docs/internals/software-architecture/

cd $PGDATA
  
vi postgresql.auto.conf
# add
shared_preload_libraries = 'babelfishpg_tds'
babelfishpg_tds.port=1922 # listens to 1433 by default, which conflicts with mssql2019, so change the port.

Start the database:

pg_ctl start

5.  Configure the sqlcmd client to the PATH of the postgres user:

su - postgres
vi .profile
# add
export PATH=/opt/mssql-tools/bin:/opt/mssql-extensibility/bin:/opt/mssql/bin:$PATH
  
. ./.profile
  
  
postgres@109c95b407d8:~$ which sqlcmd
/opt/mssql-tools/bin/sqlcmd

Test BabelFish

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; - There is no need to set. I searched through repo and found no corresponding code. It is estimated that there is one by default.
ALTER DATABASE babelfish_db SET babelfishpg_tsql.migration_mode = 'single-db';
-- ALTER DATABASE babelfish_db SET babelfishpg_tsql.migration_mode = 'multi-db';
SELECT pg_reload_conf();
CALL SYS.INITIALIZE_BABELFISH('babelfish_user');

2.  SQL server client sqlcmd connects PostgreSQL with BabelFish:

sqlcmd -S localhost,1922 -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 checks that the data written to the PG instance using sqlcmd is normal:

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)

References

1.  https://github.com/babelfish-for-postgresql/babelfish_project_website/blob/main/_internals/configuration.md

babelfishpg_tds.set_db_session_property
  • This parameter specifies the database session property used for TDS connections. This means babelfishpg_tsql.database_name is set during the login process. An error will occur if the database_name is not set.

2.  https://babelfishpg.org/docs

PostgreSQL Docker ubuntu SQL Server Tutorials Deployment Babelfish MSSQL
0 1 0
Share on

Read previous post:

An Example of PolarDB-X range_hash Sharding and Analysis of Design Boundaries

Read next post:

An Example of PostgreSQL join+order by limit Optimization

digoal

282 posts | 25 followers

You may also like

Comments

digoal

282 posts | 25 followers

Related Products

More Posts by digoal

See All