<!-- doc/src/sgml/postgres-fdw.sgml -->

<sect1 id="postgres-fdw" xreflabel="postgres_fdw">
 <title>postgres_fdw &mdash;
   access data stored in external <productname>PostgreSQL</productname>
   servers</title>

 <indexterm zone="postgres-fdw">
  <primary>postgres_fdw</primary>
 </indexterm>

 <para>
  The <filename>postgres_fdw</filename> module provides the foreign-data wrapper
  <literal>postgres_fdw</literal>, which can be used to access data
  stored in external <productname>PostgreSQL</productname> servers.
 </para>

 <para>
  The functionality provided by this module overlaps substantially
  with the functionality of the older <xref linkend="dblink"/> module.
  But <filename>postgres_fdw</filename> provides more transparent and
  standards-compliant syntax for accessing remote tables, and can give
  better performance in many cases.
 </para>

 <para>
  To prepare for remote access using <filename>postgres_fdw</filename>:
  <orderedlist spacing="compact">
   <listitem>
    <para>
     Install the  <filename>postgres_fdw</filename> extension using <xref
     linkend="sql-createextension"/>.
    </para>
   </listitem>
   <listitem>
    <para>
     Create a foreign server object, using <xref linkend="sql-createserver"/>,
     to represent each remote database you want to connect to.
     Specify connection information, except <literal>user</literal> and
     <literal>password</literal>, as options of the server object.
    </para>
   </listitem>
   <listitem>
    <para>
     Create a user mapping, using <xref linkend="sql-createusermapping"/>, for
     each database user you want to allow to access each foreign server.
     Specify the remote user name and password to use as
     <literal>user</literal> and <literal>password</literal> options of the
     user mapping.
    </para>
   </listitem>
   <listitem>
    <para>
     Create a foreign table, using <xref linkend="sql-createforeigntable"/>
     or <xref linkend="sql-importforeignschema"/>,
     for each remote table you want to access.  The columns of the foreign
     table must match the referenced remote table.  You can, however, use
     table and/or column names different from the remote table's, if you
     specify the correct remote names as options of the foreign table object.
    </para>
   </listitem>
  </orderedlist>
 </para>

 <para>
  Now you need only <command>SELECT</command> from a foreign table to access
  the data stored in its underlying remote table.  You can also modify
  the remote table using <command>INSERT</command>, <command>UPDATE</command>,
  <command>DELETE</command>, <command>COPY</command>, or
  <command>TRUNCATE</command>.
  (Of course, the remote user you have specified in your user mapping must
  have privileges to do these things.)
 </para>

 <para>
  Note that the <literal>ONLY</literal> option specified in
  <command>SELECT</command>, <command>UPDATE</command>,
  <command>DELETE</command> or <command>TRUNCATE</command>
  has no effect when accessing or modifying the remote table.
 </para>

 <para>
  Note that <filename>postgres_fdw</filename> currently lacks support for
  <command>INSERT</command> statements with an <literal>ON CONFLICT DO
  UPDATE</literal> clause.  However, the <literal>ON CONFLICT DO NOTHING</literal>
  clause is supported, provided a unique index inference specification
  is omitted.
  Note also that <filename>postgres_fdw</filename> supports row movement
  invoked by <command>UPDATE</command> statements executed on partitioned
  tables, but it currently does not handle the case where a remote partition
  chosen to insert a moved row into is also an <command>UPDATE</command>
  target partition that will be updated elsewhere in the same command.
 </para>

 <para>
  It is generally recommended that the columns of a foreign table be declared
  with exactly the same data types, and collations if applicable, as the
  referenced columns of the remote table.  Although <filename>postgres_fdw</filename>
  is currently rather