Consistent export for migration

Oracle

# Consistent logical export with Data Pump (at a single SCN)
expdp system/password schemas=shop directory=DIR_DMP \
  dumpfile=shop_%U.dmp logfile=shop_exp.log \
  flashback_scn=<scn> parallel=4

# Current SCN for point-in-time consistency:
# SELECT current_scn FROM v$database;
# Alternative: FLASHBACK_TIME=SYSTIMESTAMP

Data Pump (expdp) is the standard logical export tool for migration. For read consistency, pass FLASHBACK_SCN=<scn> (or FLASHBACK_TIME) so all tables are exported consistently at a single SCN; this is critical on a live system. PARALLEL speeds it up, and %U produces multiple files.

PostgreSQL

# Single compressed file in custom format (restorable in parallel)
pg_dump -Fc -Z6 -f shop.dump shop

# Directory format (parallel dump + parallel restore):
pg_dump -Fd -j4 -f shop_dir shop

# For the whole cluster + roles/tablespaces:
# pg_dumpall --globals-only > roles.sql

The pg_dump -Fc (custom) or -Fd (directory) format is preferred for migration; both allow selective/parallel loading with pg_restore. Because pg_dump runs inside a single transaction, its output is automatically a consistent snapshot. Cluster-wide objects such as roles and tablespaces are dumped separately with pg_dumpall --globals-only.

MySQL

# Consistent, lock-free export for InnoDB
mysqldump --single-transaction --quick --routines --triggers --events \
  --source-data=2 shop > shop.sql

# Note: older versions use --master-data=2 instead of --source-data.
# Parallel, fast alternative: mydumper -B shop -o ./backup --threads 4

On InnoDB, --single-transaction provides a single consistent snapshot and does not lock tables. --source-data=2 (formerly --master-data=2) writes the binlog position/GTID information needed to start replication/CDC as a comment at the top of the output; this is essential for a zero-downtime cutover. For large data, mydumper runs in parallel.

SQL Server

-- Full backup for physical migration (to the same/compatible version):
BACKUP DATABASE shop TO DISK = 'C:\backup\shop.bak' WITH COMPRESSION, CHECKSUM;

-- Portable/version-independent logical export (BACPAC = schema+data):
-- sqlpackage /Action:Export /SourceConnectionString:"..." /TargetFile:shop.bacpac

-- For a single table: bcp shop.dbo.customer out customer.dat -n -S server -T

When migrating to the same or a newer SQL Server, BACKUP ... RESTORE is the most reliable path (integrity is verified with CHECKSUM). For version/platform-independent migration or a move to Azure SQL, exporting schema+data into a single .bacpac file with SqlPackage /Action:Export is preferred. bcp is used for individual tables.

MongoDB

# Consistent binary (BSON) export; point-in-time consistency with the oplog
mongodump --uri="mongodb://source:27017" \
  --oplog --out=./backup --numParallelCollections=4

# --oplog captures changes made during the dump (requires a replica set);
# it works only with a FULL dump and cannot be combined with --db / --collection.
# On restore, mongorestore --oplogReplay brings it to a single point in time.
# If you need a single database: take it with --db=shop (without the oplog) or use --nsInclude='shop.*' on restore.

mongodump is the standard binary (BSON) export for migration. On a replica set, the --oplog option captures changes written while the dump is running from the oplog, providing a consistent point in time (paired with mongorestore --oplogReplay); this option is valid only for a full dump. For cluster-level migration of a large cluster, mongosync/live tools are more suitable.

Redis

# Create an RDB snapshot in the background (non-blocking)
redis-cli BGSAVE
# Check for completion:
redis-cli INFO persistence | grep rdb_bgsave_in_progress

# Move the resulting file (path: CONFIG GET dir + dbfilename)
# e.g. /var/lib/redis/dump.rdb  ->  copy to the target server

In Redis the consistent export is the RDB snapshot. BGSAVE produces a consistent dump.rdb in the background via fork without blocking the main process (SAVE blocks and should not be used). The file path is found with CONFIG GET dir and dbfilename; migrate by copying this dump.rdb into the target server's data directory.

Elasticsearch

# 1) Register a snapshot repository (shared file system / S3)
PUT _snapshot/migration_repo
{ "type": "fs", "settings": { "location": "/mnt/es_backup" } }

# 2) Take a consistent snapshot
PUT _snapshot/migration_repo/migration_1?wait_for_completion=true
{ "indices": "customer,orders", "include_global_state": false }

In Elasticsearch the consistent export method for migration is the snapshot. First a repository (fs, S3, GCS, etc.) is registered, then the snapshot is taken; snapshots are incremental and capture a consistent state as of the moment they are taken. elasticdump can also be used for small data/a single index, but snapshot is the standard.