Foreign data wrappers - PostgreSQL wiki (original) (raw)
Foreign Data Wrappers
In 2003, a new specification called SQL/MED ("SQL Management of External Data") was added to the SQL standard. It is a standardized way of handling access to remote objects from SQL databases. In 2011, PostgreSQL 9.1 was released with read-only support of this standard, and in 2013 write support was added with PostgreSQL 9.3.
There are now a variety of Foreign Data Wrappers (FDW) available which enable PostgreSQL Server to different remote data stores, ranging from other SQL databases through to flat file. This page list some of the wrappers currently available. Another fdw list can be found at the PGXN website.
Please keep in mind that most of these wrappers are not officially supported by the PostgreSQL Global Development Group (PGDG) and that some of these projects are still in Beta version. Use carefully!
Generic SQL Database Wrappers
| Data Source | Type | License | Code | Install | Doc | Notes | |
| ---------------------------------------------------------------------------------------- | ----------------------------------------------------------- | ---------------------------------------------------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | ----------------------------------------------------------------------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | |
| ODBC | Native | | github | | | CartoDB took over active development of the ODBC FDW for PG 9.5+ | |
| JDBC | Native | | github | | | Not maintained? | |
| JDBC2 | Native | | github | | | | |
| JDBC | Native | | github | | README | More recent than the above, advertises write support. | |
| SQL_Alchemy | Multicorn | PostgreSQL | GitHub | PGXN | documentation | Can be used to access data stored in any database supported by the sqlalchemy python toolkit. | |
| GDAL/OGR | Native | MIT | GitHub | yum.postgresql.org, apt.postgresql.org, and part of PostGIS windows bundle (application stackbuilder) | | Can access many kinds of data sources (Relational databases, spreadsheets, CSV files, web feature services, etc). Uses the GDAL library which supports hundreds of formats to access the data. Exposes vector data as PostGIS geometry columns if you have PostGIS installed. Works great with both spatial and non-spatial data. | |
| VirtDB | Native | GPL | GitHub | | | A generic FDW to access VirtDB data sources (SAP ERP, Oracle RDBMS) | |
| APIs (via Steampipe plugins) | Native | CLI and FDW extension: AGPL 3.0, Plugins: Apache 2.0 | CLI on GitHub, FDW extension on GitHub | Steampipe downloads | Steampipe docs, Postgres FDW docs | Steampipe bundles Postgres with an FDW extension that supports a growing ecosystem of plugins. The plugins consume APIs, map them to tables, and enable queries within and across APIs.The plugins are also available as unbundled FDWs for use in any Postgres database. | |
Access data stored in various files in a filesystem. The files are looked up based on a pattern, and parts of the file's path are mapped to various columns, as well as the file's content itself.
A wrapper for data sources with a GDAL/OGR driver, including databases like Oracle, Informix, SQLite, SQL Server, ODBC as well as file formats like Shape, FGDB, MapInfo, CSV, Excel, OpenOffice, OpenStreetMap PBF and XML, OGC WebServices, and more Spatial columns are linked in as PostGIS geometry if PostGIS is installed.
A PostgreSQL Foreign Data Wrapper to access OAI-PMH repositories (Open Archives Initiative Protocol for Metadata Harvesting). This wrapper supports the OAI-PMH 2.0 Protocol.