PostgreSQL Database Version 13.4 To MySQL Database Version 8.0.20 Migration by using SQLines Tool

PostgreSQL to MySQL
------------------------

This Document will give details about PostgreSQL to MySQL Database .

Environment Details :-

---------------------------

Server 1 == Source Database == PostgreSQL Database Version == 13.4

Server 2 == Target Database == MySQL Database Version == 8.0.20

Server 3 == Jump host == Where you will install Sqline and connect to Source DB and Target DB.

I have consider 3 EC2 Machines from AWS [ RHEL 8.x ]

postgres-source-server == 172.31.30.141 [ Private Ipaddress ] = EC2 Machine = RHEL 8.4

mysql-target-server == 172.31.17.136 [ Private Ipaddress ] = EC2 Machine = RHEL 8.4

jump-host == 172.31.27.241 [ Private Ipaddress ] = EC2 Machine = RHEL 8.4

Step - 1 Install PostgreSQL Database Version in Source Server

Step - 2 Install MySQL Database Verion in Target Server

Step - 3 Install / Setup sqline s/w in Jump host server

Step - 4 Loading sample data in Source Database i.e PostgreSQL Database

Step - 5 Create Super User / Remote user for PostgreSQL and MySQL Database Server

Step - 6 Moving / Generating DDL's of PostgreSQL Through AWS Schema Conversion Tool / MySQL Workbench Tool

Step - 7 Moving / Generating DDL's of PostgreSQL Through Full converter Tool / MySQL Workbench Tool and Workbench Issues and Solutions

Step - 8 Final DDL and Schema definations gathered from Workbench Tool

Step - 9 Take a backup of DDL's for all Databases in PostgresSQL [ For safer side ]

Step - 10 Most Imp Queries while doing Migration from PostgreSQL to MySQL

Step - 11 Configure client packages for PostgreSQL and MySQL to connect from jump host to Source and Target DB Servers

Step - 12 Prepare configuration files for PostgreSQL and MySQL Databases from Sqlines configuration files in Jump host

Step - 13 Prepare the Row count script for Source Database and Target Database in jump host [ PostgreSQL --> MySQL ]

Step - 14 Prepare the Truncate tables for Target Database [ MySQL ]

Step - 15 Run the Create Schema / Create DDL Objectives [ Tables , Views , SP , Trigger , Functions ..etc in Target Database [ MySQL ]

Step - 16 Start the Data Migration from PostgreSQL to MySQL Database by using SQline Tool

Step - 17 Stored Procedure for Tables Row count in MySQL Database

Step - 18 Final Validation of Rows count of all tables and objectives between PostgreSQL and MySQL Databases

----------------------------------------------------------------

Step - 1 Install PostgreSQL Database Version in Source Server

----------------------------------------------------------------

https://www.postgresql.org/download/linux/redhat/

(i) Install postgres Database version 13.4 based on above link in Source PostgreSQL Server

[root@postgres-source-server ~]# hostname

postgres-source-server

[root@postgres-source-server ~]# hostname -I

172.31.30.141

[root@postgres-source-server ~]# cat /etc/redhat-release

Red Hat Enterprise Linux release 8.4 (Ootpa)

[root@postgres-source-server ~]# sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm

[root@postgres-source-server ~]# sudo dnf -qy module disable postgresql

[root@postgres-source-server ~]# sudo dnf install -y postgresql13-server

[root@postgres-source-server ~]# sudo /usr/pgsql-13/bin/postgresql-13-setup initdb

Initializing database ... OK

[root@postgres-source-server ~]# sudo systemctl enable postgresql-13

Created symlink /etc/systemd/system/multi-user.target.wants/postgresql-13.service → /usr/lib/systemd/system/postgresql-13.service.

[root@postgres-source-server ~]# sudo systemctl start postgresql-13

[root@postgres-source-server ~]# sudo systemctl status postgresql-13

● postgresql-13.service - PostgreSQL 13 database server

Loaded: loaded (/usr/lib/systemd/system/postgresql-13.service; enabled; vendor preset: disabled)

Active: active (running) since Fri 2021-08-27 00:54:56 UTC; 6s ago

Docs: https://www.postgresql.org/docs/13/static/

Process: 56297 ExecStartPre=/usr/pgsql-13/bin/postgresql-13-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)

Main PID: 56302 (postmaster)

Tasks: 8 (limit: 11272)

Memory: 16.8M

CGroup: /system.slice/postgresql-13.service

├─56302 /usr/pgsql-13/bin/postmaster -D /var/lib/pgsql/13/data/

├─56304 postgres: logger

├─56306 postgres: checkpointer

├─56307 postgres: background writer

├─56308 postgres: walwriter

├─56309 postgres: autovacuum launcher

├─56310 postgres: stats collector

└─56311 postgres: logical replication launcher

Aug 27 00:54:56 postgres-source-server systemd[1]: Starting PostgreSQL 13 database server...

Aug 27 00:54:56 postgres-source-server postmaster[56302]: 2021-08-27 00:54:56.652 UTC [56302] LOG: redirecting log output to logging collector process

Aug 27 00:54:56 postgres-source-server postmaster[56302]: 2021-08-27 00:54:56.652 UTC [56302] HINT: Future log output will appear in directory "log".

Aug 27 00:54:56 postgres-source-server systemd[1]: Started PostgreSQL 13 database server.

[root@postgres-source-server ~]# ps -ef | grep -i postgres

postgres 56302 1 0 00:54 ? 00:00:00 /usr/pgsql-13/bin/postmaster -D /var/lib/pgsql/13/data/

postgres 56304 56302 0 00:54 ? 00:00:00 postgres: logger

postgres 56306 56302 0 00:54 ? 00:00:00 postgres: checkpointer

postgres 56307 56302 0 00:54 ? 00:00:00 postgres: background writer

postgres 56308 56302 0 00:54 ? 00:00:00 postgres: walwriter

postgres 56309 56302 0 00:54 ? 00:00:00 postgres: autovacuum launcher

postgres 56310 56302 0 00:54 ? 00:00:00 postgres: stats collector

postgres 56311 56302 0 00:54 ? 00:00:00 postgres: logical replication launcher

root 56370 1324 0 00:58 pts/0 00:00:00 grep --color=auto -i postgres

[root@postgres-source-server ~]# cd /var/lib/pgsql/13/data/

[root@postgres-source-server data]# pwd

/var/lib/pgsql/13/data

[root@postgres-source-server data]# ls -ltr

drwx------. 2 postgres postgres 6 Aug 27 00:54 pg_notify

drwx------. 2 postgres postgres 6 Aug 27 00:54 pg_dynshmem

drwx------. 2 postgres postgres 6 Aug 27 00:54 pg_commit_ts

-rw-------. 1 postgres postgres 3 Aug 27 00:54 PG_VERSION

drwx------. 2 postgres postgres 6 Aug 27 00:54 pg_twophase

drwx------. 2 postgres postgres 6 Aug 27 00:54 pg_tblspc

drwx------. 2 postgres postgres 6 Aug 27 00:54 pg_stat

drwx------. 2 postgres postgres 6 Aug 27 00:54 pg_snapshots

drwx------. 2 postgres postgres 6 Aug 27 00:54 pg_serial

drwx------. 2 postgres postgres 6 Aug 27 00:54 pg_replslot

drwx------. 4 postgres postgres 36 Aug 27 00:54 pg_multixact

-rw-------. 1 postgres postgres 28080 Aug 27 00:54 postgresql.conf

-rw-------. 1 postgres postgres 88 Aug 27 00:54 postgresql.auto.conf

-rw-------. 1 postgres postgres 1636 Aug 27 00:54 pg_ident.conf

-rw-------. 1 postgres postgres 4548 Aug 27 00:54 pg_hba.conf

drwx------. 2 postgres postgres 18 Aug 27 00:54 pg_xact

drwx------. 3 postgres postgres 60 Aug 27 00:54 pg_wal

drwx------. 2 postgres postgres 18 Aug 27 00:54 pg_subtrans

drwx------. 2 postgres postgres 4096 Aug 27 00:54 global

drwx------. 5 postgres postgres 41 Aug 27 00:54 base

drwx------. 4 postgres postgres 68 Aug 27 00:54 pg_logical

drwx------. 2 postgres postgres 32 Aug 27 00:54 log

-rw-------. 1 postgres postgres 30 Aug 27 00:54 current_logfiles

-rw-------. 1 postgres postgres 58 Aug 27 00:54 postmaster.opts

-rw-------. 1 postgres postgres 104 Aug 27 00:54 postmaster.pid

drwx------. 2 postgres postgres 25 Aug 27 00:57 pg_stat_tmp

[root@postgres-source-server data]#

[root@postgres-source-server ~]# rpm -qa | grep -i postgres

postgresql13-13.4-1PGDG.rhel8.x86_64

postgresql13-libs-13.4-1PGDG.rhel8.x86_64

postgresql13-server-13.4-1PGDG.rhel8.x86_64

[root@postgres-source-server ~]# su - postgres

[postgres@postgres-source-server ~]$ psql

psql (13.4)

Type "help" for help.

postgres=# select version();

version

-------------------------------------------------------------------------------------

PostgreSQL 13.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.4.1 20200928 (Red Hat 8.4.1-1), 64-bit

(1 row)

postgres=# \l

List of databases

-----------+----------+----------+-------------+-------------+-----------------------

(3 rows)

postgres=# select usename from pg_user;

usename

----------

postgres

(1 row)

postgres=#\q

Instalation is done .......

-----------------------------------------------------------

Step - 2 Install MySQL Database Verion in Target Server

-----------------------------------------------------------

(i) By using YUM installation , we are going to install MySQL Database version 8.0.20

https://repo.mysql.com//mysql80-community-release-el8-1.noarch.rpm

[root@mysql-target-server ~]# hostname -I

172.31.17.136

[root@mysql-target-server ~]# hostname

mysql-target-server

[root@mysql-target-server ~]# cat /etc/redhat-release

Red Hat Enterprise Linux release 8.4 (Ootpa)

[root@mysql-target-server ~]# sudo yum install https://repo.mysql.com//mysql80-community-release-el8-1.noarch.rpm

[root@mysql-target-server ~]# yum repolist enabled | grep "mysql.*-community.*"

[root@mysql-target-server ~]# yum repolist all | grep mysql

[root@mysql-target-server ~]# sudo yum-config-manager --disable mysql57-community

[root@mysql-target-server ~]# sudo yum-config-manager --enable mysql80-community

[root@mysql-target-server ~]# cd /etc/yum.repos.d/

[root@mysql-target-server yum.repos.d]# pwd

/etc/yum.repos.d

[root@mysql-target-server yum.repos.d]# ls -ltr

-rw-r--r--. 1 root root 1050 Oct 4 2019 mysql-community-source.repo

-rw-r--r--. 1 root root 4782 Aug 18 11:07 redhat-rhui-beta.repo.disabled

-rw-r--r--. 1 root root 5768 Aug 27 01:09 redhat-rhui.repo

-rw-r--r--. 1 root root 482 Aug 27 01:09 redhat-rhui-client-config.repo

-rw-r--r--. 1 root root 995 Aug 27 01:14 mysql-community.repo

[root@mysql-target-server yum.repos.d]# sudo yum module disable mysql

[root@mysql-target-server yum.repos.d]# sudo yum install mysql-community-server

[root@mysql-target-server yum.repos.d]# cd

[root@mysql-target-server ~]# systemctl start mysqld

[root@mysql-target-server ~]# systemctl status mysqld

● mysqld.service - MySQL Server

Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)

Active: active (running) since Fri 2021-08-27 01:17:16 UTC; 3s ago

Docs: man:mysqld(8)

http://dev.mysql.com/doc/refman/en/using-systemd.html

Process: 58339 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)

Main PID: 58410 (mysqld)

Status: "Server is operational"

Tasks: 38 (limit: 11272)

Memory: 444.8M

CGroup: /system.slice/mysqld.service

└─58410 /usr/sbin/mysqld

Aug 27 01:17:06 mysql-target-server systemd[1]: Starting MySQL Server...

Aug 27 01:17:16 mysql-target-server systemd[1]: Started MySQL Server.

[root@mysql-target-server ~]# sudo grep 'temporary password' /var/log/mysqld.log

2021-08-27T01:17:11.247580Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: srg1;1FM1k=)

[root@mysql-target-server ~]# mysql -uroot -p

Enter password: srg1;1FM1k=)

Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 8 Server version: 8.0.26 .Copyright (c) 2000, 2021, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'root';

ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'Root@123';

Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;

Query OK, 0 rows affected (0.00 sec)

mysql> exit

Bye

[root@mysql-target-server ~]# mysql -uroot -p

Enter password: Root@123

Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 9.Server version: 8.0.26 MySQL Community Server - GPL Copyright (c) 2000, 2021, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> status;

--------------

mysql Ver 8.0.26 for Linux on x86_64 (MySQL Community Server - GPL)

Connection id: 9

Current database:

Current user: root@localhost

SSL: Not in use

Current pager: stdout

Using outfile: ''

Using delimiter: ;

Server version: 8.0.26 MySQL Community Server - GPL

Protocol version: 10

Connection: Localhost via UNIX socket

Server characterset: utf8mb4

Db characterset: utf8mb4

Client characterset: utf8mb4

Conn. characterset: utf8mb4

UNIX socket: /var/lib/mysql/mysql.sock

Binary data as: Hexadecimal

Uptime: 1 min 21 sec

Threads: 2 Questions: 10 Slow queries: 0 Opens: 131 Flush tables: 3 Open tables: 47 Queries per second avg: 0.123

--------------

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| performance_schema |

| sys |

+--------------------+

4 rows in set (0.00 sec)

mysql> select user,host,authentication_string from mysql.user;

+------------------+-----------+-------------------------------------

| user | host | authentication_string |

+------------------+-----------+-------------------------------------

| mysql.infoschema | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |

| mysql.session | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |

| mysql.sys | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |

DG*pwbcdktmeDwPnE/qCRaKsqiq8mjwoEWU2Gs4OQhD6zkV. |

+------------------+-----------+-------------------------------------

4 rows in set (0.00 sec)

mysql> select user,host from mysql.user;

+------------------+-----------+

| user | host |

+------------------+-----------+

| mysql.infoschema | localhost |

| mysql.session | localhost |

| mysql.sys | localhost |

| root | localhost |

+------------------+-----------+

4 rows in set (0.00 sec)

mysql>exit

Installation is done.........

----------------------------------------------------------------

Step - 3 Install / Setup sqline s/w in Jump host server

----------------------------------------------------------------

Download the s/w from the below link. You can choose any version .But here i am using 3.1.773 S/w.

https://www.sqlines.com/download

[root@jump-host ~]# hostname -I

172.31.27.241

[root@jump-host ~]# hostname

jump-host

[root@jump-host ~]# cat /etc/redhat-release

Red Hat Enterprise Linux release 8.4 (Ootpa)

[root@jump-host ec2-user]# pwd

/home/ec2-user

[root@jump-host ec2-user]# ls -ltr

-rw-rw-r--. 1 ec2-user ec2-user 6634087 Mar 14 2019 sqlinesdata31773_x86_64_linux.tar.gz

-rw-r--r--. 1 root root 16524 Aug 27 01:46 sqlinesdata-3.1.773_x86_64_linux.tar

[root@jump-host ec2-user]# chown root:root sqlinesdata31773_x86_64_linux.tar.gz

[root@jump-host ec2-user]# ls -ltr

-rw-rw-r--. 1 root root 6634087 Mar 14 2019 sqlinesdata31773_x86_64_linux.tar.gz

-rw-r--r--. 1 root root 16524 Aug 27 01:46 sqlinesdata-3.1.773_x86_64_linux.tar

[root@jump-host ec2-user]# ls -ltr

-rw-rw-r--. 1 root root 6634087 Mar 14 2019 sqlinesdata31773_x86_64_linux.tar.gz

-rw-r--r--. 1 root root 16524 Aug 27 01:46 sqlinesdata-3.1.773_x86_64_linux.tar

[root@jump-host ec2-user]# gunzip sqlinesdata31773_x86_64_linux.tar.gz

[root@jump-host ec2-user]# ls -ltr

-rw-rw-r--. 1 root root 15872000 Mar 14 2019 sqlinesdata31773_x86_64_linux.tar

-rw-r--r--. 1 root root 16524 Aug 27 01:46 sqlinesdata-3.1.773_x86_64_linux.tar

[root@jump-host ec2-user]# tar -xvf sqlinesdata31773_x86_64_linux.tar

sqlinesdata31773_x86_64_linux/

sqlinesdata31773_x86_64_linux/sqldata

sqlinesdata31773_x86_64_linux/sqlines_dtmap.txt

sqlinesdata31773_x86_64_linux/sqlines_qf.txt

sqlinesdata31773_x86_64_linux/libQtCore.so

sqlinesdata31773_x86_64_linux/sqlines_tmap.txt

sqlinesdata31773_x86_64_linux/readme.txt

sqlinesdata31773_x86_64_linux/libQtGui.so

sqlinesdata31773_x86_64_linux/sqlines_cmap.txt

sqlinesdata31773_x86_64_linux/sqlines_cnsmap.txt

sqlinesdata31773_x86_64_linux/sqlines_twhere.txt

sqlinesdata31773_x86_64_linux/sqlines_tsel.txt

sqlinesdata31773_x86_64_linux/sqldata.cfg

sqlinesdata31773_x86_64_linux/license.txt

sqlinesdata31773_x86_64_linux/sqlines_tsel_all.txt

sqlinesdata31773_x86_64_linux/sqlines_tf.txt

[root@jump-host ec2-user]# ls -ltr

drwxr-xr-x. 2 root root 4096 Feb 8 2018 sqlinesdata31773_x86_64_linux

-rw-rw-r--. 1 root root 15872000 Mar 14 2019 sqlinesdata31773_x86_64_linux.tar

-rw-r--r--. 1 root root 16524 Aug 27 01:46 sqlinesdata-3.1.773_x86_64_linux.tar

[root@jump-host sqlinesdata31773_x86_64_linux]# pwd

/home/ec2-user/sqlinesdata31773_x86_64_linux

[root@jump-host sqlinesdata31773_x86_64_linux]# ./sqldata --version

SQLines Data 3.1.773 x86_64 Linux - Database Migration Tool.

[root@jump-host sqlinesdata31773_x86_64_linux]#

Sqline installation is done ....

Imp Note :-

--------------

In my case sqlinesdata31773 Version having some issue, so i was using with sqlinesdata-3.3.171_x86_64_linux.tar.gz version and try to load the data.

Here is the installation steps ..

[root@jump-host ec2-user]# pwd

/home/ec2-user

[root@jump-host ec2-user]# wget https://www.sqlines.com/downloads/sqlinesdata-3.3.171_x86_64_linux.tar.gz

--2021-09-06 00:45:10-- https://www.sqlines.com/downloads/sqlinesdata-3.3.171_x86_64_linux.tar.gz

Resolving www.sqlines.com (www.sqlines.com)... 173.201.209.135

Connecting to www.sqlines.com (www.sqlines.com)|173.201.209.135|:443... connected.

HTTP request sent, awaiting response... 200 OK

Length: 6802107 (6.5M) [application/x-tar]

Saving to: ‘sqlinesdata-3.3.171_x86_64_linux.tar.gz’

sqlinesdata-3.3.171_x86_64_linux.tar. 100%[==================================================================>] 6.49M 8.56MB/s in 0.8s

2021-09-06 00:45:11 (8.56 MB/s) - ‘sqlinesdata-3.3.171_x86_64_linux.tar.gz’ saved [6802107/6802107]

[root@jump-host ec2-user]# ls -ltr

-rw-rw-r--. 1 root root 15872000 Mar 14 2019 sqlinesdata31773_x86_64_linux.tar

-rw-r--r--. 1 root root 6790719 May 13 11:11 sqlinesdata-3.3.117_x86_64_linux.tar.gz

-rwxr-xr-x. 1 root root 2475 Aug 31 12:11 row_count_comparison.sh_sample_not_considered

-rwxr-xr-x. 1 root root 2963 Aug 31 16:45 row_count_comparison.sh_bkp_for_my_reference_not_considered

-rwxr-xr-x. 1 root root 1011 Sep 1 10:33 test.sh

-rwxr-xr-x. 1 root root 5206 Sep 1 10:34 row_count_comparison.sh_not_considered

-rw-r--r--. 1 root root 85 Sep 1 14:55 exp.sh

drwxr-xr-x. 2 root root 73 Sep 1 15:24 tmp_dir

drwxr-xr-x. 2 root root 125 Sep 1 15:30 output_dir

drwxrwxr-x. 5 ec2-user ec2-user 99 Sep 2 02:08 Final_Scripts

drwxr-xr-x. 8 root root 4096 Sep 3 01:21 sqlinesdata31773

drwxr-xr-x. 2 root root 4096 Sep 3 01:33 sqlinesdata-3.3.117_x86_64_linux

-rw-r--r--. 1 root root 6802107 Sep 5 20:30 sqlinesdata-3.3.171_x86_64_linux.tar.gz

[root@jump-host ec2-user]# tar -zxvf sqlinesdata-3.3.171_x86_64_linux.tar.gz

sqlinesdata-3.3.171_x86_64_linux/

sqlinesdata-3.3.171_x86_64_linux/sqldata

sqlinesdata-3.3.171_x86_64_linux/sqlines_dtmap.txt

sqlinesdata-3.3.171_x86_64_linux/sqlines_qf.txt

sqlinesdata-3.3.171_x86_64_linux/libQtCore.so

sqlinesdata-3.3.171_x86_64_linux/sqlines_tmap.txt

sqlinesdata-3.3.171_x86_64_linux/readme.txt

sqlinesdata-3.3.171_x86_64_linux/libQtGui.so

sqlinesdata-3.3.171_x86_64_linux/sqlines_cmap.txt

sqlinesdata-3.3.171_x86_64_linux/sqlines_cnsmap.txt

sqlinesdata-3.3.171_x86_64_linux/sqlines_twhere.txt

sqlinesdata-3.3.171_x86_64_linux/sqlines_tsel.txt

sqlinesdata-3.3.171_x86_64_linux/sqldata.cfg

sqlinesdata-3.3.171_x86_64_linux/license.txt

sqlinesdata-3.3.171_x86_64_linux/sqlines_tsel_all.txt

sqlinesdata-3.3.171_x86_64_linux/sqlines_tf.txt

[root@jump-host ec2-user]# pwd

/home/ec2-user

[root@jump-host ec2-user]# ls -ltr

-rw-rw-r--. 1 root root 15872000 Mar 14 2019 sqlinesdata31773_x86_64_linux.tar

-rw-r--r--. 1 root root 6790719 May 13 11:11 sqlinesdata-3.3.117_x86_64_linux.tar.gz

-rwxr-xr-x. 1 root root 2475 Aug 31 12:11 row_count_comparison.sh_sample_not_considered

-rwxr-xr-x. 1 root root 2963 Aug 31 16:45 row_count_comparison.sh_bkp_for_my_reference_not_considered

-rwxr-xr-x. 1 root root 1011 Sep 1 10:33 test.sh

-rwxr-xr-x. 1 root root 5206 Sep 1 10:34 row_count_comparison.sh_not_considered

-rw-r--r--. 1 root root 85 Sep 1 14:55 exp.sh

drwxr-xr-x. 2 root root 73 Sep 1 15:24 tmp_dir

drwxr-xr-x. 2 root root 125 Sep 1 15:30 output_dir

drwxrwxr-x. 5 ec2-user ec2-user 99 Sep 2 02:08 Final_Scripts

drwxr-xr-x. 8 root root 4096 Sep 3 01:21 sqlinesdata31773

drwxr-xr-x. 2 root root 4096 Sep 3 01:33 sqlinesdata-3.3.117_x86_64_linux

drwxr-xr-x. 2 root root 4096 Sep 5 20:27 sqlinesdata-3.3.171_x86_64_linux

-rw-r--r--. 1 root root 6802107 Sep 5 20:30 sqlinesdata-3.3.171_x86_64_linux.tar.gz

[root@jump-host ec2-user]# cd sqlinesdata-3.3.171_x86_64_linux

[root@jump-host sqlinesdata-3.3.171_x86_64_linux]# pwd

/home/ec2-user/sqlinesdata-3.3.171_x86_64_linux

[root@jump-host sqlinesdata-3.3.171_x86_64_linux]# ls -ltr

-rw-r--r--. 1 root root 7212 Aug 31 12:47 sqldata.cfg

-rw-r--r--. 1 root root 361 Sep 5 20:27 sqlines_twhere.txt

-rw-r--r--. 1 root root 579 Sep 5 20:27 sqlines_tsel.txt

-rw-r--r--. 1 root root 498 Sep 5 20:27 sqlines_tsel_all.txt

-rw-r--r--. 1 root root 192 Sep 5 20:27 sqlines_tmap.txt

-rw-r--r--. 1 root root 273 Sep 5 20:27 sqlines_tf.txt

-rw-r--r--. 1 root root 316 Sep 5 20:27 sqlines_qf.txt

-rw-r--r--. 1 root root 190 Sep 5 20:27 sqlines_dtmap.txt

-rw-r--r--. 1 root root 202 Sep 5 20:27 sqlines_cnsmap.txt

-rw-r--r--. 1 root root 401 Sep 5 20:27 sqlines_cmap.txt

-rw-r--r--. 1 root root 188 Sep 5 20:27 readme.txt

-rw-r--r--. 1 root root 118 Sep 5 20:27 license.txt

-rwxr-xr-x. 1 root root 11670344 Sep 5 20:27 libQtGui.so

-rwxr-xr-x. 1 root root 2701504 Sep 5 20:27 libQtCore.so

-rwxr-xr-x. 1 root root 2051152 Sep 5 20:29 sqldata

[root@jump-host sqlinesdata-3.3.171_x86_64_linux]# ./sqldata --version

SQLines Data 3.3.171 x86_64 Linux - Database Migration Tool.

---------------------------------------------------------------------

Step - 4 Loading sample data in Source Database i.e PostgreSQL Database

---------------------------------------------------------------------

Download the sample data from below link and keep in Postgres Server

https://www.postgresqltutorial.com/postgresql-sample-database/

https://www.postgresqltutorial.com/load-postgresql-sample-database/

The DVD rental database represents the business processes of a DVD rental store. The DVD rental database has many objects including:

15 tables

1 trigger

7 views

8 functions

1 domain

13 sequences

[root@postgres-source-server ~]# su - postgres

Last login: Fri Aug 27 00:59:08 UTC 2021 on pts/0

[postgres@postgres-source-server ~]$ psql -U postgres

psql (13.4)

Type "help" for help.

postgres=# \l

List of databases

-----------+----------+----------+-------------+-------------+-----------------------

(3 rows)

postgres=# CREATE DATABASE dvdrental;

CREATE DATABASE

postgres=# \l

List of databases

-----------+----------+----------+-------------+-------------+-----------------------

(4 rows)

postgres=# \q

[root@postgres-source-server ~]# cd /home/ec2-user/

[root@postgres-source-server ec2-user]# pwd

/home/ec2-user

[root@postgres-source-server ec2-user]# ls -ltr

-rw-rw-r--. 1 ec2-user ec2-user 2835456 May 12 2019 dvdrental.tar

[root@postgres-source-server ec2-user]# chown postgres:postgres dvdrental.tar

[root@postgres-source-server ec2-user]# pwd

/home/ec2-user

[root@postgres-source-server ec2-user]# ls -ltr

-rw-rw-r--. 1 postgres postgres 2835456 May 12 2019 dvdrental.tar

[postgres@postgres-source-server ~]$ cd /home/

[postgres@postgres-source-server home]$ ls -ltr

drwx------. 3 ec2-user ec2-user 116 Aug 27 02:03 ec2-user

[root@postgres-source-server ec2-user]# pwd

/home/ec2-user

[root@postgres-source-server ec2-user]# ls -ltr

-rw-rw-r--. 1 postgres postgres 2835456 May 12 2019 dvdrental.tar

[root@postgres-source-server ec2-user]# mv dvdrental.tar /home

[root@postgres-source-server ec2-user]# ls -ltr

[root@postgres-source-server ec2-user]# su - postgres

Last login: Fri Aug 27 02:07:19 UTC 2021 on pts/0

[postgres@postgres-source-server ~]$ pwd

/var/lib/pgsql

[postgres@postgres-source-server ~]$ cd /home/

[postgres@postgres-source-server home]$ pwd

/home

[postgres@postgres-source-server home]$ ls -ltr

-rw-rw-r--. 1 postgres postgres 2835456 May 12 2019 dvdrental.tar

drwx------. 3 ec2-user ec2-user 95 Aug 27 02:07 ec2-user

[postgres@postgres-source-server home]$ pg_restore -U postgres -d dvdrental dvdrental.tar

[postgres@postgres-source-server home]$ ls -ltr

-rw-rw-r--. 1 postgres postgres 2835456 May 12 2019 dvdrental.tar

drwx------. 3 ec2-user ec2-user 95 Aug 27 02:07 ec2-user

[postgres@postgres-source-server ~]$ psql -U postgres

psql (13.4)

Type "help" for help.

postgres=# \l

List of databases

-----------+----------+----------+-------------+-------------+-----------------------

(4 rows)

postgres=# \l+

List of databases

-----------+----------+----------+-------------+-------------+-----------------------

(4 rows)

postgres=# \c dvdrental

You are now connected to database "dvdrental" as user "postgres".

dvdrental=# \dt

List of relations

Schema | Name | Type | Owner

--------+---------------+-------+----------

public | actor | table | postgres

public | address | table | postgres

public | category | table | postgres

public | city | table | postgres

public | country | table | postgres

public | customer | table | postgres

public | film | table | postgres

public | film_actor | table | postgres

public | film_category | table | postgres

public | inventory | table | postgres

public | language | table | postgres

public | payment | table | postgres

public | rental | table | postgres

public | staff | table | postgres

public | store | table | postgres

(15 rows)

dvdrental=# select n.nspname as table_schema, c.relname as table_name, c.reltuples as rows from pg_class c join pg_namespace n on n.oid = c.relnamespace where c.relkind = 'r' and n.nspname not in ('information_schema','pg_catalog') order by c.reltuples desc;

table_schema | table_name | rows

--------------+---------------+-------

public | rental | 16044

public | payment | 14596

public | film_actor | 5462

public | inventory | 4581

public | film | 1000

public | film_category | 1000

public | address | 603

public | city | 600

public | customer | 599

public | actor | 200

public | country | 109

public | category | 16

public | language | 6

public | staff | 2

public | store | 2

(15 rows)

dvdrental=# select nsp.nspname as SchemaName,cls.relname as ObjectName,rol.rolname as ObjectOwner,case cls.relkind when 'r' then 'TABLE'

when 'm' then 'MATERIALIZED_VIEW'

when 'i' then 'INDEX'

when 'S' then 'SEQUENCE'

when 'v' then 'VIEW'

when 'c' then 'TYPE'

else cls.relkind::text

end as ObjectType from pg_class cls join pg_roles rol on rol.oid = cls.relowner join pg_namespace nsp on nsp.oid = cls.relnamespace where nsp.nspname not in ('information_schema', 'pg_catalog') and nsp.nspname not like 'pg_toast%' and rol.rolname = 'postgres'

order by ObjectType, 2;

schemaname | objectname | objectowner | objecttype

------------+-----------------------------------------------------+--

public | actor_pkey | postgres | INDEX

public | address_pkey | postgres | INDEX

public | category_pkey | postgres | INDEX

public | city_pkey | postgres | INDEX

public | country_pkey | postgres | INDEX

public | customer_pkey | postgres | INDEX

public | film_actor_pkey | postgres | INDEX

public | film_category_pkey | postgres | INDEX

public | film_fulltext_idx | postgres | INDEX

public | film_pkey | postgres | INDEX

public | idx_actor_last_name | postgres | INDEX

public | idx_fk_address_id | postgres | INDEX

public | idx_fk_city_id | postgres | INDEX

public | idx_fk_country_id | postgres | INDEX

public | idx_fk_customer_id | postgres | INDEX

public | idx_fk_film_id | postgres | INDEX

public | idx_fk_inventory_id | postgres | INDEX

public | idx_fk_language_id | postgres | INDEX

public | idx_fk_rental_id | postgres | INDEX

public | idx_fk_staff_id | postgres | INDEX

public | idx_fk_store_id | postgres | INDEX

public | idx_last_name | postgres | INDEX

public | idx_store_id_film_id | postgres | INDEX

public | idx_title | postgres | INDEX

public | idx_unq_manager_staff_id | postgres | INDEX

public | idx_unq_rental_rental_date_inventory_id_customer_id | postgres | INDEX

public | inventory_pkey | postgres | INDEX

public | language_pkey | postgres | INDEX

public | payment_pkey | postgres | INDEX

public | rental_pkey | postgres | INDEX

public | staff_pkey | postgres | INDEX

public | store_pkey | postgres | INDEX

public | actor_actor_id_seq | postgres | SEQUENCE

public | address_address_id_seq | postgres | SEQUENCE

public | category_category_id_seq | postgres | SEQUENCE

public | city_city_id_seq | postgres | SEQUENCE

public | country_country_id_seq | postgres | SEQUENCE

public | customer_customer_id_seq | postgres | SEQUENCE

public | film_film_id_seq | postgres | SEQUENCE

public | inventory_inventory_id_seq | postgres | SEQUENCE

public | language_language_id_seq | postgres | SEQUENCE

public | payment_payment_id_seq | postgres | SEQUENCE

public | rental_rental_id_seq | postgres | SEQUENCE

public | staff_staff_id_seq | postgres | SEQUENCE

public | store_store_id_seq | postgres | SEQUENCE

public | actor | postgres | TABLE

public | address | postgres | TABLE

public | category | postgres | TABLE

public | city | postgres | TABLE

public | country | postgres | TABLE

public | customer | postgres | TABLE

public | film | postgres | TABLE

public | film_actor | postgres | TABLE

public | film_category | postgres | TABLE

public | inventory | postgres | TABLE

public | language | postgres | TABLE

public | payment | postgres | TABLE

public | rental | postgres | TABLE

public | staff | postgres | TABLE

public | store | postgres | TABLE

public | actor_info | postgres | VIEW

public | customer_list | postgres | VIEW

public | film_list | postgres | VIEW

public | nicer_but_slower_film_list | postgres | VIEW

public | sales_by_film_category | postgres | VIEW

public | sales_by_store | postgres | VIEW

public | staff_list | postgres | VIEW

(67 rows)

dvdrental=# \dt

List of relations

Schema | Name | Type | Owner

--------+---------------+-------+----------

public | actor | table | postgres

public | address | table | postgres

public | category | table | postgres

public | city | table | postgres

public | country | table | postgres

public | customer | table | postgres

public | film | table | postgres

public | film_actor | table | postgres

public | film_category | table | postgres

public | inventory | table | postgres

public | language | table | postgres

public | payment | table | postgres

public | rental | table | postgres

public | staff | table | postgres

public | store | table | postgres

(15 rows)

dvdrental=# \dv

List of relations

Schema | Name | Type | Owner

--------+----------------------------+------+----------

public | actor_info | view | postgres

public | customer_list | view | postgres

public | film_list | view | postgres

public | nicer_but_slower_film_list | view | postgres

public | sales_by_film_category | view | postgres

public | sales_by_store | view | postgres

public | staff_list | view | postgres

(7 rows)

dvdrental=# \df

List of functions

Schema | Name | Result data type | Argument data types | Type

--------+----------------------------+------------------+------------

public | _group_concat | text | text, text | func

public | film_in_stock | SETOF integer | p_film_id integer, p_store_id integer, OUT p_film_count integer | func

public | film_not_in_stock | SETOF integer | p_film_id integer, p_store_id integer, OUT p_film_count integer | func

public | get_customer_balance | numeric | p_customer_id integer, p_effective_date timestamp without time zone | func

public | group_concat | text | text | agg

public | inventory_held_by_customer | integer | p_inventory_id integer | func

public | inventory_in_stock | boolean | p_inventory_id integer | func

public | last_day | date | timestamp without time zone | func

public | last_updated | trigger | | func

public | rewards_report | SETOF customer | min_monthly_purchases integer, min_dollar_amount_purchased numeric | func

(10 rows)

dvdrental=# \dD

List of domains

--------+------+---------+-----------+----------+---------+----------

(1 row)

dvdrental=# \ds

List of relations

Schema | Name | Type | Owner

--------+----------------------------+----------+----------

public | actor_actor_id_seq | sequence | postgres

public | address_address_id_seq | sequence | postgres

public | category_category_id_seq | sequence | postgres

public | city_city_id_seq | sequence | postgres

public | country_country_id_seq | sequence | postgres

public | customer_customer_id_seq | sequence | postgres

public | film_film_id_seq | sequence | postgres

public | inventory_inventory_id_seq | sequence | postgres

public | language_language_id_seq | sequence | postgres

public | payment_payment_id_seq | sequence | postgres

public | rental_rental_id_seq | sequence | postgres

public | staff_staff_id_seq | sequence | postgres

public | store_store_id_seq | sequence | postgres

(13 rows)

dvdrental=# \da

List of aggregate functions

Schema | Name | Result data type | Argument data types | Description

--------+--------------+------------------+---------------------+----

public | group_concat | text | text |

(1 row)

dvdrental=# select event_object_schema as table_schema,

event_object_table as table_name,

trigger_schema,

trigger_name,

string_agg(event_manipulation, ',') as event,

action_timing as activation,

action_condition as condition,

action_statement as definition

from information_schema.triggers

group by 1,2,3,4,6,7,8

order by table_schema,table_name;

definition

--------------+---------------+----------------+---------------------

tsvector_update_trigger('fulltext', 'pg_catalog.english', 'title', 'description')

(15 rows)

dvdrental=# \du

List of roles

Role name | Attributes | Member of

-----------+---------------------------------------------------------

postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

dvdrental=# select DISTINCT trigger_name,event_object_schema as table_schema ,event_object_table as table_name,trigger_schema from information_schema.triggers;

trigger_name | table_schema | table_name | trigger_schema

-----------------------+--------------+---------------+--------------

film_fulltext_trigger | public | film | public

last_updated | public | actor | public

last_updated | public | address | public

last_updated | public | category | public

last_updated | public | city | public

last_updated | public | country | public

last_updated | public | customer | public

last_updated | public | film | public

last_updated | public | film_actor | public

last_updated | public | film_category | public

last_updated | public | inventory | public

last_updated | public | language | public

last_updated | public | rental | public

last_updated | public | staff | public

last_updated | public | store | public

(15 rows)

dvdrental=# select DISTINCT trigger_name from information_schema.triggers;

trigger_name

-----------------------

film_fulltext_trigger

last_updated

(2 rows)

dvdrental=#\c postgres

postgres=# \l+

List of databases

-----------+----------+----------+-------------+-------------+-------

(4 rows)

postgres=# CREATE DATABASE migration_db;

CREATE DATABASE

postgres=# \l+

List of databases

--------------+----------+----------+-------------+-------------+----

(5 rows)

postgres=# \c migration_db

You are now connected to database "migration_db" as user "postgres".

migration_db=# CREATE TABLE users(id BIGINT GENERATED ALWAYS AS IDENTITY,PRIMARY KEY(id),hash_firstname TEXT NOT NULL,hash_lastname TEXT NOT NULL,gender VARCHAR(6) NOT NULL CHECK (gender IN ('male', 'female')));

CREATE TABLE

migration_db=# \dt+

List of relations

--------+-------+-------+----------+-------------+------------+------

(1 row)

migration_db=# INSERT INTO users(hash_firstname, hash_lastname, gender) SELECT md5(RANDOM()::TEXT), md5(RANDOM()::TEXT), CASE WHEN RANDOM() < 0.5 THEN 'male ' ELSE 'female' END FROM generate_series(1, 10000);

INSERT 0 10000

INSERT 0 100000

migration_db=# SELECT COUNT(*) FROM users;

count

--------

110000

(1 row)

migration_db=# SELECT COUNT(*) FROM users WHERE gender = 'male';

count

-------

54806

(1 row)

migration_db=# SELECT COUNT(*) FROM users WHERE gender = 'female';

count

-------

55194

(1 row)

migration_db=# SELECT * FROM users LIMIT 15;

id | hash_firstname | hash_lastname | gender

----+----------------------------------+-----------------------------

1 | 86e6a7508e8bbc09bbdaf4d05278d13d | ac907881016951bf5095bad5b6c6230c | male

2 | ce13312f916b291c74d8284b8e7c507e | d884663083d1566e7737c5e3a9557e58 | female

3 | f66fed9dc305c065a7550ad1bae216d5 | cdf904143c1f817b21726f04560a95d0 | female

4 | b25ab3fc57febec70aedc009d14f7961 | 5cb036ccefd5a47bae6e53139426b02e | female

5 | f7d4d0c7dd8e360ba73d5202159e1d4e | 9ea34f2313db581e749a72d72e40d286 | female

6 | 30bb6c7ac42ebe968badf9a3fa39d0fb | eb3261c1a8942320b7b222a88eec718d | male

7 | 903f082efaec509b56c43e9b4bee9053 | cb6e9d3be5a2a696dd4057dc5067e32a | female

8 | 9b6038e0380a63c66d5bf44dd63f09be | 888e4f5c925e45e9268b1616f8335938 | male

9 | ec0c541c02ef34786f73318ca05b9339 | 694e27dd2d04d8dc0041916adbb57b2f | male

10 | 24d36bd244260a96e67b826a4e5c9610 | ee86a576c05dfa06ee1a7a1f726272f6 | female

11 | 71397f30d1d2b0f590d0509db6d2fbfd | 4c3a9ed356dc2457db8dddfc9cbb7635 | female

12 | 176d6bd244eee16dd4e53707026fb26f | 189f7de8641acd36e76cb9e9499a516d | male

13 | b51f4cd03d5ec0191bcf562510d1ad4b | 7eac5237163daef2b19e6af58cbca109 | male

14 | 20f7d7a8d826790ca66365509ab8d6de | a6ae9ffdc540728d699c2e9f0395334d | female

15 | e3171ebb42a3f2af97ed342de24d6564 | 7d345b5782bc67bdcfd62b18e9bf05d5 | female

(15 rows)

migration_db=# \dt+

List of relations

--------+-------+-------+----------+-------------+-------+-----------

(1 row)

migration_db=# CREATE TABLE employee (id int8 NOT NULL,name varchar(120) NOT NULL,salary int8 NOT NULL,CONSTRAINT emp_pk PRIMARY KEY (id));

CREATE TABLE

migration_db=# \dt

List of relations

Schema | Name | Type | Owner

--------+----------+-------+----------

public | employee | table | postgres

public | users | table | postgres

(2 rows)

migration_db=# WITH salary_list AS ( SELECT '{1000, 2000, 5000}'::INT[] salary) INSERT INTO public.employee (id, name, salary) SELECT n, 'Employee ' || n as name, salary[1 + mod(n, array_length(salary, 1))] FROM salary_list, generate_series(1, 1000000) as n ;

INSERT 0 1000000

migration_db=# select count(*) from employee;

count

---------

1000000

(1 row)

migration_db=# select * from employee LIMIT 15;

id | name | salary

----+-------------+--------

1 | Employee 1 | 2000

2 | Employee 2 | 5000

3 | Employee 3 | 1000

4 | Employee 4 | 2000

5 | Employee 5 | 5000

6 | Employee 6 | 1000

7 | Employee 7 | 2000

8 | Employee 8 | 5000

9 | Employee 9 | 1000

10 | Employee 10 | 2000

11 | Employee 11 | 5000

12 | Employee 12 | 1000

13 | Employee 13 | 2000

14 | Employee 14 | 5000

15 | Employee 15 | 1000

(15 rows)

migration_db=# \c postgres

You are now connected to database "postgres" as user "postgres".

postgres=# CREATE DATABASE test_db;

CREATE DATABASE

postgres=# \l+

List of databases

--------------+----------+----------+-------------+-------------+----

(6 rows)

postgres=# \c test_db

You are now connected to database "test_db" as user "postgres".

test_db=# CREATE TABLE users(id SERIAL PRIMARY KEY,email VARCHAR(40) NOT NULL UNIQUE);

CREATE TABLE

test_db=# CREATE TABLE posts(id SERIAL PRIMARY KEY,user_id INTEGER NOT NULL REFERENCES users(id),title VARCHAR(100) NOT NULL UNIQUE);

CREATE TABLE

test_db=# CREATE TABLE comments(id SERIAL PRIMARY KEY,user_id INTEGER NOT NULL REFERENCES users(id),post_id INTEGER NOT NULL REFERENCES posts(id),body VARCHAR(500) NOT NULL);

CREATE TABLE

test_db=# \dt

List of relations

Schema | Name | Type | Owner

--------+----------+-------+----------

public | comments | table | postgres

public | posts | table | postgres

public | users | table | postgres

(3 rows)

test_db=# INSERT INTO users(email)

SELECT

'user_' || seq || '@' || (

CASE (RANDOM() * 2)::INT

WHEN 0 THEN 'gmail'

WHEN 1 THEN 'hotmail'

WHEN 2 THEN 'yahoo'

END

) || '.com' AS email

FROM GENERATE_SERIES(1, 100000) seq;

INSERT 0 100000

test_db=# select count(*) from users;

count

--------

100000

(1 row)

test_db=#INSERT INTO posts(user_id, title)

WITH expanded AS (

SELECT RANDOM(), seq, u.id AS user_id

FROM GENERATE_SERIES(1, 50) seq, users u

), shuffled AS (

SELECT e.*

FROM expanded e

INNER JOIN (

SELECT ei.seq, MIN(ei.random) FROM expanded ei GROUP BY ei.seq

) em ON (e.seq = em.seq AND e.random = em.min)

ORDER BY e.seq

)

SELECT

s.user_id,

'It is ' || s.seq || ' ' || (

CASE (RANDOM() * 2)::INT

WHEN 0 THEN 'sql'

WHEN 1 THEN 'elixir'

WHEN 2 THEN 'ruby'

END

) as title

FROM shuffled s;

INSERT 0 50

test_db=# SELECT * FROM posts LIMIT 10;

id | user_id | title

----+---------+----------------

1 | 90224 | It is 1 elixir

2 | 90272 | It is 2 sql

3 | 48796 | It is 3 elixir

4 | 25106 | It is 4 sql

5 | 9129 | It is 5 sql

6 | 47149 | It is 6 elixir

7 | 12251 | It is 7 sql

8 | 8887 | It is 8 elixir

9 | 60728 | It is 9 ruby

10 | 14985 | It is 10 sql

(10 rows)

test_db=# INSERT INTO comments(user_id, post_id, body)

WITH expanded AS (

SELECT RANDOM(), seq, u.id AS user_id, p.id AS post_id

FROM GENERATE_SERIES(1, 10) seq, users u, posts p

), shuffled AS (

SELECT e.*

FROM expanded e

INNER JOIN (

SELECT ei.seq, MIN(ei.random) FROM expanded ei GROUP BY ei.seq

) em ON (e.seq = em.seq AND e.random = em.min)

ORDER BY e.seq

)

SELECThuffled s;ment ' || s.seq AS body

s.user_id,

s.post_id,

'Here some comment ' || s.seq AS body

FROM shuffled s;

INSERT 0 10

test_db=# SELECT * FROM comments LIMIT 10;

id | user_id | post_id | body

----+---------+---------+----------------------

1 | 51609 | 41 | Here some comment 1

2 | 77322 | 34 | Here some comment 2

3 | 52112 | 17 | Here some comment 3

4 | 54972 | 32 | Here some comment 4

5 | 65922 | 17 | Here some comment 5

6 | 48016 | 37 | Here some comment 6

7 | 63634 | 15 | Here some comment 7

8 | 3617 | 8 | Here some comment 8

9 | 25895 | 6 | Here some comment 9

10 | 78573 | 41 | Here some comment 10

(10 rows)

test_db=# \dt

List of relations

Schema | Name | Type | Owner

--------+----------+-------+----------

public | comments | table | postgres

public | genres | table | postgres

public | posts | table | postgres

public | users | table | postgres

(4 rows)

test_db=# CREATE TABLE kids_name (

id INT PRIMARY KEY,

name VARCHAR

);

CREATE TABLE

test_db=# INSERT INTO kids_name (id, name) VALUES (1, 'raizel');

INSERT 0 1

test_db=# INSERT INTO kids_name (id, name) VALUES (11, 'yeshua'), (2, 'abishai'),(3, 'abiel'),(4, 'dj'),(5, 'ann');

INSERT 0 5

test_db=# CREATE TABLE IF NOT EXISTS kids_audit (

id SERIAL PRIMARY KEY,

kids_id INT,

modified_on TIMESTAMP(6) NOT NULL,

kids_name CHARACTER VARYING(100)

);

CREATE TABLE

test_db=# CREATE OR REPLACE FUNCTION name_changes_log()

RETURNS TRIGGER AS

$BODY$

BEGIN

IF NEW.name <> OLD.name THEN

INSERT INTO kids_audit(kids_id,kids_name,modified_on)

VALUES(OLD.id,OLD.name,now());

END IF;

RETURN NEW;

END;

$BODY$ LANGUAGE plpgsql;

CREATE FUNCTION

test_db=# CREATE TRIGGER name_last_modified

BEFORE UPDATE

ON kids_name

FOR EACH ROW

EXECUTE PROCEDURE name_changes_log();

CREATE TRIGGER

test_db=# UPDATE kids_name SET name = 'Josh' WHERE ID = 5;

UPDATE 1

test_db=# select * from kids_audit;

id | kids_id | modified_on | kids_name

----+---------+----------------------------+-----------

1 | 5 | 2021-08-27 06:15:51.543493 | ann

(1 row)

test_db=# UPDATE kids_name SET name = 'vasu' WHERE ID = 5;

UPDATE 1

test_db=# select * from kids_audit;

id | kids_id | modified_on | kids_name

----+---------+----------------------------+-----------

1 | 5 | 2021-08-27 06:15:51.543493 | ann

2 | 5 | 2021-08-27 06:18:51.488951 | Josh

(2 rows)

test_db=# select * from kids_name;

id | name

----+---------

1 | raizel

11 | yeshua

2 | abishai

3 | abiel

4 | dj

5 | vasu

(6 rows)

postgres=# CREATE DATABASE kidsdb;

CREATE DATABASE

postgres=# \c kidsdb

You are now connected to database "kidsdb" as user "postgres".

kidsdb=# CREATE TABLE Employees

(

Id serial,

Name VARCHAR(100),

DateOfBirth Date,

City VARCHAR(100),

Designation VARCHAR(100),

JoiningDate Date

) ;

CREATE TABLE

kidsdb=# CREATE OR REPLACE PROCEDURE AddEmployee

(

EmpId INOUT INT,

EmpName VARCHAR(100),

EmpDob DATE,

EmpCity VARCHAR(100),

EmpDesignation VARCHAR(100),

EmpJoiningDate DATE

)

LANGUAGE plpgsql AS

BEGIN

INSERT INTO Employees (Name,DateOfBirth,City,Designation,JoiningDate) VALUES

(EmpName,

EmpDob,

EmpCity,

EmpDesignation,

EmpJoiningDate

) RETURNING Id INTO EmpId;

END

$$;

CREATE PROCEDURE

kidsdb=# CALL AddEmployee(null,'Peter Parker','1997-10-01','New York' ,'Web Developer','2020-11-01');

empid

-------

(1 row)

kidsdb=# SELECT * FROM Employees;

----+--------------+-------------+----------+---------------+--------

1 | Peter Parker | 1997-10-01 | New York | Web Developer | 2020-11-01

(1 row)

kidsdb=# CREATE OR REPLACE PROCEDURE UpdateEmployee

(

EmpId INT,

EmpName VARCHAR(100),

EmpDob DATE,

EmpCity VARCHAR(100),

EmpDesignation VARCHAR(100),

EmpJoiningDate DATE

)

LANGUAGE plpgsql AS

BEGIN

UPDATE Employees SET

Name = EmpName,

DateOfBirth = EmpDob,

City = EmpCity,

Designation = EmpDesignation,

JoiningDate = EmpJoiningDate

Where Id = EmpId;

END

$$;

CREATE PROCEDURE

kidsdb=# CALL UpdateEmployee(1,'Peter S Parker','1999-10-01','New York' ,'Web Developer','2020-11-01') ;

CALL

kidsdb=# CREATE OR REPLACE PROCEDURE DeleteEmployee

(

EmpId INT

)

LANGUAGE plpgsql AS

BEGIN

DELETE FROM Employees WHERE Id = EmpId;

END

$$;

CREATE PROCEDURE

kidsdb=# CALL DeleteEmployee(2) ;

CALL

kidsdb=# CREATE OR REPLACE FUNCTION GetAllEmployees()

RETURNS Employees

LANGUAGE SQL

SELECT * FROM Employees;

$$;

CREATE FUNCTION

kidsdb=# SELECT * FROM GetAllEmployees() ;

----+----------------+-------------+----------+---------------+------

1 | Peter S Parker | 1999-10-01 | New York | Web Developer | 2020-11-01

(1 row)

kidsdb=# CREATE OR REPLACE FUNCTION GetEmployeeById(EmpId INT)

RETURNS Employees

LANGUAGE SQL

SELECT * FROM Employees WHERE Id = EmpId;

$$;

CREATE FUNCTION

kidsdb=# SELECT * FROM GetAllEmployees();

----+----------------+-------------+----------+---------------+------

1 | Peter S Parker | 1999-10-01 | New York | Web Developer | 2020-11-01

(1 row)

kidsdb=# SELECT * FROM GetEmployeeById(1) ;

----+----------------+-------------+----------+---------------+------

1 | Peter S Parker | 1999-10-01 | New York | Web Developer | 2020-11-01

(1 row)

kidsdb=# CREATE OR REPLACE FUNCTION GetEmployeeAge(EmpId INT, Age OUT VARCHAR(100))

LANGUAGE plpgsql

BEGIN

SELECT AGE(NOW()::Date,DateOfBirth) into Age FROM Employees WHERE Id = EmpId;

END;

$$ ;

CREATE FUNCTION

kidsdb=# SELECT * FROM GetEmployeeAge(1);

age

--------------------------

21 years 10 mons 26 days

(1 row)

kidsdb=# \dt

List of relations

Schema | Name | Type | Owner

--------+-----------+-------+----------

public | employees | table | postgres

(1 row)

kidsdb=#\c postgres

postgres=# CREATE DATABASE mrsdb;

CREATE DATABASE

postgres=# \l+

List of databases

--------------+----------+----------+-------------+-------------+----

(8 rows)

postgres=# CREATE USER mrs1_pg with password 'mrs1_pg';

CREATE ROLE

postgres=# GRANT ALL PRIVILEGES ON DATABASE mrsdb to mrs1_pg;

GRANT

postgres=# \du

List of roles

Role name | Attributes | Member of

--------------+------------------------------------------------------

migrate_user | Superuser | {}

mrs1_pg | | {}

postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

postgres=#

[root@postgres-source-server ~]# su - postgres

Last login: Fri Aug 27 10:20:59 UTC 2021 on pts/0

[postgres@postgres-source-server ~]$ cd /home/

[postgres@postgres-source-server home]$ ls -ltr

-rw-------. 1 postgres postgres 263 May 12 2019 3073.dat

-rwxrwxrwx. 1 postgres postgres 2835456 May 12 2019 dvdrental.tar

-rw-rw-r--. 1 postgres postgres 118410 Aug 20 15:52 Capture.PNG

-rw-rw-r--. 1 postgres postgres 470550 Aug 22 01:43 explain_analyze.PNG

-rw-rw-r--. 1 postgres postgres 319167 Aug 22 01:44 explain.PNG

-rw-rw-r--. 1 postgres postgres 31093580 Aug 27 10:18 mrsdb.sql

drwx------. 3 ec2-user ec2-user 95 Aug 27 10:21 ec2-user

[postgres@postgres-source-server home]$ psql -U postgres -d mrsdb -f mrsdb.sql

SET

...

ALTER TABLE

[postgres@postgres-source-server home]$

[postgres@postgres-source-server home]$ psql -U postgres

psql (13.4)

Type "help" for help.

postgres=# CREATE DATABASE blob;

CREATE DATABASE

postgres=# \c blob

You are now connected to database "blob" as user "postgres".

blob=# create table blob_table(id int, image bytea);

CREATE TABLE

blob=# \dt

List of relations

Schema | Name | Type | Owner

--------+------------+-------+----------

public | blob_table | table | postgres

(1 row)

blob=# \d+ blob_table

Table "public.blob_table"

--------+---------+-----------+----------+---------+----------+------

Access method: heap

blob=# create or replace function bytea_import(p_path text, p_result out bytea)

language plpgsql as $$

declare

l_oid oid;

r record;

begin

p_result := '';

select lo_import(p_path) into l_oid;

for r in ( select data

from pg_largeobject

where loid = l_oid

order by pageno ) loop

p_result = p_result || r.data;

end loop;

perform lo_unlink(l_oid);

end;$$;

CREATE FUNCTION

blob=# \d blob_table

Table "public.blob_table"

Column | Type | Collation | Nullable | Default

--------+---------+-----------+----------+---------

id | integer | | |

image | bytea | | |

blob=# insert into blob_table(image) select bytea_import('/home/Capture.PNG');

INSERT 0 1

blob=# insert into blob_table(image) select bytea_import('/home/explain.PNG');

INSERT 0 1

blob=# insert into blob_table(image) select bytea_import('/home/explain_analyze.PNG');

INSERT 0 1

blob=# select count(1) from blob_table;

count

-------

(1 row)

blob=# \d+

List of relations

--------+------------+-------+----------+-------------+--------+-----

(1 row)

blob=# \d+ blob_table

Table "public.blob_table"

--------+---------+-----------+----------+---------+----------+------

Access method: heap

I have images like this

[postgres@postgres-source-server home]$ pwd

/home

[postgres@postgres-source-server home]$ ls -lr

-rw-rw-r--. 1 postgres postgres 319167 Aug 22 01:44 explain.PNG

-rw-rw-r--. 1 postgres postgres 470550 Aug 22 01:43 explain_analyze.PNG

-rw-rw-r--. 1 postgres postgres 118410 Aug 20 15:52 Capture.PNG

---------------------------------------------------------------------

Step - 5 Create Super User / Remote user for PostgreSQL and MySQL Database Server

---------------------------------------------------------------------

Adiitional information about users creation in PostgreSQL and MySQL:-

---------------------------------------------------------------------

PostgreSQL :-

-------------

Create the AWS DMS user or any user to connect to your target database, and grant Superuser or the necessary individual privileges (or use the master username for RDS).

CREATE USER postgresql_dms_user WITH PASSWORD 'password';

ALTER USER postgresql_dms_user WITH SUPERUSER;

Create a user for AWS SCT.

Privileges for PostgreSQL as a source database The privileges required for PostgreSQL as a source are listed following:

CONNECT ON DATABASE <database_name>

USAGE ON SCHEMA <database_name>

SELECT ON ALL TABLES IN SCHEMA <database_name>

SELECT ON ALL SEQUENCES IN SCHEMA <database_name>

MySQL :-

---------

CREATE USER 'migration_user'@'%' IDENTIFIED BY 'Root@123';

GRANT ALTER, CREATE, DROP, INDEX, INSERT, UPDATE, DELETE,SELECT ON *.* TO 'migration_user'@'%';

GRANT ALL PRIVILEGES ON *.* TO 'migration_user'@'%';

flush privileges;

In PostgreSQL :-

----------------------

[postgres@postgres-source-server ~]$ psql -U postgres

psql (13.4)

Type "help" for help.

postgres=# \du

List of roles

Role name | Attributes | Member of

-----------+---------------------------------------------------------

postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

postgres=# select usename from pg_user;

usename

----------

postgres

(1 row)

postgres=# CREATE ROLE migrate_user WITH LOGIN SUPERUSER PASSWORD 'migrate_user';

CREATE ROLE

postgres=# select usename from pg_user;

usename

--------------

postgres

migrate_user

(2 rows)

postgres=# \du

List of roles

Role name | Attributes | Member of

--------------+------------------------------------------------------

migrate_user | Superuser | {}

postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

In MySQL :-

--------------

mysql> select user,host,authentication_string from mysql.user;

+------------------+-----------+-------------------------------------

| user | host | authentication_string |

+------------------+-----------+-------------------------------------

| mysql.infoschema | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |

| mysql.session | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |

| mysql.sys | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |

| root | localhost | DG*pwbcdktmeDwPnE/qCRaKsqiq8mjwoEWU2Gs4OQhD6zkV. |

+------------------+-----------+-------------------------------------

4 rows in set (0.00 sec)

mysql> CREATE user 'root'@'%' IDENTIFIED BY 'Root@123';

Query OK, 0 rows affected (0.01 sec)

mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;

Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;

Query OK, 0 rows affected (0.00 sec)

mysql> select user,host,authentication_string from mysql.user;

+------------------+-----------+-------------------------------------

| user | host | authentication_string |

+------------------+-----------+-------------------------------------

| root | % | $A$005$;AZ*{"Ktco(YxeTfLv06bgc3IjI3FswaPnSgI6v.wEOxxUFgL9QeMcN8W4S. |

| mysql.infoschema | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |

| mysql.session | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |

| mysql.sys | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |

| root | localhost | DG*pwbcdktmeDwPnE/qCRaKsqiq8mjwoEWU2Gs4OQhD6zkV. |

+------------------+-----------+-------------------------------------

5 rows in set (0.00 sec)

mysql> CREATE user 'migrate_user'@'%' IDENTIFIED BY 'Migrate@123';

Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL PRIVILEGES ON *.* TO 'migrate_user'@'%' WITH GRANT OPTION;

Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;

Query OK, 0 rows affected (0.00 sec)

mysql> select user,host,authentication_string from mysql.user;

+------------------+-----------+-------------------------------------

| user | host | authentication_string |

+------------------+-----------+-------------------------------------

| root | % | $A$005$;AZ*{"Ktco(YxeTfLv06bgc3IjI3FswaPnSgI6v.wEOxxUFgL9QeMcN8W4S. |

| mysql.infoschema | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |

| mysql.session | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |

| mysql.sys | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |

| root | localhost | DG*pwbcdktmeDwPnE/qCRaKsqiq8mjwoEWU2Gs4OQhD6zkV. |

+------------------+-----------+-------------------------------------

6 rows in set (0.00 sec)

Success and Final for user Creation :-

-------------------------------------------

Final User creation in Postgresql :-

---------------------------------------

postgres=# select usename from pg_user;

usename

----------

mrs1_pg

postgres

(2 rows)

postgres=# \du+

List of roles

Role name | Attributes | Member of | Description

-----------+---------------------------------------------------------

mrs1_pg | | {} |

postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {} |

postgres=# \du

List of roles

Role name | Attributes | Member of

-----------+---------------------------------------------------------

mrs1_pg | | {}

postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

postgres=# \l

List of databases

--------------+----------+----------+-------------+-------------+----

(9 rows)

postgres=# CREATE USER migration_user WITH PASSWORD 'migration_user';

CREATE ROLE

postgres=# GRANT CONNECT ON DATABASE mrsdb TO migration_user;

GRANT

postgres=# GRANT USAGE ON SCHEMA public TO migration_user;

GRANT

postgres=# GRANT SELECT ON ALL TABLES IN SCHEMA public TO migration_user;

GRANT

postgres=# GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO migration_user;

GRANT

In MySQL :-

----------------

mysql> select user,host from mysql.user;

+------------------+-----------+

| user | host |

+------------------+-----------+

| root | % |

| mysql.infoschema | localhost |

| mysql.session | localhost |

| mysql.sys | localhost |

| root | localhost |

+------------------+-----------+

5 rows in set (0.00 sec)

mysql> CREATE USER 'migration_user'@'%' IDENTIFIED BY 'Root@123';

Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALTER, CREATE, DROP, INDEX, INSERT, UPDATE, DELETE,SELECT ON *.* TO 'migration_user'@'%';

Query OK, 0 rows affected (0.01 sec)

mysql> GRANT ALL PRIVILEGES ON *.* TO 'migration_user'@'%';

Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;

Query OK, 0 rows affected (0.00 sec)

mysql> select user,host from mysql.user;

+------------------+-----------+

| user | host |

+------------------+-----------+

| migration_user | % |

| root | % |

| mysql.infoschema | localhost |

| mysql.session | localhost |

| mysql.sys | localhost |

| root | localhost |

+------------------+-----------+

6 rows in set (0.00 sec)

mysql> show grants for 'migration_user'@'%';

+--------------------------------------------------------------------

| Grants for migration_user@% |

+--------------------------------------------------------------------

| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `migration_user`@`%` |

| GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,FLUSH_OPTIMIZER_COSTS,FLUSH_STATUS,FLUSH_TABLES,FLUSH_USER_RESOURCES,GROUP_REPLICATION_ADMIN,INNODB_REDO_LOG_ARCHIVE,INNODB_REDO_LOG_ENABLE,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SHOW_ROUTINE,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON *.* TO `migration_user`@`%` |

+--------------------------------------------------------------------

2 rows in set (0.01 sec)

---------------------------------------------------------------------

Step - 6 Moving / Generating DDL's of PostgreSQL Through AWS Schema Conversion Tool / MySQL Workbench Tool

---------------------------------------------------------------------

(i) First install AWS Schema conversion tool locally by using below link

https://aws.amazon.com/dms/schema-conversion-tool/

(ii) If we are doing PostgreSQL to MySQL , we need to download and install postgres JDBC driver and MySQL JDBC driver from the below links to connect to the source and Target DB's .

PostgreSQL Drivers :- https://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_Installing.html#CHAP_Installing.JDBCDrivers

https://jdbc.postgresql.org/download.html#current

MySQL JDBC Drivers :- https://dev.mysql.com/downloads/

(iii) If we are using AWS schema conversion tool , once it is moved the all objectives / all objectives with data , take backup of only DDL's for all Schemas and Tables by using the below commands.

mysqldump -u root -p --no-data blob > blob.sql

mysqldump -u root -p --no-data mrsdb > mrsdb.sql

mysqldump -u root -p --no-data migration_db > migration_db.sql

mysqldump -u root -p --no-data dvdrental > dvdrental.sql

mysqldump -u root -p --no-data test_db > test_db.sql

mysqldump -u root -p --no-data kidsdb > kidsdb.sql

-------------------------------------------------------------------

Step - 7 Moving / Generating DDL's of PostgreSQL Through Full converter Tool / MySQL Workbench Tool and Workbench Issues and Solutions

-------------------------------------------------------------------

(i) Install Full Converter s/w [ 14 days Free Trail ] and move the data from PostgreSQL to MySQL , then use the below commands to take backup of Schemas only and DDL's of all Schemas by using below commands.

Export DDL's for mrsdb by using mysqldump after completed FULL converter tool with data ..

[root@mysql-target-server ~]# mysqldump -u root -p --no-data mrsdb > mrsdb.sql ====> Including indexes backup

Enter password:

[root@mysql-target-server ~]#

[root@mysql-target-server ec2-user]# mysqldump -u root -p --no-data migration_db > migration_db.sql

Enter password:

[root@mysql-target-server ec2-user]#

[root@mysql-target-server ec2-user]# mysqldump -u root -p --no-data blob > blob.sql

Enter password:

[root@mysql-target-server ec2-user]#

[root@mysql-target-server ec2-user]# mysqldump -u root -p --no-data dvdrental > dvdrental.sql

Enter password:

[root@mysql-target-server ec2-user]#

mrsdb :-

847 Tables

2661 Indexes

837 Foreign Keys

migration_db :-

Tables =2

dvdrental :-

Tables = 15

indexes = 28

FK = 14

(ii) While doing Migration from PostgreSQL to MySQL through Workbench , knows issues and solution which i am going to provide here ..

Near Schema Selection step from Workbech Migration Wizard :-

Error information :-

Starting...

Connect to source DBMS...

Connecting...

Connecting to postgresql@DRIVER=PostgreSQL ANSI;SERVER=50.16.135.236;PORT=5432...

Opening ODBC connection to DRIVER=PostgreSQL ANSI;SERVER=50.16.135.236;PORT=5432;DATABASE=kidsdb;UID=postgres;UseDeclareFetch=1;...

Connected

Connect to source DBMS done

Reverse engineer selected schemas....

Reverse engineering public from kidsdb

- Reverse engineering catalog information

Traceback (most recent call last):

File "C:\Program Files\MySQL\MySQL Workbench 8.0\workbench\wizard_progress_page_widget.py", line 190, in thread_work

self.func()

File "C:\Program Files\MySQL\MySQL Workbench 8.0\modules\migration_schema_selection.py", line 176, in task_reveng

self.main.plan.migrationSource.reverseEngineer()

File "C:\Program Files\MySQL\MySQL Workbench 8.0\modules\migration.py", line 357, in reverseEngineer

self.state.sourceCatalog = self._rev_eng_module.reverseEngineer(self.connection, self.selectedCatalogName, self.selectedSchemataNames, self.state.applicationData)

SystemError: ProgrammingError("('42703', '[42703] ERROR: column "min_value" does not exist;\nError while executing the query (1) (SQLExecDirectW)')"): error calling Python module function DbPostgresqlRE.reverseEngineer

ERROR: Reverse engineer selected schemas: ProgrammingError("('42703', '[42703] ERROR: column "min_value" does not exist;\nError while executing the query (1) (SQLExecDirectW)')"): error calling Python module function DbPostgresqlRE.reverseEngineer

Failed

Solution : -

-----------------

Mysql Workbench installation folder - in file: WORKBENCH_INSTALLATION_PATH/modules/db_postgresql_re_grt.py I have changed this (line 74):

From

seq_details_query = """SELECT min_value, max_value, start_value, increment_by, last_value, is_cycled, cache_value FROM "%s"."%s" """

To this (see some additional changes):

seq_details_query = """SELECT ps.min_value, ps.max_value, ps.start_value, ps.increment_by, "%s".last_value, ps.cycle as is_cycled, ps.cache_size as cache_value FROM "%s"."%s" JOIN pg_sequences ps ON ps.sequencename = '%s' """

Also do not forget to add additional parameters to query execution (line 80):

... seq_details_query % (seq_name, schema.name, seq_name, seq_name) ...

And the process went smoothly. The case is, that Postgres from version 13 has some data about sequences stored in different table.

(iii) While doing Migration from PostgreSQL to MySQL through Workbench , knows issues and solution which i am going to provide here ..

Near Reverse Engineer Source step from Workbech Migration Wizard :-

Error info :-

Traceback (most recent call last):

File "C:\Program Files\MySQL\MySQL Workbench 8.0\workbench\wizard_progress_page_widget.py", line 190, in thread_work

self.func()

File "C:\Program Files\MySQL\MySQL Workbench 8.0\modules\migration_object_migration.py", line 110, in task_migrate

self.main.plan.migrate()

File "C:\Program Files\MySQL\MySQL Workbench 8.0\modules\migration.py", line 503, in migrate

self.migrationTarget.catalog = self.migrationSource.migration.migrateCatalog(self.state, self.migrationSource.catalog)

SystemError: TypeError("'>' not supported between instances of 'NoneType' and 'int'"): error calling Python module function DbPostgresqlMigration.migrateCatalog

ERROR: Migrate Selected Objects: TypeError("'>' not supported between instances of 'NoneType' and 'int'"): error calling Python module function DbPostgresqlMigration.migrateCatalog

Failed

Solution :-

Delete the below line

prefix_length_limit = min(referenced_index_col.length, MYSQL_MAX_INDEX_KEY_LENGTH_INNODB_UTF8) if referenced_index_col.length > 0 else MYSQL_MAX_INDEX_KEY_LENGTH_INNODB_UTF8

Add the beliow line as per link

prefix_length_limit = MYSQL_MAX_INDEX_KEY_LENGTH_INNODB_UTF8

File name = db_generic_migration_grt.py

Location = C:\Program Files\MySQL\MySQL Workbench 8.0\modules

https://stackoverflow.com/questions/66103613/mysql-workbench-migration-wizard-erre-not-supported-between-instances-of-int

But here i am facing same issue after did above step too ,

---------------------------------------------------------------------

Step - 8 Final DDL and Schema definations gathered from Workbench Tool

---------------------------------------------------------------------

CREATE DATABASE `kidsdb` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;

CREATE DATABASE `blob` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;

CREATE DATABASE `migration_db` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;

CREATE DATABASE `mrsdb` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;

CREATE DATABASE `test_db` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;

CREATE DATABASE `dvdrental` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;

Finally , i was using to gather DDL of Tables , Views , Functions , Triggers , Sequences , Procedures for above databases from PostgreSQL to MySQL by using AWS Schema Conversion Tool , MySQL Workbench Tool.

Here is my entire scripts :-

[root@mysql-target-server final_scripts]# pwd

/home/ec2-user/final/final_scripts

[root@mysql-target-server final_scripts]# ls -ltr

-rw-rw-r--. 1 ec2-user ec2-user 2637 Aug 31 01:27 create_schemas.sql

-rw-r--r--. 1 root root 1178 Aug 31 01:29 blob_db_ddl_table_function.sql

-rw-r--r--. 1 root root 354 Aug 31 01:45 kidsdb_db_ddl_table.sql

-rw-r--r--. 1 root root 4458 Aug 31 01:49 test_db_ddl_table.sql

-rw-r--r--. 1 root root 1984 Aug 31 02:03 migration_db_ddl_table.sql

-rw-r--r--. 1 root root 1817925 Aug 31 02:07 mrsdb_tables_views_ddl.sql

-rw-r--r--. 1 root root 30362 Aug 31 02:18 dvdrental_tables_views_functions_sequences_ddl.sql

[root@mysql-target-server final_scripts]#

---------------------------------------------------------------------

Step - 9 Take a backup of DDL's for all Databases in PostgresSQL [ For Safer Side ]

---------------------------------------------------------------------

Backup in PostgreSQL Database :- [ Only DDL's of all Databases ]

[postgres@postgres-source-server tmp]$ cd backup_ddl/

[postgres@postgres-source-server backup_ddl]$ pwd

/tmp/backup_ddl

[postgres@postgres-source-server backup_ddl]$ ls -ltr

[postgres@postgres-source-server backup_ddl]$ pg_dump --username=postgres -s -d dvdrental --file=dvdrental.sql

[postgres@postgres-source-server backup_ddl]$ pg_dump --username=postgres -s -d blob --file=blob.sql

[postgres@postgres-source-server backup_ddl]$ pg_dump --username=postgres -s -d kidsdb --file=kidsdb.sql

[postgres@postgres-source-server backup_ddl]$ pg_dump --username=postgres -s -d migration_db --file=migration_db.sql

[postgres@postgres-source-server backup_ddl]$ pg_dump --username=postgres -s -d mrsdb --file=mrsdb.sql

[postgres@postgres-source-server backup_ddl]$ pg_dump --username=postgres -s -d test_db --file=test_db.sql

[postgres@postgres-source-server backup_ddl]$ pg_dumpall --globals-only --file=users.sql

[postgres@postgres-source-server backup_ddl]$ ls -ltr

-rw-r--r--. 1 postgres postgres 38537 Aug 27 12:02 dvdrental.sql

-rw-r--r--. 1 postgres postgres 1347 Aug 27 12:02 blob.sql

-rw-r--r--. 1 postgres postgres 4444 Aug 27 12:03 kidsdb.sql

-rw-r--r--. 1 postgres postgres 1829 Aug 27 12:03 migration_db.sql

-rw-r--r--. 1 postgres postgres 1873326 Aug 27 12:03 mrsdb.sql

-rw-r--r--. 1 postgres postgres 884 Aug 27 12:09 users.sql

-rw-r--r--. 1 postgres postgres 1050 Aug 27 12:15 test_db.sql

[postgres@postgres-source-server backup_ddl]$

dvdrental :-

Views = 7 Completed Success

Functions = 8 Success

Trigger = 1 = Not Success

Type= 1 = Not Success

Domain = 1 = Not success

Agreegate = 1 = Not success

---------------------------------------------------------------------

Step - 10 Most Imp Queries while doing Migration from PostgreSQL to MySQL

---------------------------------------------------------------------

In PostgreSQL :-

------------------

(i) List of Databases.

SELECT datname FROM pg_database;

(ii) List of Users.

SELECT usename from pg_user;

(iii) List of User Permissions :-

\du+

(iv) List of tables with row count of each table :-

Before running this command , we should connect to the Database .

postgres=#\c dvdrental

(v) List of Tables , MV , Index , Sequences , Views , Types in given database

Before running this command , we should connect to the Database .

postgres=#\c dvdrental

dvdrental=# select nsp.nspname as SchemaName,cls.relname as ObjectName,rol.rolname as ObjectOwner,case cls.relkind

when 'r' then 'TABLE'

when 'm' then 'MATERIALIZED_VIEW'

when 'i' then 'INDEX'

when 'S' then 'SEQUENCE'

when 'v' then 'VIEW'

when 'c' then 'TYPE'

else cls.relkind::text

end as ObjectType from pg_class cls

join pg_roles rol on rol.oid = cls.relowner

join pg_namespace nsp on nsp.oid = cls.relnamespace where nsp.nspname not in ('information_schema', 'pg_catalog') and nsp.nspname not like 'pg_toast%' and rol.rolname = 'postgres'

order by ObjectType, 2;

(vi) List of objectivies individually

Before running this command , we should connect to the Database .

postgres=#\c dvdrental

dvdrental=#

\dv ==> List of Views

\dt or SELECT * FROM pg_catalog.pg_tables WHERE schemaname != 'pg_catalog' AND schemaname != 'information_schema'; ==> List of Tables

\df ==> List of Functions

\dD ==> List of Domains

\ds ==> List of sequences

\da ==> List of agreegate Functions

select event_object_schema as table_schema,event_object_table as table_name,trigger_schema, trigger_name,string_agg(event_manipulation, ',') as event,action_timing as activation,action_condition as condition,action_statement as definition from information_schema.triggers group by 1,2,3,4,6,7,8 order by table_schema, table_name; ===> List of Triggers

select DISTINCT trigger_name,event_object_schema as table_schema ,event_object_table as table_name,trigger_schema from information_schema.triggers;

select DISTINCT trigger_name from information_schema.triggers;

In MySQL :-

-------------

https://docs.aws.amazon.com/dms/latest/sbs/chaprdsoracle2aurora.steps.verifydatamigration.html

(i) To get list of Tables in entire Instance Level.

SELECT TABLE_SCHEMA,TABLE_NAME,TABLE_ROWS FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA not in ('sys', 'information_schema','mysql', 'performance_schema') and TABLE_TYPE='BASE TABLE' order by 1;

SELECT TABLE_NAME,TABLE_ROWS FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'migration_db' and TABLE_TYPE='BASE TABLE' order by 1;

(ii) To get list of views in entire Instance Level.

SELECT TABLE_NAME FROM information_schema.`TABLES` WHERE table_schema not in ('sys', 'information_schema','mysql', 'performance_schema') and TABLE_TYPE LIKE 'VIEW' ;

SELECT TABLE_NAME FROM information_schema.`TABLES` WHERE TABLE_TYPE LIKE 'VIEW' AND TABLE_SCHEMA LIKE 'mrsdb';

(iii) To get list of Routines / Procedures in entire Instance Level.

select routine_schema as database_name,routine_name,routine_type as type,data_type as return_type from information_schema.routines where routine_schema not in ('sys', 'information_schema','mysql', 'performance_schema');

SHOW PROCEDURE STATUS WHERE Db = 'databasename';

select * from information_schema.ROUTINES where ROUTINE_SCHEMA='YOUR DATABASE NAME' and ROUTINE_TYPE='FUNCTION';

(iv) To get list of Functions in entire Instance Level.

SHOW FUNCTION STATUS WHERE Db = 'db_name';

(v) To get list of triggeres in entire Instance Level.

select TRIGGER_SCHEMA,TRIGGER_NAME from information_schema.triggers

select TRIGGER_SCHEMA,TRIGGER_NAME from information_schema.triggers where TRIGGER_SCHEMA='migration';

(vi) To get list of indexes in entire Instance level

SELECT DISTINCT TABLE_NAME, INDEX_NAME FROM INFORMATION_SCHEMA.STATISTICS WHERE table_schema not in ('sys', 'information_schema','mysql', 'performance_schema') and TABLE_SCHEMA =`schema_name`;

SELECT DISTINCT TABLE_NAME, INDEX_NAME FROM INFORMATION_SCHEMA.STATISTICS WHERE table_schema not in ('sys', 'information_schema','mysql', 'performance_schema');

(vii) To get list of seconday indexes in entire Instance Level.

SELECT DISTINCT TABLE_NAME, INDEX_NAME,NON_UNIQUE FROM INFORMATION_SCHEMA.STATISTICS WHERE INDEX_NAME <> 'PRIMARY';

SELECT DISTINCT TABLE_NAME, INDEX_NAME,NON_UNIQUE FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = 'migration' and INDEX_NAME <> 'PRIMARY';

(viii) To get list of Foreign keys in entire Instance Level.

SELECT TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE;

SELECT TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME FROM INFORMATION_SCHEMA. KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_SCHEMA ='mrsdb';

(ix) Use the following command exports tables and index definitions.

mysqldump --no-data --no-create-db --single_transaction -u root -p migration --skip-triggers > mysql_tables_indexes.sql

(x) Use following command to exports routines (stored procedures, functions, and triggers) into the file routines.sql:

mysqldump -u root --routines --no-create-info --no-data --no-create-db --skip-opt -p migration > routines.sql

(xi) Run the following query to check the relationship in tables; this query checks the departments with employees greater than 10. From Oracle to MySQL , We can use this...

SELECT B.DEPARTMENT_NAME,COUNT(*) FROM HR.EMPLOYEES A,HR.DEPARTMENTS B WHERE A.DEPARTMENT_ID=B.DEPARTMENT_ID GROUP BY B.DEPARTMENT_NAME HAVING COUNT(*) > 10 ORDER BY 1;

(xii) Views Backup

mysql -u root -p INFORMATION_SCHEMA --skip-column-names --batch -e "select table_name from tables where table_type = 'VIEW' and table_schema = 'public'" | xargs mysqldump -u root -p public > mrsdb_views.sql

---------------------------------------------------------------------Step - 11 Configure client pacakges for PostgreSQL and MySQL to connect from jump host to Source and Target DB Servers

---------------------------------------------------------------------

Install mysql client pacakges in jump host..

[root@jump-host ~]# hostname

jump-host

[root@jump-host ~]# hostname -I

172.31.27.241

[root@jump-host ~]# yum search mysql

Updating Subscription Management repositories.

Unable to read consumer identity

This system is not registered to Red Hat Subscription Management. You can use subscription-manager to register.

Last metadata expiration check: 0:00:34 ago on Tue 31 Aug 2021 05:30:16 AM UTC.

============ Name & Suy Matched: mysql ==============================

mysql.x86_64 : MySQL client programs and shared libraries

apr-util-mysql.x86_64 : APR utility library MySQL DBD driver

dovecot-mysql.x86_64 : MySQL back end for dovecot

freeradius-mysql.x86_64 : MySQL support for freeradius

grafana-mysql.x86_64 : Grafana mysql datasource

mysql-common.x86_64 : The shared files required for MySQL server and client

mysql-devel.x86_64 : Files for development of MySQL applications

mysql-errmsg.x86_64 : The error messages files required by MySQL server

mysql-libs.x86_64 : The shared libraries required for MySQL clients

mysql-selinux.noarch : SELinux policy modules for MySQL and MariaDB packages

mysql-server.x86_64 : The MySQL server and related files

mysql-test.x86_64 : The test suite distributed with MySQL

pcp-pmda-mysql.x86_64 : Performance Co-Pilot (PCP) metrics for MySQL

perl-DBD-MySQL.x86_64 : A MySQL interface for Perl

php-mysqlnd.x86_64 : A module for PHP applications that use MySQL databases

postfix-mysql.x86_64 : Postfix MySQL map support

python2-PyMySQL.noarch : Pure-Python MySQL client library

python3-PyMySQL.noarch : Pure-Python MySQL client library

python38-PyMySQL.noarch : Pure-Python MySQL client library

python39-PyMySQL.noarch : Pure-Python MySQL client library

qt5-qtbase-mysql.x86_64 : MySQL driver for Qt5's SQL classes

qt5-qtbase-mysql.i686 : MySQL driver for Qt5's SQL classes

rsyslog-mysql.x86_64 : MySQL support for rsyslog

rubygem-mysql2.x86_64 : A simple, fast Mysql library for Ruby, binding to libmysql

rubygem-mysql2-doc.noarch : Documentation for rubygem-mysql2

=================================================================== Summary Matched: mysql =================================================

mariadb-devel.x86_64 : Files for development of MariaDB/MySQL applications

mariadb-java-client.noarch : Connects applications developed in Java to MariaDB and MySQL databases

mariadb-server-utils.x86_64 : Non-essential server utilities for MariaDB/MySQL applications

[root@jump-host ~]# yum install mysql.x86_64

Updating Subscription Management repositories.

Unable to read consumer identity

This system is not registered to Red Hat Subscription Management. You can use subscription-manager to register.

Last metadata expiration check: 0:01:14 ago on Tue 31 Aug 2021 05:30:16 AM UTC.

Dependencies resolved.

=====================================================================

Package Architecture Version Repository Size

=====================================================================

Installing:

mysql x86_64 8.0.21-1.module+el8.2.0+7855+47abd494 rhel-8-appstream-rhui-rpms 12 M

Installing dependencies:

mariadb-connector-c-config noarch 3.1.11-2.el8_3 rhel-8-appstream-rhui-rpms 15 k

mysql-common x86_64 8.0.21-1.module+el8.2.0+7855+47abd494 rhel-8-appstream-rhui-rpms 148 k

Enabling module streams:

mysql 8.0

Transaction Summary

=====================================================================

Install 3 Packages

Total download size: 12 M

Installed size: 63 M

Is this ok [y/N]: y

Downloading Packages:

(1/3): mariadb-connector-c-config-3.1.11-2.el8_3.noarch.rpm 79 kB/s | 15 kB 00:00

(2/3): mysql-common-8.0.21-1.module+el8.2.0+7855+47abd494.x86_64.rpm 752 kB/s | 148 kB 00:00

(3/3): mysql-8.0.21-1.module+el8.2.0+7855+47abd494.x86_64.rpm 32 MB/s | 12 MB 00:00

---------------------------------------------------------------------

Total 24 MB/s | 12 MB 00:00

Running transaction check

Transaction check succeeded.

Running transaction test

Transaction test succeeded.

Running transaction

Preparing : 1/1

Installing : mariadb-connector-c-config-3.1.11-2.el8_3.noarch 1/3

Installing : mysql-common-8.0.21-1.module+el8.2.0+7855+47abd494.x86_64 2/3

Installing : mysql-8.0.21-1.module+el8.2.0+7855+47abd494.x86_64 3/3

Running scriptlet: mysql-8.0.21-1.module+el8.2.0+7855+47abd494.x86_64 3/3

Verifying : mariadb-connector-c-config-3.1.11-2.el8_3.noarch 1/3

Verifying : mysql-8.0.21-1.module+el8.2.0+7855+47abd494.x86_64 2/3

Verifying : mysql-common-8.0.21-1.module+el8.2.0+7855+47abd494.x86_64 3/3

Installed products updated.

Installed:

mariadb-connector-c-config-3.1.11-2.el8_3.noarch mysql-8.0.21-1.module+el8.2.0+7855+47abd494.x86_64

mysql-common-8.0.21-1.module+el8.2.0+7855+47abd494.x86_64

Complete!

[root@jump-host ~]# rpm -qa | grep -i mysql

mysql-common-8.0.21-1.module+el8.2.0+7855+47abd494.x86_64

mysql-8.0.21-1.module+el8.2.0+7855+47abd494.x86_64

[root@jump-host ~]# which mysql

/usr/bin/mysql

[root@jump-host ~]# mysql --version

mysql Ver 8.0.21 for Linux on x86_64 (Source distribution)

Connectivity from jump host to MySQL Database [ Target Database ]

---------------------------------------------------------------------

[root@jump-host ~]# mysql -u root -p -h 172.31.17.136

Enter password: Root@123

Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 9.Server version: 8.0.26 MySQL Community Server - GPL Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select user(),database();

+------------------------------------+------------+

| user() | database() |

+------------------------------------+------------+

| root@ip-172-31-27-241.ec2.internal | NULL |

+------------------------------------+------------+

1 row in set (0.00 sec)

mysql> system hostname;

jump-host

mysql> system hostname -I

172.31.27.241

mysql>

Install mysql client pacakges in jump host..

[root@jump-host ~]# yum search postgresql

Updating Subscription Management repositories.

Unable to read consumer identity

This system is not registered to Red Hat Subscription Management. You can use subscription-manager to register.

Last metadata expiration check: 0:16:39 ago on Tue 31 Aug 2021 05:30:16 AM UTC.

==================================== Name & Summary Matched: postgresql =============================================================

postgresql.x86_64 : PostgreSQL client programs

freeradius-postgresql.x86_64 : Postgresql support for freeradius

pcp-pmda-postgresql.x86_64 : Performance Co-Pilot (PCP) metrics for PostgreSQL

postgresql-contrib.x86_64 : Extension modules distributed with PostgreSQL

postgresql-docs.x86_64 : Extra documentation for PostgreSQL

postgresql-jdbc.noarch : JDBC driver for PostgreSQL

postgresql-jdbc-javadoc.noarch : API docs for postgresql-jdbc

postgresql-odbc.x86_64 : PostgreSQL ODBC driver

postgresql-plperl.x86_64 : The Perl procedural language for PostgreSQL

postgresql-plpython3.x86_64 : The Python3 procedural language for PostgreSQL

postgresql-pltcl.x86_64 : The Tcl procedural language for PostgreSQL

postgresql-server.x86_64 : The programs needed to create and run a PostgreSQL server

postgresql-server-devel.x86_64 : PostgreSQL development header files and libraries

postgresql-static.x86_64 : Statically linked PostgreSQL libraries

postgresql-test.x86_64 : The test suite distributed with PostgreSQL

postgresql-test-rpm-macros.x86_64 : Convenience RPM macros for build-time testing against PostgreSQL server

postgresql-upgrade.x86_64 : Support for upgrading from the previous major release of PostgreSQL

qt5-qtbase-postgresql.x86_64 : PostgreSQL driver for Qt5's SQL classes

qt5-qtbase-postgresql.i686 : PostgreSQL driver for Qt5's SQL classes

================================================================== Name Matched: postgresql ==================================================================

postgresql-odbc-tests.x86_64 : Testsuite files for psqlodbc

postgresql-upgrade-devel.x86_64 : Support for build of extensions required for upgrade process

================================================================ Summary Matched: postgresql =================================================================

apr-util-pgsql.x86_64 : APR utility library PostgreSQL DBD driver

libpgtypes.i686 : Map PostgreSQL database types to C equivalents

libpgtypes.x86_64 : Map PostgreSQL database types to C equivalents

libpq.x86_64 : PostgreSQL client library

libpq.i686 : PostgreSQL client library

libpq-devel.i686 : Development files for building PostgreSQL client tools

libpq-devel.x86_64 : Development files for building PostgreSQL client tools

perl-DBD-Pg.x86_64 : A PostgreSQL interface for perl

php-pgsql.x86_64 : A PostgreSQL database module for PHP

postfix-pgsql.x86_64 : Postfix PostgreSQL map support

python-psycopg2-doc.x86_64 : Documentation for psycopg python PostgreSQL database adapter

python2-psycopg2.x86_64 : A PostgreSQL database adapter for Python 2

python2-psycopg2-debug.x86_64 : A PostgreSQL database adapter for Python 2 (debug build)

python2-psycopg2-tests.x86_64 : A testsuite for A PostgreSQL database adapter for Python 2

python3-psycopg2.x86_64 : A PostgreSQL database adapter for Python 3

python38-psycopg2.x86_64 : A PostgreSQL database adapter for Python 3

python38-psycopg2-doc.x86_64 : Documentation for psycopg python PostgreSQL database adapter

python38-psycopg2-tests.x86_64 : A testsuite for A PostgreSQL database adapter for Python 2

python39-psycopg2.x86_64 : A PostgreSQL database adapter for Python 3

python39-psycopg2-doc.x86_64 : Documentation for psycopg python PostgreSQL database adapter

python39-psycopg2-tests.x86_64 : A testsuite for A PostgreSQL database adapter for Python 2

rubygem-pg.x86_64 : A Ruby interface to the PostgreSQL RDBMS

[root@jump-host ~]# yum install postgresql.x86_64

Updating Subscription Management repositories.

Unable to read consumer identity

This system is not registered to Red Hat Subscription Management. You can use subscription-manager to register.

Last metadata expiration check: 0:17:50 ago on Tue 31 Aug 2021 05:30:16 AM UTC.

Dependencies resolved.

=====================================================================

Package Architecture Version Repository Size

=====================================================================

Installing:

postgresql x86_64 10.17-1.module+el8.4.0+11249+895597ab rhel-8-appstream-rhui-rpms 1.5 M

Installing dependencies:

libpq x86_64 13.3-1.el8_4 rhel-8-appstream-rhui-rpms 197 k

Enabling module streams:

postgresql 10

Transaction Summary

=====================================================================

Install 2 Packages

Total download size: 1.7 M

Installed size: 6.2 M

Is this ok [y/N]: y

Downloading Packages:

(1/2): libpq-13.3-1.el8_4.x86_64.rpm 1.3 MB/s | 197 kB 00:00

(2/2): postgresql-10.17-1.module+el8.4.0+11249+895597ab.x86_64.rpm 7.7 MB/s | 1.5 MB 00:00

---------------------------------------------------------------------

Total 7.3 MB/s | 1.7 MB 00:00

Running transaction check

Transaction check succeeded.

Running transaction test

Transaction test succeeded.

Running transaction

Preparing : 1/1

Installing : libpq-13.3-1.el8_4.x86_64 1/2

Installing : postgresql-10.17-1.module+el8.4.0+11249+895597ab.x86_64 2/2

Running scriptlet: postgresql-10.17-1.module+el8.4.0+11249+895597ab.x86_64 2/2

Verifying : postgresql-10.17-1.module+el8.4.0+11249+895597ab.x86_64 1/2

Verifying : libpq-13.3-1.el8_4.x86_64 2/2

Installed products updated.

Installed:

libpq-13.3-1.el8_4.x86_64 postgresql-10.17-1.module+el8.4.0+11249+895597ab.x86_64

Complete!

[root@jump-host ~]# which psql

/usr/bin/psql

[root@jump-host ~]# psql --version

psql (PostgreSQL) 10.17

[root@jump-host ~]# psql -U postgres -h 172.31.30.141

psql (10.17, server 13.4)

WARNING: psql major version 10, server major version 13.

Some psql features might not work.

Type "help" for help.

postgres=# \l

List of databases

--------------+----------+----------+-------------+-------------+----

(9 rows)

postgres=# \q

[root@jump-host ~]#

Important Points :-

--------------------------

(i) In Target Database [ MySQL ] , should change the max_allowed_packet = 16 MB to 1GB and Restart the MySQL DB service .

(ii) In Source Database [ PostgreSQL ] , should change the below lines in pg_hba.conf file and restart the PostgreSQL Database Service.

# Allow users to establish connections from any host

host all all 0.0.0.0/0 trust

host all all 0.0.0.0/0 md5

Enable/ change the parameter --> listen_address = 'localhost' to '*' in postgresql.conf file and restart the DB.

port = 5432 [ Enable this Parameter ]

---------------------------------------------------------------------

Step - 12 Prepare configuration files for PostgreSQL and MySQL Databases from Sqline configuration files in Jump host

----------------------------------------------------------

(i) Create folders based on database names ..i.e

[root@jump-host sqlinesdata31773]# pwd

/home/ec2-user/sqlinesdata31773

[root@jump-host sqlinesdata31773]# mkdir test_db

[root@jump-host sqlinesdata31773]# mkdir migration_db

[root@jump-host sqlinesdata31773]# mkdir mrsdb

[root@jump-host sqlinesdata31773]# mkdir dvdrental

[root@jump-host sqlinesdata31773]# mkdir blob

[root@jump-host sqlinesdata31773]# mkdir kidsdb

[root@jump-host sqlinesdata31773]# pwd

/home/ec2-user/sqlinesdata31773

[root@jump-host sqlinesdata31773]# ls -ltr

-rw-r--r--. 1 root root 361 Feb 8 2018 sqlines_twhere.txt

-rw-r--r--. 1 root root 579 Feb 8 2018 sqlines_tsel.txt

-rw-r--r--. 1 root root 498 Feb 8 2018 sqlines_tsel_all.txt

-rw-r--r--. 1 root root 192 Feb 8 2018 sqlines_tmap.txt

-rw-r--r--. 1 root root 273 Feb 8 2018 sqlines_tf.txt

-rw-r--r--. 1 root root 316 Feb 8 2018 sqlines_qf.txt

-rw-r--r--. 1 root root 124 Feb 8 2018 sqlines_dtmap.txt

-rw-r--r--. 1 root root 202 Feb 8 2018 sqlines_cnsmap.txt

-rw-r--r--. 1 root root 401 Feb 8 2018 sqlines_cmap.txt

-rw-r--r--. 1 root root 4877 Feb 8 2018 sqldata.cfg

-rw-r--r--. 1 root root 188 Feb 8 2018 readme.txt

-rw-r--r--. 1 root root 11303 Feb 8 2018 license.txt

-rwxr-xr-x. 1 root root 11670344 Feb 8 2018 libQtGui.so

-rwxr-xr-x. 1 root root 2701504 Feb 8 2018 libQtCore.so

-rwxr-xr-x. 1 root root 1465537 Feb 12 2018 sqldata

-rw-r--r--. 1 root root 218 Aug 27 01:59 sqldata.log

drwxr-xr-x. 2 root root 6 Aug 31 06:00 test_db

drwxr-xr-x. 2 root root 6 Aug 31 06:00 migration_db

drwxr-xr-x. 2 root root 6 Aug 31 06:01 mrsdb

drwxr-xr-x. 2 root root 6 Aug 31 06:01 dvdrental

drwxr-xr-x. 2 root root 6 Aug 31 06:01 blob

drwxr-xr-x. 2 root root 6 Aug 31 06:01 kidsdb

[root@jump-host sqlinesdata31773]#

(ii) Just copy of sample sqldata.cnf file from sqlinesdata-3.1.773_x86_64_linux folder and keep this in newly created in all db folders .

i.e

[root@jump-host sqlinesdata31773]# pwd

/home/ec2-user/sqlinesdata31773

[root@jump-host sqlinesdata31773]# ls -ltr

-rw-r--r--. 1 root root 361 Feb 8 2018 sqlines_twhere.txt

-rw-r--r--. 1 root root 579 Feb 8 2018 sqlines_tsel.txt

-rw-r--r--. 1 root root 498 Feb 8 2018 sqlines_tsel_all.txt

-rw-r--r--. 1 root root 192 Feb 8 2018 sqlines_tmap.txt

-rw-r--r--. 1 root root 273 Feb 8 2018 sqlines_tf.txt

-rw-r--r--. 1 root root 316 Feb 8 2018 sqlines_qf.txt

-rw-r--r--. 1 root root 124 Feb 8 2018 sqlines_dtmap.txt

-rw-r--r--. 1 root root 202 Feb 8 2018 sqlines_cnsmap.txt

-rw-r--r--. 1 root root 401 Feb 8 2018 sqlines_cmap.txt

-rw-r--r--. 1 root root 4877 Feb 8 2018 sqldata.cfg

-rw-r--r--. 1 root root 188 Feb 8 2018 readme.txt

-rw-r--r--. 1 root root 11303 Feb 8 2018 license.txt

-rwxr-xr-x. 1 root root 11670344 Feb 8 2018 libQtGui.so

-rwxr-xr-x. 1 root root 2701504 Feb 8 2018 libQtCore.so

-rwxr-xr-x. 1 root root 1465537 Feb 12 2018 sqldata

-rw-r--r--. 1 root root 218 Aug 27 01:59 sqldata.log

drwxr-xr-x. 2 root root 6 Aug 31 06:00 test_db

drwxr-xr-x. 2 root root 6 Aug 31 06:00 migration_db

drwxr-xr-x. 2 root root 6 Aug 31 06:01 mrsdb

drwxr-xr-x. 2 root root 6 Aug 31 06:01 dvdrental

drwxr-xr-x. 2 root root 6 Aug 31 06:01 blob

drwxr-xr-x. 2 root root 6 Aug 31 06:01 kidsdb

[root@jump-host sqlinesdata31773]# cp sqldata.cfg /home/ec2-user/sqlinesdata31773/test_db/sqldata_test_db.cnf

[root@jump-host sqlinesdata31773]# cp sqldata.cfg /home/ec2-user/sqlinesdata31773/migration_db/sqldata_migration_db.cnf

[root@jump-host sqlinesdata31773]# cp sqldata.cfg /home/ec2-user/sqlinesdata31773/mrsdb/sqldata_mrsdb.cnf

[root@jump-host sqlinesdata31773]# cp sqldata.cfg /home/ec2-user/sqlinesdata31773/dvdrental/sqldata_dvdrental.cnf

[root@jump-host sqlinesdata31773]# cp sqldata.cfg /home/ec2-user/sqlinesdata31773/blob/sqldata_blob.cnf

[root@jump-host sqlinesdata31773]# cp sqldata.cfg /home/ec2-user/sqlinesdata31773/kidsdb/sqldata_kidsdb.cnf

(iii) Now change the parameters in all cnf files which we created above step..

-------------------

test_db :-

-------------------

[root@jump-host test_db]# pwd

/home/ec2-user/sqlinesdata31773/test_db

[root@jump-host test_db]# cat sqldata_test_db.cnf

-- SQLines Data options (contact us at support@sqlines.com to request a new option)

-sd=pg,postgres/postgres@3.229.186.86:5432,test_db

-td=mysql,root/Root@123@50.19.50.121,test_db

-t=*.*

-ss=6

-out=/home/ec2-user/sqlinesdata31773/test_db/

-log=test_db.log

-- DDL Options

--------------

-- Set yes to to migrate table definitions, or no to not migrate

-- DDL statements executed depends on -topt option (CREATE; DROP and CREATE TABLE, or TRUNCATE TABLE)

-ddl_tables=no

-- Set yes to to migrate constraints (this is default), or no to not migrate

-constraints=no

-- Set yes to to migrate indexes (this is default), or no to not migrate

-indexes=no

-- Data Options

---------------

-- Set yes to to migrate data (this is default), or no to not migrate

-data=yes

-------------------

migration_db :-

-------------------

[root@jump-host migration_db]# pwd

/home/ec2-user/sqlinesdata31773/migration_db

[root@jump-host migration_db]# cat sqldata_migration_db.cnf

-- SQLines Data options (contact us at support@sqlines.com to request a new option)

-sd=pg,postgres/postgres@3.229.186.86:5432,migration_db

-td=mysql,root/Root@123@50.19.50.121,migration_db

-t=*.*

-ss=6

-out=/home/ec2-user/sqlinesdata31773/migration_db/

-log=migration_db.log

-- DDL Options

--------------

-- Set yes to to migrate table definitions, or no to not migrate

-- DDL statements executed depends on -topt option (CREATE; DROP and CREATE TABLE, or TRUNCATE TABLE)

-ddl_tables=no

-- Set yes to to migrate constraints (this is default), or no to not migrate

-constraints=no

-- Set yes to to migrate indexes (this is default), or no to not migrate

-indexes=no

-- Data Options

---------------

-- Set yes to to migrate data (this is default), or no to not migrate

-data=yes

-------------------

mrsdb :-

-------------------

[root@jump-host mrsdb]# pwd

/home/ec2-user/sqlinesdata31773/mrsdb

[root@jump-host mrsdb]# ls -ltr

-rw-r--r--. 1 root root 5046 Aug 31 07:12 sqldata_mrsdb.cnf

[root@jump-host mrsdb]# cat sqldata_mrsdb.cnf

-- SQLines Data options (contact us at support@sqlines.com to request a new option)

-sd=pg,postgres/postgres@3.229.186.86:5432,mrsdb

-td=mysql,root/Root@123@50.19.50.121,mrsdb

-t=*.*

-ss=6

-out=/home/ec2-user/sqlinesdata31773/mrsdb/

-log=mrsdb.log

-- DDL Options

--------------

-- Set yes to to migrate table definitions, or no to not migrate

-- DDL statements executed depends on -topt option (CREATE; DROP and CREATE TABLE, or TRUNCATE TABLE)

-ddl_tables=no

-- Set yes to to migrate constraints (this is default), or no to not migrate

-constraints=no

-- Set yes to to migrate indexes (this is default), or no to not migrate

-indexes=no

-- Data Options

---------------

-- Set yes to to migrate data (this is default), or no to not migrate

-data=yes

-------------------

dvdrental :-

-------------------

[root@jump-host dvdrental]# pwd

/home/ec2-user/sqlinesdata31773/dvdrental

[root@jump-host dvdrental]# ls -ltr

-rw-r--r--. 1 root root 4877 Aug 31 06:05 sqldata_dvdrental.cnf

[root@jump-host dvdrental]# cat sqldata_dvdrental.cnf

-- SQLines Data options (contact us at support@sqlines.com to request a new option)

-sd=pg,postgres/postgres@3.229.186.86:5432,dvdrental

-td=mysql,root/Root@123@50.19.50.121,dvdrental

-t=*.*

-ss=6

-out=/home/ec2-user/sqlinesdata31773/dvdrental/

-log=dvdrental.log

-- DDL Options

--------------

-- Set yes to to migrate table definitions, or no to not migrate

-- DDL statements executed depends on -topt option (CREATE; DROP and CREATE TABLE, or TRUNCATE TABLE)

-ddl_tables=no

-- Set yes to to migrate constraints (this is default), or no to not migrate

-constraints=no

-- Set yes to to migrate indexes (this is default), or no to not migrate

-indexes=no

-- Data Options

---------------

-- Set yes to to migrate data (this is default), or no to not migrate

-data=yes

-------------------

blob :-

-------------------

[root@jump-host blob]# pwd

/home/ec2-user/sqlinesdata31773/blob

[root@jump-host blob]# ls -ltr

-rw-r--r--. 1 root root 4877 Aug 31 06:05 sqldata_blob.cnf

[root@jump-host blob]# cat sqldata_blob.cnf

-- SQLines Data options (contact us at support@sqlines.com to request a new option)

-sd=pg,postgres/postgres@3.229.186.86:5432,blob

-td=mysql,root/Root@123@50.19.50.121,blob

-t=*.*

-ss=6

-out=/home/ec2-user/sqlinesdata31773/blob/

-log=blob.log

-- DDL Options

--------------

-- Set yes to to migrate table definitions, or no to not migrate

-- DDL statements executed depends on -topt option (CREATE; DROP and CREATE TABLE, or TRUNCATE TABLE)

-ddl_tables=no

-- Set yes to to migrate constraints (this is default), or no to not migrate

-constraints=no

-- Set yes to to migrate indexes (this is default), or no to not migrate

-indexes=no

-- Data Options

---------------

-- Set yes to to migrate data (this is default), or no to not migrate

-data=yes

-------------------

kidsdb :-

-------------------

[root@jump-host kidsdb]# pwd

/home/ec2-user/sqlinesdata31773/kidsdb

[root@jump-host kidsdb]# ls -ltr

-rw-r--r--. 1 root root 4877 Aug 31 06:05 sqldata_kidsdb.cnf

[root@jump-host kidsdb]# cat sqldata_kidsdb.cnf

-- SQLines Data options (contact us at support@sqlines.com to request a new option)

-sd=pg,postgres/postgres@3.229.186.86:5432,kidsdb

-td=mysql,root/Root@123@50.19.50.121,kidsdb

-t=*.*

-ss=6

-out=/home/ec2-user/sqlinesdata31773/kidsdb/

-log=kids.log

-- DDL Options

--------------

-- Set yes to to migrate table definitions, or no to not migrate

-- DDL statements executed depends on -topt option (CREATE; DROP and CREATE TABLE, or TRUNCATE TABLE)

-ddl_tables=no

-- Set yes to to migrate constraints (this is default), or no to not migrate

-constraints=no

-- Set yes to to migrate indexes (this is default), or no to not migrate

-indexes=no

-- Data Options

---------------

-- Set yes to to migrate data (this is default), or no to not migrate

-data=yes

(iv) Verify whether it's connecting to source and target databases by using sqline ...

To just test connections you can specify non-existing table in -t option. Then the tool will connect and terminate.

[root@jump-host sqlinesdata31773]# pwd

/home/ec2-user/sqlinesdata31773

[root@jump-host sqlinesdata31773]# ./sqldata -sd=pg,postgres/postgres@3.229.186.86,mrsdb -td=mysql,root@172.31.17.136,test_db -t=test

SQLines Data 3.1.773 x86_64 Linux - Database Migration Tool.

Connecting to databases (372 ms)

PostgreSQL 13.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.4.1 20200928 (Red Hat 8.4.1-1), 64-bit (Ok, 372 ms)

MySQL - Error: libmysqlclient.so: cannot open shared object file: No such file or directory (Failed, 0 ms)

So here libmysqlclient pacakge issue .

Install mysql-libs and reconnect again ...

[root@jump-host sqlinesdata31773]# yum install mysql-libs.x86_64

[root@jump-host sqlinesdata31773]# yum install mysql-devel.x86_64

[root@jump-host sqlinesdata31773]# rpm -qa | grep -i mysql

mysql-common-8.0.21-1.module+el8.2.0+7855+47abd494.x86_64

mysql-libs-8.0.21-1.module+el8.2.0+7855+47abd494.x86_64

mysql-8.0.21-1.module+el8.2.0+7855+47abd494.x86_64

mysql-devel-8.0.21-1.module+el8.2.0+7855+47abd494.x86_64

[root@jump-host sqlinesdata31773]# pwd

/home/ec2-user/sqlinesdata31773

[root@jump-host sqlinesdata31773]# ./sqldata -sd=pg,postgres/postgres@3.229.186.86,mrsdb -td=mysql,root/Root@123@50.19.50.121,test_db -t=test

SQLines Data 3.1.773 x86_64 Linux - Database Migration Tool.

Connecting to databases (84 ms)

PostgreSQL 13.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.4.1 20200928 (Red Hat 8.4.1-1), 64-bit (Ok, 84 ms)

MySQL 8.0.26 MySQL Community Server - GPL x86_64 Linux 1.2.11 (Ok, 11 ms)

Reading the database schema

[root@jump-host sqlinesdata31773]#

So connections are established ...so working fine ..

---------------------------------------------------------------------

Now with sqlinesdata-3.3.171_x86_64_linux.tar.gz Version :- Please use this version to migrate the data

---------------------------------------------------------------------

[root@jump-host sqlinesdata33171]# mkdir test_db

[root@jump-host sqlinesdata33171]# mkdir mrsdb

[root@jump-host sqlinesdata33171]# mkdir migration_db

[root@jump-host sqlinesdata33171]# mkdir dvdrental

[root@jump-host sqlinesdata33171]# mkdir blob

[root@jump-host sqlinesdata33171]# mkdir kidsdb

[root@jump-host sqlinesdata33171]# pwd

/home/ec2-user/sqlinesdata33171

[root@jump-host sqlinesdata33171]# ls -ltr

-rw-r--r--. 1 root root 7212 Aug 31 12:47 sqldata.cfg

-rw-r--r--. 1 root root 361 Sep 5 20:27 sqlines_twhere.txt

-rw-r--r--. 1 root root 579 Sep 5 20:27 sqlines_tsel.txt

-rw-r--r--. 1 root root 498 Sep 5 20:27 sqlines_tsel_all.txt

-rw-r--r--. 1 root root 192 Sep 5 20:27 sqlines_tmap.txt

-rw-r--r--. 1 root root 273 Sep 5 20:27 sqlines_tf.txt

-rw-r--r--. 1 root root 316 Sep 5 20:27 sqlines_qf.txt

-rw-r--r--. 1 root root 190 Sep 5 20:27 sqlines_dtmap.txt

-rw-r--r--. 1 root root 202 Sep 5 20:27 sqlines_cnsmap.txt

-rw-r--r--. 1 root root 401 Sep 5 20:27 sqlines_cmap.txt

-rw-r--r--. 1 root root 188 Sep 5 20:27 readme.txt

-rw-r--r--. 1 root root 118 Sep 5 20:27 license.txt

-rwxr-xr-x. 1 root root 11670344 Sep 5 20:27 libQtGui.so

-rwxr-xr-x. 1 root root 2701504 Sep 5 20:27 libQtCore.so

-rwxr-xr-x. 1 root root 2051152 Sep 5 20:29 sqldata

-rw-r--r--. 1 root root 272 Sep 6 00:45 sqldata.log

-rw-r--r--. 1 root root 410 Sep 6 00:54 sqldata_ddl.sql

-rw-r--r--. 1 root root 1410 Sep 6 00:54 test_db_user_table_171_06-09-2021.log

drwxr-xr-x. 2 root root 6 Sep 6 01:05 test_db

drwxr-xr-x. 2 root root 6 Sep 6 01:05 mrsdb

drwxr-xr-x. 2 root root 6 Sep 6 01:05 migration_db

drwxr-xr-x. 2 root root 6 Sep 6 01:05 dvdrental

drwxr-xr-x. 2 root root 6 Sep 6 01:06 blob

drwxr-xr-x. 2 root root 6 Sep 6 01:06 kidsdb

[root@jump-host sqlinesdata33171]# pwd

/home/ec2-user/sqlinesdata33171

This is for test_db database :-

-----------------------------------------------

[root@jump-host sqlinesdata33171]# cp sqldata.cfg test_db/

[root@jump-host sqlinesdata33171]# cd test_db

[root@jump-host test_db]# pwd

/home/ec2-user/sqlinesdata33171/test_db

[root@jump-host test_db]# ls -ltr

-rw-r--r--. 1 root root 7212 Sep 6 01:09 sqldata.cfg

[root@jump-host test_db]# cat sqldata.cfg ================> Default cnf file

-- SQLines Data options (contact us at support@sqlines.com to request a new option)

--- Please note that all configuration options can be specified in the command line as well.

-- DDL Options

--------------

-- Set yes to to migrate table definitions, or no to not migrate

-- DDL statements executed depends on -topt option (CREATE; DROP and CREATE TABLE, or TRUNCATE TABLE)

-ddl_tables=

-- Set yes to to migrate constraints (this is default), or no to not migrate

-- Please note that if -topt=truncate or -topt=none is specified, constraints are not migrated

-constraints=

-- Set yes to to migrate indexes (this is default), or no to not migrate

-- Please note that if -topt=truncate or -topt=none is specified, indexes are not migrated

-indexes=

-- Data Options

---------------

-- Set yes to to migrate data (this is default), or no to not migrate

-data=

-- The maximum number of rows in read/write batch, by default it is defined by the buffer size divided by the table row size

-batch_max_rows=10000

-- Work with LOBs as VARCHAR columns to increase performance (use if LOB columns contain short values less than a few MB i.e.), default is no

-fetch_lob_as_varchar=

-- Fixed size buffer in bytes to read LOB values by binding, not by reading part by part (can cause truncation error if the buffer is less than

-- the maximum LOB value, default is to read LOBs by separate calls

-lob_bind_buffer=10000000

-- When converting from ASCII or UTF16/UCS-2 character sets in the source database to UTF8 i.e. in the target database depending on

-- the actual data you may need greater storage size. And vice versa converting in opposite direction you may require smaller storage size.

-- This parameter specifies the length change ratio. If the source length is 100, and ratio is 1.1 then the target length will be 110

-char_length_ratio=

[root@jump-host test_db]# mv sqldata.cfg sqldata_test_db.cfg

[root@jump-host test_db]# pwd

/home/ec2-user/sqlinesdata33171/test_db

[root@jump-host test_db]# ls -ltr

-rw-r--r--. 1 root root 7212 Sep 6 01:09 sqldata_test_db.cfg

[root@jump-host test_db]# vi sqldata_test_db.cfg

[root@jump-host test_db]# cat sqldata_test_db.cfg

-- SQLines Data options (contact us at support@sqlines.com to request a new option)

--- Please note that all configuration options can be specified in the command line as well.

-sd=pg,postgres/postgres@3.229.186.86:5432,test_db

-td=mysql,root/Root@123@18.234.112.125,test_db

-- t=*.*

-t=*.*

-ss=6

-- out=/home/ec2-user/sqlinesdata31773/test_db/

-- log=test_db.log

-- DDL Options

--------------

-- Set yes to to migrate table definitions, or no to not migrate

-- DDL statements executed depends on -topt option (CREATE; DROP and CREATE TABLE, or TRUNCATE TABLE)

-ddl_tables=no

-- Set yes to to migrate constraints (this is default), or no to not migrate

-- Please note that if -topt=truncate or -topt=none is specified, constraints are not migrated

-constraints=no

-- Set yes to to migrate indexes (this is default), or no to not migrate

-- Please note that if -topt=truncate or -topt=none is specified, indexes are not migrated

-indexes=no

-- Data Options

---------------

-- Set yes to to migrate data (this is default), or no to not migrate

-data=yes

[root@jump-host test_db]#

---------------------------------------------------------------------

Step - 13 Prepare the Row count script for Source Database and Target Database in jump host [ PostgreSQL --> MySQL ]

----------------------------------------------------------------

[root@jump-host ec2-user]# vi row_count_comparison.sh

[root@jump-host ec2-user]# ls -ltr

-rw-rw-r--. 1 root root 15872000 Mar 14 2019 sqlinesdata31773_x86_64_linux.tar

drwxr-xr-x. 8 root root 4096 Aug 31 11:33 sqlinesdata31773

-rw-r--r--. 1 root root 2398 Aug 31 12:02 row_count_comparison.sh

[root@jump-host ec2-user]# chmod +x row_count_comparison.sh

[root@jump-host ec2-user]# ls -ltr

-rw-rw-r--. 1 root root 15872000 Mar 14 2019 sqlinesdata31773_x86_64_linux.tar

drwxr-xr-x. 8 root root 4096 Aug 31 11:33 sqlinesdata31773

-rwxr-xr-x. 1 root root 2398 Aug 31 12:02 row_count_comparison.sh

Required Directories to create before running the shell script

[root@jump-host ec2-user]# cd /home/ec2-user/

[root@jump-host ec2-user]# pwd

/home/ec2-user

[root@jump-host ec2-user]# mkdir tmp_dir

[root@jump-host output_dir]# cd

[root@jump-host ~]# cd /home/ec2-user/

[root@jump-host ec2-user]# pwd

/home/ec2-user

[root@jump-host ec2-user]# ls -ltr

-rw-rw-r--. 1 root root 15872000 Mar 14 2019 sqlinesdata31773_x86_64_linux.tar

drwxr-xr-x. 8 root root 4096 Aug 31 11:33 sqlinesdata31773

-rw-r--r--. 1 root root 0 Aug 31 15:52 table_list.txt

-rwxr-xr-x. 1 root root 2876 Aug 31 15:53 row_count_comparison.sh

drwxr-xr-x. 2 root root 6 Aug 31 15:53 tmp_dir

drwxr-xr-x. 2 root root 6 Aug 31 15:54 output_dir

[root@jump-host ec2-user]#

[root@jump-host ec2-user]# ./row_count_comparison.sh

Arguments counts doesn't match!

USAGE : ./scriptname <DB_NAME> <POSTGRES_SCHEMA_NAME> <MYSQL_SCHEMA_NAME>

[root@jump-host ec2-user]# pwd

/home/ec2-user

[root@jump-host ec2-user]# ls -ltr

-rw-rw-r--. 1 root root 15872000 Mar 14 2019 sqlinesdata31773_x86_64_linux.tar

drwxr-xr-x. 8 root root 4096 Aug 31 11:33 sqlinesdata31773

-rwxr-xr-x. 1 root root 5206 Sep 1 10:34 row_count_comparison.sh

drwxr-xr-x. 2 root root 73 Sep 1 15:17 tmp_dir

drwxr-xr-x. 2 root root 152 Sep 1 15:19 output_dir

[root@jump-host ec2-user]# ./row_count_comparison.sh test_db test_db test_db

Postgres Schema entered : test_db

MySQL Schema/Database entered : test_db

Check the .CSV files under /home/ec2-user/output_dir/ for results

[root@jump-host ec2-user]#

[root@jump-host ec2-user]# cd output_dir/

[root@jump-host output_dir]# pwd

/home/ec2-user/output_dir

[root@jump-host output_dir]# ls -ltr

-rw-r--r--. 1 root root 206 Sep 1 15:24 Row_count_test_db.csv

-rw-r--r--. 1 root root 206 Sep 1 15:24 Row_count_test_db_2021-09-01-1524.csv

[root@jump-host tmp_dir]# pwd

/home/ec2-user/tmp_dir

[root@jump-host tmp_dir]# ls -ltr

-rw-r--r--. 1 root root 159 Sep 1 15:24 tb_count.log

-rw-r--r--. 1 root root 839 Sep 1 15:24 complete_count.log

-rw-r--r--. 1 root root 0 Sep 1 15:24 error_log.txt

[root@jump-host output_dir]# cat Row_count_test_db_2021-09-01-1524.csv

SCHEMA_NAME.TABLE_NAME;Postgres_R_Count;MySQL_R_Count;DIFF

test_db.comments;10;10;0

test_db.genres;5;5;0

test_db.kids_audit;2;2;0

test_db.kids_name;6;6;0

test_db.posts;50;50;0

test_db.users;100000;100000;0

[root@jump-host output_dir]#

Sample Script is here :-

-----------------------------

[root@jump-host ec2-user]# cat temp.sh

#!/bin/bash

LOG_DIR=/home/ec2-user/tmp_dir

SCR_HOME=/home/ec2-user

RESULT_DIR=/home/ec2-user/output_dir > $LOG_DIR/complete_count.log

#export PGPASSWORD=postgres

MYSQL_HOME=/usr/bin

if [ $# -lt 3 ] ; then

echo "Arguments counts doesn't match!"

echo "USAGE : ./scriptname <DB_NAME> <POSTGRES_SCHEMA_NAME> <MYSQL_SCHEMA_NAME>"

exit 0

if [ ! -d tmp_dir ]; then

mkdir tmp_dir

if [ ! -d output_dir ]; then

mkdir output_dir

POSTGRES_USER=postgres

POSTGRES_PASSWORD=postgres

HOST_NAME=3.229.186.86

MYSQL_USER=root

MYSQL_PASS=Root@123

MYSQL_HOST=54.235.4.24

schema_diff()

{

P_DBNAME=$1

P_SCHEMA_NAME=$2

M_SCHEMA_NAME=$3

#echo $SCHEMA_NAME

echo "Postgres Schema entered : $P_SCHEMA_NAME"

echo "MySQL Schema/Database entered : $M_SCHEMA_NAME"

$MYSQL_HOME/mysql -u$MYSQL_USER -p$MYSQL_PASS -h$MYSQL_HOST $M_SCHEMA_NAME -N -s -e "show tables;" 2>&1|grep -v " [Warning]" > $SCR_HOME/table_list.txt

> $LOG_DIR/tb_count.log

echo "SCHEMA_NAME.TABLE_NAME;Postgres_R_Count;MySQL_R_Count;DIFF" > $SCR_HOME/output_dir/Row_count_$P_SCHEMA_NAME.csv

for i in `cat $SCR_HOME/table_list.txt`

echo "MySQL: $M_SCHEMA_NAME.$i" >> $LOG_DIR/complete_count.log

$MYSQL_HOME/mysql -u$MYSQL_USER -p$MYSQL_PASS -h$MYSQL_HOST $M_SCHEMA_NAME -e "select count(*) from $i;" 2>&1|grep -v " [Warning]" >> $LOG_DIR/tb_count.log

echo "Postgres: $P_SCHEMA_NAME.$i" >> $LOG_DIR/complete_count.log

psql -U $POSTGRES_USER -p$POSTGRES_PASSWORD -h$HOST_NAME -d $P_SCHEMA_NAME --port 5432 -t -q -c"select '$i:'||':'|| count(*) as cnt from $i;" >> $LOG_DIR/tb_count.log 2>&1

cat $LOG_DIR/tb_count.log >> $LOG_DIR/complete_count.log

grep -i error $LOG_DIR/tb_count.log > $LOG_DIR/error_log.txt

if [ `cat $LOG_DIR/error_log.txt|wc -l` -ge 1 ]; then

echo "Check the error, Table count failed for the table $i:"

cat $LOG_DIR/error_log.txt

> $LOG_DIR/tb_count.log

else

p_count=`cat $LOG_DIR/tb_count.log|tail -2|head -1|awk -F'::' '{print $2}' |tr -d '[:space:]'`

m_count=`cat $LOG_DIR/tb_count.log|tail -3|head -1|tr -d '[:space:]'`

diff=`expr $p_count - $m_count|tr -d '[:space:]'`

#echo Count on Oracle for table $i : $o_count

#echo Count on postgresql for table $i : $m_count

#echo Diff for table $i : $diff

echo "$P_SCHEMA_NAME.$i;$p_count;$m_count;$diff" >> $SCR_HOME/output_dir/Row_count_$P_SCHEMA_NAME.csv

done

cat $SCR_HOME/output_dir/Row_count_$P_SCHEMA_NAME.csv > $SCR_HOME/output_dir/Row_count_"$P_SCHEMA_NAME"_`date +%Y-%m-%d-%H%M`.csv

#done < $SCR_HOME/schema.lst

}

schema_diff $1 $2 $3

echo "Check the .CSV files under $SCR_HOME/output_dir/ for results"

[root@jump-host ec2-user]#

-----------------------------------------------------------------------

Step - 14 Prepare the Truncate tables for Target Database [ MySQL ]

-----------------------------------------------------------------------

[root@mysql-target-server ec2-user]# pwd

/home/ec2-user

[root@mysql-target-server ec2-user]# mkdir Truncate_Scripts

[root@mysql-target-server ec2-user]# cd Truncate_Scripts/

[root@mysql-target-server Truncate_Scripts]# pwd

/home/ec2-user/Truncate_Scripts

(i) By using below SQL statement , to generate the truncate tables for all Databases ...

mysql -u root -p -s

SELECT CONCAT('TRUNCATE TABLE ',table_schema,'.',TABLE_NAME, ';') FROM INFORMATION_SCHEMA.TABLES where table_schema in ('dvdrental');

SELECT CONCAT('TRUNCATE TABLE ',table_schema,'.',TABLE_NAME, ';') FROM INFORMATION_SCHEMA.TABLES where table_schema in ('kidsdb');

SELECT CONCAT('TRUNCATE TABLE ',table_schema,'.',TABLE_NAME, ';') FROM INFORMATION_SCHEMA.TABLES where table_schema in ('migration_db');

SELECT CONCAT('TRUNCATE TABLE ',table_schema,'.',TABLE_NAME, ';') FROM INFORMATION_SCHEMA.TABLES where table_schema in ('mrsdb');

SELECT CONCAT('TRUNCATE TABLE ',table_schema,'.',TABLE_NAME, ';') FROM INFORMATION_SCHEMA.TABLES where table_schema in ('test_db');

SELECT CONCAT('TRUNCATE TABLE ',table_schema,'.',TABLE_NAME, ';') FROM INFORMATION_SCHEMA.TABLES where table_schema in ('blob');

(ii) Here is the all .sql files for each database trncate scripts.

[root@mysql-target-server Truncate_Scripts]# pwd

/home/ec2-user/Truncate_Scripts

[root@mysql-target-server Truncate_Scripts]# ls -ltr

-rw-r--r--. 1 root root 858 Sep 1 16:50 truncate_dvdrental.sql

-rw-r--r--. 1 root root 87 Sep 1 16:54 kidsdb.sql

-rw-r--r--. 1 root root 127 Sep 1 16:55 migration_db.sql

-rw-r--r--. 1 root root 249 Sep 1 16:56 test_db.sql

-rw-r--r--. 1 root root 88 Sep 1 16:56 blob.sql

-rw-r--r--. 1 root root 34615 Sep 1 16:58 mrsdb.sql

[root@mysql-target-server Truncate_Scripts]#

Sample truncate sql statements are :-

[root@mysql-target-server Truncate_Scripts]# cat test_db.sql

SET FOREIGN_KEY_CHECKS=0;

TRUNCATE TABLE test_db.comments;

TRUNCATE TABLE test_db.genres;

TRUNCATE TABLE test_db.kids_audit;

TRUNCATE TABLE test_db.kids_name;

TRUNCATE TABLE test_db.posts;

TRUNCATE TABLE test_db.users;

SET FOREIGN_KEY_CHECKS=1;

[root@mysql-target-server Truncate_Scripts]#

Possible Error while Creating the tables / Truncating the Data :-

---------------------------------------------------------------------

Error like: ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails

Solution :- Disable Foreign Key: -------> SET FOREIGN_KEY_CHECKS=0;

Enable Foreign Key: -------> SET FOREIGN_KEY_CHECKS=1;

log_bin_trust_function_creators = OFF should enable while creating functions [ SET GLOBAL log_bin_trust_function_creators = ON; ]

set global local_infile=true; [ By default it will be OFF ]

---------------------------------------------------------------------

Step - 15 Run the Create Schema / Create DDL Objectivies [ Tables , Views , SP , Trigger , Functions ..etc in Target Database [ MySQL ]

--------------------------------------------------------------------

The below folder i prepared all DDL's and validation scripts in my jump host server like this ....

[root@jump-host Final_Scripts]# pwd

/home/ec2-user/Final_Scripts

[root@jump-host Final_Scripts]# ls -ltr

drwxrwxr-x. 2 ec2-user ec2-user 130 Sep 2 02:00 Truncate_Scripts

drwxrwxr-x. 2 ec2-user ec2-user 256 Sep 2 02:03 All_Schema_Objectives_DDL_Databases

drwxrwxr-x. 2 ec2-user ec2-user 52 Sep 2 02:07 Validation_Scripts

[root@jump-host Final_Scripts]# cd Truncate_Scripts/

[root@jump-host Truncate_Scripts]# pwd

/home/ec2-user/Final_Scripts/Truncate_Scripts

[root@jump-host Truncate_Scripts]# ls -ltr

-rw-rw-r--. 1 ec2-user ec2-user 858 Sep 1 16:50 truncate_dvdrental.sql

-rw-rw-r--. 1 ec2-user ec2-user 87 Sep 1 16:54 kidsdb.sql

-rw-rw-r--. 1 ec2-user ec2-user 127 Sep 1 16:55 migration_db.sql

-rw-rw-r--. 1 ec2-user ec2-user 249 Sep 1 16:56 test_db.sql

-rw-rw-r--. 1 ec2-user ec2-user 88 Sep 1 16:56 blob.sql

-rw-rw-r--. 1 ec2-user ec2-user 34615 Sep 1 16:58 mrsdb.sql

[root@jump-host Truncate_Scripts]#

[root@jump-host Final_Scripts]# cd All_Schema_Objectives_DDL_Databases/

[root@jump-host All_Schema_Objectives_DDL_Databases]# pwd

/home/ec2-user/Final_Scripts/All_Schema_Objectives_DDL_Databases

[root@jump-host All_Schema_Objectives_DDL_Databases]# ls -ltr

-rw-rw-r--. 1 ec2-user ec2-user 2637 Aug 31 01:27 create_schemas.sql

-rw-rw-r--. 1 ec2-user ec2-user 1178 Aug 31 01:29 blob_db_ddl_table_function.sql

-rw-rw-r--. 1 ec2-user ec2-user 354 Aug 31 01:45 kidsdb_db_ddl_table.sql

-rw-rw-r--. 1 ec2-user ec2-user 4458 Aug 31 01:49 test_db_ddl_table.sql

-rw-rw-r--. 1 ec2-user ec2-user 1984 Aug 31 02:03 migration_db_ddl_table.sql

-rw-rw-r--. 1 ec2-user ec2-user 1817925 Aug 31 02:07 mrsdb_tables_views_ddl.sql

-rw-rw-r--. 1 ec2-user ec2-user 30362 Aug 31 02:18 dvdrental_tables_views_functions_sequences_ddl.sql

[root@jump-host All_Schema_Objectives_DDL_Databases]#

[root@jump-host Final_Scripts]# cd Validation_Scripts/

[root@jump-host Validation_Scripts]# pwd

/home/ec2-user/Final_Scripts/Validation_Scripts

[root@jump-host Validation_Scripts]# ls -ltr

-rwxr-xr-x. 1 root root 2820 Sep 1 15:30 row_count_comparison_final_consider.sh

[root@jump-host Validation_Scripts]#

(i) If we have any sample data in target Database [ MySQL ] Please truncate the data in databases .. by using below method ..

[root@jump-host Truncate_Scripts]# pwd

/home/ec2-user/Final_Scripts/Truncate_Scripts

[root@jump-host Truncate_Scripts]# ls -ltr

-rw-rw-r--. 1 ec2-user ec2-user 858 Sep 1 16:50 truncate_dvdrental.sql

-rw-rw-r--. 1 ec2-user ec2-user 87 Sep 1 16:54 kidsdb.sql

-rw-rw-r--. 1 ec2-user ec2-user 127 Sep 1 16:55 migration_db.sql

-rw-rw-r--. 1 ec2-user ec2-user 249 Sep 1 16:56 test_db.sql

-rw-rw-r--. 1 ec2-user ec2-user 88 Sep 1 16:56 blob.sql

-rw-rw-r--. 1 ec2-user ec2-user 34615 Sep 1 16:58 mrsdb.sql

[root@jump-host Truncate_Scripts]# mysql -u root -p -h 3.90.66.114 < truncate_dvdrental.sql

Enter password:

[root@jump-host Truncate_Scripts]#

[root@jump-host Truncate_Scripts]# mysql -u root -p -h 3.90.66.114 < kidsdb.sql

Enter password:

[root@jump-host Truncate_Scripts]#

[root@jump-host Truncate_Scripts]# mysql -u root -p -h 3.90.66.114 < test_db.sql

Enter password:

[root@jump-host Truncate_Scripts]#

[root@jump-host Truncate_Scripts]# mysql -u root -p -h 3.90.66.114 < migration_db.sql

Enter password:

[root@jump-host Truncate_Scripts]#

[root@jump-host Truncate_Scripts]# mysql -u root -p -h 3.90.66.114 < mrsdb.sql

Enter password:

[root@jump-host Truncate_Scripts]#

[root@jump-host Truncate_Scripts]# mysql -u root -p -h 3.90.66.114 < blob.sql

Enter password:

[root@jump-host Truncate_Scripts]#

Till now we truncated all tables data if we have any in target database ...

(ii) Creating databases / Schemas in Target Database MySQL :-