DBAtlas · Database operations atlas

Import into the target

Oracle

# Import with Data Pump; remap schema and tablespace
impdp system/password directory=DIR_DMP dumpfile=shop_%U.dmp \
  logfile=shop_imp.log \
  remap_schema=shop:shop_new \
  remap_tablespace=USERS:DATA parallel=4

# For existing objects: TABLE_EXISTS_ACTION=TRUNCATE|APPEND|REPLACE

impdp loads the .dmp file taken with expdp into the target. REMAP_SCHEMA moves the source into a different schema and REMAP_TABLESPACE into a different tablespace; these are used often when migrating between environments. Behavior for existing tables is set with TABLE_EXISTS_ACTION, and speed with PARALLEL.

PostgreSQL

# Parallel restore (from custom/directory format)
pg_restore -j4 -d targetdb --no-owner --clean --if-exists shop.dump

# A specific table only: pg_restore -d targetdb -t customer shop.dump
# For a plain .sql dump:  psql -d targetdb -f shop.sql

With pg_restore -j, custom/directory format dumps are loaded in parallel (multiple processes); this speeds up loading significantly for large databases. --clean --if-exists drops objects in the target first, and --no-owner adapts to a different role structure. Plain SQL dumps are loaded with psql -f (not parallel).

MySQL

# Load a plain SQL dump
mysql --binary-mode shop < shop.sql

# Parallel/fast load (if taken with mydumper):
myloader --directory=./backup --database=shop --threads=4 --overwrite-tables

# Tip: SET FOREIGN_KEY_CHECKS=0; unique_checks=0 before loading speeds it up.

A mysqldump SQL dump is loaded by redirecting it to the mysql client (mysql shop < dump.sql). For parallel and much faster loading, a directory taken with mydumper is restored with myloader --threads. Disabling FOREIGN_KEY_CHECKS and UNIQUE_CHECKS during the load improves performance.

SQL Server

-- Restore from a physical backup (move file paths to the new environment)
RESTORE DATABASE shop FROM DISK = 'C:\backup\shop.bak'
WITH MOVE 'shop_data' TO 'D:\data\shop.mdf',
     MOVE 'shop_log'  TO 'D:\log\shop.ldf', REPLACE;

-- Import from a BACPAC (schema+data):
-- sqlpackage /Action:Import /TargetConnectionString:"..." /SourceFile:shop.bacpac

A .bak backup is loaded with RESTORE DATABASE; when moving to a different disk layout, WITH MOVE redirects the data/log files to new paths. For a version-independent/Azure SQL target, SqlPackage /Action:Import creates the .bacpac file (schema + data) as a new database.

MongoDB

# Restore the BSON backup; replay the oplog to land on a single point in time
mongorestore --uri="mongodb://target:27017" --oplogReplay \
  --numParallelCollections=4 ./backup

# Renaming: --nsFrom='shop.*' --nsTo='shop_new.*'
# Drop existing data before loading: --drop

mongorestore loads mongodump BSON output into the target. --oplogReplay replays the oplog changes in a backup taken with --oplog, bringing the dump to a single consistent point in time. Database/collection names are changed with --nsFrom/--nsTo, and existing data can be cleared with --drop.

Redis

# Stop the server, place dump.rdb in the data directory, start it again
# 1) systemctl stop redis  (or SHUTDOWN NOSAVE)
# 2) cp dump.rdb  <path found with CONFIG GET dir>/dump.rdb
# 3) systemctl start redis   -> Redis loads the RDB into memory at startup

# Alternative (live): feed a RESP command stream with redis-cli --pipe

Redis loads the RDB only at startup. Therefore the target server is stopped, the migrated dump.rdb is placed in the data directory (CONFIG GET dir), and the server is restarted; at startup memory is populated from the RDB. To load without stopping the server, stream the keys in RESP format with redis-cli --pipe.

Elasticsearch

# Register the same repository on the target cluster, then restore the snapshot
PUT _snapshot/migration_repo
{ "type": "fs", "settings": { "location": "/mnt/es_backup" } }

POST _snapshot/migration_repo/migration_1/_restore
{ "indices": "customer,orders",
  "rename_pattern": "(.+)", "rename_replacement": "restored_$1" }

On the target cluster, register the same snapshot repository as the source (the files must be accessible), then restore the indices with _snapshot/.../_restore. Index names can be changed with rename_pattern/rename_replacement. Existing indices cannot be restored while open; close or rename them first.

Open in DBAtlas →