Converting from other Databases to PostgreSQL

From PostgreSQL wiki

Jump to: navigation, search

Contents

Non-specific

  • Ispirer MnMTK Ispirer Migration and Modernization Toolkit (Ispirer MnMTK) performs automatic migration of any popular RDBMS to PostgreSQL.
  • Full Convert Database conversion from more than 30 database engines to PostgreSQL and back.
  • pgloader knows how to load data from MySQL, SQLite, MS SQL Server, dBase files, CSV files and fixed-width data files, and more. Released under The PostgreSQL Licence.
  • DBConvert PostgreSQL database migration and sync software Database conversion and synchronization between PostgreSQL/ Amazon RDS, MySQL, MS SQL Server, SQL Azure, Oracle, MS Access.
  • Converting data between PostgreSQL and others database formats ESF Database Migration Toolkit enables you to transfer data across various databases, supporting PostgreSQL, MySQL, Oracle, SQL Server, IBM DB2, Informix, Microsoft Access, Microsoft Excel, dBase, Foxpro, Firbird, SQLite etc. - by Martin Williams
  • Comparison of different SQL implementations by Troels Arvin (covers PG 8.4 and MySQL 5.0)
  • Transactional DDL in PostgreSQL: A Competitive Analysis by Greg Smith
  • Migrating from one database to another with Pentaho ETL by Nicola Benaglia
  • dataPro Conversion tool for PostgreSQL, SQLite, MySQL, Oracle, SQL Server and Microsoft Access. Transfer database objects between different databases and servers, convert tables schema and migrate data from one database type to another.
  • DataDiff CrossDB is a Windows GUI utility to compare and synchronize/transfer data from PostgreSQL to/from Oracle, MSSQL, MS Access or MySQL databases.
  • PostgreSQL Data Wizard is a Windows GUI utility to transfer both schema and data from any ADO-compatible source (like MS Access, MySQL, SQL Server, Oracle, etc) to PostgreSQL.
  • SQL::Translator is a Perl module for translating table definitions between different software.
  • Foreign data wrappers may be useful for exporting data from other databases
  • Postgres Migration Toolkit Software pack to convert Oracle, MySQL, SQL Server and FoxPro to PostgreSQL, and vice versa.
  • sqlacrossover SQLAlchemy-based cross-database migration tool
  • Skyvia Web service for cloud data integration for PostgreSQL with Salesforce, Dynamics CRM, SugarCRM, Zoho CRM, QuickBooks, FreshBooks, ExactTarget, MailChimp, Bigcommerce, MySQL, SQL Server, SQL Azure, Amazon RDS.
  • OmniDB Open source full-featured web tool for database management. Currently supports PostgreSQL only. More RDBMS support coming soon, including the ability of converting databases from any supported RDBMS to PostgreSQL and back.
  • Exportizer Enterprise Universal data conversion tool for PostgreSQL, Oracle, SQL Server, SQLite, Interbase, Firebird, MySQL, DB2, Informix, SQL Anywhere, Microsoft Access, Microsoft Excel, dBase, CSV. Can export data either from GUI or from command line.

Apache Derby

  • derby2pg Converter program that will convert the tables, data and indexes in a given schema to a script that can be used to populate a PostgreSQL database.

DBase II, III, IV+ / DBF Format

FileMaker Pro

IBM DB2

  • Ispirer MnMTK Ispirer Migration and Modernization Toolkit (Ispirer MnMTK) automatically migrates the entire database schema (tables, views, stored procedures, functions, triggers, etc.) and transfers data from DB2 LUW, DB2 AS/400 (iSeries) and DB2 OS/390 (z/OS) to PostgreSQL.
  • Migrating from DB2 to PostgreSQL
  • Full Convert Database conversion from more than 30 database engines, including IBM DB2, to PostgreSQL and back.
  • db2topg Migration tool to convert a DB2 UDB Database into a PostgreSQL database.
  • Exportizer Enterprise Converts data from DB2 to PostgreSQL and back.

InterBase

  • Ispirer MnMTK Ispirer Migration and Modernization Toolkit (Ispirer MnMTK) automatically migrates the entire database schema (tables, views, stored procedures, functions, triggers, etc.) and transfers data from InterBase as well as Firebird to PostgreSQL.
  • DBReplicate - Simplify Interbase->PostgreSQL conversions by Kirk Strauser
  • Full Convert Database conversion from more than 30 database engines, including Interbase, to PostgreSQL and back
  • Exportizer Enterprise Converts data from Interbase to PostgreSQL and back.

Microsoft Access

  • Linking to MS Access tables with ogr_fdw If your PostgreSQL is on windows, you can use ogr_fdw foreign data wrapper, packaged with PostGIS 2.2+ bundle for windows via application stackbuilder. With PostgresQL 9.5, you can use IMPORT FOREIGN SCHEMA to link in all the MS access tables and then cherry pick how you want to restructure.
  • exportSQL - a Microsoft Access module which exports Access Database into MySQL, mSQL and PostgreSQL by Dobrica Pavlinusic. Based on the work of Pedro Freire
  • Full Convert Database conversion from more than 30 database engines, including Access, to PostgreSQL and back
  • Exportizer Enterprise Converts data from MS Access to PostgreSQL and back.
  • MDB Tools by Brian Bruns
    • A quick way to dump all tables as tsv or csv files 
for TT in $(mdb-tables file.mdb); do
     mdb-export -Q -d '\t' -D '%Y-%m-%d %H:%M:%S' file.mdb "$TT" > "${TT}.tsv"
done

for TT in $(mdb-tables file.mdb); do
     mdb-export -D '%Y-%m-%d %H:%M:%S' file.mdb "$TT" > "${TT}.csv"
done

If the tablenames have embedded spaces... 

mdb-tables -1 file.mdb| while read TT
do
     mdb-export -D '%Y-%m-%d %H:%M:%S' file.mdb "$TT" > "${TT}.csv"
done

A shell script that may be useful for converting entire databases: 

#!/bin/sh -e

mdbfn=$1
schemafn=$2
fkfn=$3
datafn=$4
schema=$5

tf=$(tempfile)

pre=""
[ -n "${schema}" ] && pre="\"${schema}\"."

mdb-schema "${mdbfn}" postgres > "${tf}"

# Schema file
echo "BEGIN;\n" > "${schemafn}"

sp=""
[ -n "${schema}" ] && echo "CREATE SCHEMA \"${schema}\";\n" >> "${schemafn}"
[ -n "${schema}" ] && sp="SET search_path = \"${schema}\", pg_catalog;\n" 

echo ${sp} >> "${schemafn}"

awk '($0 !~ /^ALTER TABLE.*FOREIGN KEY.*REFERENCES/) {print;}' "${tf}" >> "${schemafn}"

echo "\nEND;" >> "${schemafn}"

# Foreign keys file
echo "BEGIN;\n" > "${fkfn}"
echo ${sp} >> "${fkfn}"

awk '($0 ~ /^ALTER TABLE.*FOREIGN KEY.*REFERENCES/) {print;}' "${tf}" >> "${fkfn}"

echo "\nEND;" >> "${fkfn}"

# Data file
echo "BEGIN;\n" > "${datafn}"
echo "SET CONSTRAINTS ALL DEFERRED;\n" >> "${datafn}"

mdb-tables -1 "${mdbfn}" | while read TT
do
    mdb-export -Q -d '\t' -D '%Y-%m-%d %H:%M:%S' "${mdbfn}" "$TT" > "${tf}"

    awk -v pre="${pre}" -v TT="${TT}" \
	'(NR==1) {gsub(/\t/,"\",\""); print "COPY " pre "\"" TT "\"(\"" $0 "\") FROM stdin;";}' "${tf}" >> "${datafn}"
    awk '(NR>1) {gsub(/\t\t/,"\t\\N\t"); gsub(/\t$/,"\t\\N"); gsub(/\t\t/,"\t\\N\t"); print;}' "${tf}" >> "${datafn}"

    echo "\\.\n" >> "${datafn}"
done

echo "END;" >> "${datafn}"

rm -f "${tf}"

If this script is saved to the file access2psql.sh and made executable, then it would be used as follows: 

access2psql.sh file.mdb schema.sql foreignkeys.sql data.sql pg_schema_name
psql -f schema.sql pg_db_name
psql -f data.sql pg_db_name
psql -f foreignkeys.sql pg_db_name

This script won't work properly if there are tab characters in text columns, though the call to mdb-export could be modified to export INSERT statements to fix this. Also, mdb-schema has trouble representing multi-column foreign keys, so foreignkeys.sql may need some manual editing.

Microsoft SQL Server

MySQL

Scripts, programs

pg_chameleon is a python procedure which replicates MySQL into PostgreSQL. The tool can detach the replica for minimal downtime migrations.

pgloader supports full migration of the database schema and data from a single command line and provides a command language for more complex or specific scenarios. It is still fully supported: please report any bugs on its GitHub page. It appears in the 2013 section here because that's when it has been published first.


2015

2014

2013

Previously 

 # To install mysql2psql (under ubuntu 11.10): No need to get from github, just:
 sudo apt-get install ruby gems libmysqlclient-dev libpq-dev
 gem install mysql pg mysql2psql
 # To get info about the mysql socket:
 netstat -l | grep mysql
 mysql2psql # creates a .yml templae
 vi mysql2psql.yml # edit the template
 mysql2psql # connects to mysql database and write into postgres database

Documentation

Oracle

Utilities, tools, scripts etc.

Documentation, articles, presentations etc.

PostgreSQL documentation

Articles and presentations (in reverse chronological order)

Progress RDBMS

Utilities, tools, scripts etc.

  • Ispirer MnMTK Ispirer Migration and Modernization Toolkit (Ispirer MnMTK) automatically migrates the entire database schema and transfers data from Progress to PostgreSQL.

Converting PostgreSQL Databases to other Databases

  • Ispirer MnMTK Ispirer Migration and Modernization Toolkit (Ispirer MnMTK) performs automatic migration of PostgreSQL to any popular RDBMS.
  • PostgreSQL to InterBase dump file converter by Dobrica Pavlinusic
  • Full Convert Database conversion from PostgreSQL to more than 30 database engines
  • OmniDB Open source full-featured web tool for database management. Currently supports PostgreSQL only. More RDBMS support coming soon, including the ability of converting databases from any supported RDBMS to PostgreSQL and back.
  • Exportizer Enterprise Supports data conversion from PostgreSQL to Oracle, SQL Server, SQLite, Interbase, Firebird, MySQL, DB2, Informix, SQL Anywhere, Microsoft Access, Microsoft Excel, dBase, Paradox, CSV.
Retrieved from "https://wiki.postgresql.org/index.php?title=Converting_from_other_Databases_to_PostgreSQL&oldid=34064"
Personal tools