How To Import an Ingres Database Into PostgreSQL the Easy Way

While the Ingres database technology may have peaked in the 1980s, the tech is still in use, especially among large government and commercial organizations. 

But for a variety of reasons, it may be necessary to import an Ingres database to PostgreSQL. 

To do this, you have two options:

Manually import the Ingres database
Use a tool to import your database automatically

In this short post, we’ll review both.  

Manually Importing an Ingres Database Into PostgreSQL

The precise process for importing your Ingres database into PostgreSQL will depend on the particulars of your system. 

But generally, the manual process requires that you extract data from the Ingres database in a format you can import into PostgreSQL, such as a CSV file, and then load it into PostgreSQL. 

At a minimum, this would require:

Writing a custom script to extract data from the Ingres database and generate a CSV file.
Loading the CSV files into PostgreSQL with a Postgres client or COPY command. 
Creating a database schema and writing a script for any field types you need to change from Ingres to PostgreSQL.

For a one-time import, these are your high-level steps. But you need to regularly update PostgreSQL with changes to your Ingres database; you’ll need to create and run another script that extracts changes from Ingres and loads them into PostgreSQL. 

Importing an Ingres Database Into PostgreSQL the Easy Way

You can avoid the painstaking process of manually creating schema and writing scripts with the drag-and-drop functionality of StreamSets Data Collector. With StreamSets, the importing process can look as simple as: 

Create a JDBC connection.
Configure a consumer origin by entering your Ingres login credentials and choosing what Ingres data you want to send to PostgreSQL.
Configure a JDBC destination by entering your PostgreSQL login credentials and configuring the fields to be mapped within PostgreSQL.  

In the image below, you can see what the final pipeline to import an Ingres Database into PostgreSQL would look like:

The process of configuring this pipeline boils down to selecting an origin and destination from a dropdown menu, logging into your accounts, and choosing the relevant fields. The fields you choose are up to you, depending on what data you want to send from Ingres to Postgres. 

NOTE: Ingres is not an officially supported system in StreamSets, but the JDBC Connector makes it possible to connect to an Ingres database. See JDBC Documentation for more information.

Simplifying Data in Motion

Data engineers’ time shouldn’t be squandered on repetitive, time-consuming tasks like writing (and rewriting) scripts to extract and load data. 

While it might seem inconsequential in a one-off case to manually import data from Ingres to PostgreSQL, data pipelines have a way of becoming ever more complex. Sources and targets multiply and change, as do the required transformations. And the manual work to manage all this increases exponentially.

That’s why the StreamSets method of importing a database is far superior. The time and headache it saves multiply, along with your data processing needs. And rather than rewriting scripts and manually managing schema, you can use StreamSets to create reusable patterns, quickly stand up operational data pipelines, and enable other, less technical users with self-service. 

To see how much more you can do with StreamSets, try the Data Collector Engine today.

The post How To Import an Ingres Database Into PostgreSQL the Easy Way appeared first on StreamSets.

Read MoreStreamSets