Connect Magento to PostgreSQL: Simplified 101

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.

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 100+ data sources(including 40+ 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. In the Configure your Magento via MySQL Source page, make the following changes:

• Step 2: Configuring PostgreSQL as a Destination. Complete Connect Magento to PostgreSQL by providing your PostgreSQL database credentials such as your authorized Username and Password, along with information about your Host IP Address and Port Number value. You will also need to provide a name for your database and a unique name for this 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.

Try Hevo Today!

SIGN UP HERE FOR A 14-DAY FREE TRIAL

Method 2: Using Custom Code to Move Data from Magento to PostgreSQL

To manually connect Magento to PostgreSQL, you’ll need to do the following:

• Magento to PostgreSQL: Retrieve the Magento BI Public Key
• Magento to PostgreSQL: Allow Access to the Magento BI IP Address
• Magento to PostgreSQL: Create a Linux User for Magento BI
• Magento to PostgreSQL: Create a Postgres User for Magento BI
• Magento to PostgreSQL: Enter the Connection and User Info Into Magento BI

Magento to PostgreSQL: Retrieve the Magento BI Public Key

This is the first step to manually connecting Magento to PostgreSQL.The Magento BI Linux user is authorized using the public key. The user will then be created, and the key will be 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.

Magento to PostgreSQL: 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.

Magento to PostgreSQL: 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 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 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: 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.

Magento to PostgreSQL: 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.

Magento to PostgreSQL: Enter the Connection and User Info Into Magento BI

Finally, 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)

To finish the setup, click the Save & Test button to connect Magento to PostgreSQL.

Conclusion

This blog discusses methods for Magento to PostgreSQL Integration. In addition to that, it also gives a brief introduction to PostgreSQL and Magento.

visit our website to explore hevo

Hevo Data, a No-code Data Pipeline provides you with a consistent and reliable solution to manage data transfer between a variety of sources and a wide variety of Desired Destinations, with a few clicks. Hevo Data with its strong integration with 100+ sources (including 40+ free sources) allows you to not only export data from your desired data sources & load it to the destination of your choice, but also transform & enrich your data to make it analysis-ready so that you can focus on your key business needs and perform insightful analysis using BI tools.

Want to take Hevo for a spin? Sign Up for a 14-day free trial and experience the feature-rich Hevo suite first hand. You can also have a look at the unbeatable pricing that will help you choose the right plan for your business needs.