MSSQL to Azure Database for PostgreSQL: Slow and unreliable :(

[StanleyP]

Hello,

I am experiencing VERY slow operation times and VERY high CPU usage using pgloader to copy data from Microsoft SQL Server 2016 (SP3) (KB5003279) - 13.0.6300.2 (X64) to Azure Database for PostgreSQL (single server - version 11) from Azure VM. I tried tweaking all parameters (workers, concurrency, work_mem, maintenance_work_mem, batch parameters...), but to no avail.

Source server: SQL Server 2016 SP3 Target server: Azure Database for PostgreSQL Pgloader server: 4xCPU 16GB memory Data size: ~35GB Copy time: ~8h

• I tried to compile pgloader with SBCL, but the run always fails on memory allocation.
• After the successful load a tried to dump data via pg_dump -Fd and restore it via pg_restore and the load time was ~30mins
• I also tried to dump data from SQL Server via Azure VM into CSV and the biggest table (~10GB) took 2 minutes, so connectivity to SQL Server is not the issue
• I also had to apply this patch https://github.com/dimitri/pgloader/pull/1243 to make pgloader work with SQL Server

Any help or hints would be gladly appreciated. We try to replace our proprietary DB init-load tools with pgloader, but so far the results are really disappointing.

Kind regards, StanleyP

• [X] pgloader --version

It is compiled from tag v3.6.9

pgloader version "3.6.7~devel"
compiled with Clozure Common Lisp Version 1.12 (v1.12) LinuxX8664

• my configuration

load database
     from mssql://{{MSSQL_CONNSTR}}
     into postgresql://{{PGSQL_CONNSTR}}

with create no indexes, reset sequences, workers = 4, concurrency = 1, uniquify index names,
     batch rows = 25000, batch size = 20MB, prefetch rows = 100000, on error stop

set work_mem to '64MB', maintenance_work_mem to '256MB'

cast type datetime  to timestamp,
     type datetime2 to timestamp,
     type char      to varchar drop typemod,
     type nchar     to varchar drop typemod,
     type varchar   to varchar drop typemod,
     type nvarchar  to varchar drop typemod,
     type xml       to varchar drop typemod

including only table names like '%' in schema 'schemaX'
including only table names like '%' in schema 'schemaY'