PostgreSQL wiki - User contributions [en]

Apt

2014-07-06T15:17:15Z

Dve: /* Quickstart */ Get wget and root certs before using them

==PostgreSQL packages for Debian and Ubuntu==

The PostgreSQL Global Development Group (PGDG) maintains an APT repository of PostgreSQL packages for Debian and Ubuntu located at http://apt.postgresql.org/pub/repos/apt/.
We aim at building PostgreSQL server packages as well as extensions and modules packages on several Debian/Ubuntu releases for all PostgreSQL versions supported.

Currently, we support

* '''Debian 6.0''' (squeeze), '''7.0''' (wheezy), and '''unstable''' (sid) 64/32 bit (amd64/i386)
* '''Ubuntu 10.04''' (lucid), '''12.04''' (precise), '''13.10''' (saucy), '''14.04''' (trusty) 64/32 bit (amd64/i386)
* '''PostgreSQL 8.4, 9.0, 9.1, 9.2, 9.3, [[Apt/FAQ#I_want_to_try_the_beta_version_of_the_next_PostgreSQL_release|9.4 beta]]'''
* Server extensions such as Slony-I, various PL languages, and datatypes
* Applications like pgadmin3, pgbouncer, and pgpool-II

Packages for older PostgreSQL versions and older Debian/Ubuntu distributions will continue to stay in the repository; updates for those will be provided on an ad-hoc basis.

==Quickstart==

Create '''/etc/apt/sources.list.d/pgdg.list'''. The distributions are called ''codename'''''-pgdg'''. In the example, replace ''wheezy'' with the actual distribution you are using:

<nowiki>deb http://apt.postgresql.org/pub/repos/apt/</nowiki> ''wheezy''-pgdg main

(You may determine the codename of your distribution by running '''lsb_release -c'''.)

Import the repository key from '''https://www.postgresql.org/media/keys/ACCC4CF8.asc''', update the package lists, and start installing packages:

<nowiki>sudo apt-get install wget ca-certificates
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -</nowiki>
sudo apt-get update
sudo apt-get upgrade
sudo apt-get install postgresql-9.3 pgadmin3

Alternately, [http://anonscm.debian.org/loggerhead/pkg-postgresql/postgresql-common/trunk/download/head:/apt.postgresql.org.s-20130224224205-px3qyst90b3xp8zj-1/apt.postgresql.org.sh this shell script] will do the above steps for you.

'''9.4 beta only:''' See [[Apt/FAQ#I_want_to_try_the_beta_version_of_the_next_PostgreSQL_release|FAQ on beta releases]]

Have a look at the '''[[Apt/FAQ|FAQ]]'''.

'''Note:''' This repository provides "postgresql", "postgresql-contrib", and "postgresql-client" ''meta-packages'' that depend on the latest postgresql-x.y, ... packages, similar to the ones present in Debian and Ubuntu. Once a new PostgreSQL version is released, these meta-packages will be updated to depend on the new version. If you rather want to stay with a particular PostgreSQL version, you should install specific packages like "postgresql-9.3" instead of "postgresql".

==News==

* 2014-05-16: 9.4 beta packages available: http://www.df7cb.de/blog/2014/PostgreSQL_9.4_on_Debian.html
* 2014-02-18: Ubuntu saucy (13.10) added (seeded with a copy of the packages from precise)
* 2014-01-09: Ubuntu trusty (14.04) packages available: http://www.postgresql.org/message-id/20140109124716.GA5659@msgid.credativ.de
* 2013-11-22: Users concerned about the [[Nov2013ReplicationIssue]] can find 9.2.4/9.1.9/9.0.13 packages at <strike>http://apt.postgresql.org/pub/repos/apt/nov2013replicationissue/</strike>
* 2013-10-10: New pgdg-keyring version extending the key expiration date. The old expiration date is 2013-10-13. Announcement: http://www.postgresql.org/message-id/20131010083115.GC24812@msgid.df7cb.de Additional blog post: http://blog.endpoint.com/2013/10/postgresql-debian-apt-repository-gnupg.html
* 2013-09-09: postgresql-9.3 released! (This also includes new postgresql-9.2 and postgresql-common packages.)
* 2013-05-12: postgresql-9.3 beta1 is now available, see [[Apt/FAQ#I_want_to_try_the_beta_version_of_the_next_PostgreSQL_release|FAQ entry]] for instructions
* 2013-05-11: Temporary problems with libpq5 packages: http://www.postgresql.org/message-id/20130511050816.GB8469@msgid.df7cb.de
* 2013-04-10: New postgresql-common version 141 introducing a dependency on the pgdg-keyring package. (If you were following the (old) installation instructions, you already have that package installed.)
* 2013-02-23: pgapt.debian.net is gone now. If you were using that, change to apt.postgresql.org using the instructions above.
* 2013-02-22: Default repository pin priority changed; see [[Apt/FAQ#I_want_only_specific_packages_from_this_repository|Apt pinning]] for details.
* 2012-12-18: Ubuntu Lucid (10.04) is now part of the repository; PostgreSQL and pgadmin3 packages are built.
* 2012-12-06: Official announcement: http://archives.postgresql.org/pgsql-announce/2012-12/msg00008.php, http://www.postgresql.org/about/news/1432/
* 2012-12-06: Minor releases 9.2.2, 9.1.7, 9.0.11, 8.4.15, 8.3.22 built.
* 2012-11-30: pgadmin3 added to the repository
* 2012-11-22: Ubuntu Precise (12.04) added to the repository, and built 9.2.1, 9.1.6, 9.0.10, 8.4.14, 8.3.21 for it.
* 2012-10-13: Repository key renewed with new expiry date.
* 2012-10-02: Minor releases 9.2.1, 9.1.6, 9.0.10, 8.4.14, 8.3.21 built
* 2012-09-15: 9.2.0 is in the repository
* 2012-08-22: PostgreSQL in Debian Hackathon: [http://www.df7cb.de/blog/2012/PostgreSQL_in_Debian_Hackathon.html Blog posting] and [https://github.com/dimitri/apt.postgresql.org/blob/master/hackaton-20120821.md notes]. The distribution names changed from *-pgapt to *-pgdg, please update your sources.list.
* 2012-06-07: 9.2 added, Lenny marked unsupported now
* 2012-06-07: separate components "9.0", "9.2" etc added for libpq5/libpg-dev/lib... packages that would otherwise overwrite packages from other versions in the same suite. (Usually, using the latest stable libpq5 version will be ok for users. The packages built from other PostgreSQL versions are provided only for completeness.)

==Resources==

* [[Apt/FAQ|FAQ]]
* [https://pgdgbuild.dus.dg-i.net/repos/apt/dists/ Overview of available packages and versions] (WIP)
* [http://apt.postgresql.org/pub/repos/apt/dists/ Dists directory]
* [http://apt.postgresql.org/pub/repos/apt/pool/ Packages pool directory]
* [[ExtensionPackaging]] (PostgreSQL Wiki)
* [http://wiki.debian.org/pkg-postgresql pkg-postgresql] (Debian Wiki)

===Contact===

* Mailing list: pgsql-pkg-debian@postgresql.org ([http://archives.postgresql.org/pgsql-pkg-debian/ Archives])
* IRC channel: #postgresql-apt @ irc.freenode.net
* People: Christoph Berg <myon@debian.org>, Dimitri Fontaine <dimitri@2ndquadrant.fr>, Magnus Hagander <magnus@hagander.net>

===Bugs===

Please report bugs on the pgsql-pkg-debian@postgresql.org mailing list, or open a bug in the [http://bugs.debian.org/ Debian BTS].

===Documentation===

* [[Apt/RepoDocs]]
* [[Apt/Jenkins]]
* [[Apt/NewPostgreSQLVersion]]

==Acknowledgements==

Work on setting up the archive was kindly supported by [http://www.credativ.de/ credativ], [http://www.2ndquadrant.com/ 2ndQuadrant], [http://redpill-linpro.com/ Redpill Linpro],
and funding from the European Union's Seventh Framework Programme (FP7/2007-2013) under grant agreement 258862.

The build server is kindly hosted by [https://www.dg-i.net/ DG-i]. The archive is hosted on postgresql.org hardware.

Foreign data wrappers

2013-01-16T14:32:25Z

Dve: /* Foreign Data Wrappers */ add link to fdw writing docs

= Foreign Data Wrappers =
In 2003, a new extension 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 in SQL databases. In 2011, PostgreSQL 9.1 was released with a great support of this standard.

In a nutshell, you can now use various Foreign Data Wrappers (FDW) to connect a PostgreSQL Server to remote data stores. This page is an incomplete list of the Wrappers available right now. Another [http://pgxn.org/tag/fdw/ fdw list] can be found at [http://pgxn.org/ the PGXN website].

The information about how to write a FDW can be found [http://www.postgresql.org/docs/9.2/static/fdwhandler.html here].

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!

== SQL Databases Wrappers ==

===oracle_fdw===
* [http://pgfoundry.org/projects/oracle-fdw/ source code]
* [http://pgxn.org/dist/oracle_fdw/ install with PGXN]

===mysql_fdw===
This extension implements a Foreign Data Wrapper for MySQL. It is supported on PostgreSQL 9.1 and above.
* [http://github.com/dpage/mysql_fdw source code]
* [http://pgxn.org/dist/mysql_fdw/ install with PGXN]
* [http://blogs.enterprisedb.com/2011/08/01/postgresql-9-1-meet-mysql/ example]

===tds_fdw(not working)===
This project is not maintained anymore. Consider using odbc_fdw below to access Microsoft SQL Server and derived.
I guess you can meanwhile do the reverse, access Postgresql database within Microsoft SQL Server using Linked Servers.

===odbc_fdw===
A wrapper for databases with an ODBC driver, including Oracle, DB2, Microsoft SQL Server, Sybase, Pervasive SQL, IBM Lotus Domino, MySQL, PostgreSQL and desktop database products such as FileMaker and Microsoft Access:
* [https://github.com/ZhengYang/odbc_fdw source code]
* [http://pgxn.org/dist/odbc_fdw/ install with PGXN]
* [http://www.postgresonline.com/journal/archives/249-ODBC-Foreign-Data-wrapper-to-query-SQL-Server-on-Window---Part-2.html Using odbc_fdw on windows to query sql server (Postgres OnLine Journal March/April 2012)]

===jdbc_fdw===
A wrapper for databases with a JDBC driver.
* [https://github.com/atris/JDBC_FDW source code]

===informix_fdw (WIP)===
* [https://github.com/credativ/informix_fdw source code]

== NoSQL Databases Wrappers ==

===couchdb_fdw===
A wrapper for [http://couchdb.apache.org/ CouchDB]
* [https://github.com/ZhengYang/couchdb_fdw source code]
* [http://pgxn.org/dist/couchdb_fdw/ install with PGXN]

===mongo_fdw===
A wrapper for [http://www.mongodb.org/ MongoDB]
* [https://github.com/citusdata/mongo_fdw source code]
* [http://pgxn.org/dist/mongo_fdw/ install with PGXN]

===redis_fdw===
A wrapper for [http://redis.io/ Redis]
* [https://github.com/dpage/redis_fdw source code]
* [http://pgxn.org/dist/redis_fdw/ install with PGXN]

== File Wrappers ==

===file_fdw===
A CSV files wrapper. Delivered as an official extension of PostgreSQL 9.1
* [http://www.postgresql.org/docs/9.1/static/file-fdw.html documentation]
* [http://www.depesz.com/index.php/2011/03/14/waiting-for-9-1-foreign-data-wrapper/ example]
* [http://www.postgresonline.com/journal/archives/250-File-FDW-Family-Part-1-file_fdw.html Another example] Postgres OnLine Journal May 2012

===file_text_array_fdw===
Another CSV wrapper
* [https://github.com/adunstan/file_text_array_fdw source code]
* [http://www.postgresonline.com/journal/archives/251-File-FDW-Family-Part-2-file_textarray_fdw-Foreign-Data-Wrapper.html How to use it] Postgres OnLine Journal May 2012

===file_fixed_length_record_fdw===
Fixed-width flat file wrapper
* [https://github.com/adunstan/file_fixed_length_record_fdw source code]

== Others ==

===twitter_fdw===
A wrapper fetching text messages from Twitter over the Internet and returning a table
* [https://github.com/umitanuki/twitter_fdw source code]
* [http://pgxn.org/dist/twitter_fdw/ install with PGXN]

===ldap_fdw===
Allows PostgreSQL to query an LDAP server and retrieve data from some pre-configured Organizational Unit
* [https://github.com/guedes/ldap_fdw source code]
* [http://pgxn.org/dist/ldap_fdw/ install with PGXN]

===PGStrom===

uses GPU devices to accelarate sequential scan on massive amount of records with complex qualifiers.
* http://wiki.postgresql.org/wiki/PGStrom

===s3_fdw===
Reads files located in Amazon S3
* [https://github.com/umitanuki/s3_fdw source code]
* [http://pgxn.org/dist/s3_fdw/ install with PGXN]

===www_fdw===
Allows to query different web services:
* [https://github.com/cyga/www_fdw/ source code]
* [https://github.com/cyga/www_fdw/wiki documentation/examples]
* [http://pgxn.org/dist/www_fdw/ install with PGXN]

=Multicorn Foreign Data Wrappers=

[http://multicorn.org/ Multicorn] is an extension that allows FDWs to be written in Python

== SQL Database Wrappers ==

===multicorn.sqlalchemyfdw===
This fdw can be used to access data stored in any database supported by the sqlalchemy python toolkit.
* [http://multicorn.org/foreign-data-wrappers/#sqlalchemy-foreign-data-wrapper documentation]

== File Wrappers ==

===muticorn.fsfdw===
This fdw can be used to 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.
* [http://multicorn.org/foreign-data-wrappers/#filesystem-foreign-data-wrapper documentation]

===multicorn.csvfdw===
This fdw can be used to access data stored in CSV files. Each column defined in the table will be mapped, in order, against columns in the CSV file.
* [http://multicorn.org/foreign-data-wrappers/#csv-foreign-data-wrapper documentation]

== Others ==

===multicorn.rssfdw===
This fdw can be used to access items from an rss feed.
* [http://multicorn.org/foreign-data-wrappers/#rss-foreign-data-wrapper documentation]

===multicorn.hivefdw===
Used to access Apache Hive tables.
* [https://github.com/youngwookim/hive-fdw-for-postgresql source code]
[[Category:Foreign-data wrapper|!]]