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:
For importing directly from a database the following syntax is used:
so for example
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:
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
In my case the file was called openbill.load so:
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