Converting from other Databases to PostgreSQL

From PostgreSQL wiki

Jump to: navigation, search

Contents

Non-specific

  • Full Convert Database conversion and synchronization between Microsoft Access, dBase, FoxPro, Microsoft Excel, Firebird, Interbase, MySQL, Oracle, Paradox, Microsoft SQL Server, PostgreSQL, SQL Server, SQL Server Azure, SQL Server Compact(SQLCE), SQLite, Delimited text files (CSV), XML and many more via ODBC.
  • 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 popular data sources to PostgreSQL, and vice versa.

DBase II, III, IV+ / DBF Format

FileMaker Pro

IBM DB2

Interbase

Microsoft Access

  • exportSQL - a Microsoft Access module which exports Access Database into MySQL, mSQL and PostgreSQL by Dobrica Pavlinusic. Based on the work of Pedro Freire
  • 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

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

Personal tools