Migrate to a different database (heterogeneous)

Oracle

# Oracle -> PostgreSQL: Ora2Pg (schema + data + PL/SQL conversion)
ora2pg -c ora2pg.conf -t TABLE   -o schema.sql
ora2pg -c ora2pg.conf -t COPY    -o data.sql   # data in COPY format
ora2pg -t SHOW_REPORT --estimate_cost           # migration cost estimate

# Oracle -> (multiple targets) managed/online: AWS DMS or Oracle GoldenGate

For heterogeneous migration from Oracle (especially to PostgreSQL), the most common open-source tool is Ora2Pg: it converts tables, constraints, PL/SQL and data into the target dialect; --estimate_cost estimates the migration effort. For online/low-downtime migration, AWS DMS or Oracle GoldenGate (with CDC) is used.

PostgreSQL

# MySQL -> PostgreSQL: pgloader (schema+data+type conversion in one command)
pgloader mysql://app_user:password@source/shop \
         postgresql://app_user:password@target/shop

# Also from SQLite/MSSQL sources: with a pgloader ./migration.load script
# For a managed/online PostgreSQL target: AWS DMS

pgloader is the standard tool for migrating to PostgreSQL from other engines (MySQL, SQLite, MS SQL): with connection strings it creates the schema in a single command, maps types and copies data quickly; custom transformations/type mappings are defined in a .load script. If the source is Oracle, Ora2Pg is preferred; for online migration, AWS DMS.

MySQL

# PostgreSQL/Oracle/MSSQL -> MySQL: AWS DMS (managed) is the most common path
# or adapt the source-specific export for MySQL:
#   pg_dump --inserts ... | (type/syntax fixes) | mysql shop

# MySQL <-> MariaDB (compatible): mysqldump output loads directly
mysqldump --single-transaction shop | mysql -h target_mariadb shop

There is no single dominant open tool for migrating to MySQL from other engines; the most common solution is AWS DMS (managed, CDC-capable) or adapting the source dump for syntax/types and loading it with the mysql client. MySQL and MariaDB are largely compatible; mysqldump output usually migrates directly.

SQL Server

# SQL Server -> PostgreSQL: with pgloader
pgloader mssql://app_user:password@source/shop \
         postgresql://app_user:password@target/shop

# SQL Server -> PostgreSQL compatibility layer: Babelfish for Aurora PostgreSQL
# (runs T-SQL and the TDS protocol on top of PostgreSQL)
# To other targets: SSMA (SQL Server Migration Assistant) or AWS DMS

pgloader (mssql:// source) is used to migrate from SQL Server to PostgreSQL; if you want to keep T-SQL without changing the application, Babelfish for Aurora PostgreSQL offers a compatibility layer. Microsoft's SSMA tool automates migration from other engines (Oracle, MySQL, DB2) to SQL Server. For online migration, AWS DMS.

MongoDB

# Relational <-> MongoDB conversion requires schema mapping (ETL):
#   - Relational Migrator (official MongoDB tool): maps tables to the document model
#   - or generate JSON from the source -> load with mongoimport
mongoexport --db=shop --collection=customer --type=csv --fields=id,name > c.csv
# (To an SQL target: mongoexport CSV -> relational COPY/LOAD DATA)

Migration between MongoDB and SQL engines requires a row<->document model transformation (ETL); there is no automatic single command. From SQL to MongoDB, MongoDB Relational Migrator maps tables to a document schema; in simple cases, generate CSV/JSON from the source and load it with mongoimport. In the reverse direction, mongoexport output is loaded into the target relational table with COPY/LOAD DATA.

Redis

# With RIOT (Redis Input/Output Tools), to/from Redis from other sources:
#   Relational -> Redis:
riot db-import --jdbc-url "jdbc:postgresql://host/shop" \
  --sql "SELECT * FROM customer" hset --keyspace customer --keys id
#   Redis -> another Redis / file:  riot replicate / riot file-export

In heterogeneous migration Redis is usually the cache layer as target/source; to load data into Redis from relational or file sources, Redis's official RIOT tool is used (db-import maps an SQL result to HSET/SET structures). For Redis to Redis or to a file there are the riot replicate / file-export commands.

Elasticsearch

# Relational/other source -> Elasticsearch: Logstash (JDBC input)
# logstash.conf: input { jdbc { statement => "SELECT * FROM customer" } }
#                output { elasticsearch { index => "customer" } }

# ES -> ES (version/cluster upgrade): reindex from a remote source
POST _reindex
{ "source": { "remote": { "host": "http://old:9200" }, "index": "customer" },
  "dest": { "index": "customer" } }

The most common tool for migrating from other databases to Elasticsearch is Logstash (JDBC/file input -> elasticsearch output); Elastic's own connectors/Beats are also used. When moving from an old Elasticsearch cluster to a new one, remote reindex (_reindex with a 'remote' source) is common; reindex.remote.whitelist must be defined on the target.

Open in DBAtlas →