1. Install unixODBC
apt-get install unixodbc unixodbc-dev
2. Install Microsoft ODBC Driver 17 for SQL Server
curl https://packages.microsoft.com/config/ubuntu/16.04/prod.list > /etc/apt/sources.list.d/mssql-release.list
apt-get update
apt-get install msodbcsql17 mssql-tools
If: W: GPG error: http://security.ubuntu.com trusty-security Release: The following signatures couldn't be verified because the public key is not available: NO_PUBKEY 40976EAF437D05B5
**Solution: * * apt key adv -- recv keys -- KeyServer keyserver.ubuntu.com 40976eaf437d05b5
3. configure ODBC
a. Configure driver
vi /etc/odbcinst.ini
#Add the following:
[SQLServer17] #Driver name
Description=Microsoft ODBC Driver 17 for SQL Server
Driver=/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.2.so.0.1
UsageCount=1
b. Configure dsn. This step is not necessary. I will talk about it later
vi /etc/odbc.ini
#Add the following:
[erp-test] #dsn name
Description = erp sqlserver test
Trace = On
TraceFile = stderr
Driver = SQLServer17 #Driver name, which should be consistent with the name in ODBC inst.ini
Server = 192.168.1.123
PORT = 1433
encoding = UTF8
4. Install ODBC FDW
apt-get install postgresql-server-dev-10
wget https://github.com/CartoDB/odbc_fdw/archive/0.3.0.tar.gz
make
make install
Log in pg to execute:
Create extension ODBC FDW; add extension
Re execution
\dx
If you see:
Indicates successful addition
Reference: https://github.com/CartoDB/odbc_fdw
#--The addition of extension is based on database, that is to say, it needs to be added again when the database is switched CREATE EXTENSION odbc_fdw; #--Use the dsn configured in odbc.ini to create the server, who creates the server and who owns it CREATE SERVER erp_sqlserver FOREIGN DATA WRAPPER odbc_fdw OPTIONS ( dsn 'erp-test' #--Need to be consistent with the dsn name in odbc.ini ); #--You can also create a server without using dsn CREATE SERVER erp_sqlserver2 FOREIGN DATA WRAPPER odbc_fdw OPTIONS ( odbc_DRIVER 'SQLServer17', odbc_SERVER '192.168.1.123', odbc_port '1433' ); #--Grant server permission to other users GRANT USAGE ON FOREIGN SERVER erp_sqlserver to erp_manager; #--Create a mapping relationship between users and server s CREATE USER MAPPING FOR erp_manager SERVER erp_sqlserver OPTIONS ( "odbc_UID" 'admin', "odbc_PWD" '123456'); #--Import the schema of the external database. You can import all or part of the tables in the specified schema of the external database into the schema specified in pg at one time IMPORT FOREIGN SCHEMA dbo LIMIT TO (table1 ,table2) #Specify the table to be imported, optional, import all by default FROM SERVER erp_sqlserver INTO "erp_test" OPTIONS ( odbc_DATABASE 'database-83336442_Test'); #--Create external tables, specify fields, and filter data through sql, similar to views CREATE FOREIGN TABLE "public".test_gbk_20180916 ( id integer, name varchar(255) ) SERVER erp_sqlserver OPTIONS ( odbc_DATABASE 'ddrobot', sql_query 'select id,name from `zt`.`test_gbk_20180916`', sql_count 'select count(id) from `zt`.`test_gbk_20180916`' #--encoding 'UTF8' );
The parameters in OPTIONS are defined by fdw extension itself, so different extension parameters are also different. For the explanation of ODBC fdw parameters, please refer to: https://github.com/CartoDB/odbc_fdw
Problems encounteredERROR: length for type varchar must be at least 1 LINE 1: ...imestamp, "CallBackCount" integer, "BusinessInfo" varchar(0)
This is because of my external sqlserver There are some in the library varchar The length of the field is set to 0( sqlserver I'm not familiar, am I sqlserver Not strict here?) , the error message is clear, so I won't talk about it
--
SSL SYSCALL error: EOF detected
The reason for this problem is not clear. It should be a general error message. There are many possibilities to see this error on the Internet. Finally, my solution is to replace the SQL Server ODBC driver to version 17. The previous version of "apt get install msodbcsql" is 13
--
ERROR: Connecting to driver
The most common problem is that the driver is not installed, the driver configuration is faulty, and the connection of the external database itself will report this error. It's very painful to check...
To modify a pg profile:
vi /etc/postgresql/10/main/postgresql.conf
#Turn on log redirection to log file logging_collector = on #The log directory can use absolute path or relative path. When using relative path, it is relative to the path represented by variable 'PGDATA'. Search in the configuration file to find the path log_directory = 'pg_log' #Specify log level log_min_messages = warning