MIGRATION
MySQL
POSTGRESQL

Migrating Data Between Databases Using DbVisualizer

intro

In this article, we tell you how to migrate data between databases (a source database and a target database) while making use of DbVisualizer’s import and export features.

Tools used in the tutorial
Tool Description Link
Dbvisualizer DBVISUALIZER
TOP RATED DATABASE MANAGEMENT TOOL AND SQL CLIENT

Data importing and exporting are important in database development and data analysis because they allow for the integration of data from various originations, making it possible to work with a larger and more diverse set of data. This can be crucial in many fields, such as business, where data from multiple sources is needed to make well-informed decisions.

Data import is the process of bringing in data from an external source into a software system. Data import can involve a variety of exercises, such as converting data into a compatible format, cleaning and transforming data, loading data into a database, etc.

Data export on the other hand, is the process of taking data that is currently stored within a software program or system and conveying it to an external destination.

Setting Up

For the purpose of this article, we’re going to utilize MySQL and PostgreSQL database management systems. To set up both database connections, we will need to first install both DBMS systems and then connect them to DbVisualizer.

Setting Up MySQL

To set up MySQL, navigate to the MySQL download page to download the recommended installer for your operating system. Use these instructions: Windows instructionmacOS instructionLinux instruction to help you install MySQL on Windows, macOS, and Linux operating systems respectively.

Setting Up Postgres

To install Postgres, navigate to the Postgres download page and download the recommended installer for your operating system. You can follow this tutorial for installation on macOS, this installation tutorial to help you install Postgres on a Windows operating system, and this tutorial for installation on a Linux operating system.

Setting Up DbVisualizer

To install DbVisualizer, navigate to the DbVisualizer download page to download the recommended installer for your operating system.

Run the download installer to install DbVisualizer on your computer. Open DbVisualizer once it has been installed, and you will be prompted to create a database connection.

Connecting to Postgres

Step 1: Run the command below in the terminal of your computer to confirm the successful installation of the Postgres on your computer.

Copy
        
$ psql -U postgres

When the prompt opens, set a password for PostgreSQL from the prompt given.

Now, let us create a database called CPIforecast which will hold records of the average price change over time of consumer goods and services. Run the command below in the terminal of your computer to create the database.

Copy
        
$ CREATE DATABASE cpiforecast;

To check if the database has been created successfully in the list of databases, run the command below.

Copy
        
$ \list

You can verify that the CPIforecast database has been created.

A view of the newly created Postgres database.
A view of the newly created Postgres database.

Step 2: Start the DbVisualizer software and click on the “Create a Connection” button.

Creating a database connection in DbVisualizer.
Creating a database connection in DbVisualizer.
Searching and selecting Postgres driver in DbVisualizer
Searching and selecting Postgres driver in DbVisualizer

Step 3: Fill in the database connection input fields with the appropriate credentials.

  • Name: CPI Forecast
  • Database: cpiforecast
  • Database Userid: postgres
  • Database Password: Your Postgres password

Click on “Connect” after filling the required fields to successfully connect to the database.

Object view tab for the Postgres connection.
Object view tab for the Postgres connection.

Connecting to MySQL

To create your MySQL database connection, open the MySQL window and follow the steps below.

Step 1: Click on the “Start MySQL Server” button and enter your operating user password to start the server.

Step 2: Open the terminal window of your computer and run the command below to open the MySQL prompt.

Copy
        
$ mysql -u root -p

When the prompt opens, enter the password used during the MySQL installation process.

Step 3: Now, let us create a database called ‘CPIforecast Annex’ by running the command below in the terminal and pressing ‘enter’.

Copy
        
$ CREATE DATABASE cpiforecastannex;

To confirm if the database has been created, run the command below.

Copy
        
$ SHOW DATABASES;

We can see that the “cpiforecastannex” database has been created.

A view of the newly created MySQL database.
A view of the newly created MySQL database.

Step 4: Let us now connect the “cpiforecastannex” created to DbVisualizer. Under the “Databases” tab in the DbVisualizer window, click on the “create new database connection button”. Search and select ‘MySQL 8’ from the list of drivers.

Creating the new database connection.
Creating the new database connection.

Step 5: Fill in the database connection input fields with the appropriate credentials.

  • Name: ‘CPI forecast Annex’
  • Database: ‘cpiforecastannex’
  • Database Userid: ‘root’
  • Database Password: password for root user.

Click on “Connect” after filling the required fields to successfully connect to the database.

Object view tab for MySQL connection.
Object view tab for MySQL connection.

Now, you have more than one database connected to DbVisualizer.

Importing Data Table Into Postgres Using DbVisualizer

Before you can migrate data from one database to another, you will typically have to import data into one database before migrating it to another. The process of migration typically involves extracting data from the source database, transforming it if necessary, and then loading it into the target database.

Step 1: Navigate to this CPI Forecast file and download the dataset.

Step 2: Next, Open the database connection CPI Forecast tab tree as shown below.

Opening database connection tab tree.
Opening database connection tab tree.

Open the database tab tree.

Opening CPI Forecast database tab tree.
Opening CPI Forecast database tab tree.

Inside the Databases tab tree, open the cpi forecast database tab tree.

Opening the cpi forecast tab tree.
Opening the cpi forecast tab tree.

Inside the cpi forecast database tab tree, open the schemas tab tree.

Opening the schemas tab tree.
Opening the schemas tab tree.

Inside the schemas tab tree, open the public tab tree.

Opening the public tab tree.
Opening the public tab tree.

Step 3: Right-click on the ‘Tables’ and select ‘Import Table Data’ from the menu. Navigate to the location of the downloaded CPI forecast data set on your local computer and click on “open.”

Selecting and opening CPI Forecast data file
Selecting and opening CPI Forecast data file.

Step 4: Click on “Next” until you get to the final import window as shown below.

Making necessary effects in the final import window.
Making necessary effects in the final import window.

Change the table name to “CPIForecastData.” Since SQL does not allow spaces in table names, let us rename the column name to something with no spaces by omitting all spaces in the “Changes in Consumer Price Indexes for food, 2020 through 2023” column name.

Step 5: Click on the ‘Next’ button and the ‘import’ button to import the table data into the Postgres database.

Importing CPI Forecast table data.
Importing CPI Forecast table data.

You should see success in the import log as shown below.

Import success.
Import success.

Once it has been imported, right-click on the table tab and click on the “Refresh Objects Tree” option. Click on the Data tab to see the table data as shown below.

Imported CPI Forecast data table.
Imported CPI Forecast data table.

Importing the Data From PostgreSQL Into MySQL

Since both Postgres and MySQL databases are connected, let us navigate to the schema and table we want to export from in the Postgres database. In this case, our table is ‘cpiforecastdata.’

Step 1: Right-click on the table and select “Export Data” from the context menu.

Exporting table data into MySQL from Postgres.
Exporting table data into MySQL from Postgres.

Step 2: In the Export Wizard, select the ‘SQL’ as the output format, select ‘SQL commander’ as the output destination and check the ‘GENERATE CREATE’ option.

Implementing required settings.
Implementing required settings.

Step 3: Click on the export button and close the export window. This will open up the SQL commander as shown below.

Queries for creating tables in the target database.
Queries for creating tables in the target database.

Step 4: Since we want to export the data to another database, let us select the target database connection. In this case, our target database (MySQL) is named as “CPI Forecast Annex”

Selecting connection where you want to export the table data.
Selecting connection where you want to export the table data.

Step 5: From the DbVisualizer menu bar, click on ‘SQL Commander’ and select Execute to execute the SQL commands. You should see success in the DbVisualizer log.

Export log showing table data export success.
Export log showing table data export success.

Once it has been exported, navigate to the MySQL database tree, locate the table tab tree, right click on the table tab and refresh to see the exported table as shown below.

Exported table data in MySQL.
Exported table data in MySQL.

We can see that the table data we imported into our Postgres database has been successfully exported into our MySQL database. Migrating data between databases is an important step in data analysis because it allows for the integration of data from multiple sources. This can be useful in a variety of ways, such as: data warehousing, data integration, data distribution, data replication. The ability to migrate data between databases is critical for organizations that need to make sense of large and complex data sets. It allows them to easily integrate and analyze data from different sources, which in turn can lead to better decision-making, improved efficiency, and increased competitiveness.

Dbvis download link img

Conclusion

In this blog, we have learnt how to migrate data from one database to another while making use of DbVisualizer. This is an important factor in data analysis for organizations that make sense of large and complex data. We hope you enjoyed working with DbVisualizer.

If you're looking for an all-in-one database management tool, look no further than DbVisualizer. With its easy-to-use interface and powerful features, you can easily connect to and manage multiple databases, including MySQL, PostgreSQL, Oracle, and more. Plus, with its free trial and free version options, there's no risk in giving it a try. Upgrade your database management game with DbVisualizer. Try it out today!

About the author
Leslie S. Gyamfi.
Leslie S. Gyamfi
Leslie Gyamfi is a mobile/web app developer with a passion for creating innovative solutions. He is dedicated to delivering high-quality products and technical articles. You can connect with him on LinkedIn
The Table Icon
Sign up to receive The Table's roundup
More from the table
Title Author Tags Length Published
title

5 Ways to Split a String in PostgreSQL

author Antonello Zanini tags POSTGRESQL Split 6 min 2024-02-29
title

MySQL IFNULL – Everything You Need to Know

author Leslie S. Gyamfi tags IFNULL MySQL 6 min 2024-02-26
title

PostgreSQL CASE: A Comprehensive Guide

author Leslie S. Gyamfi tags CASE POSTGRESQL 7 min 2024-02-22
title

MySQL CREATE DATABASE Statement: Definitive Guide

author Antonello Zanini tags Create database MySQL 7 min 2024-02-08
title

Discover All PostgreSQL Data Types

author Antonello Zanini tags DATA TYPES POSTGRESQL 12 min 2024-02-05
title

A Complete Guide to pg_dump With Examples, Tips, and Tricks

author Antonello Zanini tags pg_dump POSTGRESQL 8 min 2024-01-25
title

A Comprehensive Guide to Data Types in Postgres

author Leslie S. Gyamfi tags DATA TYPES POSTGRESQL 10 min 2024-01-23
title

A Complete Guide to SUBSTRING_INDEX in SQL

author Antonello Zanini tags MySQL 6 min 2024-01-16
title

SQL DISTINCT: A Comprehensive Guide

author Bonnie tags DISTINCT POSTGRESQL SQL 5 MINS 2024-01-11
title

Error: MySQL Shutdown Unexpectedly - The Solution

author Lukas Vileikis tags MySQL 5 MINS 2024-01-04

The content provided on dbvis.com/thetable, including but not limited to code and examples, is intended for educational and informational purposes only. We do not make any warranties or representations of any kind. Read more here.

Cookie policy

We use cookies to ensure that we give you the best experience on our website. However you can change your cookie settings at any time in your browser settings. Please find our cookie policy here ↗