Converting from other Databases to PostgreSQL

From PostgreSQL wiki

Jump to: navigation, search

Contents

Non-specific

  • 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 web tool for database management and conversion. Currently supports PostgreSQL, Oracle, MariaDB, MySQL, Firebird, SQLite, MS Access, MS SQL Server, MS SQL Compact. OmniDB converts from any supported RDBMS to PostgreSQL and back.

DBase II, III, IV+ / DBF Format

FileMaker Pro

IBM DB2

Interbase

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
  • 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

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

Converting PostgreSQL Databases to other Databases

  • PostgreSQL to InterBase dump file converter by Dobrica Pavlinusic
  • Full Convert Database conversion from PostgreSQL to more than 30 database engines
  • OmniDB Open source web tool for database management and conversion. Currently supports PostgreSQL, Oracle, MariaDB, MySQL, Firebird, SQLite, MS Access, MS SQL Server, MS SQL Compact. OmniDB converts from any supported RDBMS to PostgreSQL and back.
Personal tools