How to import data from PostgreSQL to Magento 2

While there are dozens of various databases, none of them offers a direct import of data to Magento. At the same time, you can leverage multiple community-proven ways to achieve this goal. Below, we compare a traditional approach to importing data from a remote PostgreSQL table to Magento 2 with a user-friendly one. If you are tired of a complicated import procedure – you’ve come to the right place: below, we explain how to import data from PostgreSQL to Magento 2 in the most seamless manner.

Traditional Approach to Moving Data from PostgreSQL to Magento 2

For all databases, we explore the same situation: you have a remote table with data that should be imported into Magento 2. Since we are talking about the traditional approach to moving data from PostgreSQL to Magento 2, you have to perform lots of various actions which can be divided into three main stages:

• PostgreSQL export to CSV;
• Attributes mapping;
• CSV import to Magento 2.

Let’s take a look at each stage individually.

PostgreSQL Export to CSV

There are various official techniques to export data from PostgreSQL tables to CSV files. If you know any community-provided equivalents, you can share them in comments to this article. The following example is based on the “persons” table that consists of the following columns: id, first_name, last_name, dob, and email – an analog of a table with information about customers in Magento 2. The table contains two records.

Export data from PostgreSQL table to CSV via COPY statement

First of all, you can create a CSV file with all the necessary data with the help of the “COPY” statement. It is the easiest way to get the desired output. Let’s assume that we want to create the output named persons_db.csv. The location of the file is C:\tmp.

Use the following statement to run the export process:

Note that it is not only necessary to specify the name of the file and its location, you should also set the delimiter – comma (‘,’), and enable the creation of a header with column names. If you don’t want to export the header – remove the HEADER flag in the COPY statement.

If you need only specific columns to be exported, specify their names together with table name after the COPY keyword as follows:

This command will create a CSV file that contains only two columns and displays the appropriate data in each row. Check the video below for more details:

Several more requirements for exporting data from a remote PostgreSQL table to a CSV file via the COPY statement:

• The CSV file name in the COPY command must be written by the server directly.
• The CSV file must reside on the database server machine.
• The CSV file needs to be writable by the same user that the PostgreSQL server runs as.

It was the first official recommendation regarding how to export data from PostgreSQL to CSV. Let’s take a look at another way, who knows, maybe it is more useful in your particular case.

Export data from PostgreSQL table to CSV via \copy command

If you have insufficient privileges, the official PostgreSQL tutorial recommends using the \copy command to export data to a CSV file. The command repeats the algorithm described above but transfers data from the server to your local file system instead of server writing. Thus, you can still get the desired output even without the necessary privileges.

Note that the \copy command requires sufficient privileges to your local machine. To export the table from the previous example, use the following command:

You will get the customers.csv file under C:\tmp. Both ways of exporting a CSV file from a remote PostgreSQL table look more user-friendly and intuitive than the appropriate experience of Oracle Database for example. For further information, follow check this official tutorial: Export PostgreSQL Table To CSV File.

In case of errors, go right to the corresponding topic on Stack Overflow: PostgreSQL: export data to csv file.

Attributes Mapping

Now, when you know how to export a PostgreSQL table to a CSV file, we can proceed to the second stage of the traditional approach to moving data from PostgreSQL to Magento 2 – attributes mapping. As you might have already seen in the example above, column names of the PostgreSQL CSV output may differ a lot from the default Magento 2 requirements. Consequently, it is impossible to take the exported file and move it to Magento 2 directly.

You should map column names to the corresponding Magento 2 attributes. In case of the table above, it won’t take much time, but if you work with massive databases, lots of export files, and frequent updates, your daily routine can become a painful headache. If you still want to apply mapping manually, check our tutorial related to the Magento 2 import/export processes. We describe the structure of an import file suitable for the update. You will find all Magento 2 attributes and corresponding values in the following article: The Complete Guide to Magento 2 Product Import / Export.

Even if manual editing doesn’t frighten you, there is a more user-friendly mechanism of transferring the PostgreSQL output into a suitable form. We describe it further in the article, but now it’s time to draw your attention to the last stage of the traditional approach to moving data from PostgreSQL to Magento 2.

CSV Import to Magento 2

Congratulations! Your CSV file is ready for the final stage – import to Magento 2. You can use either the default system tools or rely on third-party extensions. In case of errors, check the CSV file once again. Perhaps, some attributes were mapped incorrectly. Thus, you need to return to the second stage and explore the edited file more carefully.

And note that the traditional routine will look like a Sisyphean task due to the following reasons:

• You need to export a new CSV file for the update;
• Next, you should edit a CSV file manually;
• Only then, you can upload it to Magento 2;
• If the data wasn’t appropriately edited, you should repeat the second stage;
• It is necessary to repeat the algorithm every time update from a PostgreSQL database is required.

How much time do you need to import data from PostgreSQL to Magento 2? Due to a user-friendly SCV export of the database, it won’t take as much time, as in case of some similar solutions, but you still have to repeat the same set of actions to provide your store with the fresh information. Luckily, there is a user-friendly approach to moving data from PostgreSQL to Magento 2. Let’s take a look at its core benefits.

User-Friendly Approach to Moving Data from PostgreSQL to Magento 2

You need the Improved Import & Export Magento 2 extension to be installed to revamp the import process and make it a way better. Due to the advanced functionality of our extension, you can not only eliminate the use of data files but also automate the updates and map attributes more efficiently. Below, we explore the renewed way of importing data from a remote PostgreSQL database to Magento 2.

Direct PostgreSQL Connection

The Improved Import & Export Magento 2 extension establishes a direct connection with PostgreSQL so that you don’t need to export data files from the database. Thus, the module provides the ability to eliminate time expenditures necessary for the export processes. In case of the PostgreSQL database, the procedure is relatively fast and user-friendly, so the core time-saver is introduced below.

Mapping & Data Import from PostgreSQL to Magento 2

We’ve been talking a lot about the inconveniences of the default import process caused by the necessity to map external attributes to the Magento requirements. Manual editing consumes lots of time especially if you work with big data files or a large number of them. Usually, this may happen together so that you won’t see anything except CSV tables exported from the PostgreSQL database.

A situation will change dramatically if you use the Improved Import & Export Magento 2 extension. You get a convenient interface where the update is performed – your Magento 2 admin. The mapping process itself is reduced to just a few steps:

1. Go to your admin and open the interface of our module.
2. Create a new import profile.
3. Specify an external PostgreSQL database.
4. Select tables you want to import.
5. Map attributes to the Magento 2 standards.  
6. Run import.

It is possible to specify the schedule of updates automating the Magento 2 PostgreSQL synchronization. Also, note that the created mapping scheme is used for further updates so that you don’t need to map attributes every time a new data should be imported. Before going any further, let’s take a look at core benefits of the user-friendly approach to moving data from a remote PostgreSQL database to Magento 2 over the traditional one.

• No Data Files. The Improved Import & Export Magento 2 extension provides the ability to eliminate the use of data files from the update reducing time expenditures. At the same time, file updates are still possible.
• Single Interface. In case of the traditional approach, you should perform each new stage in a completely different environment. The Improved Import & Export Magento 2 extension allows you apply all configurations right in the Magento admin. There is no necessity to use multiple interfaces to import data from a remote PostgreSQL table to Magento 2.
• Single Tool. You don’t need to rely on any third-party apps or systems. Everything is available right in your Magento backend with the help of our module.
• Schedules. The Improved Import & Export extension provides the ability to automate import from PostgreSQL to Magento 2 by creating schedules. Thus, you configure a new import profile, and it maintains all further updates.
• Mapping. The fully-featured mapping interface of the Improved Import & Export Magento 2 extension provides the ability to eliminate data file editing. If the connection is established directly, you can still use the interface to map attributes to each other saving tons of time.

The benefits of the user-friendly approach over a traditional one are apparent. Therefore, let’s say a few words about the PostgreSQL database.

Get Improved Import & Export Magento 2 Extension

PostgreSQL Features

PostgreSQL is an open source object-relational database system based on the SQL language that extends and combines its features with multiple other standards to safely store and scale the most difficult data workloads. The project earned a strong reputation due to a set of such characteristics as its proven architecture, advanced reliability, extended data integrity, huge feature set, and the dedication of the open source community.

PostgreSQL supports the following data types:

• Primitives. This category includes such sub-types as integer, string, numeric, and boolean.
• Structured Data. Of course, structured data is a part of the system as well. PostgreSQL works with date/time, array, range, UUID, etc.
• Documents. The following documents can be stored as well: JSON/JSONB, XML, Key-value (Hstore), etc.
• Geometry is another vital data type that includes
  • Point
  • Line
  • Circle
  • Polygon
• Customizations are Composite and Custom Types

Data Integrity features include UNIQUE, NOT NULL; Primary and Foreign Keys, Exclusion Constraints, Explicit and Advisory Locks.

As for the concurrency and performance functionality of the platform, it is represented by exceptional indexing opportunities with not only B-tree, Multicolumn, Expressions, and Partial, but GiST, SP-Gist, KNN Gist, GIN, BRIN, Bloom filters, etc. The sophisticated query planner and optimizer, transactions and nested transactions, MVCC, parallelization of read queries, table partitioning, and many other features will make your interaction with databases much better.

As for the excellent reliability of the PostgreSQL database, it is based on write-ahead logging; asynchronous, synchronous, and logical replication; point-in-time-recovery, and tablespaces. At the same time, the system is secure since it provides support for multiple authentication types, a robust access-control system, and various security improvements on the column and row-level security.

Strong extensibility opportunities are represented with stored procedures, support for multiple procedural languages, foreign data wrappers, as well as additional extensions. And don’t forget about international character sets and full-text search. You can discover more features of PostgreSQL here: What is PostgreSQL.

Improved Import & Export Magento 2 Extension

Now, it’s time to say a few words about the Improved Import & Export Magento 2 extension. You know core features of PostgreSQL, so take a look at how our module can improve your interaction with the database and any external systems.

The core goal behind the Improved Import & Export module is to help you connect Magento 2 to any external platform in the most efficient way. That’s why the extension introduces a user-friendly approach to importing data from PostgreSQL to Magento 2  – the method that has many benefits over the traditional one. Since the direct connection is not always necessary, the plugin offers extensive support for multiple file types as well.

File Formats

While Magento works with CSV by default and many third-party modules adds support for XML, our extension goes further introducing full compatibility with the JSON format. You can freely import/export files of all three types between Magento 2 and external systems with the help of the Improved Import & Export plugin. Moreover, it also uses native Excel files for internal purposes. And when it comes to file transfers, it is also necessary to say a few words about supported file sources

File Sources

First of all, the Improved Import & Export Magento 2 extension introduces extensive FTP/SFTP support. It means that you can easily transfer data files through your local server. Since this option is not always enough, our module allows you to import/export information using a remote server.

The second important file source is related to cloud technologies. Everything works in the cloud nowadays. As for Improved Import & Export extension, it supports Dropbox integration. Thus, you can use your cloud account to transfer data files to your e-commerce website.

When it comes to importing processes only, our plugin allows transferring data to the system via a direct URL. Also, note that it unzips all compressed files automatically.

Alternative Ways of Import/Export

If you don’t want to deal with files, like in case of Magento 2 PostgreSQL data import, you can fully leverage alternative ways of information transfers. The Improved Import & Export Magento 2 extension supports Google Sheets. The module provides the ability to import data right from the popular service. The process looks as follows:

Besides, the Improved Import & Export Magento 2 extension introduces the opportunity to leverage REST API connections. Due to this feature, our module allows creating integrations even with the most complicated systems.

In both cases, you can also map attributes within a convenient interface and create schedules making data updates fully automated.

Schedules

After the extended connectivity options, we’d like to draw your attention to the aforementioned time-savers and user-oriented features of the Improved Import & Export Magento 2 extension. Our module is designed to automate every connection. And you can not only select a predefined time interval from the list of available values but also create your custom schedule. It is only necessary to use the default cron syntax.

At the same time, the module introduces an opportunity to launch every update manually even if it is scheduled. Alternatively, you can create profiles designed for a manual run only. Such flexibility allows satisfying even the most demanding situations. You can learn how to configure a section with schedules from the following gif image:

Mapping

Perhaps, the most crucial time-saver introduced with the Improved Import & Export extension is its mapping interface. It not only provides a much more user-friendly way to match attributes, but it also saves the created mapping schemes and applies them to further updates automatically. Thus, you only need to map attributes for a single time. The procedure itself is displayed below:

As you can see, the extension allows you to work only with the necessary attributes instead of exploring a vast document. Moreover, the Improved Import & Export Magento 2 extension provides mapping presets which are pre-made mapping schemes created by our specialists. Contact our support team regarding mapping schemes for your particular integrations since not all presets are available. If the desired scheme is included, attributes matching is reduced to a few clicks as shown here:

For further information about the Improved Import & Export Magento 2 extension, watch this video and follow the link below the article:

What can we learn from this blog post? You can always find a tool that will dramatically enhance your working experience. There is no need to spend hours of time and face tons of headaches when there is a seamless way to achieve the same goal. Let’s summarize how our module improves the traditional approach to importing data from PostgreSQL to Magento 2:

• No files are used;
• No manual data file editing occurs;
• No additional instruments are used;
• Everything takes place in the Magento admin;
• Advanced mapping interface saves lots of time;
• Schedules automate updates.

If you also want to apply all these improvements to the way you import data from PostgreSQL or any external platform to Magento 2, quickly follow the link below and get your copy of the Improved Import & Export Magento 2 extension!

Get Improved Import & Export Magento 2 Extension