digoal
2020-11-15
PostgreSQL , sql server , fdw , odbc , ogr , gdal , tds_fdw
在PostgreSQL中通过外部表访问SQL Server的数据, 方法较多:
1、odbc_fdw
2、tds_fdw
3、ogr_fdw
4、multicorn sql_alchemy
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:
postgres=# \dx
List of installed extensions
Name | Version | Schema | Description
----------+---------+------------+----------------------------------------------------------------
odbc_fdw | 0.5.2 | public | Foreign data wrapper for accessing remote databases using ODBC
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
(2 rows)
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
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
或者直接打开客户端日志排查
set client_min_messages=debug5;
https://github.com/CartoDB/odbc_fdw
http://www.postgresonline.com/article_pfriendly/355.html
https://programmer.help/blogs/postgresql-connects-to-sql-server-using-odbc_fdw.html
https://wiki.postgresql.org/wiki/Foreign_data_wrappers
/etc/odbc.ini
/etc/odbcinst.ini
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
