Untitled

Percona

Unbiased Open Source Database Experts

Migrating from

MSSQL to

PostgreSQL

Robert Bernier

PostgreSQL Consultant

robert.bernier@percona.com

SUMMARY STEPS

A Simpli!ed Approach

1) Install Foreign Data Wrapper, tds_fdw

2) Identify Foreign Table connection info

3) Create Foreign Table(s)

4) Populate table(s) (Copy FT data into PostgreSQL)

SUMMARY DETAILS

●

PostgreSQL version >= 9.6.*

●

Architect The Target Database

●

Create Table Schema

●

Identify System Catalogs and Tables of Interest

–

Functions

–

Tables

–

Constraints: PK, FK, CHECK

–

Views

–

Triggers

●

Address Functions/sprocs

●

Install Forgeign Data Wrapper, tds_fdw

●

Con!gure Foreign Table connection info

●

Create Foreign Table(s)

●

Populate table(s) (Copy FT data into PostgreSQL)

●

Apply Constraints

●

Process Triggers

●

Process Views (an iterative LIFE-CYCLE approach)

The SECRET SAUCE

●

MSSQL vs PostgreSQL

–

ANSI compliant

–

The schema "sys" is equivalent to the postgres "pg_catalog" schema

–

Information Schema

●

Overloading Operator and Datatype conversions simpli!es the task

●

Recognize that importing functions/sprocs is tedious work

●

Recognize the need to be able to rebuild your environment over and over

again

●

Get yourself a dependable, linux based, CLI mssql client

System Catalog Vs Information

Schema

MSSQL CLI

PREPARING THE TARGET

DATABASE

CREATE YOUR DATABASE, SCHEMA

- schema layout

- public, -- default postgres

- dbo, -- default MSSQL

- mssql_information_schema, -- foreign tables, information_schema

- mssql, -- foreign tables

- run_information_schema, -- imported tables, information_schema

- run, -- imported tables

- views, -- regenerated views

TIP:

alter database <mydatabase>

set search_path=public,run,views,run_information_schema,

mssql,mssql_information_schema,dbo;

INSTALLING POSTGRES

Update your repository:

echo "deb http://apt.postgresql.org/pub/repos/apt/ xenial-pgdg main" \

> /etc/apt/sources.list.d/pgdg.list

Import the repository signing key:

wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc \

| sudo apt-key add -

Update the package lists:

apt-get update

apt-get dist-upgrade -y

Install PostgreSQL

apt-get install -y postgresql-contrib-9.6 postgresql-server-dev-9.6

THE tds_fdw FOREIGN DATA

WRAPPER

Install necessary, dependent packages on your OS

apt-get -y install make gcc git libsybdb5 freetds-dev freetds-common

Get the Foreign Data Wrapper for MSSQL

git clone https://github.com/tds-fdw/tds_fdw.git

Install the foreign data wrapper (assumes pg_config is in PATH)

cd tds_fdw

make USE_PGXS=1 install

Create Extension

createdb <mydatabase>;

psql <mydatabase> -c 'create extension if not exists tds_fdw with schema public'

CONNECTING PG AND MSSQL

/* -- EX: Variables, file:conn.info --

USER=<postgres ROLE>

MSSQL_SERVER=<MSSQL FQDN>

MSSQL_PORT='1433'

MSSQL_DB=<MSSQL database name>

MSSQL_USER=<MSSQL database username>

MSSQL_PASS=<MSSQL password>

*/

-- NOTICE the use of variable names

create server mssql_svr

foreign data wrapper tds_fdw

options (servername 'MSSQL_SERVER',

port 'MSSQL_PORT',

database 'MSSQL_DB',

tds_version '7.3',

msg_handler 'notice');

create user mapping for USER

server mssql_svr

options (username 'MSSQL_USER', password 'MSSQL_PASS');

-- SECRET SAUCE

import foreign schema dbo

from server mssql_svr

into mssql

options (import_default 'true');

IMPORTING TABLES

IMPORT FOREIGN SCHEMA remote_schema

[ { LIMIT TO | EXCEPT } ( table_name [, ...] ) ]

FROM SERVER server_name

INTO local_schema

[ OPTIONS ( option 'value' [, ... ] ) ]

EXAMPLE: (based upon a pre-existing set of definitions)

DATABASE: temp

SCHEMA: dbo

SERVER: mssql_svr

import foreign schema dbo

from server mssql_svr

into mssql

options (import_default 'true');

EXAMPLE:

create table t1_new (like t1);

PROCESSING FUNCTIONS/SPROCS

Issues:

- about datatypes

- about operators

- function overloading

- steps

- process table CHECK constraints

- use IN and OUT arguments,

- don't put any code in the body

- incorporate, implement, and then debug

TIP:don't bother tweaking the tables, instead, overload your

operators and data types.. then fix your tables

MASSAGING POSTGRES

MAKING POSTGRES BEHAVE MORE LIKE MSSQL

Is this a good idea?

MASSAGING POSTGRES

CONVERSION CONSIDERATIONS

●

Creating/Overloading vs Editing

●

Editing MSSQL code (Regular Expressions are your friend)

–

sed

–

grep

–

awk

–

postgres functions

●

regexp_matches(string text, pattern text [, flags text])

●

regexp_replace(string text, pattern text, replacement text [, flags

text])

–

Generate scanners: recognizing lexical patterns in text

●

lex, flex, etc

●

yacc, bison, etc

MASSAGING POSTGRES

CONVERSION CONSIDERATIONS cont’d

●

About

–

SQL queries

–

Functions

●

CHECK Constraints

●

Views

●

Examples (MSSQL->PG equivalents)

–

datepart

–

dateadd

–

datediff

–

year

–

Month

–

ISNULL vs COALESCE

–

Operators & Datatypes

●

string concatenation

–

select 'hello'||' '||'world' -- PG

–

select 'hello'+' '+'world' -- MSSQL

●

money conversion

–

select '$23'::money::numeric+34 – PG

–

select '$23'::money+34 -- MSSQL

–

Triggers

MASSAGING POSTGRES

Examples ...

IMPORT DATABASE

script: 01.create_FT_mssql.sh

CAVEATS:

- Casting Data Types

- Column and Table mixed-case names

POPULATE TABLES

script: 02.populate_tables_mssql.sh

IMPORT SYSTEM CATALOGS

REQUIRED FOR CONSTRAINTS AND VIEWS

script: 03.create_FT_information_schema.sh

IMPORTING THE FOLLOWING:

information_schema.constraint_column_usage

information_schema.table_constraints

information_schema.referential_constraints

information_schema.key_column_usage

information_schema.views -- BEWARE OF MS CATALOG 2kb

-- LIMITATIONS

information_schema.view_table_usage

information_schema.view_column_usage

information_schema.tables

sys.objects

sys.sql_modules

sys.syscolumns

sys.sysobject

ATTENTION: MS tables not fully ANSI compliant!

POPULATE SYS CATALOGS

script: 04.populate_tables_information_schema.sh

IMPORT CONSTRAINTS PK, FK

script: 05.add_primary_keys.sh

06.add_foreign_keys.sh

PROCESS THE TRIGGERS

script: 07.get_trigger_lists.sh

- Generate a list: take advantage of your mssql client

i.e. execute custom query (cheat: google for it!)

- Output MSSQL trigger definition to individual text files

- Process/edit each individual MSSQL definition

- PG issues:

- function definition

- trigger definition

TIPS:

- Leverage shortcuts

- Alternatively: import trigger definitions using FDW

PROCESS THE VIEWS

LIFE-CYCLE

script: 08a.add_views.sh

08b.regenerate_views.sh

08c.get_problem_views.sh

- MSSQL Information Schema

- Identify those relations pertaining to views

- Import Information Schema as Foreign Tables

- Copy view definitions into target tables using an ETL process

- DEBUG: Create table that will contain meta data (failed SQL create view(s))

- VIEW CREATION CYCLE: life cycle of success/failure of view creation

- execute a single pass through all view definitions

- execute view SQL: register failure into meta table

- repeat previous steps: until no new views are being created

- output all failed view definitions as individual SQL files

- edit previously generated view definitions

- update view catalog with updated view definitions

- repeat VIEW CREATION CYCLE

TIPS:

- Each pass of the VIEW CREATION CYCLE focuses exclusively upon previously failed views.

- Focus first on views with incorrect or missing functions, improper casting etc.

- Work on those views dependent upon other views last.

- Remember; you are only creating views, validating them is extra :-)

Migrating MSSQL to PostgreSQL

Issues encountered ...

Thank You