How to Connect Magento to PostgreSQL Within 1 Minute?

Unlock the full potential of your Magento data by integrating it seamlessly with PostgreSQL. With Hevo’s automated pipeline, get data flowing effortlessly—watch our 1-minute demo below to see it in action!

Magento is an eCommerce platform built on open source technology that provides online merchants with a flexible shopping cart system, as well as control over the look, content, and functionality of their online store. Magento offers powerful marketing, search engine optimization, and catalog-management tools.

PostgreSQL is a robust, open-source object-relational database system that has a strong reputation for reliability, feature robustness, and performance. It has been actively developed for over 30 years.

This article explains how to connect Magento to PostgreSQL. It also gives an overview of Magento and PostgreSQL.

Magento: What’s it all about?

Magento is an open-source e-commerce platform written in PHP, widely used by businesses to create and manage online stores. It provides a flexible, scalable framework for building custom e-commerce websites, allowing merchants to control the look, content, and functionality of their stores. Magento is popular for its rich feature set, strong community support, and ability to handle complex e-commerce needs.

Key Features of Magento:

• Customization: Highly customizable platform for building unique online stores.
• Scalability: Supports small businesses to large enterprises with high traffic.
• Multi-store Functionality: Manage multiple stores from a single backend interface.
• Mobile-friendly: Responsive design ensures smooth operation on mobile devices.
• SEO Optimization: Built-in SEO features to improve search engine rankings.

Learn about Magento’s pricing plan through Hevo’s detailed Blog to find out how much it will cost you in 2024.

PostgreSQL: What is the Hype About?

PostgreSQL is an open-source, general-purpose, object-relational database management system, or ORDBMS. It is widely used and provides advanced features along with standard compliance. Some of these features include complex queries, foreign keys, triggers, and views—all supporting transactional integrity with full ACID compliance. Inherent in it is vast extensibility for custom data types, functions, and operators.

Key Features of PostgreSQL

• It provides built-in support for JSON, XML, arrays, hstores, and custom types, making it ideal for modern applications that require complex data models.
• PostgreSQL is released under the PostgreSQL License (similar to MIT), which makes it free to use, modify, and distribute.
• Supports Atomicity, Consistency, Isolation, and Durability (ACID), ensuring reliable transactions and data integrity.

Connecting Magneto to PostgreSQL

Method 1: Using Hevo Data to Move Data from Magento to PostgreSQL

Hevo provides an Automated No-code Data Pipeline that helps you move your Magento swiftly to BigQuery. Hevo is fully-managed and completely automates the process of not only loading data from your 150+ data sources(including 60+ free sources)but also enriching the data and transforming it into an analysis-ready form without having to write a single line of code. Its fault-tolerant architecture ensures that the data is handled in a secure, consistent manner with zero data loss.

Using Hevo Data, you can connect Magento to PostgreSQL in the following 2 steps:

Step 1: Configuring Magento as a Source.

Step 2: Configuring PostgreSQL as a Destination.

Here are more reasons to try Hevo:

• Smooth Schema Management: Hevo takes away the tedious task of schema management & automatically detects the schema of incoming data and maps it to your schema in the desired Data Warehouse.
• Exceptional Data Transformations: Best-in-class & Native Support for Complex Data Transformation at fingertips. Code & No-code Flexibility is designed for everyone.
• Quick Setup: Hevo with its automated features, can be set up in minimal time. Moreover, with its simple and interactive UI, it is extremely easy for new customers to work on and perform operations.
• Built To Scale: As the number of sources and the volume of your data grows, Hevo scales horizontally, handling millions of records per minute with very little latency.
• Live Support: The Hevo team is available round the clock to extend exceptional support to its customers through chat, email, and support calls.

Hevo lets you connect Magento to various other sources, such as Snowflake and BigQuery.  To experience seamless data migration Sign up for a 14-day free trial and try Hevo today!

Method 2: Using Custom Code to Migrate Magento to PostgreSQL

To migrate Magento to PostgreSQL, follow these steps:

• Step 1: Retrieve the Magento BI Public Key
• Step 2: Allow Access to the Magento BI IP Address
• Step 3: Create a Linux User for Magento BI
• Step 4: Create a Postgres User for Magento BI
• Step 5: Enter the Connection and User Info Into Magento BI

Step 1: Retrieve the Magento BI Public Key

This is the first step in manually migrate Magento to PostgreSQL.The public key authorizes the Magento BI Linux user. The user is then created, and the key is imported.

• Step 1: Click the Add a Data Source button under Manage Data > Connections.
• Step 2: Select PostgreSQL from the drop-down menu.
• Step 3: Toggle the Encrypted button to Yes once the PostgreSQL credentials page has opened. This will bring up the SSH configuration form.
• Step 4: This form’s public key can be found beneath it.

Step 2: Allow access to the Magento BI IP Address

Your firewall must be configured to allow access from our IP address for the connection to work. It’s 54.88.76.97/32, but the PostgreSQL credentials page lists it as well.

Step 3: Create a Linux User for Magento BI

It doesn’t matter if this is a primary or secondary machine, as long as it has real-time (or frequently updated) data. You can limit this user in any way you want as long as it has access to the PostgreSQL server.

• Step 3.1: Run the commands below as root on your Linux server to create the new user:

 adduser rjmetric -p<password>
        mkdir /home/rjmetric
        mkdir /home/rjmetric/.ssh

• Step 3.2: You must import the key into authorized keys to ensure the user has access to the database. In the authorized keys file, paste the entire key as follows: 

 touch /home/rjmetric/.ssh/authorized_keys
     "<PASTE KEY HERE>" >> /home/rjmetric/.ssh/authorized_keys

• Step 3.3: To complete the user’s creation, change the permissions on the /home/rjmetric directory to allow SSH access:

chown -R rjmetric:rjmetric /home/rjmetric
        chmod -R 700 /home/rjmetric/.ssh

Only certain users will have server access if the sshd config file associated with the server is not set to the default option, which will prevent a successful connection to Magento BI. In these cases, a command like AllowUsers must be used to grant the rjmetric user access to the server.

Step 4: Create a Postgres User for Magento BI

Although your company may have its procedures, the simplest way to create this user is to run the following query while logged into Postgres as a user with the right to grant privileges. The user should also be the owner of the schema to which Magento BI has access.

 GRANT CONNECT ON DATABASE <database name> TO rjmetric WITH PASSWORD <secure password>;GRANT USAGE ON SCHEMA <schema name> TO rjmetric;GRANT SELECT ON ALL TABLES IN SCHEMA <schema name> TO rjmetric;ALTER DEFAULT PRIVILEGES IN SCHEMA <schema name> GRANT SELECT ON TABLES TO rjmetric;

** should be replaced with a secure password that is not the same as the SSH password. Also, make sure that ** and ** in your database are replaced with the appropriate names.

Repeat this procedure as needed if you want to connect multiple databases or schemas.

Step 5: Enter the Connection and User Info Into Magento BI

Finally, to migrate Magento to PostgreSQL, we must enter the connection and user information into Magento BI. If you don’t see the PostgreSQL icon, go to Manage Data > Connections and click the Add a Data Source button. Make sure the Encrypted button is set to Yes.

Starting with the Database Connection section, fill in the following information on this page:

• Username: The username for RJMetrics Postgres (should be rjmetric)
• Password: The password for RJMetrics Postgres.
• Port: PostgreSQL port on your server (5432 by default)
• Host: 127.0.0.1

Under “SSH Connection,” make the following changes:

• Username: The IP address or hostname of the server we’ll SSH into. Remote Address: Our SSH username and password (should be rjmetric)
• SSH Port: Your server’s SSH port (22 by default)

And you are done!

Conclusion

Migrating from Magento to PostgreSQL is a crucial step for businesses seeking to enhance their e-commerce operations and data management capabilities. By leveraging PostgreSQL’s advanced features, such as robust performance, scalability, and strong data integrity, organizations can optimize their databases to handle increasing volumes of transactions and customer data more efficiently. 

For a seamless migration experience, Try Hevo’s automated no-code platform. Hevo is a reliable, cost-effective and fast tool that migrates your data from source to destination within minutes without any manual effort. Sign up for Hevo’s 14-day free trial and take your data migration game to a completely new level!

FAQ on Magento to PostgreSQL