Foreign data wrappers

From PostgreSQL wiki
Jump to navigationJump to search

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.

Specific SQL Database Wrappers

Data Source Type License Code Install Doc Notes
PostgreSQL Native PostgreSQL git.postgresql.org documentation
Oracle Native PostgreSQL github PGXN website
MySQL Native github PGXN example FDW for MySQL
Informix Native PostgreSQL github
DB2 Native github
Firebird Native PostgreSQL github PGXN README version 1.3.0 released (2022-12)
SQLite Native PostgreSQL github PGXN README An FDW for SQLite3 (write support and several pushdown optimization)
Sybase / MS SQL Server Native github PGXN An FDW for Sybase and Microsoft SQL server
MonetDB Native github

NoSQL Database Wrappers

Data Source Type License Code Install Doc Notes
BigTable or HBase Native Rust Binding (RPGFFI) MIT Github
Cassandra Multicorn MIT Github Rankactive
Cassandra2 Native MIT Github
Cassandra Multicorn PostgreSQL Github
ClickHouse Multicorn BSD Github README
ClickHouse Native Apache Github README
ClickHouse Native Github README
ClickHouse Wrappers Apache GitHub documentation A foreign data wrapper for ClickHouse
CouchDB Native PostgreSQL Github PGXN Original version
CouchDB Native PostgreSQL Github golgauth version (9.1 - 9.2+ compatible)
GridDB Native PostgreSQL Github README
InfluxDB Native PostgreSQL Github README
Kafka Native PostgreSQL GitHub README
Kyoto Tycoon Native MIT Github
MongoDB Native GPL3+ Github PGXN README EDB version
MongoDB Multicorn MIT Github
MongoDB Multicorn Github Yet Another Postgres FDW for MongoDB
Neo4j Multicorn GPLv3 Github README FWD for Neo4j and also add a Cypher function to Pg
Neo4j Native ? Github
Quasar Native Apache Github
Redis Native PostgreSQL Github
Redis Native BSD Github
RethinkDB Multicorn MIT Github blog
Riak Multicorn PostgreSQL Github
RocksDB Native Apache Github README FDW for RocksDB
SPARQL Multicorn2 PostgreSQL Github
WhiteDB Native MIT Github
RDF Triplestores (SPARQL endpoints) Native MIT Github A Foreign Data Wrapper to easily access RDF Triplestores via SPARQL, including pushdown of several SQL query clauses.

File Wrappers

Data Source Type License Code Install Doc Notes
CSV Native PostgreSQL git.postgresql.org documentation Delivered as an official extension of PostgreSQL 9.1 / example / Another example
CSV Multicorn PostgreSQL GitHub PGXN documentation Each column defined in the table will be mapped, in order, against columns in the CSV file.
CSV / Text Array Native GitHub How to Another CSV wrapper
CSV / Fixed-length Native GitHub
CSV / gzipped Multicorn GitHub PostgreSQL Foreign Data Wrapper for gzipped cvs file
Compressed File Native GitHub
Document Collection Native PostgreSQL GitHub wiki
JSON Native GPL3 GitHub Example
Multi-File Multicorn PostgreSQL GitHub PGXN doc 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.
Multi CDR Native PostgreSQL GitHub PGXN
Parquet Native PostgreSQL GitHub Foreign data wrapper for reading Parquet files using libarrow/libparquet
pg_dump Native New BSD GitHub Allows querying of data directly against Postgres custom format files created by pg_dump
TAR Files Native GitHub
XML Multicorn PostgreSQL GitHub PGXN
ZIP Files Native GitHub

Geo Wrappers

Data Source Type License Code Install Doc Notes
GDAL/OGR Native MIT GitHub 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.
Geocode / GeoJSON Multicorn GPL GitHub a collection of PostGIS-related foreign data wrappers
Open Street Map PBF Native PostgreSQL GitHub

LDAP Wrappers

Data Source Type License Code Install Doc Notes
LDAP Native GitHub PGXN Allows to query an LDAP server and retrieve data from some pre-configured Organizational Unit
LDAP Multicorn PostgreSQL GitHub PGXN documentation

Generic Web Wrappers

Data Source Type License Code Install Doc Notes
Git Multicorn PostgreSQL GitHub PGXN
Git Native MIT GitHub
ICAL Multicorn PostgreSQL GitHub pdf
IMAP Multicorn PostgreSQL GitHub PGXN documentation
RSS Multicorn PostgreSQL GitHub PGXN documentation This fdw can be used to access items from an rss feed.
www Native PostgreSQL GitHub PGXN wiki Allows to query different web services
pgsql-http Native PostgreSQL GitHub Compile Allows to query any http resource using CURL libs. By Paul Ramsey

Specific Web Wrappers

Data Source Type License Code Install Doc Notes
OAI-PMH Native MIT GitHub README 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.
Cloudsmith.io Multicorn PostgreSQL GitHub
Database.com Multicorn BSD GitHub
Dun & Badstreet Multicorn PostgreSQL GitHub Access to the Data Universal Numbering System (DUNS)
DynamoDB Multicorn GPL GitHub
DynamoDB Native PostgreSQL GitHub
Facebook Multicorn GitHub
Fixer.io based on www_fdw GitHub
Google Multicorn PostgreSQL GitHub PGXN
Heroku dataclips Native PostgreSQL GitHub
Keycloak Multicorn MIT GitHub PGXN README Direct database integration with the Keycloak open-source Identity/Access Management solution.
Mailchimp Multicorn PostgreSQL GitHub Beta
Parse Multicorn MIT GitHub
S3 Native PostgreSQL GitHub PGXN
S3 Wrappers Apache GitHub documentation A foreign data wrapper for S3, supports CVS, JSONL, Parquet files and gzip, bzip2, xz, zlib compression.
S3CSV Multicorn GPL 3 GitHub This is meant to replace s3_fdw that does is not supported on PostgreSQL version 9.2+
ParquetS3 Native PostgreSQL GitHub Foreign data wrapper for reading Parquet files using libarrow/libparquet on Amazon S3 / Minio
Telegram Multicorn PostgreSQL GitHub telegram_fdw is a Telegram BOT implemented using the PostgreSQL foreign data wrapper interface.
Twitter Native PostgreSQL GitHub PGXN A wrapper fetching text messages from Twitter over the Internet and returning a table
Treasure Data Native Apache GitHub PGXN A FDW for Treasure Data internally using a Rust library
Treasure Data Multicorn Apache GitHub
Google Spreadsheets Multicorn MIT GitHub
Open Weather Map Multicorn MIT GitHub A FDW for Open Weather Map (single city)
Airtable Wrappers Apache GitHub documentation A foreign data wrapper for Airtable
Firebase Wrappers Apache GitHub documentation A foreign data wrapper for Firebase
Logflare Wrappers Apache GitHub documentation A foreign data wrapper for Logflare
Stripe Wrappers Apache GitHub documentation A foreign data wrapper for Stripe

Big Data Wrappers

Data Source Type License Code Install Doc Notes
Elasticsearch Multicorn MIT GitHub Supports up to PG 13, ES 7.
Google BigQuery Multicorn MIT GitHub Documentation bigquery_fdw is a BigQuery FDW compatible with PostgreSQL >= 9.5
Google BigQuery Wrappers Apache GitHub documentation A foreign data wrapper for BigQuery
file_fdw-gds (Hadoop) Native GitHub Hadoop file_fdw is a slightly modified version of PostgreSQL 9.3's file_fdw module.
Hadoop Native PostgreSQL Bitbucket Allows read and write access to HBase as well as to HDFS via Hive.
HDFS Native Apache GitHub
Hive Multicorn GitHub Used to access Apache Hive tables.
Hive / ORC File Native GitHub
Impala Native BSD GitHub
Apache Arrow Native GPLv2 GitHub A part of PG-Strom feature; as a columnar data source with support of SSD-to-GPU Direct SQL

Column-Oriented Wrappers

Data Source Type License Code Install Doc Notes
Columnar Store Native github example A Columnar Store for PostgreSQL.
MonetDB Native github
GPU Memory Store Native GPL v2 github FDW to GPU device memory; a part of PG-Strom feature for PL/CUDA

Scientific Wrappers

Data Source Type License Code Install Doc Notes
Ambry Multicorn GitHub
ROOT files Native GitHub https://root.cern.ch
VCF files (Genotype) Multicorn GitHub https://en.wikipedia.org/wiki/Variant_Call_Format

Operating System Wrappers

Data Source Type License Code Install Doc Notes
Docker Multicorn Expat GitHub
Log files Multicorn PostgreSQL GitHub
OpenStack / Telemetry Multicorn PostgreSQL GitHub
OS Query Multicorn PostgreSQL GitHub Like Facebook's OSQuery, but for Postgres
Passwd Native PostgreSQL GitHub reads linux/unix password and group files.
Process Multicorn PostgreSQL GitHub A foreign datawrapper for querying system stats based on statgrab
Environment Variables Multicorn MIT GitHub envFDW is a forign data wrapper for processing environment variables

Exotic Wrappers

Data Source Type License Code Install Doc Notes
faker_fdw Multicorn PostgreSQL GitHub faker_fdw is a foreign data wrapper for PostgreSQL that generates fake data.
fdw_fdw Multicorn PostgreSQL GitHub the Meta FDW ! reads this page and returns the list of all the FDW
PPG Native GitHub distributed parallel query engine, based on fdw and hooks of PG
Open Civic Data Multicorn Expat GitHub
Phillips Hue Lighting Systems Multicorn MIT GitHub
Random Number Multicorn PostgreSQL GitHub A random number generator foreign data wrapper for postgres
Rotfang Native PostgreSQL BitBucket slides Advanced random number generator
Template Tables Native BSD GitHub PostgreSQL data wrapper for template tables - any DML and SELECT operations are disallowed
VMware vSphere Multicorn MIT GitHub A PostgreSQL FDW to query your VMware vSphere service

Example Wrappers

Data Source Type License Code Install Doc Notes
Dummy Native BSD GitHub Readable null FDW for testing
Hello World GitHub
Hello World Wrappers Apache GitHub README A demo foreign data wrapper
Black Hole bitbucket a skeleton FDW pre-populated with relevant excerpts from the documentation

Writing Foreign Database Wrappers