DEV Community

Cover image for Migrating from MySQL 8 to PostgreSQL 17 on Ubuntu 24.04 LTS
Chitt Ranjan Mahto (Chirag)
Chitt Ranjan Mahto (Chirag)

Posted on

Migrating from MySQL 8 to PostgreSQL 17 on Ubuntu 24.04 LTS

#mysql #postgressql #migration #database

inchirags@gmail.com Chirag's PostgreSQL DBA Tutorial https://www.chirags.in

 Migrating from MySQL 8 to PostgreSQL 17 on Ubuntu 24.04 LTS
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

Step 2: Install MySQL server.

sudo apt install mysql-server -y
Enter fullscreen mode Exit fullscreen mode

Step 3: Secure MySQL installation.

sudo mysql_secure_installation
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

Step 5: Log into MySQL.

sudo mysql -u root -p
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

After running this, restart the MySQL server:

sudo systemctl restart mysql
Enter fullscreen mode Exit fullscreen mode

Note:

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

SET GLOBAL validate_password.policy = LOW; SET GLOBAL validate_password.length = 4;
Enter fullscreen mode Exit fullscreen mode

-- Set a lower length if needed

Check the Changes:

SHOW VARIABLES LIKE 'validate_password%';
Enter fullscreen mode Exit fullscreen mode

Step 7: Flush Privileges (Optional)

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

FLUSH PRIVILEGES;
Enter fullscreen mode Exit fullscreen mode

Step 8: Exit MySQL

To exit the MySQL shell, run:

EXIT;
Enter fullscreen mode Exit fullscreen mode
  1. 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');
Enter fullscreen mode Exit fullscreen mode

Verify the data:

SELECT * FROM users; exit
Enter fullscreen mode Exit fullscreen mode
  1. Configure mysqld.cnf 
vi /etc/mysql/mysql.conf.d/mysqld.cnf default_authentication_plugin = mysql_native_password sudo systemctl restart mysql
Enter fullscreen mode Exit fullscreen mode
  1. 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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

Step 2: Start and enable PostgreSQL.

sudo systemctl start postgresql sudo systemctl enable postgresql
Enter fullscreen mode Exit fullscreen mode
  1. Create a Test Database in PostgreSQL

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

sudo -i -u postgres psql
Enter fullscreen mode Exit fullscreen mode

Change Password for the postgres User

ALTER USER postgres PASSWORD 'admin123';
Enter fullscreen mode Exit fullscreen mode

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

CREATE DATABASE test_db; exit exit
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

The command format for pgloader is as follows:

./build/bin/pgloader mysql://root:admin123@localhost/test_db postgresql://postgres:admin123@localhost/test_db
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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.

Top comments (0)