To continue our migration series, today’s post will focus on pgloader. Pgloader is another Open Source data migration utility for PostgreSQL from MySQL and SQL Server. Today’s demo will migrate a sample database (StackOverflow) from MS SQL Server 2019 to Postgresql v10.
To continue our migration series, today’s post will focus on pgloader. Pgloader is another Open Source data migration utility for PostgreSQL from MySQL and SQL Server. Today’s demo will migrate a sample database (StackOverflow) from MS SQL Server 2019 to Postgresql v10.
StackOverflow contains the following tables
1> use [StackOverflow]
2> go
Changed database context to 'StackOverflow'.
1> select name from sys.tables order by name
2> go
---------------------------------------------------------------------------
Badges
Comments
LinkTypes
PostLinks
Posts
PostTypes
Users
Votes VoteTypes
(9 rows affected)- Install pgloader – on ubuntu this is a simple apt-get install pgloader but you can also build from source
- Pglolader uses the FreeTDS driver (on RedHat I needed to install the following freetds freetds-libs freetds-common)
- Pgloader has multiple options
# pgloader
pgloader [ option ... ] command-file ...
pgloader [ option ... ] SOURCE TARGET
--help -h boolean Show usage and exit.
--version -V boolean Displays pgloader version and exit.
--quiet -q boolean Be quiet
--verbose -v boolean Be verbose
--debug - boolean Display debug level information.
--client-min-messages string Filter logs seen at the console (default: "warning")
--log-min-messages string Filter logs seen in the logfile (default: "notice")
--summary -S string Filename where to copy the summary
--root-dir -D string Output root directory. (default: #P"/tmp/pgloader/")
--upgrade-config -U boolean Output the command(s) corresponding to .conf file for v2.x
--list-encodings -E boolean List pgloader known encodings and exit.
--logfile -L string Filename where to send the logs.
--load-lisp-file -l string Read user code from files
--dry-run boolean Only check database connections, don't load anything.
--on-error-stop boolean Refrain from handling errors properly.
--no-ssl-cert-verification boolean Instruct OpenSSL to bypass verifying certificates.
--context -C string Command Context Variables
--with string Load options
--set string PostgreSQL options
--field string Source file fields specification
--cast string Specific cast rules
--type string Force input source type
--encoding string Source expected encoding
--before string SQL script to run before loading the data
--after string SQL script to run after loading the data
--self-upgrade string Path to pgloader newer sources
--regress boolean Drive regression testing- Create the database in postgres – add the uuid-ossp extension
postgres=# create database stackoverflow owner btpg10;
CREATE DATABASE
postgres=# \c stackoverflow btpg10;
You are now connected to database "stackoverflow" as user "btpg10".
stackoverflow=# create extension "uuid-ossp";
CREATE EXTENSION- I define a parfile for specific flags – we can alter table names, exclude tables, define data type conversions - for this demo we will only define our source/target connections and rename the default MS SQL schema(dbo) to the default postgres (public)
load database
from mssql://SA:@host1:1433/StackOverflow
into postgresql://btpg10:@host2:5432/stackoverflow
alter schema
before load do $$ drop schema if exists dbo cascade; $$;- Define freetds config file in same directory
# view .freetds.conf
[global]
tds version = 7.4
client charset = UTF-8- Test connectivity
# pgloader --dry-run ss_so.cmd
2020-06-25T18:33:26.014000Z LOG pgloader version "3.6.1"
2020-06-25T18:33:26.058000Z LOG Loading the FreeTDS shared librairy (sybdb)
2020-06-25T18:33:26.061000Z LOG DRY RUN, only checking connections.
2020-06-25T18:33:26.062000Z LOG Attempting to connect to #<MSSQL-CONNECTION mssql://[email protected]:1433/StackOverflow {100696F6F3}>
2020-06-25T18:33:26.190000Z LOG Success, opened #<MSSQL-CONNECTION mssql://[email protected]:1433/StackOverflow {100696F6F3}>.
2020-06-25T18:33:26.190000Z LOG Running a simple query: SELECT 1;
2020-06-25T18:33:26.210000Z LOG Attempting to connect to #<PGSQL-CONNECTION pgsql://[email protected]:5432/stackoverflow {1006970C13}>
2020-06-25T18:33:26.244000Z LOG Success, opened #<PGSQL-CONNECTION pgsql://[email protected]:5432/stackoverflow {1006970C13}>.
2020-06-25T18:33:26.245000Z LOG Running a simple query: SELECT 1;
2020-06-25T18:33:26.245000Z LOG report summary reset
table name errors rows bytes total time
-------------- ---------- -------- --------- --------------
-------------- ---------- -------- --------- --------------- Run data migration
# pgloader --verbose ss_so.cmd
2020-06-25T18:42:07.745000Z LOG report summary reset
table name errors read imported bytes total time read write
----------------------- --------- --------- --------- --------- -------------- --------- ---------
before load 0 1 1 0.017s
fetch meta data 0 18 18 0.457s
Create Schemas 0 0 0 0.001s
Create SQL Types 0 0 0 0.009s
Create tables 0 18 18 0.102s
Set Table OIDs 0 9 9 0.006s
----------------------- --------- --------- --------- --------- -------------- --------- ---------
public.badges 0 1102019 1102019 50.5 MB 10.400s 10.396s 6.802s
public.comments 0 3875183 3875183 743.1 MB 1m32.008s 1m32.002s 58.180s
public.postlinks 0 161519 161519 7.5 MB 1.709s 1.700s 1.111s
public.posttypes 0 8 8 0.1 kB 0.143s 0.137s
public.votes 0 10143364 10143364 424.5 MB 1m38.394s 1m37.466s 1m6.057s
public.linktypes 0 2 2 0.0 kB 0.132s 0.130s
public.posts 0 3729195 3729195 2.8 GB 5m8.075s 5m8.051s 2m37.241s
public.users 0 299398 299398 42.7 MB 12.007s 5.144s 4.256s
public.votetypes 0 15 15 0.2 kB 0.134s 0.131s
----------------------- --------- --------- --------- --------- -------------- --------- ---------
COPY Threads Completion 0 4 4 6m52.193s
Create Indexes 0 9 9 41.768s
Index Build Completion 0 9 9 4.211s
Reset Sequences 0 9 9 0.443s
Primary Keys 0 9 9 0.067s
Create Foreign Keys 0 0 0 0.000s
Create Triggers 0 0 0 0.002s
Install Comments 0 0 0 0.000s
----------------------- --------- --------- --------- --------- -------------- --------- ---------
Total import time ✓ 19310703 19310703 4.1 GB 7m38.684s- Verify tables / data in Postgresql
postgres=# \c stackoverflow
You are now connected to database "stackoverflow" as user "postgres".
stackoverflow=# \dt
List of relations
Schema | Name | Type | Owner
--------+-----------+-------+--------
public | badges | table | btpg10
public | comments | table | btpg10
public | linktypes | table | btpg10
public | postlinks | table | btpg10
public | posts | table | btpg10
public | posttypes | table | btpg10
public | users | table | btpg10
public | votes | table | btpg10
public | votetypes | table | btpg10
(9 rows)Author
Reference
microsoft-sql-server-postgresql-migration-using-pgloader
作者：古道轻风