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;
