Hi, I have installed freetds and can connect to the remote mssql server:
postgres@pgreporting:/home/postgres/ [PGREP] tsql -S mssql -U ds2user -P xxxxxxx -D ds2 -o v locale is "LC_CTYPE=en_US.UTF-8;LC_NUMERIC=de_CH.UTF-8;LC_TIME=en_US.UTF-8;LC_COLLATE=en_US.UTF-8;LC_MONETARY=de_CH.UTF-8;LC_MESSAGES=en_US.UTF-8;LC_PAPER=de_CH.UTF-8;LC_NAME=de_CH.UTF-8;LC_ADDRESS=de_CH.UTF-8;LC_TELEPHONE=de_CH.UTF-8;LC_MEASUREMENT=de_CH.UTF-8;LC_IDENTIFICATION=de_CH.UTF-8" locale charset is "UTF-8" using default charset "UTF-8" Setting ds2 as default database in login packet 1> select count(*) from sys.databases; 2> go 5 (1 row affected) using TDS version 7.3 PostgreSQL version is 9.5.2: postgres=# select version(); version ---------------------------------------------------------------------------------------------------------------------------- PostgreSQL 9.5.2 dbi services build on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-4), 64-bit (1 row) Created the fdw stuff like this: postgres=# \dx List of installed extensions Name | Version | Schema | Description ---------+---------+------------+----------------------------------------------------------------------------------- plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language tds_fdw | 1.0.7 | public | Foreign data wrapper for querying a TDS database (Sybase or Microsoft SQL Server) (2 rows) postgres=# create server mssql_svr foreign data wrapper tds_fdw options ( servername '192.168.22.102', port '1433', database 'ds2', tds_version '7.3', msg_handler 'notice' ); CREATE SERVER postgres=# CREATE USER MAPPING FOR postgres SERVER mssql_svr OPTIONS (username 'ds2user', password 'xxxxxx'); CREATE USER MAPPING postgres=# create foreign table ds2_mssql.customers (CUSTOMERID int ,FIRSTNAME varchar(50) ,LASTNAME varchar(50) ,ADDRESS1 varchar(50) ,ADDRESS2 varchar(50) ,CITY varchar(50) ,STATE varchar(50) ,ZIP int ,COUNTRY varchar(50) ,REGION int ,EMAIL varchar(50) ,PHONE varchar(50) ,CREDITCARDTYPE int ,CREDITCARD varchar(50) ,CREDITCARDEXPIRATION varchar(50) ,USERNAME varchar(50) ,PASSWORD varchar(50) ,AGE int ,GENDER varchar(50)) SERVER mssql_svr OPTIONS (query 'select * from dbo.customers', row_estimate_method 'showplan_all'); CREATE FOREIGN TABLE Once I do a select from the foreign table the server just crashes: postgres=# select count(*) from ds2_mssql.customers; server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: Failed. !> This is from the log: 2016-04-24 14:34:31.896 CEST - 16 - 23252 - - @ LOG: server process (PID 23796) was terminated by signal 11: Segmentation fault 2016-04-24 14:34:31.896 CEST - 17 - 23252 - - @ DETAIL: Failed process was running: select count(*) from ds2_mssql.customers; 2016-04-24 14:34:31.896 CEST - 18 - 23252 - - @ LOG: terminating any other active server processes 2016-04-24 14:34:31.898 CEST - 2 - 23454 - - @ WARNING: terminating connection because of crash of another server process 2016-04-24 14:34:31.898 CEST - 3 - 23454 - - @ DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. 2016-04-24 14:34:31.898 CEST - 4 - 23454 - - @ HINT: In a moment you should be able to reconnect to the database and repeat your command. 2016-04-24 14:34:31.901 CEST - 19 - 23252 - - @ LOG: archiver process (PID 23455) exited with exit code 1 2016-04-24 14:34:31.901 CEST - 1 - 23797 - [local] - postgres@postgres FATAL: the database system is in recovery mode 2016-04-24 14:34:31.902 CEST - 20 - 23252 - - @ LOG: all server processes terminated; reinitializing 2016-04-24 14:34:31.931 CEST - 1 - 23798 - - @ LOG: database system was interrupted; last known up at 2016-04-24 14:33:15 CEST 2016-04-24 14:34:32.262 CEST - 2 - 23798 - - @ LOG: database system was not properly shut down; automatic recovery in progress 2016-04-24 14:34:32.264 CEST - 3 - 23798 - - @ LOG: redo starts at 0/7074278 2016-04-24 14:34:32.264 CEST - 4 - 23798 - - @ LOG: invalid record length at 0/7077270 2016-04-24 14:34:32.264 CEST - 5 - 23798 - - @ LOG: redo done at 0/7076100 2016-04-24 14:34:32.266 CEST - 6 - 23798 - - @ LOG: checkpoint starting: end-of-recovery immediate 2016-04-24 14:34:32.270 CEST - 7 - 23798 - - @ LOG: checkpoint complete: wrote 3 buffers (0.0%); 0 transaction log file(s) added, 0 removed, 0 recycled; write=0.000 s, sync=0.001 s, total=0.005 s; sync files=1, longest=0.001 s, average=0.001 s; distance=12 kB, estimate=12 kB 2016-04-24 14:34:32.272 CEST - 8 - 23798 - - @ LOG: MultiXact member wraparound protections are now enabled 2016-04-24 14:34:32.274 CEST - 21 - 23252 - - @ LOG: database system is ready to accept connections 2016-04-24 14:34:32.274 CEST - 1 - 23802 - - @ LOG: autovacuum launcher started If I increase the log level: postgres=# alter system set log_min_messages='INFO'; ALTER SYSTEM ... I additionally get this: 2016-04-24 14:43:56.265 CEST - 1 - 24539 - [local] - postgres@postgres NOTICE: DB-Library notice: Msg #: 5701, Msg state: 2, Msg: Changed database context to 'ds2'., Server: WSCORE\SQL2014, Process: , Line: 1, Level: 0 2016-04-24 14:43:56.265 CEST - 2 - 24539 - [local] - postgres@postgres NOTICE: DB-Library notice: Msg #: 5703, Msg state: 1, Msg: Changed language setting to us_english., Server: WSCORE\SQL2014, Process: , Line: 1, Level: 0 2016-04-24 14:43:56.268 CEST - 3 - 24539 - [local] - postgres@postgres NOTICE: DB-Library notice: Msg #: 5701, Msg state: 2, Msg: Changed database context to 'ds2'., Server: WSCORE\SQL2014, Process: , Line: 1, Level: 0 2016-04-24 14:43:56.268 CEST - 4 - 24539 - [local] - postgres@postgres NOTICE: DB-Library notice: Msg #: 5703, Msg state: 1, Msg: Changed language setting to us_english., Server: WSCORE\SQL2014, Process: , Line: 1, Level: 0 The OS is (64bit): postgres@pgreporting:/home/postgres/ [PGREP] cat /etc/centos-release CentOS Linux release 7.2.1511 (Core) Any ideas? Thanks in advance Daniel
