Using PGLoader to Copy Data from MS-SQL to PostgreSQL

PGLoader can painlessly copy data from a SQLServer into PostgreSQL, including setting up the schemas and copying the data. A very slick tool that I’d never used before.

Setup

Create the database with: createdb mydb

Load the UUID generation extension, maybe postgis if you have geographic data:

psql -d mydb -c 'CREATE EXTENSION IF NOT EXISTS "uuid-ossp";'
psql -d mydb -c 'CREATE EXTENSION IF NOT EXISTS "postgis";'

When I tried to load from my MS-SQL database, I had to set up the following ~/.freetds.conf file:

[global]
tds version = 7.4
client charset = UTF-8
min pool conn = 20
max pool conn = 8192
max member age = 120

Without that pgloader was failing with “Max connections reached, increase value of TDS_MAX_CONN”, and pgloader would bomb out to the lisp debugger.

Trial Run

I was running into problems so first I tried loading a sqlite file:

pgloader ./deploymanager.sqlite pgsql:///mydb

Note that I set up trusted local connections on my test box, you can use password authentication with:

postgresql://USER:PW@localhost:5432/mydb

Simple Database Load

I was able to load the MS-SQL database using:

pgloader mssql://USER:PW@host:port/dbname pgsql:///dbname

More Complex Database Load

For more control, I set up a “load.cmd” file:

load database
     from      mssql://user:password@sqlhostname:1433/dbname
     into pgsql:///dbname

 WITH include drop, create tables, truncate,
      create indexes, reset sequences, foreign keys
-- Add "schema only" to above to only load the schema, not the data

  SET maintenance_work_mem to '128MB', work_mem to '12MB'

 CAST type datetime to timestamptz
                drop default drop not null using zero-dates-to-null,
      type date drop not null drop default using zero-dates-to-null

 -- Comments start with --
 -- MATERIALIZE VIEWS XXX, YYY

 -- this table has an autoincrement column and wouldn't load
 excluding table names like 'footable' in schema 'dbo'

 BEFORE LOAD DO
 $$ drop schema if exists dbo cascade; $$,
 $$ CREATE EXTENSION IF NOT EXISTS "postgis" $$,
 $$ CREATE EXTENSION IF NOT EXISTS "uuid-ossp" $$;

Then run it with:

pgloader load.cmd

Lots more information here: https://pgloader.readthedocs.io/en/latest/ref/mssql.html