Continuing our blog series, Integrating PostgreSQL—which kicked off with this post covering Application Integration—we’ll now further explore PostgreSQL integration to help enterprises do more and go faster.
This blog post will dive into our next key focus area within this landscape: Data Integration and Migration.
Data Integration and Migration
Successful PostgreSQL® implementations are fully integrated with the enterprise environment and do not create ‘data islands’.
PostgreSQL has a native capability to integrate with the ecosystem: Foreign Data Wrappers (FDW), which are an implementation of the SQL MED standard. FDWs were first introduced in PostgreSQL 9.1 and are now widely used to integrate with remote PostgreSQL databases, Hadoop, MySQL, Mongo, Oracle, and so on. As opposed to ETL (Extract, Transform, Load) type implementation tools, FDWs do not copy or move the data; they access the data in place and push as many operations as possible to the remote (foreign) data store.
Well-implemented FDWs support aggregate and join pushdown, as well as column selection. The FDWs for HDFS, MongoDB, and MySQL are very popular and maintained by EDB. This blog has how-to instructions for using FDWs.
An increasing number of ETL vendors are adding PostgreSQL as sources and targets to their integration tools. In a 2021 survey of EDB website visitors, the following tools proved to be very popular with PostgreSQL users (in alphabetical order):
- EDB*Loader
- EDB Replication Server
- IBM InfoSphere DataStage
- Informatica PowerCenter
- Microsoft SSIS
- Oracle GoldenGate
- Pentaho Data Integration
- Qlik (Attunity)
- Talend Open Studio
Migration from legacy databases, such as Oracle and SQL Server, is supported by enterprise-grade solutions, such as EDB’s Migration Toolkit and EDB Replication Server, and open source tools, for instance, Orafce or OR2PG. Open source tools tend to be limited to migrations of data definitions and common data types. Specialty tools, like EDB’s Migration Portal and EDB Replication Server, provide a more complete set of capabilities, including change data capture for incremental migrations.
In the next blog in this series, we'll cover Development Tools.
Check out our new infographic to see the bigger picture in regard to PostgreSQL integration.
Relevant Blogs
