English

Single-Table Migration Tool - mxgate

MatrixGate has supported migration mode since version 4.3. It can migrate table data at high speed from Greenplum 5, Greenplum 6, and YMatrix clusters into the current YMatrix cluster.

1 High-Speed Migration Principle

High-speed migration allows data to bypass the master node. Data is read directly from the Segments of the source cluster, forwarded through mxgate, and sent to the Segments of the target cluster. There is no single-master bottleneck.

The number of Segments in the source and target clusters can differ.

2 Usage

2.1 Single-Table Migration

Single-table migration transfers all data of a table. Example usage:

mxgate --source transfer \
         --src-host 172.31.41.7 \
         --src-port 5432 \
         --src-db postgres \
         --src-user ec2-user \
         --src-password abc \
         --src-schema public \
         --src-table trans_ao \
         --compress "gzip" \
         --port-base 9129 \
         --local-ip 172.31.33.128 \
         --db-database ttt \
         --target public.trans_ao \
         --format text \
         --time-format raw \
         --use-auto-increment=false

Where:

Parameter	Description
--source	Functional entry point; must be set to 'transfer'
--src-host	IP address of the master of the source database
--src-port	Port number of the master of the source database
--src-user	User name for connecting to the source database (a superuser is recommended)
--src-password	Connection password
--src-schema	Schema name of the source table
--src-table	Table name of the source table
--compress	Transfer method from the segment hosts of the source database to this database: Empty string “”, meaning no compression, plain-text transfer gzip: Use gzip compression; the gzip Linux command must be installed on the segment hosts of the source database lz4: Use lz4 compression; the lz4 Linux command must be installed on the segment hosts of the source database lz4 > gzip > no compression is recommended
--port-base	A range of ports starting from 9129 is occupied during the transfer
--local-ip	Must be an IP address of this machine that the source database can connect to
--db-database	Name of the database where the migration target table resides
--target	Name of the migration target table, in the form \<schema>.\<table>; if the schema name is omitted, it defaults to public
--format	text or csv. Use CSV only when the migrated data contains complex strings (including line breaks, quotes, and delimiters). When text/csv are both acceptable, prefer the text mode
--time-format	Must be raw in transfer mode
--use-auto-increment	When the target table contains serial auto-increment fields, mxgate skips such fields by default. Add this option to disable the skipping logic in mxgate

Starting from 6.8.4, single-table migration establishes mappings by field name: migration works even when the field order differs between the source and target tables, as long as the field names match.

2.2 Export to File

Another use of migration mode is to quickly export data to files:

mxgate --source transfer \
         --src-host 172.31.41.7 \
         --src-port 5432 \
         --src-db postgres \
         --src-user ec2-user \
         --src-schema public \
         --src-table trans_ao_1 \
         --compress "lz4" \
         --port-base 9129 \
         --local-ip 172.31.33.128 \
         --save-to-dir /tmp/receive/ \
         --db-database ttt \
         --transform nil \
         --writer nil \
         --target trans_ao

Use the --save-to-dir parameter to specify the file storage path.

Note that even when exporting to files, you must provide the --db-database and --target parameters to specify the target database and table, and the target database and table must exist.

2.3 Filtered Migration

Filtered migration uses the --src-sql parameter to specify SQL that filters the data to synchronize. It can be used for both table-to-table migration and table-to-file migration:

mxgate --source transfer \
         --src-host 172.31.41.7 \
         --src-port 5432 \
         --src-db postgres \
         --src-user ec2-user \
         --src-sql "select * from demo where c1 = 'xxxx'" \
         --compress "lz4" \
         --port-base 9129 \
         --local-ip 172.31.33.128 \
         --save-to-dir /tmp/receive/ \
         --db-database ttt \
         --transform nil \
         --writer nil \
         --target trans_ao

For detailed parameters of the migration tool, see mxgate.

← Previous

Connecting to MatrixGate

Full-database migration - MatrixShift