Migrating from MySQL 8 to PostgreSQL 17 on Ubuntu 24.04 LTS

inchirags@gmail.com 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%';

Step 7: Flush Privileges (Optional)

After changing the password, you can flush the privileges to ensure that all changes take effect:

FLUSH PRIVILEGES;

Step 8: Exit MySQL

To exit the MySQL shell, run:

EXIT;

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', 'inchirags@gmail.com'), ('Purab Kumar', 'purabapna@gmail.com');

Verify the data:

SELECT * FROM users;

exit

3. Configure mysqld.cnf

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

default_authentication_plugin = mysql_native_password

sudo systemctl restart mysql

4. 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

5. 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.