.::: create Tools Migration from PostgreSQL to MariaDB using bash shell script :::.

  

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 Postgres
ip : 10.10.10.9
db : teguhth
port 5432
user : admin
password : admin

server B Mariadb
ip : 10.10.10.90
db : teguhthedb
port 3306
user : admin
password : admin

buat script shell untuk migrasi dari server A postgres to server B
 

3. run script migrasi 

sh migrasi_barang_edb_to_mariadb.sh
sh migrasi_customer_edb_to_mariadb.sh
sh migrasi_suplier_edb_to_mariadb.sh
sh migrasi_pasok_edb_to_mariadb.sh
sh migrasi_pembelian_edb_to_mariadb.sh
 

4. result after migrasi 
 

5. script migrasi table barang 
[root@teguhth-all edb]# pwd
/data/migrasi/edb
[root@teguhth-all edb]# cat migrasi_barang_edb_to_mariadb.sh
#!/bin/bash

POSTGRES_HOST="10.10.10.9"
POSTGRES_PORT="5432"
POSTGRES_DB="teguhth"
POSTGRES_USER="admin"
POSTGRES_PASSWORD="admin"

MARIADB_HOST="10.10.10.90"
MARIADB_PORT="3306"
MARIADB_DB="teguhthedb"
MARIADB_USER="admin"
MARIADB_PASSWORD="admin"

LOG_FILE="/tmp/migration_$(date +%Y%m%d_%H%M%S).log"

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

log_message "Starting migration..."

log_message "Ensure table barang exists..."

cat <<EOF | /usr/bin/mariadb \
    -h "$MARIADB_HOST" \
    -P "$MARIADB_PORT" \
    -u "$MARIADB_USER" \
    -p"$MARIADB_PASSWORD" \
    "$MARIADB_DB"

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)
);
EOF

PGPASSWORD="$POSTGRES_PASSWORD" psql \
    -h "$POSTGRES_HOST" \
    -p "$POSTGRES_PORT" \
    -U "$POSTGRES_USER" \
    -d "$POSTGRES_DB" \
    -A -t -F"," \
    -c "
    SELECT
        quote_literal(KODE_BARANG),
        quote_literal(NAMA_BARANG),
        quote_literal(SATUAN_BARANG),
        STOK_BARANG
    FROM barang
    ORDER BY KODE_BARANG;
    " | \
while IFS= read -r line; do
    [ -z "$line" ] && continue
    echo "INSERT INTO barang (KODE_BARANG, NAMA_BARANG, SATUAN_BARANG, STOK_BARANG) VALUES ($line);"
done | \
/usr/bin/mariadb \
    -h "$MARIADB_HOST" \
    -P "$MARIADB_PORT" \
    -u "$MARIADB_USER" \
    -p"$MARIADB_PASSWORD" \
    "$MARIADB_DB"

log_message "Migration process completed"

[root@teguhth-all edb]#

6. script migrasi table suplier 

[root@teguhth-all edb]# pwd
/data/migrasi/edb
[root@teguhth-all edb]# cat migrasi_suplier_edb_to_mariadb.sh
#!/bin/bash

POSTGRES_HOST="10.10.10.9"
POSTGRES_PORT="5432"
POSTGRES_DB="teguhth"
POSTGRES_USER="admin"
POSTGRES_PASSWORD="admin"

MARIADB_HOST="10.10.10.90"
MARIADB_PORT="3306"
MARIADB_DB="teguhthedb"
MARIADB_USER="admin"
MARIADB_PASSWORD="admin"

LOG_FILE="/tmp/migration_$(date +%Y%m%d_%H%M%S).log"

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


log_message "Migrating table suplier..."
log_message "Ensure table suplier exists..."

cat <<EOF | /usr/bin/mariadb \
    -h "$MARIADB_HOST" \
    -P "$MARIADB_PORT" \
    -u "$MARIADB_USER" \
    -p"$MARIADB_PASSWORD" \
    "$MARIADB_DB"

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)
);
EOF

PGPASSWORD="$POSTGRES_PASSWORD" psql \
    -h "$POSTGRES_HOST" \
    -p "$POSTGRES_PORT" \
    -U "$POSTGRES_USER" \
    -d "$POSTGRES_DB" \
    -A -t -F"," \
    -c "
    SELECT
        quote_literal(KODE_SUPLIER),
        quote_literal(NAMA_SUPLIER),
        quote_literal(ALAMAT_SUPLIER),
        quote_literal(KOTA_SUPLIER),
        quote_literal(TELEPON_SUPLIER)
    FROM suplier
    ORDER BY KODE_SUPLIER;
    " | \
while IFS= read -r line; do
    [ -z "$line" ] && continue
    echo "INSERT INTO suplier
          (KODE_SUPLIER, NAMA_SUPLIER, ALAMAT_SUPLIER, KOTA_SUPLIER, TELEPON_SUPLIER)
          VALUES ($line);"
done | \
/usr/bin/mariadb \
    -h "$MARIADB_HOST" \
    -P "$MARIADB_PORT" \
    -u "$MARIADB_USER" \
    -p"$MARIADB_PASSWORD" \
    "$MARIADB_DB"

log_message "Migration table suplier completed"

[root@teguhth-all edb]#

7. script migrasi table customer 

[root@teguhth-all edb]# pwd
/data/migrasi/edb
[root@teguhth-all edb]# cat migrasi_customer_edb_to_mariadb.sh
#!/bin/bash

POSTGRES_HOST="10.10.10.9"
POSTGRES_PORT="5432"
POSTGRES_DB="teguhth"
POSTGRES_USER="admin"
POSTGRES_PASSWORD="admin"

MARIADB_HOST="10.10.10.90"
MARIADB_PORT="3306"
MARIADB_DB="teguhthedb"
MARIADB_USER="admin"
MARIADB_PASSWORD="admin"

LOG_FILE="/tmp/migration_customer_$(date +%Y%m%d_%H%M%S).log"

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

log_message "Ensure table customer exists..."

cat <<EOF | /usr/bin/mariadb \
    -h "$MARIADB_HOST" \
    -P "$MARIADB_PORT" \
    -u "$MARIADB_USER" \
    -p"$MARIADB_PASSWORD" \
    "$MARIADB_DB"

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)
);
EOF

log_message "Migrating table customer..."

PGPASSWORD="$POSTGRES_PASSWORD" psql \
    -h "$POSTGRES_HOST" \
    -p "$POSTGRES_PORT" \
    -U "$POSTGRES_USER" \
    -d "$POSTGRES_DB" \
    -A -t -F"," \
    -c "
    SELECT
        quote_literal(KODE_CUSTOMER),
        quote_literal(NAMA_CUSTOMER),
        quote_literal(ALAMAT_CUSTOMER),
        quote_literal(KOTA_CUSTOMER),
        quote_literal(TELEPON_CUSTOMER)
    FROM customer
    ORDER BY KODE_CUSTOMER;
    " | \
while IFS= read -r line; do
    [ -z "$line" ] && continue
    echo "INSERT INTO customer
          (KODE_CUSTOMER, NAMA_CUSTOMER, ALAMAT_CUSTOMER, KOTA_CUSTOMER, TELEPON_CUSTOMER)
          VALUES ($line);"
done | \
/usr/bin/mariadb \
    -h "$MARIADB_HOST" \
    -P "$MARIADB_PORT" \
    -u "$MARIADB_USER" \
    -p"$MARIADB_PASSWORD" \
    "$MARIADB_DB"

log_message "Migration table customer completed"

[root@teguhth-all edb]#

8. script migrasi table pasok
[root@teguhth-all edb]# pwd
/data/migrasi/edb
[root@teguhth-all edb]# cat migrasi_pasok_edb_to_mariadb.sh
#!/bin/bash

POSTGRES_HOST="10.10.10.9"
POSTGRES_PORT="5432"
POSTGRES_DB="teguhth"
POSTGRES_USER="admin"
POSTGRES_PASSWORD="admin"

MARIADB_HOST="10.10.10.90"
MARIADB_PORT="3306"
MARIADB_DB="teguhthedb"
MARIADB_USER="admin"
MARIADB_PASSWORD="admin"

LOG_FILE="/tmp/migration_pasok_$(date +%Y%m%d_%H%M%S).log"

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

log_message "Ensure table pasok exists..."

cat <<EOF | /usr/bin/mariadb \
    -h "$MARIADB_HOST" \
    -P "$MARIADB_PORT" \
    -u "$MARIADB_USER" \
    -p"$MARIADB_PASSWORD" \
    "$MARIADB_DB"

CREATE TABLE IF NOT EXISTS pasok (
    KODE_PASOK CHAR(10) NOT NULL,
    KODE_BARANG CHAR(6) NOT NULL,
    KODE_SUPLIER CHAR(5) NOT NULL,
    TANGGAL_PASOK DATE,
    JUMLAH_PASOK DECIMAL(4),
    PRIMARY KEY (KODE_PASOK, KODE_BARANG, KODE_SUPLIER),
    CONSTRAINT fk_pasok_barang
        FOREIGN KEY (KODE_BARANG) REFERENCES barang(KODE_BARANG),
    CONSTRAINT fk_pasok_suplier
        FOREIGN KEY (KODE_SUPLIER) REFERENCES suplier(KODE_SUPLIER)
);
EOF

log_message "Migrating table pasok..."

PGPASSWORD="$POSTGRES_PASSWORD" psql \
    -h "$POSTGRES_HOST" \
    -p "$POSTGRES_PORT" \
    -U "$POSTGRES_USER" \
    -d "$POSTGRES_DB" \
    -A -t -F"," \
    -c "
    SELECT
        quote_literal(KODE_PASOK),
        quote_literal(KODE_BARANG),
        quote_literal(KODE_SUPLIER),
        quote_literal(TANGGAL_PASOK),
        JUMLAH_PASOK
    FROM pasok
    ORDER BY KODE_PASOK;
    " | \
while IFS= read -r line; do
    [ -z "$line" ] && continue
    echo "INSERT INTO pasok
          (KODE_PASOK, KODE_BARANG, KODE_SUPLIER, TANGGAL_PASOK, JUMLAH_PASOK)
          VALUES ($line);"
done | \
/usr/bin/mariadb \
    -h "$MARIADB_HOST" \
    -P "$MARIADB_PORT" \
    -u "$MARIADB_USER" \
    -p"$MARIADB_PASSWORD" \
    "$MARIADB_DB"

log_message "Migration table pasok completed"

[root@teguhth-all edb]#

9. script migrasi table pembelian

[root@teguhth-all edb]# pwd
/data/migrasi/edb
[root@teguhth-all edb]# cat migrasi_pembelian_edb_to_mariadb.sh
#!/bin/bash

POSTGRES_HOST="10.10.10.9"
POSTGRES_PORT="5432"
POSTGRES_DB="teguhth"
POSTGRES_USER="admin"
POSTGRES_PASSWORD="admin"

MARIADB_HOST="10.10.10.90"
MARIADB_PORT="3306"
MARIADB_DB="teguhthedb"
MARIADB_USER="admin"
MARIADB_PASSWORD="admin"

LOG_FILE="/tmp/migration_pembelian_$(date +%Y%m%d_%H%M%S).log"

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

log_message "Ensure table pembelian exists..."

cat <<EOF | /usr/bin/mariadb \
    -h "$MARIADB_HOST" \
    -P "$MARIADB_PORT" \
    -u "$MARIADB_USER" \
    -p"$MARIADB_PASSWORD" \
    "$MARIADB_DB"

CREATE TABLE IF NOT EXISTS pembelian (
    KODE_PEMBELIAN CHAR(10) NOT NULL,
    KODE_BARANG CHAR(6) NOT NULL,
    KODE_CUSTOMER CHAR(6) NOT NULL,
    TANGGAL_PEMBELIAN DATE,
    JUMLAH_PEMBELIAN DECIMAL(4),
    PRIMARY KEY (KODE_PEMBELIAN, KODE_BARANG, KODE_CUSTOMER),
    CONSTRAINT fk_pembelian_barang
        FOREIGN KEY (KODE_BARANG) REFERENCES barang(KODE_BARANG),
    CONSTRAINT fk_pembelian_customer
        FOREIGN KEY (KODE_CUSTOMER) REFERENCES customer(KODE_CUSTOMER)
);
EOF

log_message "Migrating table pembelian..."

PGPASSWORD="$POSTGRES_PASSWORD" psql \
    -h "$POSTGRES_HOST" \
    -p "$POSTGRES_PORT" \
    -U "$POSTGRES_USER" \
    -d "$POSTGRES_DB" \
    -A -t -F"," \
    -c "
    SELECT
        quote_literal(KODE_PEMBELIAN),
        quote_literal(KODE_BARANG),
        quote_literal(KODE_CUSTOMER),
        quote_literal(TANGGAL_PEMBELIAN),
        JUMLAH_PEMBELIAN
    FROM pembelian
    ORDER BY KODE_PEMBELIAN;
    " | \
while IFS= read -r line; do
    [ -z "$line" ] && continue
    echo "INSERT INTO pembelian
          (KODE_PEMBELIAN, KODE_BARANG, KODE_CUSTOMER, TANGGAL_PEMBELIAN, JUMLAH_PEMBELIAN)
          VALUES ($line);"
done | \
/usr/bin/mariadb \
    -h "$MARIADB_HOST" \
    -P "$MARIADB_PORT" \
    -u "$MARIADB_USER" \
    -p"$MARIADB_PASSWORD" \
    "$MARIADB_DB"

log_message "Migration table pembelian completed"

[root@teguhth-all edb]#