Migrate data from MatrixDB 4 to MatrixDB 4

This section of the documentation mainly introduces best practices for migrating data from MatrixDB 4 to MatrixDB 4.

1 Environmental preparation

Once you decide to do an important operation, be fully prepared and start, whether it is actual conditions or mentally prepared (because you may encounter problems at all times). Mental preparation Everyone has their own way. We will list a relatively complete plan for you, including optional steps, as follows:

| Serial number | Preparation steps | Instructions | Optional | --- | --- | | 1 | Backup source cluster data | Data migration only reads the source cluster data, no writes, so it does not involve the risk of data corruption caused by migration | Yes | 2 | Install and deploy the target database software | | No, necessary steps | 3 | Deploy monitoring for target clusters | Depend on demand | Yes | 4 | Prohibit all DDL operations on the business side | This step is an important step, which will bring risks to migration execution. Please be sure to pay attention to | No, necessary steps | 5 | Interrupt all business connections | This step is an important step, which will bring risks to migration execution. Please be sure to pay attention to | No, necessary steps | 6 | Collect source cluster and target cluster information | Software and hardware configuration information, source cluster topology information, target cluster topology information, etc. | No, necessary steps | 7 | Backup source cluster source information | DDL, pattern name, user information, etc. | No, necessary steps | 8 | Add whitelist to all nodes in the source cluster and the target cluster | | No, necessary steps | 9 | Create a user for the target cluster | | No, necessary steps | 10 |Create DDL for the target cluster| In MatrixDB, recreating indexes after performing migration operations is more efficient. Therefore, when creating DDL for the target cluster before migration, it is recommended to create a statement without index | No, necessary steps | 11 |Restore table structure | | No, necessary steps

Based on the table above, we give specific examples.

1.1 Backup source cluster data

Data migration only has read operations and no write operations for source cluster data, so it does not involve the risk of data corruption caused by migration. But if you are still worried, or have other business needs that require data, you can use the mxbackup tool to implement cluster parallel backup.

1.2 Install and deploy the target database software

Notes!
We recommend that you do not deploy mirror nodes (Mirrors) when deploying the cluster, and add them after the migration is completed to improve migration efficiency.

Notes!
The target cluster host name and the source cluster cannot be repeated!

Please refer to the standard cluster deployment document:

1.3 Deploy monitoring for target clusters

Please refer to the monitoring alarm document:

1.4 All DDL operations on the business side are prohibited

Notes!
Before officially stopping all services on the business side, the source MatrixDB 4 cluster service side does not allow any DDL to be executed, including creating objects, modifying objects, adding fields, deleting fields, and prohibiting the execution of CREATE, ALTER, TRUNCATE, and DROP statements.

1.5 Disconnect all business connections

Modify the pg_hba.conf file on the source MatrixDB 4 cluster master.

$ vim  pg_hba.conf

Add the business client address in the following format to disable remote access.

host     all         all         <Client IP Address>/<Subnet Mask Bits>       reject

Then reload the configuration to make the modified configuration file take effect

$ gpstop -u

1.6 Collect source cluster and target cluster information

Collect source cluster and target cluster information, including the number of physical machines, operating system, CPU, memory, disk type, disk usage, network card information, source cluster topology, target cluster topology, database license, resource group configuration, etc., and use it according to the specific scenario to make comprehensive migration execution preparations. The following commands may be used:

Serial number	Command	Purpose
1	free -g	View operating system memory information
2	lscpu	View CPU quantity
3	cat /etc/system-release	View operating system version information
4	uname -a	Output all kernel information in the following order (where the detection results of -p and -i are omitted if they are agnostic): kernel name; host name on network node; kernel issue number; kernel version; host hardware architecture name; processor type (not portable); hardware platform or (not portable); operating system name
5	tail -11 /proc/cpuinfo	View CPU information
6	gpcheckperf	Network performance, bandwidth, disk I/O performance detection

1.7 Backup source cluster source information

Under Super User, use the pg_dump tool to back up the source MatrixDB 4 cluster DDL, schema name, user information, etc.

# Backup global user objects
$ pg_dumpall -g -f global_user.sql

# Backup table structure
$ pg_dump <Source Database Name> -s -f orig.sql          

# Copy a backup
$ cp orig.sql copy.sql

Generates the SQL file that creates the index.

$ cat get_index.sql

WITH soi (oid, toid, SIZE, tschema, tname) AS
  ( SELECT soioid,
           soitableoid,
           soisize,
           soitableschemaname,
           soitablename
   FROM gp_toolkit.gp_size_of_index 
  ),
  childrel (oid, coid)AS
  ( SELECT t.parentrelid::oid, 
           t.relid::oid 
    FROM pg_partitioned_table, pg_partition_tree(partrelid) t 
    where t.isleaf
  ),
  irn (oid, toid, SIZE, tschema, tname, rn) AS
  ( SELECT *,
           row_number() OVER (
            ORDER BY dt.ssize DESC) rn
   FROM
     ( SELECT soi.oid,
              soi.toid ,
              sum(coalesce(dt2.SIZE, soi.SIZE)) ssize ,
                                                soi.tschema,
                                                soi.tname
      FROM soi
      LEFT JOIN
        ( SELECT childrel.oid,
                 soi.SIZE
         FROM soi
         INNER JOIN childrel ON soi.toid = childrel.coid ) dt2 ON soi.toid = dt2.oid
      GROUP BY 1,
               2,
               4,
               5 ) dt )
SELECT SQL || ';'
FROM
  ( SELECT pg_get_indexdef(oid) AS SQL ,
           (rn % 12 + (rn / 12)::int) % 12 AS orderkey
   FROM irn
   WHERE toid NOT IN
       (SELECT coid
        FROM childrel) ) dt
WHERE SQL NOT LIKE 'CREATE UNIQUE INDEX%'
ORDER BY dt.orderkey ;

Execute the above SQL via psql.

$ psql -d <Source Database Name> -U mxadmin -t -f get_index.sql > index.sql

1.8 Add whitelists to all nodes in the source cluster and target cluster

Notes!
If the source cluster and the target cluster are running on the same server, just skip this step.

In the master of the source cluster and the target cluster, execute the following command to add the host IP addresses of all nodes of the source cluster and the target cluster to the pg_hba.conf file. The IP address and subnet mask in the example are 172.16.100.2/32 and 172.16.100.3/32.

Notes!
If there are multiple hosts, you need to write all host IPs to the script.
$ cat  config_hba.sh

#!/bin/bash
for line  in `psql -Atc "select hostname||','|| datadir
 from gp_segment_configuration order by datadir desc"`
do
    hostname=`echo $line|awk -F "," '{print $1}'`
    datadir=`echo $line|awk -F "," '{print $2}'`

    gpssh -h $hostname -v -e "echo host    all    all    172.16.100.2/32    md5>> ${datadir}/pg_hba.conf"
    gpssh -h $hostname -v -e "echo host    all    all    172.16.100.3/32    md5>> ${datadir}/pg_hba.conf"

done

In the source cluster and the target cluster master, execute the following command to add the host IP address and host name of all nodes of the source cluster and the target cluster to the /etc/hosts file. In the example, the host IP address is 172.16.100.195 and the host name is sdw1.

$ cat  add_hosts.sh

#!/bin/bash
for line  in `psql -Atc "select distinct  hostname from gp_segment_configuration order by datadir desc"`
do
    gpssh -h $hostname -v -e "echo 172.16.100.195 sdw1 >> /etc/hosts"

done

Then reload the configuration to make the modified configuration file take effect

$ gpstop -u

1.9 Create a user for the target cluster

Execute the following command in the target MatrixDB 4 cluster environment.

$ psql -h <YMatrix Server IP Address> -p  <Target cluster port number> -d <Target database> -U <Target database superuser name> -f global_user.sql

1.10 Create DDL for the target cluster

Execute the following command in the target MatrixDB 4 cluster environment.

$ psql -h <YMatrix Server IP Address> -p  <Target cluster port number> -d <Target database> -U <Target database superuser name> -f orig.sql

1.11 Restore table structure

Use the backup orig.sql file to restore the table structure in the target MatrixDB 4 cluster.

$ time psql -d <Target database name> -f orig.sql > restoreddl.log  2>&1 &

2 Migration execution

Notes!
For detailed parameters, please refer to mxshift

First write the configuration file config_path.toml.

[database]
        [database.source]
        ## Name of database
        db-database= "testdb"
        ## Hostname of database master
        db-host="sdw3"
        ## password of database
        db-password="xxxx"
        ## Port of database master
        db-port=54322
        ## user name of database
        db-user="mxadmin"

    [database.target]
        ## Name of database
        db-database="destdb"
        ## Hostname of database master
        db-host="172.16.100.32"
        ## password of database
        db-password="yyyy"
        ## Port of database master
        db-port=5432
        ## user name of database
        db-user="mxadmin"

[scope]
compress_method="lz4"
gphome="/opt/ymatrix/matrixdb5"
mode="normal"
        [[scope.table-list]]
                schema="test_schema_1"
                name="table_001"
        [[scope.table-list]]
                schema="test_schema_2"
                name="table_002"

        [[scope.exclude-table-list]]
                schema="test_schema_3"
                name="table_003"

         schema-list=["test_schema_1", "test_schema_2"]
         exclude-schema-list=["test_schema_5", "test_schema_8"]

[log]
log-level="info"
## Print log without color.
# no-color=false

[controller]
both-way=true
concurrency=5

[transfer]
verify=true
[transfer.table-data-where-sql]
enabled=false
global="txdate >= '2022-10-01' AND batchnum >= 100000000"
[[transfer.table-data-where-sql.override]]
        where="abc > 10"
        [transfer.table-data-where-sql.override.table]
        schema="test_schema_1"
        name="table_001"
[[transfer.table-data-where-sql.override]]
        where="tag != 'aabbcc' AND ts > '2022-01-01'"
        [transfer.table-data-where-sql.override.table]
        schema="test_schema_2"
        name="another_table"

[ddl]
enabled=true
# file-path="/tmp/mxshift.sql"
# mode="output"
only-ddl=false
## During the DDL transfer, whether to skip the transfer of resource queue or group, by default, it is true.
# skip-resource-queue-and-group=true
## During the DDL transfer, whether to skip the transfer of tablespace, by default, it is true.
# skip-table-space=true
        [[ddl.replace]]
        ## Only applicable for the case of migration from Greenplum to YMatrix
                category="role"
                [[ddl.replace.pairs]]
                        old="mxadmin"
                        new="mxadmin"

Then perform data migration on the target MatrixDB 4 cluster.

$ mxshift -c config_path.toml

3 Follow-up tasks

3.1 Execute to create an index

Perform the creation of an index on the target MatrixDB 4 cluster.

$ psql -h localhost -p  <Target cluster port number> -d <Target database name> -U <Target database superuser name> -f index.sql >>idx.out 2>&1 &

3.2 Execute the analyzedb command

Update the library statistics on the target MatrixDB 4 cluster.

$ export PGPORT=<Target cluster port number>
time analyzedb -d <Target database name> -p 10 -a

3.3 Adding Mirror

Add Mirror on the target MatrixDB 4 cluster. The steps are as follows:

# First, check the current cluster instance information
postgres=# SELECT * from gp_segment_configuration order by 1;
 dbid | content | role | preferred_role | mode | status | port | hostname | address |                   datadir
-----+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    1 |      -1 | p    | p              | n    | u      | 5432 | mdw      | mdw     | /home/mxdata_20220925154450/master/mxseg-1
    2 |       0 | p    | p              | n    | u      | 6000 | sdw2     | sdw2    | /home/mxdata_20220925154450/primary/mxseg0
    3 |       1 | p    | p              | n    | u      | 6001 | sdw2     | sdw2    | /home/mxdata_20220925154450/primary/mxseg1
    4 |       2 | p    | p              | n    | u      | 6000 | sdw3     | sdw3    | /home/mxdata_20220925154450/primary/mxseg2
    5 |       3 | p    | p              | n    | u      | 6001 | sdw3     | sdw3    | /home/mxdata_20220925154450/primary/mxseg3
    6 |      -1 | m    | m              | s    | u      | 5432 | sdw1     | sdw1    | /home/mxdata_20220925154450/standby/mxseg-1
(6 rows)

# Create a file with all hostnames
$ cat /home/mxadmin/seg_hosts 
sdw1
sdw2
sdw3
sdw4

# Batch increase of Mirror directory via gpssh command
$ gpssh -f /home/mxadmin/seg_hosts -e 'mkdir -p /home/mxdata_20220925154450/mirror'

# Generate Mirror template file
$ gpaddmirrors -o ./addmirror

# View Mirror template files
$ cat addmirror

# Perform the Add Mirror Operation
$ gpaddmirrors -i addmirror

# Finally, check the cluster instance again
postgres=# SELECT * from gp_segment_configuration order by 1;
 dbid | content | role | preferred_role | mode | status | port | hostname | address |                   datadir
-----+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    1 |      -1 | p    | p              | n    | u      | 5432 | mdw      | mdw     | /home/mxdata_20220925154450/master/mxseg-1
    2 |       0 | p    | p              | n    | u      | 6000 | sdw2     | sdw2    | /home/mxdata_20220925154450/primary/mxseg0
    3 |       1 | p    | p              | s    | u      | 6001 | sdw2     | sdw2    | /home/mxdata_20220925154450/primary/mxseg1
    4 |       2 | p    | p              | s    | u      | 6000 | sdw3     | sdw3    | /home/mxdata_20220925154450/primary/mxseg2
    5 |       3 | p    | p              | s    | u      | 6001 | sdw3     | sdw3    | /home/mxdata_20220925154450/primary/mxseg3
    6 |      -1 | m    | m              | s    | u      | 5432 | sdw1     | sdw1    | /home/mxdata_20220925154450/standby/mxseg-1
    7 |       0 | m    | m              | n    | d      | 7000 | sdw3     | sdw3    | /home/mxdata_20220925154450/mirror/mxseg0
    8 |       1 | m    | m              | s    | u      | 7001 | sdw3     | sdw3    | /home/mxdata_20220925154450/mirror/mxseg1
    9 |       2 | m    | m              | s    | u      | 7000 | sdw2     | sdw2    | /home/mxdata_20220925154450/mirror/mxseg2
   10 |       3 | m    | m              | s    | u      | 7001 | sdw2     | sdw2    | /home/mxdata_20220925154450/mirror/mxseg3
(10 rows)

After completing the above steps, restore business access and observe business operation status, and keep track of it for a period of time (the specific time depends on the specific timing scenario). If it runs stably, congratulations to the successful completion of the data migration!