.::: create Tools Migration from MariaDB to PostgreSQL using bash shell script (no csv):::.

  

correlation https://teguhth.blogspot.com/2025/12/create-tools-migration-from-postgresql.html

1. sample data edb & mariadb

MySQL MariaDB
https://teguhth.blogspot.com/2019/04/study-kasus-praktis-belajar-query-mysql.html

PostgreSQL
https://teguhth.blogspot.com/2019/09/study-kasus-praktis-belajar-query.html

2. data server A & B 

server A Mariadb
ip : 10.10.10.9
db : teguhth
port 3306
user : admin
password : admin

server B Postgres
ip : 10.10.10.90
db : mariaedb
port 5432
user : admin
password : admin
 
3. run script migrasi 

[root@teguhth-all maria-to-edb]# ls | grep migrasi_
migrasi_barang_mariadb_to_postgres.sh
migrasi_customer_mariadb_to_postgres.sh
migrasi_pasok_mariadb_to_postgres.sh
migrasi_pembelian_mariadb_to_postgres.sh
migrasi_suplier_mariadb_to_postgres.sh
[root@teguhth-all maria-to-edb]#
 

4. result after migrasi 
 

5. script migrasi table barang 

[root@teguhth-all maria-to-edb]# pwd
/data/migrasi/maria-to-edb
[root@teguhth-all maria-to-edb]# cat migrasi_barang_mariadb_to_postgres.sh
#!/bin/bash
set -euo pipefail

# =========================
# SOURCE : MariaDB
# =========================
MARIADB_HOST="10.10.10.9"
MARIADB_PORT="3306"
MARIADB_DB="teguhth"
MARIADB_USER="admin"
MARIADB_PASSWORD="admin"

# =========================
# DESTINATION : PostgreSQL
# =========================
POSTGRES_HOST="10.10.10.90"
POSTGRES_PORT="5432"
POSTGRES_DB="mariaedb"
POSTGRES_USER="admin"
POSTGRES_PASSWORD="admin"

rm -f /data/migrasi/maria-to-edb/log_maria_to_edb_barang.log

LOG_FILE="/data/migrasi/maria-to-edb/log_maria_to_edb_barang.log"

log_message() {
    echo "[$(date '+%Y-%m-%d %H:%M:%S')] $1" | tee -a "$LOG_FILE"
}

log_message "Starting migration MariaDB -> PostgreSQL..."

# =========================
# Ensure database exists
# =========================
log_message "Ensure database mariaedb exists..."

PGPASSWORD="$POSTGRES_PASSWORD" psql \
    -h "$POSTGRES_HOST" \
    -p "$POSTGRES_PORT" \
    -U "$POSTGRES_USER" \
    -d postgres <<EOF
DO \$\$
BEGIN
   IF NOT EXISTS (
      SELECT 1 FROM pg_database WHERE datname = '$POSTGRES_DB'
   ) THEN
      CREATE DATABASE $POSTGRES_DB;
   END IF;
END
\$\$;
EOF

# =========================
# Ensure table exists
# =========================
log_message "Ensure table barang exists in PostgreSQL..."
log_message "Truncate table barang "
PGPASSWORD="$POSTGRES_PASSWORD" psql \
    -h "$POSTGRES_HOST" \
    -p "$POSTGRES_PORT" \
    -U "$POSTGRES_USER" \
    -d "$POSTGRES_DB" <<EOF
CREATE TABLE IF NOT EXISTS barang (
    KODE_BARANG CHAR(6) NOT NULL,
    NAMA_BARANG VARCHAR(25),
    SATUAN_BARANG VARCHAR(20),
    STOK_BARANG DECIMAL(4),
    PRIMARY KEY (KODE_BARANG)
);

truncate table barang;

EOF

# =========================
# Migrate data
# =========================
log_message "Migrating data from MariaDB..."

 /usr/bin/mariadb \
    --skip-ssl \
    -h "$MARIADB_HOST" \
    -P "$MARIADB_PORT" \
    -u "$MARIADB_USER" \
    -p"$MARIADB_PASSWORD" \
    "$MARIADB_DB" \
    -B -N -e "
    SELECT
        QUOTE(KODE_BARANG),
        QUOTE(NAMA_BARANG),
        QUOTE(SATUAN_BARANG),
        STOK_BARANG
    FROM barang
    ORDER BY KODE_BARANG;
    " | \
while IFS=$'\t' read -r kode nama satuan stok; do
    [ -z "$kode" ] && continue
    echo "INSERT INTO barang
          (KODE_BARANG, NAMA_BARANG, SATUAN_BARANG, STOK_BARANG)
          VALUES ($kode, $nama, $satuan, $stok)
          ON CONFLICT (KODE_BARANG) DO NOTHING;"
done | \
PGPASSWORD="$POSTGRES_PASSWORD" psql \
    -h "$POSTGRES_HOST" \
    -p "$POSTGRES_PORT" \
    -U "$POSTGRES_USER" \
    -d "$POSTGRES_DB"

log_message "Migration completed SUCCESSFULLY."

[root@teguhth-all maria-to-edb]#

6. script migrasi table suplier 

[root@teguhth-all maria-to-edb]# pwd
/data/migrasi/maria-to-edb
[root@teguhth-all maria-to-edb]# cat migrasi_suplier_mariadb_to_postgres.sh
#!/bin/bash
set -euo pipefail

# =========================
# SOURCE : MariaDB
# =========================
MARIADB_HOST="10.10.10.9"
MARIADB_PORT="3306"
MARIADB_DB="teguhth"
MARIADB_USER="admin"
MARIADB_PASSWORD="admin"

# =========================
# DESTINATION : PostgreSQL
# =========================
POSTGRES_HOST="10.10.10.90"
POSTGRES_PORT="5432"
POSTGRES_DB="mariaedb"
POSTGRES_USER="admin"
POSTGRES_PASSWORD="admin"

rm -f /data/migrasi/maria-to-edb/log_maria_to_edb_suplier.log

LOG_FILE="/data/migrasi/maria-to-edb/log_maria_to_edb_suplier.log"

log_message() {
    echo "[$(date '+%Y-%m-%d %H:%M:%S')] $1" | tee -a "$LOG_FILE"
}

log_message "Starting migration table suplier (MariaDB -> PostgreSQL)..."

# =========================
# Ensure database exists
# =========================
log_message "Ensure database mariaedb exists..."

PGPASSWORD="$POSTGRES_PASSWORD" psql \
  -h "$POSTGRES_HOST" \
  -p "$POSTGRES_PORT" \
  -U "$POSTGRES_USER" \
  -d postgres <<EOF
DO \$\$
BEGIN
   IF NOT EXISTS (
      SELECT 1 FROM pg_database WHERE datname = '$POSTGRES_DB'
   ) THEN
      CREATE DATABASE $POSTGRES_DB;
   END IF;
END
\$\$;
EOF

# =========================
# Ensure table exists
# =========================
log_message "Ensure table suplier exists in PostgreSQL..."
log_message "Truncate table suplier"

PGPASSWORD="$POSTGRES_PASSWORD" psql \
  -h "$POSTGRES_HOST" \
  -p "$POSTGRES_PORT" \
  -U "$POSTGRES_USER" \
  -d "$POSTGRES_DB" <<EOF
CREATE TABLE IF NOT EXISTS suplier (
    KODE_SUPLIER CHAR(5) NOT NULL,
    NAMA_SUPLIER VARCHAR(30),
    ALAMAT_SUPLIER VARCHAR(30),
    KOTA_SUPLIER VARCHAR(15),
    TELEPON_SUPLIER VARCHAR(15),
    PRIMARY KEY (KODE_SUPLIER)
);
truncate table suplier;
EOF

# =========================
# Migrate data
# =========================
log_message "Migrating data from MariaDB table suplier..."

 /usr/bin/mariadb \
    --skip-ssl \
    -h "$MARIADB_HOST" \
    -P "$MARIADB_PORT" \
    -u "$MARIADB_USER" \
    -p"$MARIADB_PASSWORD" \
    "$MARIADB_DB" \
    -B -N -e "
    SELECT
        QUOTE(KODE_SUPLIER),
        QUOTE(NAMA_SUPLIER),
        QUOTE(ALAMAT_SUPLIER),
        QUOTE(KOTA_SUPLIER),
        QUOTE(TELEPON_SUPLIER)
    FROM suplier
    ORDER BY KODE_SUPLIER;
    " | \
while IFS=$'\t' read -r kode nama alamat kota telp; do
    [ -z "$kode" ] && continue
    echo "INSERT INTO suplier
          (KODE_SUPLIER, NAMA_SUPLIER, ALAMAT_SUPLIER, KOTA_SUPLIER, TELEPON_SUPLIER)
          VALUES ($kode, $nama, $alamat, $kota, $telp)
          ON CONFLICT (KODE_SUPLIER) DO NOTHING;"
done | \
PGPASSWORD="$POSTGRES_PASSWORD" psql \
  -h "$POSTGRES_HOST" \
  -p "$POSTGRES_PORT" \
  -U "$POSTGRES_USER" \
  -d "$POSTGRES_DB"

log_message "Migration table suplier completed SUCCESSFULLY."

[root@teguhth-all maria-to-edb]#

7. script migrasi table customer 

[root@teguhth-all maria-to-edb]# pwd
/data/migrasi/maria-to-edb
[root@teguhth-all maria-to-edb]# cat migrasi_customer_mariadb_to_postgres.sh
#!/bin/bash
set -euo pipefail

# =========================
# SOURCE : MariaDB
# =========================
MARIADB_HOST="10.10.10.9"
MARIADB_PORT="3306"
MARIADB_DB="teguhth"
MARIADB_USER="admin"
MARIADB_PASSWORD="admin"

# =========================
# DESTINATION : PostgreSQL
# =========================
POSTGRES_HOST="10.10.10.90"
POSTGRES_PORT="5432"
POSTGRES_DB="mariaedb"
POSTGRES_USER="admin"
POSTGRES_PASSWORD="admin"

rm -f /data/migrasi/maria-to-edb/log_maria_to_edb_customer.log

LOG_FILE="/data/migrasi/maria-to-edb/log_maria_to_edb_customer.log"

log_message() {
    echo "[$(date '+%Y-%m-%d %H:%M:%S')] $1" | tee -a "$LOG_FILE"
}

log_message "Starting migration CUSTOMER MariaDB -> PostgreSQL..."

# =========================
# Ensure database exists
# =========================
log_message "Ensure database mariaedb exists..."

PGPASSWORD="$POSTGRES_PASSWORD" psql \
    -h "$POSTGRES_HOST" \
    -p "$POSTGRES_PORT" \
    -U "$POSTGRES_USER" \
    -d postgres <<EOF
DO \$\$
BEGIN
   IF NOT EXISTS (
      SELECT 1 FROM pg_database WHERE datname = '$POSTGRES_DB'
   ) THEN
      CREATE DATABASE $POSTGRES_DB;
   END IF;
END
\$\$;
EOF

# =========================
# Ensure table exists
# =========================
log_message "Ensure table customer exists in PostgreSQL..."
log_message "truncate table customer"

PGPASSWORD="$POSTGRES_PASSWORD" psql \
    -h "$POSTGRES_HOST" \
    -p "$POSTGRES_PORT" \
    -U "$POSTGRES_USER" \
    -d "$POSTGRES_DB" <<EOF
CREATE TABLE IF NOT EXISTS customer (
    KODE_CUSTOMER CHAR(6) NOT NULL,
    NAMA_CUSTOMER VARCHAR(30),
    ALAMAT_CUSTOMER VARCHAR(30),
    KOTA_CUSTOMER VARCHAR(15),
    TELEPON_CUSTOMER VARCHAR(15),
    PRIMARY KEY (KODE_CUSTOMER)
);
truncate table customer;
EOF

# =========================
# Migrate data
# =========================
log_message "Migrating data from MariaDB table customer..."

 /usr/bin/mariadb \
    --skip-ssl \
    -h "$MARIADB_HOST" \
    -P "$MARIADB_PORT" \
    -u "$MARIADB_USER" \
    -p"$MARIADB_PASSWORD" \
    "$MARIADB_DB" \
    -B -N -e "
    SELECT
        QUOTE(KODE_CUSTOMER),
        QUOTE(NAMA_CUSTOMER),
        QUOTE(ALAMAT_CUSTOMER),
        QUOTE(KOTA_CUSTOMER),
        QUOTE(TELEPON_CUSTOMER)
    FROM customer
    ORDER BY KODE_CUSTOMER;
    " | \
while IFS=$'\t' read -r kode nama alamat kota telepon; do
    [ -z "$kode" ] && continue
    echo "INSERT INTO customer
          (KODE_CUSTOMER, NAMA_CUSTOMER, ALAMAT_CUSTOMER, KOTA_CUSTOMER, TELEPON_CUSTOMER)
          VALUES ($kode, $nama, $alamat, $kota, $telepon)
          ON CONFLICT (KODE_CUSTOMER) DO NOTHING;"
done | \
PGPASSWORD="$POSTGRES_PASSWORD" psql \
    -h "$POSTGRES_HOST" \
    -p "$POSTGRES_PORT" \
    -U "$POSTGRES_USER" \
    -d "$POSTGRES_DB"

log_message "Migration CUSTOMER completed SUCCESSFULLY."

[root@teguhth-all maria-to-edb]#

8. script migrasi table pasok 

[root@teguhth-all maria-to-edb]# pwd
/data/migrasi/maria-to-edb
[root@teguhth-all maria-to-edb]# cat migrasi_pasok_mariadb_to_postgres.sh
#!/bin/bash
set -euo pipefail

# =========================
# SOURCE : MariaDB
# =========================
MARIADB_HOST="10.10.10.9"
MARIADB_PORT="3306"
MARIADB_DB="teguhth"
MARIADB_USER="admin"
MARIADB_PASSWORD="admin"

# =========================
# DESTINATION : PostgreSQL
# =========================
POSTGRES_HOST="10.10.10.90"
POSTGRES_PORT="5432"
POSTGRES_DB="mariaedb"
POSTGRES_USER="admin"
POSTGRES_PASSWORD="admin"

rm -f /data/migrasi/maria-to-edb/log_maria_to_edb_pasok.log

LOG_FILE="/data/migrasi/maria-to-edb/log_maria_to_edb_pasok.log"

log_message() {
    echo "[$(date '+%Y-%m-%d %H:%M:%S')] $1" | tee -a "$LOG_FILE"
}

log_message "Starting migration PASOK MariaDB -> PostgreSQL..."

# =========================
# Ensure database exists
# =========================
log_message "Ensure database mariaedb exists..."

PGPASSWORD="$POSTGRES_PASSWORD" psql \
    -h "$POSTGRES_HOST" \
    -p "$POSTGRES_PORT" \
    -U "$POSTGRES_USER" \
    -d postgres <<EOF
DO \$\$
BEGIN
   IF NOT EXISTS (
      SELECT 1 FROM pg_database WHERE datname = '$POSTGRES_DB'
   ) THEN
      CREATE DATABASE $POSTGRES_DB;
   END IF;
END
\$\$;
EOF

# =========================
# Ensure table exists
# =========================
log_message "Ensure table pasok exists in PostgreSQL..."
log_message "Truncate table pasok"

PGPASSWORD="$POSTGRES_PASSWORD" psql \
    -h "$POSTGRES_HOST" \
    -p "$POSTGRES_PORT" \
    -U "$POSTGRES_USER" \
    -d "$POSTGRES_DB" <<EOF
CREATE TABLE IF NOT EXISTS pasok (
    KODE_PASOK CHAR(10),
    KODE_BARANG CHAR(6),
    KODE_SUPLIER CHAR(5),
    TANGGAL_PASOK DATE,
    JUMLAH_PASOK DECIMAL(4),
    PRIMARY KEY (KODE_PASOK, KODE_BARANG, KODE_SUPLIER),
    FOREIGN KEY (KODE_BARANG) REFERENCES barang(KODE_BARANG),
    FOREIGN KEY (KODE_SUPLIER) REFERENCES suplier(KODE_SUPLIER)
);
truncate table pasok;

EOF

# =========================
# Migrate data
# =========================
log_message "Migrating data from MariaDB table pasok..."

 /usr/bin/mariadb \
    --skip-ssl \
    -h "$MARIADB_HOST" \
    -P "$MARIADB_PORT" \
    -u "$MARIADB_USER" \
    -p"$MARIADB_PASSWORD" \
    "$MARIADB_DB" \
    -B -N -e "
    SELECT
        QUOTE(KODE_PASOK),
        QUOTE(KODE_BARANG),
        QUOTE(KODE_SUPLIER),
        IFNULL(QUOTE(TANGGAL_PASOK), 'NULL'),
        JUMLAH_PASOK
    FROM pasok
    ORDER BY KODE_PASOK, KODE_BARANG, KODE_SUPLIER;
    " | \
while IFS=$'\t' read -r kode_pasok kode_barang kode_suplier tanggal jumlah; do
    [ -z "$kode_pasok" ] && continue
    echo "INSERT INTO pasok
          (KODE_PASOK, KODE_BARANG, KODE_SUPLIER, TANGGAL_PASOK, JUMLAH_PASOK)
          VALUES ($kode_pasok, $kode_barang, $kode_suplier, $tanggal, $jumlah)
          ON CONFLICT (KODE_PASOK, KODE_BARANG, KODE_SUPLIER) DO NOTHING;"
done | \
PGPASSWORD="$POSTGRES_PASSWORD" psql \
    -h "$POSTGRES_HOST" \
    -p "$POSTGRES_PORT" \
    -U "$POSTGRES_USER" \
    -d "$POSTGRES_DB"

log_message "Migration PASOK completed SUCCESSFULLY."

[root@teguhth-all maria-to-edb]#

9. script migrasi table pembelian 

[root@teguhth-all maria-to-edb]# pwd
/data/migrasi/maria-to-edb
[root@teguhth-all maria-to-edb]# cat migrasi_pembelian_mariadb_to_postgres.sh
#!/bin/bash
set -euo pipefail

# =========================
# SOURCE : MariaDB
# =========================
MARIADB_HOST="10.10.10.9"
MARIADB_PORT="3306"
MARIADB_DB="teguhth"
MARIADB_USER="admin"
MARIADB_PASSWORD="admin"

# =========================
# DESTINATION : PostgreSQL
# =========================
POSTGRES_HOST="10.10.10.90"
POSTGRES_PORT="5432"
POSTGRES_DB="mariaedb"
POSTGRES_USER="admin"
POSTGRES_PASSWORD="admin"

rm -f /data/migrasi/maria-to-edb/log_maria_to_edb_pembelian.log

LOG_FILE="/data/migrasi/maria-to-edb/log_maria_to_edb_pembelian.log"

log_message() {
    echo "[$(date '+%Y-%m-%d %H:%M:%S')] $1" | tee -a "$LOG_FILE"
}

log_message "Starting migration PEMBELIAN MariaDB -> PostgreSQL..."

# =========================
# Ensure database exists
# =========================
log_message "Ensure database mariaedb exists..."

PGPASSWORD="$POSTGRES_PASSWORD" psql \
    -h "$POSTGRES_HOST" \
    -p "$POSTGRES_PORT" \
    -U "$POSTGRES_USER" \
    -d postgres <<EOF
DO \$\$
BEGIN
   IF NOT EXISTS (
      SELECT 1 FROM pg_database WHERE datname = '$POSTGRES_DB'
   ) THEN
      CREATE DATABASE $POSTGRES_DB;
   END IF;
END
\$\$;
EOF

# =========================
# Ensure table exists
# =========================
log_message "Ensure table pembelian exists in PostgreSQL..."
log_message "Truncate table pembelian"

PGPASSWORD="$POSTGRES_PASSWORD" psql \
    -h "$POSTGRES_HOST" \
    -p "$POSTGRES_PORT" \
    -U "$POSTGRES_USER" \
    -d "$POSTGRES_DB" <<EOF
CREATE TABLE IF NOT EXISTS pembelian (
    KODE_PEMBELIAN CHAR(10),
    KODE_BARANG CHAR(6),
    KODE_CUSTOMER CHAR(6),
    TANGGAL_PEMBELIAN DATE,
    JUMLAH_PEMBELIAN DECIMAL(4),
    PRIMARY KEY (KODE_PEMBELIAN, KODE_BARANG, KODE_CUSTOMER),
    FOREIGN KEY (KODE_BARANG) REFERENCES barang(KODE_BARANG),
    FOREIGN KEY (KODE_CUSTOMER) REFERENCES customer(KODE_CUSTOMER)
);
truncate table pembelian;
EOF

# =========================
# Migrate data
# =========================
log_message "Migrating data from MariaDB table pembelian..."

 /usr/bin/mariadb \
    --skip-ssl \
    -h "$MARIADB_HOST" \
    -P "$MARIADB_PORT" \
    -u "$MARIADB_USER" \
    -p"$MARIADB_PASSWORD" \
    "$MARIADB_DB" \
    -B -N -e "
    SELECT
        QUOTE(KODE_PEMBELIAN),
        QUOTE(KODE_BARANG),
        QUOTE(KODE_CUSTOMER),
        IFNULL(QUOTE(TANGGAL_PEMBELIAN), 'NULL'),
        JUMLAH_PEMBELIAN
    FROM pembelian
    ORDER BY KODE_PEMBELIAN, KODE_BARANG, KODE_CUSTOMER;
    " | \
while IFS=$'\t' read -r kode_pembelian kode_barang kode_customer tanggal jumlah; do
    [ -z "$kode_pembelian" ] && continue
    echo "INSERT INTO pembelian
          (KODE_PEMBELIAN, KODE_BARANG, KODE_CUSTOMER, TANGGAL_PEMBELIAN, JUMLAH_PEMBELIAN)
          VALUES ($kode_pembelian, $kode_barang, $kode_customer, $tanggal, $jumlah)
          ON CONFLICT (KODE_PEMBELIAN, KODE_BARANG, KODE_CUSTOMER) DO NOTHING;"
done | \
PGPASSWORD="$POSTGRES_PASSWORD" psql \
    -h "$POSTGRES_HOST" \
    -p "$POSTGRES_PORT" \
    -U "$POSTGRES_USER" \
    -d "$POSTGRES_DB"

log_message "Migration PEMBELIAN completed SUCCESSFULLY."

[root@teguhth-all maria-to-edb]#