babelfish-for-postgresql / babelfish_extensions 安装（git官网）

Contents

This package includes 5 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.
• babelfishpg_unit
  • Unit testing framework for babelfish.

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 --enable-cassert CFLAGS="-ggdb" --with-libxml --with-uuid=ossp --with-icu
   make -j 4 2>error.txt
   make install
   make check
   

   Alternatively, if you want to build the engine with SSL support, configure the PG engine with --with-openssl:

   ./configure --prefix=$HOME/postgres/ --without-readline --without-zlib --enable-debug --enable-cassert CFLAGS="-ggdb" --with-libxml --with-uuid=ossp --with-icu --with-openssl
   

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

   cd contrib && make && sudo make install
   

Install & build dependencies

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
   

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)
   

   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.

3. 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=~/workplace/postgresql_modified_for_babelfish
   

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

   export cmake=~/workplace/cmake-3.20.6-linux-x86_64/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

Build babelfishpg_unit extension if you want to run/add unit tests (Optional):

cd contrib/babelfishpg_unit
make && make install

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

1. 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/ and ~/postgres/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 lines to your ~/.zshrc file:

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

2. 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     10.x.y.z/32            trust
  

• Now run this to apply the changes:

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

4. Additionally, if you want to configure the babelfish server with SSL enabled:

• Create private key and certificate as mentioned here.

• Modify ~/postgres/data/postgresql.conf by uncommenting and adjusting the following 3 properties as mentioned here:

  ssl = on
  ssl_cert_file = 'server.crt'
  ssl_key_file = 'server.key'
  

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

  hostssl    all             all     10.x.y.z/32            trust
  

• Now run this to apply the changes:

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

5. 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.

   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
     

   • If you want to install babelfishpg_unit extension, run the following command after connecting from psql endpoint (switch to the database where babelfish extensions are installed):

     \c babelfish_db
     CREATE EXTENSION IF NOT EXISTS "babelfishpg_unit";
     

6. Try connecting to Babelfish via SQLCMD

   sqlcmd -S localhost -U babelfish_user -P 12345678
   

   Alternatively, use the -N and -C flags to request encryption and trust the server certificate respectively:

   sqlcmd -N -C -S localhost -U babelfish_user -P 12345678
   

7. You can query the pg_stat_ssl view to see if the connection is encrypted using SSL:

   1> select * from pg_stat_ssl where pid = @@spid
   2> go
   pid ssl version cipher bits client_dn client_serial issuer_dn 
   ----------- --- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    8426 1 TLSv1.2 ECDHE-RSA-AES256-GCM-SHA384 256 NULL NULL NULL 
   
   
   (1 rows affected)
   

How to run the unit tests?

Run the following command from psql endpoint using ~/postgres/bin/psql -U your_user_name -d babelfish_db:

SELECT * FROM babelfishpg_unit.babelfishpg_unit_run_tests();

How to run the JDBC regression tests?

1. Install Maven: https://maven.apache.org/install.html
2. cd to test/JDBC

   cd test/JDBC
   

3. run cleanup.sh, init.sh and mvn test

   ./cleanup.sh
   ./init.sh
   mvn test
   

For detailed instructions on how to write, add, and run tests in JDBC test framework, refer to the online instructions.

How to build the babelfishpg_tsql extension with linked servers enabled

1. To work with linked servers, you must install the tds_fdw extension. More information about building and installing the extension can be found at this link. The linked servers feature is supported using the FreeTDS library which is licensed under the GNU LGPL license. See COPYING_LIB.txt for details.
2. Build the babelfishpg_tsql extension as follows:

   PG_CPPFLAGS='-I/usr/include -DENABLE_TDS_LIB' SHLIB_LINK='-lsybdb -L/usr/lib64' make
   PG_CPPFLAGS='-I/usr/include -DENABLE_TDS_LIB' SHLIB_LINK='-lsybdb -L/usr/lib64' make install
   

3. Create rest of the Babelfish extensions as usual, and initialize Babelfish.
4. Create the tds_fdw extension in the Babelfish database:

   babelfish_db=> CREATE EXTENSION tds_fdw;
   CREATE EXTENSION
   

How to build the babelfishpg_tsql extension with Support for Spatial Datatypes enabled

1. To work with Spatial Datatypes, you must install the PostGIS extension.
   Steps on how to get PostGIS working on open-source:

   wget http://postgis.net/stuff/postgis-3.4.0.tar.gz
   tar -xvzf postgis-3.4.0.tar.gz
   sudo yum install https://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm
   sudo yum install gdal gdal-devel
   sudo yum install https://www.rpmfind.net/linux/epel/8/Everything/x86_64/Packages/g/geos-3.7.2-1.el8.x86_64.rpm
   sudo yum install https://www.rpmfind.net/linux/epel/8/Everything/x86_64/Packages/g/geos-devel-3.7.2-1.el8.x86_64.rpm
   wget https://download.osgeo.org/proj/proj-9.2.1.tar.gz
   tar -xvzf proj-9.2.1.tar.gz
   cd proj-9.2.1
   mkdir build
   cd build
   cmake -DCMAKE_INSTALL_LIBDIR="lib/x86_64-linux-gnu" -DCMAKE_INSTALL_PREFIX="/usr" ..
   cmake --build .
   sudo cmake --build . --target install
   cd ../../postgis-3.4.0
   ./configure
   make
   sudo make install
   

2. More information about building and installing the extension can be found at this link
3. Build the babelfishpg_common extension as follows:

   PG_CPPFLAGS='-I/usr/include -DENABLE_SPATIAL_TYPES' make -j 4
   PG_CPPFLAGS='-I/usr/include -DENABLE_SPATIAL_TYPES' make install
   

4. Build the babelfishpg_tsql extension as follows:

   PG_CPPFLAGS='-I/usr/include -DENABLE_SPATIAL_TYPES' make
   PG_CPPFLAGS='-I/usr/include -DENABLE_SPATIAL_TYPES' make install
   

5. Create rest of the Babelfish extensions as usual, and initialize Babelfish.

How to build the Babelfish server with Kerberos authentication enabled

Please note that Kerberos authentication feature is available on Babelfish server with version 3.1.0 or higher.

1. To build the Babelfish server with Kerberos authentication enabled, you will need to install build-essential and libkrb5-dev packages.
2. Build the Babelfish server according to the instructions mentioned here and use --with-gssapi flag to configure Babelfish in order to enable the GSSAPI APIs.