On RTA production server ------------------------------ * export pland data from postgres ssh webr@108.61.149.14 PGPASSWORD="pland" pg_dump --clean -U pland pland | gzip > /home/webr/db_backup/database_backup/pland_prod01-06-2022.psql.gz PGPASSWORD=pland pg_dump -t "^migrate*" --clean -U pland -f /home/hradmin/rta2.0/local/pland_prod04-06-2022.psql prod_rta gzip /home/hradmin/rta2.0/local/pland_prod04-06-2022.psql scp -P 2222 /home/webr/db_backup/database_backup/pland_prod01-06-2022.psql.gz hradmin@162.55.94.68:/home/hradmin/rta2.0/ On RTA2.0 Production server ------------------------------ cd /home/hradmin/rta2/ PGPASSWORD=pland pg_dump -d prod_rta -U pland --no-owner -t "^migrate*" -f /home/hradmin/rta2.0/local/local_psql_pland_prod04-06-2022.psql gzip /home/hradmin/rta2.0/local/local_psql_pland_prod04-06-2022.psql mysqldump -uroot -p prod_rta | gzip > /home/hradmin/rta2.0/local/local_mysql_prod_rta_04-06_2022.sql.gz gzip /home/hradmin/rta2.0/local/local_mysql_prod_rta_02-06_2022.sql mysql -uroot -p prod_rta < /var/www/html/prod_rta/sql/clean-up.sql -- su - postgres psql \c prod_rta DROP SCHEMA public CASCADE; CREATE SCHEMA public; GRANT ALL ON SCHEMA public TO postgres; GRANT ALL ON SCHEMA public TO public; -- PGPASSWORD=pland psql -d prod_rta -U pland -f /var/www/html/prod_rta/sql/pgdump_migrate_tables.sql gunzip /home/hradmin/rta2.0/pland_prod01-06-2022.psql.gz PGPASSWORD=pland psql -d prod_rta -U pland -f /home/hradmin/rta2.0/pland_prod01-06-2022.psql PGPASSWORD=pland psql -d prod_rta -U pland -f /var/www/html/prod_rta/sql/psql_migration_prep.sql PGPASSWORD=pland psql -d prod_rta -U pland -f /var/www/html/prod_rta/sql/create_invalid_vehicle.sql PGPASSWORD=pland psql -d prod_rta -U pland -f /var/www/html/prod_rta/sql/create_valid_vehicle.sql PGPASSWORD=pland psql -d prod_rta -U pland -f /var/www/html/prod_rta/sql/create_migrate_payment.sql PGPASSWORD=pland psql -d prod_rta -U pland -f /var/www/html/prod_rta/sql/create_migrate_title_transactions.sql PGPASSWORD=pland psql -d prod_rta -U pland -f /var/www/html/prod_rta/sql/create_migrate_tax_transactions.sql Open rta2.0 app in web browser and run the migration job https://rta2.puntlandgov.org/en/login https://rta2.puntlandgov.org/en/migrate?all mysql -uroot -p prod_rta < /var/www/html/prod_rta/storage/app/migrate_title.sql mysql -uroot -p prod_rta < /var/www/html/prod_rta/storage/app/migrate_tax.sql -- cleanups @run cleanup_vehicle_color.sql @add unknown make INSERT INTO `make` (`id`, `name`, `created_by`, `updated_by`, `created_at`, `updated_at`) VALUES ('999', 'UNKNOWN', '1', '1', '2022-06-04 13:06:42', '2022-06-04 13:06:42'); @run cleanup_vehicle_model.sql delete from vehicle_color where id not in (select distinct vehicle_color_id from vehicle); delete from make where id not in (select distinct make_id from model); delete from model where id not in (select distinct model_id from vehicle); SET FOREIGN_KEY_CHECKS=0;truncate transaction;truncate party_vehicle;truncate party_vehicle_purchase;truncate invoice;truncate payment;truncate invoice_group;truncate invoice_item;truncate invoice_logs;truncate invoice_payment;truncate transaction_workflow;truncate vehicle_tax;truncate vehicle_title; SET FOREIGN_KEY_CHECKS=1; mysql -uroot -p prod_rta < /var/www/html/prod_rta/sql/create_migration_party_vehicle.sql ---- * the database backup will be generated in /home/webr/db_backup/database_backup folder * two part migration process - for tax and for all non tax data migration * for non tax - copy the pland_prod*.psql.gz to rta2.0 production server * for tax - 1) extract the pland_prod*.psql.gz file 2) convert the postgres to mysql 3) copy the converted mysql to rta2.0 production server -- tax migration steps cd /home/webr/db_backup/database_backup/ gunzip pland_prod01-06-2022.psql.gz php /home/webr/rta2.0/pg2mysql_cli.php pland_prod01-06-2022.psql mysql_pland_prod01-06-2022.sql MyISAM gzip mysql_pland_prod01-06-2022.sql scp -P 2222 mysql_pland_prod01-06-2022.sql.gz hradmin@162.55.94.68:/home/hradmin/rta2.0/ ----- -- tax migration steps cd /home/hradmin/rta2/ gunzip mysql_pland_prod01-06-2022.sql.gz mysql --force -uroot -p rta_migration < /var/www/html/prod_rta/sql/rta_migration_schema_only.sql pv /home/hradmin/rta2.0/mysql_pland_prod01-06-2022.sql | mysql --force -uroot -p rta_migration -- on postgres sql PGPASSWORD=pland psql -P pager=off -d prod_rta -U pland -f /var/www/html/prod_rta/sql/tax-migration-generation-script.sql > tax_migrated_data.sql mysql --force -uroot -p prod_rta < tax_migrated_data.sql > tax_migrated_data.sql.out -- import the msyql and then run online migration for all data except tax -- for tax migration run the sql below PGPASSWORD=pland pg_dump -t "^migrate_tax_transaction" --clean -U pland -f /home/hradmin/rta2.0/local/pland_prod04-06-2022.psql prod_rta