PostgreSQL - Migrating Databases to Postgres

In our PostgreSQL training course a common question is how to migrate an application's data from an existing database, such as MySQL or Oracle to Postgres. If you have followed good practice and kept your business logic out of the database, any stored procedures or functions, and use of non-ANSI SQL features to a handful of instances it can be a relatively easy exercise. If you have tons of stored procedures and made extensive use of proprietary SQL extensions your life will be a lot harder.

PGLoader - Quick and Simple Migration of Data to PostgreSQL

PGLoader is one of a number of tools that can quickly and painlessly convert a MySQL, SQLite or Microsoft MSSQL database to Postgres. The utility will convert auto-incrementing surrogate primary keys to sequences, do date reformatting, as well as converting other data types from your source database to the appropriate PostgreSQL data type. PGLoader also supports importing from files in csv,fixed,dbf, db3 or ixf format.

At its simplest pgloader can be run from the command line as:


"pgloader SOURCE TARGET"

Where source for files is of the format:

format:///absolute/path/to/file.ext  
format://./relative/path/to/file.ext 

For importing directly from a database the following syntax is used:

db://user:[email protected]:port/dbname 

so for example 

pgloader  mysql://dbu1:[email protected]/openbill postgresql://dbu1:[email protected]/openbill

Postgres PGLoader Configuration Options

PGloader can take several options on the command line or in a configuration file that affects how data conversion is handled. A simple configuration file is listed below:

====================================

load database
     from      mysql://dbu1:[email protected]/openbill
     into postgresql://dbu1:[email protected]/openbill

 WITH include drop, create tables, no truncate,
      create indexes, reset sequences, foreign keys

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

 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

 -- Create the database in postgresql to import into. If your db already exist this can be skipped
 BEFORE LOAD DO
 $$ create schema if not exists openbill; $$;

====================================

To run pgloader with the configuration file above simple run

pgloader name-of-config-file

In my case the file was called openbill.load so:

pgloader openbill.load

When loading the utility will spit out warning about none existing tables. These table will be auto-created for you if you specify the tool to do so in your config file.

"2015-04-27T08:55:08.325000+03:00 WARNING Postgres warning: table "company" does not exist, skipping"

Ubuntu/Debian PGLoader Outdated Package

The pgloader utility that is packaged in Ubuntu/Debian repositories is old and will insist on their being a configuration file and ignore your loader file. The best option is to get the latest version, as a deb package, of the tool by downloading it from the home page