A maneira mais rápida de transferir dados de PostgreSQL para MS SQL

Antes eu precisava buscar regularmente grandes quantidades de dados no MS SQL do PostgreSQL. De repente, descobriu-se que a maneira mais óbvia, através do Linked Server para ODBC nativo para PostgreSQL, é muito lenta.

---

História do problema

Na fase de prototipagem, tudo correu bem. Simplesmente porque apenas alguns milhares de registros foram digitados. Assim que passamos para o desenvolvimento, imediatamente surgiu a suspeita de que algo estava errado com o desempenho:

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 - . . , , .

  
  
  
  
  

Se alguém conhece uma maneira mais rápida de obter dados em MS SQL a partir do PostgreSQL - ficarei muito feliz em ver uma descrição deste método nos comentários.