La forma más rápida de transferir datos de PostgreSQL a MS SQL

Una vez tuve que buscar regularmente cantidades relativamente grandes de datos en MS SQL desde PostgreSQL. De repente resultó que la forma más obvia, a través de Linked Server a ODBC nativo a PostgreSQL, es muy lenta.

---

Historia del problema

En la etapa de creación de prototipos, todo estuvo bien. Simplemente porque solo se mecanografiaron unos pocos miles de registros. Tan pronto como pasamos al desarrollo, surgió inmediatamente la sospecha de que algo andaba mal con el rendimiento:

SET STATISTICS TIME ON
DECLARE
  @sql_str nvarchar(max)

DROP TABLE IF EXISTS #t
CREATE TABLE #t (
  N int,
  T datetime
)

SELECT @sql_str='
  SELECT N, T
  FROM generate_series(1,1000,1) N
  CROSS JOIN generate_series($$2020-01-01$$::timestamp,
    $$2020-12-31$$::timestamp, $$1 day$$::interval) T'
INSERT #t (N, T)
EXEC (@sql_str) AT LINKED_SERVER_TO_POSTGRES
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

366 :

SQL Server Execution Times:
   CPU time = 8187 ms,  elapsed time = 14793 ms.
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

, - ODBC. MS bcp Linux. bcp , PostgreSQL :

SET STATISTICS TIME ON
DECLARE
  @sql_str        nvarchar(max),
  @proxy_account  sysname='proxy_account',
  @proxy_password sysname='111111'

DROP TABLE IF EXISTS ##t
CREATE TABLE ##t (
  N int,
  T datetime
)
SELECT @sql_str='
  COPY (
    SELECT N, T
    FROM generate_series(1,1000,1) N
    CROSS JOIN generate_series($$2020-01-01$$::timestamp,
      $$2020-12-31$$::timestamp, $$1 day$$::interval) T )
  TO PROGRAM $pgm$ tmp_file=$'+'(mktemp /tmp/pgsql_bcp_to_mssql.XXXXXXXXX); '
    +'cat > $tmp_file; /opt/mssql-tools/bin/bcp ''##t'' '
    +'in $tmp_file -S '+REPLACE(@@SERVERNAME,'','\')
    +' -U '+@proxy_account+' -P '''
    +@proxy_password+''' -c -b 10000000 -a 65535; '
    +'rm $tmp_file $pgm$ NULL $nil$$nil$;'
EXEC (@sql_str) AT LINKED_SERVER_TO_POSTGRES
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

, :

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 881 ms.
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

, . , bcp Linux Kerberos. .

, bcp . . .

, SQL , . . .

, , . SQL.

:

DECLARE
  @sql_str        nvarchar(max),
  @proxy_account  sysname='proxy_account',
  @proxy_password sysname='111111'

SELECT @sql_str='
  DROP TABLE IF EXISTS ##proxy_table_'+CONVERT(nvarchar(max),@@SPID)+'
  CREATE TABLE ##proxy_table_'+CONVERT(nvarchar(max),@@SPID)+' (
    N int,
    T datetime
  )'
EXEC (@sql_str)

SELECT @sql_str='
  COPY (
    SELECT N, T
    FROM generate_series(1,1000,1) N
    CROSS JOIN generate_series($$2020-01-01$$::timestamp,
      $$2020-12-31$$::timestamp, $$1 day$$::interval) T )
  TO PROGRAM $pgm$ tmp_file=$'+'(mktemp /tmp/pgsql_bcp_to_mssql.XXXXXXXXX); '
    +'cat > $tmp_file; /opt/mssql-tools/bin/bcp ''##proxy_table_'''
    +CONVERT(nvarchar(max),@@SPID)+' '
    +'in $tmp_file -S '+REPLACE(@@SERVERNAME,'\','\\')
    +' -U '+@proxy_account+' -P '''
    +@proxy_password+''' -c -b 10000000 -a 65535; '
    +'rm $tmp_file $pgm$ NULL $nil$$nil$;'
EXEC (@sql_str) AT LINKED_SERVER_TO_POSTGRES
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

PostgreSQL COPY . sh. COPY, , , mktemp. , bcp , .

, COPY , bcp, COPY NULL $nil$$nil$

bcp:

• -c - , PostgreSQL MS SQL ;

  
  
  
  
  

• -b - , . . , , , ;

  
  
  
  
  

• -a - . . , , .

  
  
  
  
  

Si alguien conoce una forma más rápida de obtener datos en MS SQL desde PostgreSQL, estaré muy contento de ver una descripción de este método en los comentarios.