Migrating from MySQL 8 to PostgreSQL 17 on Ubuntu 24.04 LTS

[email protected]? Chirag's PostgreSQL DBA Tutorial https://www.chirags.in

************************************************************************************************

Migrating from MySQL 8 to PostgreSQL 17 on Ubuntu 24.04 LTS

************************************************************************************************

Here's a comprehensive, step-by-step guide to installing MySQL and PostgreSQL 17 on Ubuntu 24.04 LTS, creating test databases, and migrating data from MySQL to PostgreSQL.

1. Install MySQL on Ubuntu 24.04 LTS

Step 1: Update system packages.

sudo apt update && sudo apt upgrade -y        

Step 2: Install MySQL server.

sudo apt install mysql-server -y        

Step 3: Secure MySQL installation.

sudo mysql_secure_installation        

Follow the prompts to set up security options, such as removing anonymous users and disabling remote root logins.

Step 4: Start and enable MySQL.

sudo systemctl start mysql        

sudo systemctl enable mysql        

Step 5: Log into MySQL.

sudo mysql -u root -p        

Step 6: Change the Root Password

Once you are logged in to the MySQL shell, you can change the root password using the following command. Replace new_password with your desired password:

ALTER USER 'root'@'localhost' IDENTIFIED WITH 'mysql_native_password' BY 'admin123';        

FLUSH PRIVILEGES;        

After running this, restart the MySQL server:

sudo systemctl restart mysql        

Note:

Change the Password Policy Level (e.g., to low):

SET GLOBAL validate_password.policy = LOW;
SET GLOBAL validate_password.length = 4;         

-- Set a lower length if needed

Check the Changes:

SHOW VARIABLES LIKE 'validate_password%';        

2. Create a Test Database in MySQL

Once logged in to MySQL, run the following SQL commands:

mysql -u root -p
CREATE DATABASE test_db;
USE test_db;        

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    email VARCHAR(50)
);        

INSERT INTO users (name, email) VALUES ('Chirag Mahto', '[email protected]'), ('Purab Kumar', '[email protected]');        

Verify the data:

SELECT * FROM users;

exit        

2. Configure mysqld.cnf

vi /etc/mysql/mysql.conf.d/mysqld.cnf        

default_authentication_plugin = mysql_native_password        

sudo systemctl restart mysql        

3. Install PostgreSQL 17 on Ubuntu 24.04 LTS

Step 1: Update the package list and install PostgreSQL.

Since Ubuntu’s official repositories may not have PostgreSQL 17, we need to add the PostgreSQL Apt repository.

sudo apt install -y curl ca-certificates        

sudo install -d /usr/share/postgresql-common/pgdg        

sudo curl -o /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc --fail https://www.postgresql.org/media/keys/ACCC4CF8.asc        

Next, add the PostgreSQL repository:

sudo sh -c 'echo "deb [signed-by=/usr/share/postgresql-common/pgdg/apt.postgresql.org.asc] https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'        

sudo apt update        

sudo apt install postgresql -y        

Step 2: Start and enable PostgreSQL.

sudo systemctl start postgresql
sudo systemctl enable postgresql        

1. Create a Test Database in PostgreSQL

Step 1: Switch to the PostgreSQL user and change password.

sudo -i -u postgres        

psql        

Change Password for the postgres User

ALTER USER postgres PASSWORD 'admin123';        

Step 2: Create a new database and user, then connect to the new database.

CREATE DATABASE test_db;        

exit        

exit        

## Migrate Data from MySQL to PostgreSQL ##

To migrate data from MySQL to PostgreSQL, you can use the pgloader tool, which simplifies the migration process.

Step 1: Install pgloader.

git clone https://github.com/dimitri/pgloader.git        

apt-get install sbcl unzip libsqlite3-dev make curl gawk freetds-dev libzip-dev        

cd pgloader        

make pgloader        

./build/bin/pgloader --help        

The command format for pgloader is as follows:

./build/bin/pgloader mysql://root:admin123@localhost/test_db postgresql://postgres:admin123@localhost/test_db        

Replace password with the actual MySQL and PostgreSQL passwords. This command will copy the data and structure from MySQL to PostgreSQL.

Step 2: Verify Data in PostgreSQL

After the migration, log into PostgreSQL and check the test_db database to verify the migrated data:

sudo -i -u postgres        

psql -d test_db        

SELECT * FROM users;        

Summary

MySQL Installation: Installed and configured a test database.

PostgreSQL Installation: Set up PostgreSQL 17, created a test database, and verified table structure.

Migration with pgloader: Moved data from MySQL to PostgreSQL, ensuring data integrity.

Let me know if you'd like further customization or have any questions!

For any doubts and query, please write on YouTube video comments section.

Note : Flow the Process shown in video.

??Please, Subscribe and like for more videos:

https://youtube.com/@chiragstutorial

??Don't forget to, ??Follow, ??Like, ??Share ??&, Comment

Thanks & Regards,

Chitt Ranjan Mahto "Chirag"

_________________________________________________________________________________________

Note: All scripts used in this demo will be available in our website.

Link will be available in description.