inchirags@gmail.com Chirag's PostgreSQL DBA Tutorial https://www.chirags.in
*************************************************************************************************
* Migrating from MySQL 8 to PostgreSQL 17 on Ubuntu 24.04 LTS *
*************************************************************************************************
YouTube Video:
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', 'inchirags@gmail.com'), ('Purab Kumar', 'purabapna@gmail.com');
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
Tutorial Link :
https://www.chirags.in/tutorials/PostgreSQL_Database
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.
#postgres
#postgresql
#pointintimerecovery
#postgresqlTutorial
#pg_basebackup
#chiragstutorial
#chiragsdatabasetutorial
#database
#psql
#pgbouncer
#patroni
#pgpool
#pgpool2
#pgpoolII
#replication
Chirags Tutorial,Chirags PostgreSQL Database Tutorial,Postgresql,PostgreSQL Point in Time Recovery,pg_basebackup in PostgreSQL,WAL,Write Ahead Log (WAL),Chirags Database Tutorial,PostgreSQL Database Migration,PostgreSQL Database Upgradation,Upgrade PostgreSQL 14 to PostgreSQL 16,PostgreSQL Installation,PostgreSQL Database Install in Ubuntu 22.04 LTS,PostgreSQL Database Install in Linux,PostgreSQL Replication,PgBouncer,Patroni,PgBouncer to improve performance
