Location Research Breakthrough Possible @S-Logix pro@slogix.in

Office Address

Social List

Cloud migration and database modernisation of a legacy manufacturing inventory and warehouse management application

Description

This project is about moving a legacy manufacturing inventory and warehouse management application from an on-premises server to Cloud and modernizing its database. The existing application is built using Python and uses an old proprietary database. The application will be moved to cloud, and the old database will be converted and migrated. The application will then be connected to the new PostgreSQL database.

Aim

To migrate the existing inventory application from the on-premises server to cloud and modernize its old database by moving it to PostgreSQL 14 on an cloud EC2 server. The project will also use automation tools to configure the system and monitoring tools to track application and database performance.

Objectives

01 Check the existing server, database, storage, and system performance.
02 Create a secure cloud network and required cloud infrastructure.
03 Convert the old database structure to PostgreSQL 14.
04 Migrate the existing inventory and warehouse data to PostgreSQL.
05 Configure the application to connect to the new PostgreSQL database.
06 Automate server and application configuration.
07 Monitor database performance, CPU, memory, and system health.

Application Workflow

01

Material Receiving

Process

Raw materials and components are received from suppliers. The received quantity and delivery information are recorded and verified before the materials proceed for inspection.

Tools
Apache PostgreSQL 14
Implementation

Warehouse users access the receiving function through the web interface. Nginx receives incoming requests on the EC2 instance and forwards them to the application. Receiving details and quantities are stored in PostgreSQL 14.

02

Material Inspection

Process

Received materials are checked for quantity, condition, and quality. Accepted materials are approved for inventory, while rejected materials are recorded separately.

Tools
PostgreSQL 14
Implementation

Inspection results and material acceptance information are processed on the EC2-hosted application and stored in PostgreSQL 14.

03

Inventory Management

Process

Accepted materials are added to available inventory. Stock quantities are updated whenever materials are received, issued, transferred, or adjusted.

Tools
PostgreSQL 14
Implementation

Item information, available quantities, and inventory transactions are stored in PostgreSQL 14 on the EC2 instance.

04

Warehouse Storage

Process

Materials are assigned to warehouse locations such as warehouses, zones, racks, and bins. This allows warehouse staff to identify and retrieve materials efficiently.

Tools
PostgreSQL 14
Implementation

Warehouse locations and the corresponding stock information are maintained in PostgreSQL 14 on the EC2 instance.

05

Material Issue to Production

Process

The production team requests required materials. Available stock is checked and the required quantities are issued from the warehouse to production.

Tools
PostgreSQL 14
Implementation

The application checks available inventory from PostgreSQL 14. After materials are issued, the inventory quantity and issue transaction are updated in the database.

06

Stock Transfer

Process

Materials are transferred between warehouse locations when required. The source location, destination location, and transferred quantity are recorded.

Tools
PostgreSQL 14
Implementation

Stock-transfer transactions are processed on the EC2-hosted application and stored in PostgreSQL 14. Source and destination stock quantities are updated accordingly.

07

Finished Goods Receipt

Process

After manufacturing is completed, finished products are received from production and added to the finished-goods inventory.

Tools
PostgreSQL 14
Implementation

Finished-product quantities, product information, and receipt transactions are stored in PostgreSQL 14 on the EC2 instance.

08

Picking and Dispatch

Process

Finished products are picked from warehouse locations according to customer or dispatch requirements and prepared for delivery.

Tools
Apache PostgreSQL 14 REST APIs
Implementation

Warehouse users access the picking and dispatch functions through Nginx. Picking and dispatch transactions are stored in PostgreSQL 14. REST APIs can exchange dispatch or shipment information with external logistics or transportation systems.

09

Inventory Reconciliation

Process

Physical stock is compared with the quantities recorded in the inventory records. Differences are investigated and approved adjustments are recorded.

Tools
PostgreSQL 14
Implementation

Physical-count results, discrepancies, and approved inventory adjustments are stored in PostgreSQL 14 on the EC2 instance.

10

Monitoring and Reporting

Process

Inventory and warehouse operations are reviewed through reports, while infrastructure performance is monitored to identify operational or server-related problems.

Tools
Prometheus Node Exporter Grafana
Implementation

Node Exporter runs on the EC2 instance and collects CPU, memory, disk, and network metrics. Prometheus collects and stores these metrics, while Grafana displays them through monitoring dashboards.

Current Infrastructure

Existing on-premises infrastructure and technologies used before migration.

Existing On-Premises Environment

Application Hosting On-Premises Server

Hosts the legacy manufacturing inventory and warehouse management application, PostgreSQL database, application files, configuration files, and supporting services. The application currently depends on a single physical server.

Operating System Ubuntu Server 24.04 LTS

Provides the operating environment for the manufacturing inventory and warehouse management application, web server, PostgreSQL database, and supporting services.

Web Server Apache HTTP Server

Receives incoming HTTP/HTTPS requests and forwards them to the manufacturing inventory and warehouse management application. It acts as the web-facing entry point for users and connected systems.

Database Server PostgreSQL 14

Stores product, material, inventory, warehouse, stock movement, and transaction data used by the manufacturing inventory and warehouse management application.

Storage Local SSD/HDD with ext4

Provides persistent local storage for the operating system, application files, PostgreSQL database files, configuration files, and logs.

Network On-Premises LAN using TCP/IP

Provides communication between users, the application, PostgreSQL database, and connected manufacturing or business systems within the organization's network.

Security UFW + Linux PAM

UFW controls network access to the server, while Linux PAM manages user authentication and system access permissions.

Process Management systemd

Starts, stops, and manages the application, Apache HTTP Server, PostgreSQL, and supporting services.

Logging Apache Access/Error Logs + Application Logs + PostgreSQL Logs

Records web requests, application events, database activities, system events, and errors for troubleshooting and operational analysis.

Monitoring Prometheus + Node Exporter + Grafana

Monitors server resources and application infrastructure, including CPU, memory, disk usage, network activity, and service availability.

Backup pgBackRest

Performs PostgreSQL database backup and recovery to protect manufacturing inventory and warehouse data and support restoration in case of data loss or server failure.

External Integration REST APIs

Enables the application to communicate with external manufacturing and business systems for exchanging inventory, product, material, warehouse, and transaction-related information.

Target Infrastructure

Cloud-hosted infrastructure and technologies used after migration and modernization.

Cloud-Hosted Modernized Environment

Database Engine PostgreSQL 14

Replaces the existing legacy database with PostgreSQL 14. The required inventory, product, material, warehouse, stock movement, and transaction data is migrated into PostgreSQL.

Operating System Ubuntu Server 24.04 LTS

The application continues to use the same Linux operating system, now running on Cloud.

Process Management Systemd

Starts, stops, and manages the application and PostgreSQL services on the Cloud server.

Security and Access Linux PAM

Manages SSH access, user accounts, and permissions.

System Logging Journald

Collects application, database, and system logs.

Network and Firewall TCP/IP + UFW

The application uses port 8080, PostgreSQL uses port 5432, and UFW controls allowed network traffic.

Networking Cloud VPC

Provides the isolated Cloud network environment for the application and database. Subnets and routing are configured to control communication between the required resources

Cloud Compute Cloud EC2

Provides the virtual server where the migrated application, Ubuntu Server, and PostgreSQL 14 are hosted.

Storage Cloud EBS

Provides persistent block storage attached to the EC2 instance for the operating system, application files, PostgreSQL database files, configuration files, and logs

Deployment Location Cloud Environment

The migrated application and PostgreSQL 14 database run on Cloud EC2 inside the Cloud VPC, with Cloud EBS providing persistent storage.

Network Discovery Tool Nmap

Scans the existing network to find active servers, open ports, and services before migration.

Infrastructure Inventory Tool Open-AudIT

Collects information about the existing server hardware and installed software.

System Monitoring and Audit Tool osquery

Checks running processes, user accounts, and installed software.

Infrastructure as Code Tool OpenTofu

Creates and manages cloud resources such as the VPC, EC2 server, and EBS storage using code.

Configuration Automation Tool Ansible

Automatically configures the cloud server, installs required packages, and updates application settings.

Monitoring Tool Prometheus

Collects performance information such as CPU, memory, disk usage, and application metrics.

Monitoring Dashboard Grafana

Displays the monitoring information in visual dashboards.

Migration Tool Cloud MGN

Migrates the existing on-premises server and its data to cloud.

Implementation Process

01
Assess the Existing On-Premises Infrastructure
  • Use Nmap to identify active systems, open ports, and network services on the existing on-premises server.
  • Use Open-AudIT to collect information about the physical server, including hardware, operating system, storage, and installed software.
  • Use osquery to verify running services, processes, users, packages, and system configuration.
  • Review the existing legacy database, Systemd, ext4 storage, PAM, UFW, Journald, and port 8080 configuration to identify migration requirements.
  • Record CPU, memory, storage, network usage, and database usage to establish a baseline before migration.
02
Create the Cloud Target Infrastructure
  • Use OpenTofu to define the required Cloud infrastructure.
  • Create the Cloud VPC and required subnets for the target environment.
  • Create the Cloud EC2 instance with appropriate compute resources for the application and PostgreSQL 14.
  • Create and attach Cloud EBS storage for the operating system, application files, and database data.
  • Configure Cloud Security Groups and UFW to control network access to the Cloud EC2 environment.
03
Replicate and Migrate the Existing Environment
  • Install the cloud MGN agent on the existing on-premises server.
  • Use cloud MGN to continuously replicate the existing server data and required application files to the Cloud environment.
  • Monitor the replication process and verify that the source server remains synchronized with the Cloud staging environment.
  • Launch a test Cloud EC2 instance from the replicated server for migration validation.
  • Verify the migrated Ubuntu Server 24.04 LTS, application files, Systemd services, and storage before proceeding with database modernisation.
04
Modernise the Database and Configure the Cloud Environment
  • Prepare PostgreSQL 14 on the Cloud EC2 environment as the target database engine.
  • Export the required inventory, product, warehouse, stock movement, and transaction data from the legacy database.
  • Convert and load the required data into PostgreSQL 14, while validating tables, records, relationships, and data integrity.
  • Use Ansible to configure PostgreSQL 14, Systemd services, UFW, application settings, and required server configuration.
  • Verify the complete flow between the application, PostgreSQL 14, Cloud EBS, and Cloud EC2.
05
Validate, Monitor, and Complete the Cutover
  • Stop or restrict new transactions on the on-premises server during the final migration window.
  • Allow cloud MGN to complete the final replication and launch the final workload on the Cloud EC2.
  • Configure Node Exporter to expose Cloud EC2 CPU, memory, disk, and network metrics.
  • Use Prometheus to scrape and store the metrics, and Grafana to display them through monitoring dashboards.
  • Update the DNS record to direct users and connected systems to the Cloud Cloud environment, then validate the application, PostgreSQL 14, security, monitoring, and database operations before retiring the on-premises server.

Proposed Solution

The proposed solution moves the manufacturing inventory and warehouse application from the on-premises server to cloud. cloud MGN moves the existing application server to cloud, while OpenTofu creates the required cloud infrastructure. Ansible configures the migrated server. The Python application is mostly kept the same, but the old legacy database is replaced with PostgreSQL 14. The existing database data is converted and moved into PostgreSQL. Prometheus and Grafana are used to monitor the new cloud environment. This gives the company a modern database, less dependency on physical hardware, easier management, and better monitoring.

Benefits

Lower database licensing costs: PostgreSQL is open-source, so there are no proprietary database license fees.
Less hardware dependency: The application no longer depends on the physical factory server.
Automated setup: OpenTofu and Ansible reduce manual configuration work.
Better monitoring: Prometheus and Grafana show CPU, memory, disk, and database performance.
Flexible storage: cloud EBS storage can be increased when more storage is required.
Modern database: PostgreSQL provides a more flexible and widely supported database platform.

Challenges

Database conversion: The old database structure must be correctly converted to PostgreSQL without losing data.
Application compatibility: The Python application may need changes to work with the new PostgreSQL database.
Network changes: Warehouse systems and barcode scanners may need to connect to the new cloud endpoint.
Cloud costs: Large EC2 instances and other cloud resources can increase monthly costs.
Migration downtime: The final cutover must be carefully planned to reduce disruption.
Data validation: The migrated database must be checked to make sure all inventory and warehouse data is correct.
← Back to Project Titles