[radi.neradi]

Kako inače radite migraciju MySQL baze podataka na PostgreSQL?

Sada sam ovako napravio - dva docker containera sa MySQL i PostgreSQL. U MySQL ide novi user koji koristi mysql_native_password jer inače faila sa caching_sha2_password kojeg pgloader ne podržava. Zatim ide import MySQL dumpa, i konverzija. Prema source SQL dumpu napisani su castovi za konverziju column data typeova - uspije rješiti oko 380 nepodudaranosti. Preostaju četiri za koje mislim da su nekakav bug u pgloaderu za konverziju primary keyeva. Preostale sam rješio ručno. Iz nekog razloga kada sam definirao 'cast' parametar direktno kroz command line ne daje iste rezultate kao sa migration datotekom. Stvar rješena ali da pročitam kako vi to radite. https://github.com/dimitri/pgloader

export SQL_FILE="/var/shm/data.sql"

docker network create dbnet

docker run -d --name psql1 --network dbnet -e POSTGRES_PASSWORD=pass postgres:16
docker exec -e PGPASSWORD=pass psql1 createdb -U postgres data

docker run -d --name mysql1 --network dbnet -e MARIADB_ROOT_PASSWORD=pass mariadb:10.11
docker exec -i mysql1 mariadb -uroot -ppass -e "CREATE DATABASE data; CREATE USER 'pgloader'@'%' IDENTIFIED BY 'pass'; GRANT ALL ON data.* TO 'pgloader'@'%'; FLUSH PRIVILEGES;"
docker exec -i mysql1 mariadb -uroot -ppass data < $SQL_FILE

export MYSQL_IP=$(docker inspect -f '{{range .NetworkSettings.Networks}}{{.IPAddress}}{{end}}' mysql1)
export PSQL_IP=$(docker inspect -f '{{range .NetworkSettings.Networks}}{{.IPAddress}}{{end}}' psql1)

# docker run --network dbnet --rm dimitri/pgloader pgloader mysql://pgloader:pass@$MYSQL_IP:3306/data postgresql://postgres:pass@$PSQL_IP:5432/data

cat << EOF > migration.load
LOAD DATABASE
  FROM mysql://pgloader:pass@$MYSQL_IP:3306/data
  INTO postgresql://postgres:pass@$PSQL_IP:5432/data

  WITH create tables, create indexes, reset sequences, foreign keys

  CAST type tinyint when (>= precision 1) to smallint drop typemod,
  type smallint when (>= precision 4) to smallint drop typemod,
  type mediumint when (>= precision 8) to int drop typemod,
  type int when (>= precision 10) to integer drop typemod,
  type bigint when (>= precision 20) to integer drop typemod,
  type text to "character varying",
  type mediumtext to text,
  type datetime to timestamp drop default drop not null using zero-dates-to-null;
EOF

docker run --network dbnet --rm -v $(pwd)/migration.load:/migration.load dimitri/pgloader pgloader /migration.load

            COPY Threads Completion         0        40                     25.680s
                     Create Indexes         1        1910                   84.970s
             Index Build Completion         0        1920                   43.640s
                    Reset Sequences         0        400                    0.390s
                       Primary Keys         0        640                    0.430s
                Create Foreign Keys         0        0                      0.000s
                    Create Triggers         0        0                      0.020s
                    Set Search Path         0        10                     0.000s
                   Install Comments         0        0                      0.000s
-----------------------------------  ---------  ---------  ---------  --------------
                  Total import time         ✓     2354190   570.0 MB        155.130s