Downloaded 62 times
Uploaded byEDB
Foreign Data Wrappers and You with Postgres
The document discusses the functionality and advantages of foreign data wrappers (FDWs) in PostgreSQL, which enable SQL access to various data sources, including remote databases and non-traditional data repositories. It highlights the extensibility of PostgreSQL, its open-source nature, and the active community supporting it, along with EnterpriseDB's offerings that enhance PostgreSQL capabilities. The document provides examples of creating foreign servers and tables and mentions advanced features like pushdown capabilities for queries.
In this document
Powered by AI
Overview of the presentation on using foreign data wrappers for connectivity by Bruce Momjian.
PostgreSQL as an open source object-relational DBMS, adhering to ANSI-SQL standards, and supporting various languages and data types.
Description of the independent PostgreSQL community, its active role, and support from major companies.
EnterpriseDB as a leader in Postgres products/services and its contributions to the community.
Foreign data wrappers enable SQL access to various data sources, enhancing data flexibility.
Foreign data wrappers allow data queries across diverse databases, including NoSQL and SQL databases.
Foreign data wrappers support various interfaces, including JDBC, LDAP, and ODBC.
FDWs can also interface with non-traditional data sources like files and cloud storage.
Example demonstrating the creation of a foreign server and mapping in PostgreSQL.
Types of data sources that can be read and written to using foreign data wrappers.
Discussion of advanced features like table restriction pushdown and cross-server compatibility.
Demonstrates optimization techniques like pushdown for SQL queries involving joins and aggregates.
Illustrates the ability to perform parallel queries across foreign data sources.
Details on managing global snapshots and transactions across foreign servers.
Information on where to download PostgreSQL and to find product/service information.
Wrap up of the presentation, reiterating key points discussed regarding foreign data wrappers.
Foreign Data Wrappers and You with Postgres
- 1. Foreign Data Wrappersand You BRUCE MOMJIAN April, 2015 This talk explains how foreign data wrappers can be used to leverage such connectivity. Creative Commons Attribution License http://momjian.us/presentations 1 / 19
- 2. PostgreSQL the database… OpenSource Object Relational DBMS since 1996 Distributed under the PostgreSQL License Similar technical heritage as Oracle, SQL Server & DB2 However, a strong adherence to standards (ANSI-SQL 2008) Highly extensible and adaptable design Languages, indexing, data types, etc. E.g. PostGIS, JSONB, SQL/MED Extensive use throughout the world for applications and organizations of all types Bundled into Red Hat Enterprise Linux, Ubuntu, CentOS and Amazon Linux Foreign Data Wrappers and You 2 / 19
- 3. PostgreSQL the community… Independentcommunity led by a Core Team of six Large, active and vibrant community www.postgresql.org Downloads, Mailing lists, Documentation Sponsors sampler: Google, Red Hat, VMWare, Skype, Salesforce, HP and EnterpriseDB http://www.postgresql.org/community/ Foreign Data Wrappers and You 3 / 19
- 4. EnterpriseDB the company… Theworldwide leader of Postgres based products and services founded in 2004 Customers include 50 of the Fortune 500 and 98 of the Forbes Global 2000 Enterprise offerings: PostgreSQL Support, Services and Training Postgres Plus Advanced Server with Oracle Compatibility Tools for Monitoring, Replication, HA, Backup & Recovery Community Citizenship Contributor of key features: Materialized Views, JSON, & more Nine community members on staff Foreign Data Wrappers and You 4 / 19
- 5. EnterpriseDB the company… ForeignData Wrappers and You 5 / 19
- 6. Why use ForeignData Wrappers Foreign data wrappers allow SQL access to data in: Postgres clusters on the same server, perhaps in different databases or clusters Remote Postgres servers Stored in non-Postgres data repositories Stored in data repositories with different performance and storage characteristics Foreign Data Wrappers and You 6 / 19
- 7. Foreign Data Wrappers(Database Federation) Foreign data wrappers (SQL MED) allow queries to read and write data to foreign data sources. Foreign database support includes: CouchDB Hadoop Informix MongoDB MySQL Neo4j Oracle Postgres Redis The transfer of joins, aggregates, and sorts to foreign servers is not yet implemented. EnterpriseDB Advanced Server’s database links provide similar functionality for Oracle. http://www.postgresql.org/docs/current/static/ddl-foreign-data.html http://wiki.postgresql.org/wiki/Foreign_data_wrappers Foreign Data Wrappers and You 7 / 19
- 8. Foreign Data Wrappersto Interfaces JDBC LDAP ODBC Foreign Data Wrappers and You 8 / 19
- 9. Foreign Data Wrappersto Non-Traditional Data Sources Files HTTP AWS S3 Twitter Foreign Data Wrappers and You 9 / 19
- 10. Foreign Data WrapperExample CREATE SERVER postgres_fdw_test FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host ’localhost’, dbname ’fdw_test’); CREATE USER MAPPING FOR PUBLIC SERVER postgres_fdw_test OPTIONS (password ’’); CREATE FOREIGN TABLE other_world (greeting TEXT) SERVER postgres_fdw_test OPTIONS (table_name ’world’); det List of foreign tables Schema | Table | Server --------+-------------+------------------- public | other_world | postgres_fdw_test (1 row) Foreign Postgres server name in red; foreign table name in blue Foreign Data Wrappers and You 10 / 19
- 11. Read and Read/WriteData Sources Postgres ora_tab tw_tab mon_tab MongoDB Twitter Oracle Foreign Data Wrappers and You 11 / 19
- 12. Complex FDW Capabilities Tablerestriction pushdown (supported by the postgres_fdw) Join/aggregate/sort pushdown to FDW sources that support it Parallel FDW access Cross-server snapshot synchronization (perhaps possible with serialization control) Cross-server transaction durability (possible with two-phase commit or XA transactions) Foreign Data Wrappers and You 12 / 19
- 13. Foreign Data WrapperPushdown (FDW) SQL Queries SQL Queries PG FDW Foreign Server Foreign Server Foreign Server Foreign Data Wrappers and You 13 / 19
- 14. FDW Sort/Join/Aggregate Pushdown SQLQueries SQL Queries PG FDW with joins, sorts, aggregates Foreign Server Foreign Server Foreign Server Foreign Data Wrappers and You 14 / 19
- 15. Parallel FDW Access SQLQueries PG FDW Parallel Queries Foreign Server Foreign Server Foreign Server Foreign Data Wrappers and You 15 / 19
- 16. Global Snapshot Manager SQLQueries SQL Queries Manager Global Snapshot PG FDW Foreign Server Foreign Server Foreign Server Foreign Data Wrappers and You 16 / 19
- 17. Global Transaction Manager SQLQueries SQL Queries Foreign Server Manager Global Transaction PG FDW Foreign Server Foreign Server Foreign Data Wrappers and You 17 / 19
- 18. Additional Resources… Postgres Downloads: www.enterprisedb.com/downloads Productand Services information: info@enterprisedb.com Foreign Data Wrappers and You 18 / 19
- 19.