postgresql connects to SQL server using odbc_fdw

Install and configure ODBC? FDW

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

Reference: docs.microsoft.com

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 use of 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 encountered

ERROR: 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...

Open pg log

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  

Tags: odbc Database SQL Ubuntu

Posted on Sat, 28 Dec 2019 12:05:33 -0500 by leoo24