doc/src/sgml/postgres-fdw.sgml

   1 <!-- doc/src/sgml/postgres-fdw.sgml -->
   2
   3 <sect1 id="postgres-fdw" xreflabel="postgres_fdw">
   4  <title>postgres_fdw &mdash;
   5    access data stored in external <productname>PostgreSQL</productname>
   6    servers</title>
   7
   8  <indexterm zone="postgres-fdw">
   9   <primary>postgres_fdw</primary>
  10  </indexterm>
  11
  12  <para>
  13   The <filename>postgres_fdw</filename> module provides the foreign-data wrapper
  14   <literal>postgres_fdw</literal>, which can be used to access data
  15   stored in external <productname>PostgreSQL</productname> servers.
  16  </para>
  17
  18  <para>
  19   The functionality provided by this module overlaps substantially
  20   with the functionality of the older <xref linkend="dblink"/> module.
  21   But <filename>postgres_fdw</filename> provides more transparent and
  22   standards-compliant syntax for accessing remote tables, and can give
  23   better performance in many cases.
  24  </para>
  25
  26  <para>
  27   To prepare for remote access using <filename>postgres_fdw</filename>:
  28   <orderedlist spacing="compact">
  29    <listitem>
  30     <para>
  31      Install the  <filename>postgres_fdw</filename> extension using <xref
  32      linkend="sql-createextension"/>.
  33     </para>
  34    </listitem>
  35    <listitem>
  36     <para>
  37      Create a foreign server object, using <xref linkend="sql-createserver"/>,
  38      to represent each remote database you want to connect to.
  39      Specify connection information, except <literal>user</literal> and
  40      <literal>password</literal>, as options of the server object.
  41     </para>
  42    </listitem>
  43    <listitem>
  44     <para>
  45      Create a user mapping, using <xref linkend="sql-createusermapping"/>, for
  46      each database user you want to allow to access each foreign server.
  47      Specify the remote user name and password to use as
  48      <literal>user</literal> and <literal>password</literal> options of the
  49      user mapping.
  50     </para>
  51    </listitem>
  52    <listitem>
  53     <para>
  54      Create a foreign table, using <xref linkend="sql-createforeigntable"/>
  55      or <xref linkend="sql-importforeignschema"/>,
  56      for each remote table you want to access.  The columns of the foreign
  57      table must match the referenced remote table.  You can, however, use
  58      table and/or column names different from the remote table's, if you
  59      specify the correct remote names as options of the foreign table object.
  60     </para>
  61    </listitem>
  62   </orderedlist>
  63  </para>
  64
  65  <para>
  66   Now you need only <command>SELECT</command> from a foreign table to access
  67   the data stored in its underlying remote table.  You can also modify
  68   the remote table using <command>INSERT</command>, <command>UPDATE</command>,
  69   <command>DELETE</command>, <command>COPY</command>, or
  70   <command>TRUNCATE</command>.
  71   (Of course, the remote user you have specified in your user mapping must
  72   have privileges to do these things.)
  73  </para>
  74
  75  <para>
  76   Note that the <literal>ONLY</literal> option specified in
  77   <command>SELECT</command>, <command>UPDATE</command>,
  78   <command>DELETE</command> or <command>TRUNCATE</command>
  79   has no effect when accessing or modifying the remote table.
  80  </para>
  81
  82  <para>
  83   Note that <filename>postgres_fdw</filename> currently lacks support for
  84   <command>INSERT</command> statements with an <literal>ON CONFLICT DO
  85   UPDATE</literal> clause.  However, the <literal>ON CONFLICT DO NOTHING</literal>
  86   clause is supported, provided a unique index inference specification
  87   is omitted.
  88   Note also that <filename>postgres_fdw</filename> supports row movement
  89   invoked by <command>UPDATE</command> statements executed on partitioned
  90   tables, but it currently does not handle the case where a remote partition
  91   chosen to insert a moved row into is also an <command>UPDATE</command>
  92   target partition that will be updated elsewhere in the same command.
  93  </para>
  94
  95  <para>
  96   It is generally recommended that the columns of a foreign table be declared
  97   with exactly the same data types, and collations if applicable, as the
  98   referenced columns of the remote table.  Although <filename>postgres_fdw</filename>
  99   is currently rather forgiving about performing data type conversions at
 100   need, surprising semantic anomalies may arise when types or collations do
 101   not match, due to the remote server interpreting query conditions
 102   differently from the local server.
 103  </para>
 104
 105  <para>
 106   Note that a foreign table can be declared with fewer columns, or with a
 107   different column order, than its underlying remote table has.  Matching
 108   of columns to the remote table is by name, not position.
 109  </para>
 110
 111  <sect2 id="postgres-fdw-options">
 112   <title>FDW Options of postgres_fdw</title>
 113
 114   <sect3 id="postgres-fdw-options-connection">
 115    <title>Connection Options</title>
 116
 117    <para>
 118     A foreign server using the <filename>postgres_fdw</filename> foreign data wrapper
 119     can have the same options that <application>libpq</application> accepts in
 120     connection strings, as described in <xref linkend="libpq-paramkeywords"/>,
 121     except that these options are not allowed or have special handling:
 122
 123     <itemizedlist spacing="compact">
 124      <listitem>
 125       <para>
 126        <literal>user</literal>, <literal>password</literal> and <literal>sslpassword</literal> (specify these
 127        in a user mapping instead, or use a service file)
 128       </para>
 129      </listitem>
 130      <listitem>
 131       <para>
 132        <literal>client_encoding</literal> (this is automatically set from the local
 133        server encoding)
 134       </para>
 135      </listitem>
 136      <listitem>
 137       <para>
 138        <literal>application_name</literal> - this may appear in
 139        <emphasis>either or both</emphasis> a connection and
 140        <xref linkend="guc-pgfdw-application-name"/>.
 141        If both are present, <varname>postgres_fdw.application_name</varname>
 142        overrides the connection setting.
 143        Unlike <application>libpq</application>,
 144        <filename>postgres_fdw</filename> allows
 145        <varname>application_name</varname> to include
 146        <quote>escape sequences</quote>.
 147        See <xref linkend="guc-pgfdw-application-name"/> for details.
 148       </para>
 149      </listitem>
 150      <listitem>
 151       <para>
 152        <literal>fallback_application_name</literal> (always set to
 153        <literal>postgres_fdw</literal>)
 154       </para>
 155      </listitem>
 156      <listitem>
 157       <para>
 158        <literal>sslkey</literal> and <literal>sslcert</literal> - these may
 159        appear in <emphasis>either or both</emphasis> a connection and a user
 160        mapping. If both are present, the user mapping setting overrides the
 161        connection setting.
 162       </para>
 163      </listitem>
 164     </itemizedlist>
 165    </para>
 166
 167    <para>
 168     Only superusers may create or modify user mappings with the
 169     <literal>sslcert</literal> or <literal>sslkey</literal> settings.
 170    </para>
 171    <para>
 172     Non-superusers may connect to foreign servers using password
 173     authentication or with GSSAPI delegated credentials, so specify the
 174     <literal>password</literal> option for user mappings belonging to
 175     non-superusers where password authentication is required.
 176    </para>
 177    <para>
 178     A superuser may override this check on a per-user-mapping basis by setting
 179     the user mapping option <literal>password_required 'false'</literal>, e.g.,
 180 <programlisting>
 181 ALTER USER MAPPING FOR some_non_superuser SERVER loopback_nopw
 182 OPTIONS (ADD password_required 'false');
 183 </programlisting>
 184     To prevent unprivileged users from exploiting the authentication rights
 185     of the unix user the postgres server is running as to escalate to superuser
 186     rights, only the superuser may set this option on a user mapping.
 187     </para>
 188     <para>
 189     Care is required to ensure that this does not allow the mapped
 190     user the ability to connect as superuser to the mapped database per
 191     CVE-2007-3278 and CVE-2007-6601. Don't set
 192     <literal>password_required=false</literal>
 193     on the <literal>public</literal> role. Keep in mind that the mapped
 194     user can potentially use any client certificates,
 195     <filename>.pgpass</filename>,
 196     <filename>.pg_service.conf</filename> etc. in the unix home directory of the
 197     system user the postgres server runs as.   (For details on how home
 198     directories are found, see <xref linkend="libpq-pgpass"/>.)  They can
 199     also use any trust
 200     relationship granted by authentication modes like <literal>peer</literal>
 201     or <literal>ident</literal> authentication.
 202    </para>
 203   </sect3>
 204
 205   <sect3 id="postgres-fdw-options-object-name">
 206    <title>Object Name Options</title>
 207
 208    <para>
 209     These options can be used to control the names used in SQL statements
 210     sent to the remote <productname>PostgreSQL</productname> server.  These
 211     options are needed when a foreign table is created with names different
 212     from the underlying remote table's names.
 213    </para>
 214
 215    <variablelist>
 216
 217     <varlistentry>
 218      <term><literal>schema_name</literal> (<type>string</type>)</term>
 219      <listitem>
 220       <para>
 221        This option, which can be specified for a foreign table, gives the
 222        schema name to use for the foreign table on the remote server.  If this
 223        option is omitted, the name of the foreign table's schema is used.
 224       </para>
 225      </listitem>
 226     </varlistentry>
 227
 228     <varlistentry>
 229      <term><literal>table_name</literal> (<type>string</type>)</term>
 230      <listitem>
 231       <para>
 232        This option, which can be specified for a foreign table, gives the
 233        table name to use for the foreign table on the remote server.  If this
 234        option is omitted, the foreign table's name is used.
 235       </para>
 236      </listitem>
 237     </varlistentry>
 238
 239     <varlistentry>
 240      <term><literal>column_name</literal> (<type>string</type>)</term>
 241      <listitem>
 242       <para>
 243        This option, which can be specified for a column of a foreign table,
 244        gives the column name to use for the column on the remote server.
 245        If this option is omitted, the column's name is used.
 246       </para>
 247      </listitem>
 248     </varlistentry>
 249
 250    </variablelist>
 251
 252   </sect3>
 253
 254   <sect3 id="postgres-fdw-options-cost-estimation">
 255    <title>Cost Estimation Options</title>
 256
 257    <para>
 258     <filename>postgres_fdw</filename> retrieves remote data by executing queries
 259     against remote servers, so ideally the estimated cost of scanning a
 260     foreign table should be whatever it costs to be done on the remote
 261     server, plus some overhead for communication.  The most reliable way to
 262     get such an estimate is to ask the remote server and then add something
 263     for overhead &mdash; but for simple queries, it may not be worth the cost
 264     of an additional remote query to get a cost estimate.
 265     So <filename>postgres_fdw</filename> provides the following options to control
 266     how cost estimation is done:
 267    </para>
 268
 269    <variablelist>
 270
 271     <varlistentry>
 272      <term><literal>use_remote_estimate</literal> (<type>boolean</type>)</term>
 273      <listitem>
 274       <para>
 275        This option, which can be specified for a foreign table or a foreign
 276        server, controls whether <filename>postgres_fdw</filename> issues remote
 277        <command>EXPLAIN</command> commands to obtain cost estimates.
 278        A setting for a foreign table overrides any setting for its server,
 279        but only for that table.
 280        The default is <literal>false</literal>.
 281       </para>
 282      </listitem>
 283     </varlistentry>
 284
 285     <varlistentry>
 286      <term><literal>fdw_startup_cost</literal> (<type>floating point</type>)</term>
 287      <listitem>
 288       <para>
 289        This option, which can be specified for a foreign server, is a floating
 290        point value that is added to the estimated startup cost of any
 291        foreign-table scan on that server.  This represents the additional
 292        overhead of establishing a connection, parsing and planning the query on
 293        the remote side, etc.
 294        The default value is <literal>100</literal>.
 295       </para>
 296      </listitem>
 297     </varlistentry>
 298
 299     <varlistentry>
 300      <term><literal>fdw_tuple_cost</literal> (<type>floating point</type>)</term>
 301      <listitem>
 302       <para>
 303        This option, which can be specified for a foreign server, is a floating
 304        point value that is used as extra cost per-tuple for foreign-table
 305        scans on that server.  This represents the additional overhead of
 306        data transfer between servers.  You might increase or decrease this
 307        number to reflect higher or lower network delay to the remote server.
 308        The default value is <literal>0.2</literal>.
 309       </para>
 310      </listitem>
 311     </varlistentry>
 312
 313    </variablelist>
 314
 315    <para>
 316     When <literal>use_remote_estimate</literal> is true,
 317     <filename>postgres_fdw</filename> obtains row count and cost estimates from the
 318     remote server and then adds <literal>fdw_startup_cost</literal> and
 319     <literal>fdw_tuple_cost</literal> to the cost estimates.  When
 320     <literal>use_remote_estimate</literal> is false,
 321     <filename>postgres_fdw</filename> performs local row count and cost estimation
 322     and then adds <literal>fdw_startup_cost</literal> and
 323     <literal>fdw_tuple_cost</literal> to the cost estimates.  This local
 324     estimation is unlikely to be very accurate unless local copies of the
 325     remote table's statistics are available.  Running
 326     <xref linkend="sql-analyze"/> on the foreign table is the way to update
 327     the local statistics; this will perform a scan of the remote table and
 328     then calculate and store statistics just as though the table were local.
 329     Keeping local statistics can be a useful way to reduce per-query planning
 330     overhead for a remote table &mdash; but if the remote table is
 331     frequently updated, the local statistics will soon be obsolete.
 332    </para>
 333
 334    <para>
 335     The following option controls how such an <command>ANALYZE</command>
 336     operation behaves:
 337    </para>
 338
 339    <variablelist>
 340
 341     <varlistentry>
 342      <term><literal>analyze_sampling</literal> (<type>string</type>)</term>
 343      <listitem>
 344       <para>
 345        This option, which can be specified for a foreign table or a foreign
 346        server, determines if <command>ANALYZE</command> on a foreign table
 347        samples the data on the remote side, or reads and transfers all data
 348        and performs the sampling locally. The supported values
 349        are <literal>off</literal>, <literal>random</literal>,
 350        <literal>system</literal>, <literal>bernoulli</literal>
 351        and <literal>auto</literal>. <literal>off</literal> disables remote
 352        sampling, so all data are transferred and sampled locally.
 353        <literal>random</literal> performs remote sampling using the
 354        <literal>random()</literal> function to choose returned rows,
 355        while <literal>system</literal> and <literal>bernoulli</literal> rely
 356        on the built-in <literal>TABLESAMPLE</literal> methods of those
 357        names. <literal>random</literal> works on all remote server versions,
 358        while <literal>TABLESAMPLE</literal> is supported only since 9.5.
 359        <literal>auto</literal> (the default) picks the recommended sampling
 360        method automatically; currently it means
 361        either <literal>bernoulli</literal> or <literal>random</literal>
 362        depending on the remote server version.
 363       </para>
 364      </listitem>
 365     </varlistentry>
 366
 367    </variablelist>
 368
 369   </sect3>
 370
 371   <sect3 id="postgres-fdw-options-remote-execution">
 372    <title>Remote Execution Options</title>
 373
 374    <para>
 375     By default, only <literal>WHERE</literal> clauses using built-in operators and
 376     functions will be considered for execution on the remote server.  Clauses
 377     involving non-built-in functions are checked locally after rows are
 378     fetched.  If such functions are available on the remote server and can be
 379     relied on to produce the same results as they do locally, performance can
 380     be improved by sending such <literal>WHERE</literal> clauses for remote
 381     execution.  This behavior can be controlled using the following option:
 382    </para>
 383
 384    <variablelist>
 385
 386     <varlistentry>
 387      <term><literal>extensions</literal> (<type>string</type>)</term>
 388      <listitem>
 389       <para>
 390        This option is a comma-separated list of names
 391        of <productname>PostgreSQL</productname> extensions that are installed, in
 392        compatible versions, on both the local and remote servers.  Functions
 393        and operators that are immutable and belong to a listed extension will
 394        be considered shippable to the remote server.
 395        This option can only be specified for foreign servers, not per-table.
 396       </para>
 397
 398       <para>
 399        When using the <literal>extensions</literal> option, <emphasis>it is the
 400        user's responsibility</emphasis> that the listed extensions exist and behave
 401        identically on both the local and remote servers.  Otherwise, remote
 402        queries may fail or behave unexpectedly.
 403       </para>
 404      </listitem>
 405     </varlistentry>
 406
 407     <varlistentry>
 408      <term><literal>fetch_size</literal> (<type>integer</type>)</term>
 409      <listitem>
 410       <para>
 411        This option specifies the number of rows <filename>postgres_fdw</filename>
 412        should get in each fetch operation. It can be specified for a foreign
 413        table or a foreign server. The option specified on a table overrides
 414        an option specified for the server.
 415        The default is <literal>100</literal>.
 416       </para>
 417      </listitem>
 418     </varlistentry>
 419
 420     <varlistentry>
 421      <term><literal>batch_size</literal> (<type>integer</type>)</term>
 422      <listitem>
 423       <para>
 424        This option specifies the number of rows <filename>postgres_fdw</filename>
 425        should insert in each insert operation. It can be specified for a
 426        foreign table or a foreign server. The option specified on a table
 427        overrides an option specified for the server.
 428        The default is <literal>1</literal>.
 429       </para>
 430
 431       <para>
 432        Note the actual number of rows <filename>postgres_fdw</filename> inserts at
 433        once depends on the number of columns and the provided
 434        <literal>batch_size</literal> value. The batch is executed as a single
 435        query, and the libpq protocol (which <filename>postgres_fdw</filename>
 436        uses to connect to a remote server) limits the number of parameters in a
 437        single query to 65535. When the number of columns * <literal>batch_size</literal>
 438        exceeds the limit, the <literal>batch_size</literal> will be adjusted to
 439        avoid an error.
 440       </para>
 441
 442       <para>
 443        This option also applies when copying into foreign tables.  In that case
 444        the actual number of rows <filename>postgres_fdw</filename> copies at
 445        once is determined in a similar way to the insert case, but it is
 446        limited to at most 1000 due to implementation restrictions of the
 447        <command>COPY</command> command.
 448       </para>
 449      </listitem>
 450     </varlistentry>
 451
 452    </variablelist>
 453
 454   </sect3>
 455
 456   <sect3 id="postgres-fdw-options-asynchronous-execution">
 457    <title>Asynchronous Execution Options</title>
 458
 459    <para>
 460     <filename>postgres_fdw</filename> supports asynchronous execution, which
 461     runs multiple parts of an <structname>Append</structname> node
 462     concurrently rather than serially to improve performance.
 463     This execution can be controlled using the following option:
 464    </para>
 465
 466    <variablelist>
 467
 468     <varlistentry>
 469      <term><literal>async_capable</literal> (<type>boolean</type>)</term>
 470      <listitem>
 471       <para>
 472        This option controls whether <filename>postgres_fdw</filename> allows
 473        foreign tables to be scanned concurrently for asynchronous execution.
 474        It can be specified for a foreign table or a foreign server.
 475        A table-level option overrides a server-level option.
 476        The default is <literal>false</literal>.
 477       </para>
 478
 479       <para>
 480        In order to ensure that the data being returned from a foreign server
 481        is consistent, <filename>postgres_fdw</filename> will only open one
 482        connection for a given foreign server and will run all queries against
 483        that server sequentially even if there are multiple foreign tables
 484        involved, unless those tables are subject to different user mappings.
 485        In such a case, it may be more performant to disable this option to
 486        eliminate the overhead associated with running queries asynchronously.
 487       </para>
 488
 489       <para>
 490        Asynchronous execution is applied even when an
 491        <structname>Append</structname> node contains subplan(s) executed
 492        synchronously as well as subplan(s) executed asynchronously.
 493        In such a case, if the asynchronous subplans are ones processed using
 494        <filename>postgres_fdw</filename>, tuples from the asynchronous
 495        subplans are not returned until after at least one synchronous subplan
 496        returns all tuples, as that subplan is executed while the asynchronous
 497        subplans are waiting for the results of asynchronous queries sent to
 498        foreign servers.
 499        This behavior might change in a future release.
 500       </para>
 501      </listitem>
 502     </varlistentry>
 503
 504    </variablelist>
 505   </sect3>
 506
 507   <sect3 id="postgres-fdw-options-transaction-management">
 508    <title>Transaction Management Options</title>
 509
 510    <para>
 511     As described in the Transaction Management section, in
 512     <filename>postgres_fdw</filename> transactions are managed by creating
 513     corresponding remote transactions, and subtransactions are managed by
 514     creating corresponding remote subtransactions. When multiple remote
 515     transactions are involved in the current local transaction, by default
 516     <filename>postgres_fdw</filename> commits or aborts those remote
 517     transactions serially when the local transaction is committed or aborted.
 518     When multiple remote subtransactions are involved in the current local
 519     subtransaction, by default <filename>postgres_fdw</filename> commits or
 520     aborts those remote subtransactions serially when the local subtransaction
 521     is committed or aborted.
 522     Performance can be improved with the following options:
 523    </para>
 524
 525    <variablelist>
 526
 527     <varlistentry>
 528      <term><literal>parallel_commit</literal> (<type>boolean</type>)</term>
 529      <listitem>
 530       <para>
 531        This option controls whether <filename>postgres_fdw</filename> commits,
 532        in parallel, remote transactions opened on a foreign server in a local
 533        transaction when the local transaction is committed. This setting also
 534        applies to remote and local subtransactions. This option can only be
 535        specified for foreign servers, not per-table. The default is
 536        <literal>false</literal>.
 537       </para>
 538      </listitem>
 539     </varlistentry>
 540
 541     <varlistentry>
 542      <term><literal>parallel_abort</literal> (<type>boolean</type>)</term>
 543      <listitem>
 544       <para>
 545        This option controls whether <filename>postgres_fdw</filename> aborts,
 546        in parallel, remote transactions opened on a foreign server in a local
 547        transaction when the local transaction is aborted. This setting also
 548        applies to remote and local subtransactions. This option can only be
 549        specified for foreign servers, not per-table. The default is
 550        <literal>false</literal>.
 551       </para>
 552      </listitem>
 553     </varlistentry>
 554
 555    </variablelist>
 556
 557    <para>
 558     If multiple foreign servers with these options enabled are involved in a
 559     local transaction, multiple remote transactions on those foreign servers
 560     are committed or aborted in parallel across those foreign servers when
 561     the local transaction is committed or aborted.
 562    </para>
 563
 564    <para>
 565     When these options are enabled, a foreign server with many remote
 566     transactions may see a negative performance impact when the local
 567     transaction is committed or aborted.
 568    </para>
 569
 570   </sect3>
 571
 572   <sect3 id="postgres-fdw-options-updatability">
 573    <title>Updatability Options</title>
 574
 575    <para>
 576     By default all foreign tables using <filename>postgres_fdw</filename> are assumed
 577     to be updatable.  This may be overridden using the following option:
 578    </para>
 579
 580    <variablelist>
 581
 582     <varlistentry>
 583      <term><literal>updatable</literal> (<type>boolean</type>)</term>
 584      <listitem>
 585       <para>
 586        This option controls whether <filename>postgres_fdw</filename> allows foreign
 587        tables to be modified using <command>INSERT</command>, <command>UPDATE</command> and
 588        <command>DELETE</command> commands.  It can be specified for a foreign table
 589        or a foreign server.  A table-level option overrides a server-level
 590        option.
 591        The default is <literal>true</literal>.
 592       </para>
 593
 594       <para>
 595        Of course, if the remote table is not in fact updatable, an error
 596        would occur anyway.  Use of this option primarily allows the error to
 597        be thrown locally without querying the remote server.  Note however
 598        that the <literal>information_schema</literal> views will report a
 599        <filename>postgres_fdw</filename> foreign table to be updatable (or not)
 600        according to the setting of this option, without any check of the
 601        remote server.
 602       </para>
 603      </listitem>
 604     </varlistentry>
 605
 606    </variablelist>
 607   </sect3>
 608
 609   <sect3 id="postgres-fdw-options-truncatability">
 610    <title>Truncatability Options</title>
 611
 612    <para>
 613     By default all foreign tables using <filename>postgres_fdw</filename> are assumed
 614     to be truncatable.  This may be overridden using the following option:
 615    </para>
 616
 617    <variablelist>
 618
 619     <varlistentry>
 620      <term><literal>truncatable</literal> (<type>boolean</type>)</term>
 621      <listitem>
 622       <para>
 623        This option controls whether <filename>postgres_fdw</filename> allows
 624        foreign tables to be truncated using the <command>TRUNCATE</command>
 625        command. It can be specified for a foreign table or a foreign server.
 626        A table-level option overrides a server-level option.
 627        The default is <literal>true</literal>.
 628       </para>
 629
 630       <para>
 631        Of course, if the remote table is not in fact truncatable, an error
 632        would occur anyway.  Use of this option primarily allows the error to
 633        be thrown locally without querying the remote server.
 634       </para>
 635      </listitem>
 636     </varlistentry>
 637    </variablelist>
 638   </sect3>
 639
 640   <sect3 id="postgres-fdw-options-importing">
 641    <title>Importing Options</title>
 642
 643    <para>
 644     <filename>postgres_fdw</filename> is able to import foreign table definitions
 645     using <xref linkend="sql-importforeignschema"/>.  This command creates
 646     foreign table definitions on the local server that match tables or
 647     views present on the remote server.  If the remote tables to be imported
 648     have columns of user-defined data types, the local server must have
 649     compatible types of the same names.
 650    </para>
 651
 652    <para>
 653     Importing behavior can be customized with the following options
 654     (given in the <command>IMPORT FOREIGN SCHEMA</command> command):
 655    </para>
 656
 657    <variablelist>
 658     <varlistentry>
 659      <term><literal>import_collate</literal> (<type>boolean</type>)</term>
 660      <listitem>
 661       <para>
 662        This option controls whether column <literal>COLLATE</literal> options
 663        are included in the definitions of foreign tables imported
 664        from a foreign server. The default is <literal>true</literal>.  You might
 665        need to turn this off if the remote server has a different set of
 666        collation names than the local server does, which is likely to be the
 667        case if it's running on a different operating system.
 668        If you do so, however, there is a very severe risk that the imported
 669        table columns' collations will not match the underlying data, resulting
 670        in anomalous query behavior.
 671       </para>
 672
 673       <para>
 674        Even when this parameter is set to <literal>true</literal>, importing
 675        columns whose collation is the remote server's default can be risky.
 676        They will be imported with <literal>COLLATE "default"</literal>, which
 677        will select the local server's default collation, which could be
 678        different.
 679       </para>
 680      </listitem>
 681     </varlistentry>
 682     <varlistentry>
 683      <term><literal>import_default</literal> (<type>boolean</type>)</term>
 684      <listitem>
 685       <para>
 686        This option controls whether column <literal>DEFAULT</literal> expressions
 687        are included in the definitions of foreign tables imported
 688        from a foreign server. The default is <literal>false</literal>.  If you
 689        enable this option, be wary of defaults that might get computed
 690        differently on the local server than they would be on the remote
 691        server; <function>nextval()</function> is a common source of problems.
 692        The <command>IMPORT</command> will fail altogether if an imported default
 693        expression uses a function or operator that does not exist locally.
 694       </para>
 695      </listitem>
 696     </varlistentry>
 697     <varlistentry>
 698      <term><literal>import_generated</literal> (<type>boolean</type>)</term>
 699      <listitem>
 700       <para>
 701        This option controls whether column <literal>GENERATED</literal> expressions
 702        are included in the definitions of foreign tables imported
 703        from a foreign server. The default is <literal>true</literal>.
 704        The <command>IMPORT</command> will fail altogether if an imported generated
 705        expression uses a function or operator that does not exist locally.
 706       </para>
 707      </listitem>
 708     </varlistentry>
 709     <varlistentry>
 710      <term><literal>import_not_null</literal> (<type>boolean</type>)</term>
 711      <listitem>
 712       <para>
 713        This option controls whether column <literal>NOT NULL</literal>
 714        constraints are included in the definitions of foreign tables imported
 715        from a foreign server. The default is <literal>true</literal>.
 716       </para>
 717      </listitem>
 718     </varlistentry>
 719    </variablelist>
 720
 721    <para>
 722     Note that constraints other than <literal>NOT NULL</literal> will never be
 723     imported from the remote tables.  Although <productname>PostgreSQL</productname>
 724     does support check constraints on foreign tables, there is no
 725     provision for importing them automatically, because of the risk that a
 726     constraint expression could evaluate differently on the local and remote
 727     servers.  Any such inconsistency in the behavior of a check
 728     constraint could lead to hard-to-detect errors in query optimization.
 729     So if you wish to import check constraints, you must do so
 730     manually, and you should verify the semantics of each one carefully.
 731     For more detail about the treatment of check constraints on
 732     foreign tables, see <xref linkend="sql-createforeigntable"/>.
 733    </para>
 734
 735    <para>
 736     Tables or foreign tables which are partitions of some other table are
 737     imported only when they are explicitly specified in
 738     <literal>LIMIT TO</literal> clause.  Otherwise they are automatically
 739     excluded from <xref linkend="sql-importforeignschema"/>.
 740     Since all data can be accessed through the partitioned table
 741     which is the root of the partitioning hierarchy, importing only
 742     partitioned tables should allow access to all the data without
 743     creating extra objects.
 744    </para>
 745
 746   </sect3>
 747
 748   <sect3 id="postgres-fdw-options-connection-management">
 749     <title>Connection Management Options</title>
 750
 751     <para>
 752      By default, all connections that <filename>postgres_fdw</filename>
 753      establishes to foreign servers are kept open in the local session
 754      for re-use.
 755     </para>
 756
 757     <variablelist>
 758
 759      <varlistentry>
 760       <term><literal>keep_connections</literal> (<type>boolean</type>)</term>
 761       <listitem>
 762        <para>
 763         This option controls whether <filename>postgres_fdw</filename> keeps
 764         the connections to the foreign server open so that subsequent
 765         queries can re-use them. It can only be specified for a foreign server.
 766         The default is <literal>on</literal>. If set to <literal>off</literal>,
 767         all connections to this foreign server will be discarded at the end of
 768         each transaction.
 769       </para>
 770       </listitem>
 771      </varlistentry>
 772
 773     </variablelist>
 774    </sect3>
 775  </sect2>
 776
 777 <sect2 id="postgres-fdw-functions">
 778   <title>Functions</title>
 779
 780   <variablelist>
 781    <varlistentry>
 782     <term><function>postgres_fdw_get_connections(
 783       IN check_conn boolean DEFAULT false, OUT server_name text,
 784       OUT user_name text, OUT valid boolean, OUT used_in_xact boolean,
 785       OUT closed boolean)
 786       returns setof record</function></term>
 787     <listitem>
 788      <para>
 789       This function returns information about all open connections postgres_fdw
 790       has established from the local session to foreign servers. If there are
 791       no open connections, no records are returned.
 792      </para>
 793      <para>
 794       If <literal>check_conn</literal> is set to <literal>true</literal>,
 795       the function checks the status of each connection and shows
 796       the result in the <literal>closed</literal> column.
 797       This feature is currently available only on systems that support
 798       the non-standard <symbol>POLLRDHUP</symbol> extension to
 799       the <symbol>poll</symbol> system call, including Linux.
 800       This is useful to check if all connections used within
 801       a transaction are still open. If any connections are closed,
 802       the transaction cannot be committed successfully,
 803       so it is better to roll back as soon as a closed connection is detected,
 804       rather than continuing to the end. Users can roll back the transaction
 805       immediately if the function reports connections where both
 806       <literal>used_in_xact</literal> and <literal>closed</literal> are
 807       <literal>true</literal>.
 808      </para>
 809      <para>
 810       Example usage of the function:
 811 <screen>
 812 postgres=# SELECT * FROM postgres_fdw_get_connections(true);
 813  server_name | user_name | valid | used_in_xact | closed
 814 -------------+-----------+-------+--------------+--------
 815  loopback1   | postgres  | t     | t            | f
 816  loopback2   | public    | t     | t            | f
 817  loopback3   |           | f     | t            | f
 818 </screen>
 819       The output columns are described in
 820       <xref linkend="postgres-fdw-get-connections-columns"/>.
 821      </para>
 822
 823     <table id="postgres-fdw-get-connections-columns">
 824      <title><function>postgres_fdw_get_connections</function> Output Columns</title>
 825      <tgroup cols="3">
 826       <thead>
 827        <row>
 828         <entry>Column</entry>
 829         <entry>Type</entry>
 830         <entry>Description</entry>
 831        </row>
 832       </thead>
 833
 834       <tbody>
 835        <row>
 836         <entry><structfield>server_name</structfield></entry>
 837         <entry><type>text</type></entry>
 838         <entry>
 839          The foreign server name of this connection. If the server is
 840          dropped but the connection remains open (i.e., marked as
 841          invalid), this will be <literal>NULL</literal>.
 842         </entry>
 843        </row>
 844        <row>
 845         <entry><structfield>user_name</structfield></entry>
 846         <entry><type>text</type></entry>
 847         <entry>
 848          Name of the local user mapped to the foreign server of this
 849          connection, or <literal>public</literal> if a public mapping is used.
 850          If the user mapping is dropped but the connection remains open
 851          (i.e., marked as invalid), this will be <literal>NULL</literal>.
 852         </entry>
 853        </row>
 854        <row>
 855         <entry><structfield>valid</structfield></entry>
 856         <entry><type>boolean</type></entry>
 857         <entry>
 858          False if this connection is invalid, meaning it is used in
 859          the current transaction, but its foreign server or
 860          user mapping has been changed or dropped.
 861          The invalid connection will be closed at the end of
 862          the transaction. True is returned otherwise.
 863         </entry>
 864        </row>
 865        <row>
 866         <entry><structfield>used_in_xact</structfield></entry>
 867         <entry><type>boolean</type></entry>
 868         <entry>
 869          True if this connection is used in the current transaction.
 870         </entry>
 871        </row>
 872        <row>
 873         <entry><structfield>closed</structfield></entry>
 874         <entry><type>boolean</type></entry>
 875         <entry>
 876          True if this connection is closed, false otherwise.
 877          <literal>NULL</literal> is returned if <literal>check_conn</literal>
 878          is set to <literal>false</literal> or if the connection status check
 879          is not available on this platform.
 880         </entry>
 881        </row>
 882       </tbody>
 883      </tgroup>
 884     </table>
 885
 886     </listitem>
 887    </varlistentry>
 888
 889    <varlistentry>
 890     <term><function>postgres_fdw_disconnect(server_name text) returns boolean</function></term>
 891     <listitem>
 892      <para>
 893       This function discards the open connections that are established by
 894       <filename>postgres_fdw</filename> from the local session to
 895       the foreign server with the given name.  Note that there can be
 896       multiple connections to the given server using different user mappings.
 897       If the connections are used in the current local transaction,
 898       they are not disconnected and warning messages are reported.
 899       This function returns <literal>true</literal> if it disconnects
 900       at least one connection, otherwise <literal>false</literal>.
 901       If no foreign server with the given name is found, an error is reported.
 902       Example usage of the function:
 903 <screen>
 904 postgres=# SELECT postgres_fdw_disconnect('loopback1');
 905  postgres_fdw_disconnect
 906 -------------------------
 907  t
 908 </screen>
 909      </para>
 910     </listitem>
 911    </varlistentry>
 912
 913    <varlistentry>
 914     <term><function>postgres_fdw_disconnect_all() returns boolean</function></term>
 915     <listitem>
 916      <para>
 917       This function discards all the open connections that are established by
 918       <filename>postgres_fdw</filename> from the local session to
 919       foreign servers.  If the connections are used in the current local
 920       transaction, they are not disconnected and warning messages are reported.
 921       This function returns <literal>true</literal> if it disconnects
 922       at least one connection, otherwise <literal>false</literal>.
 923       Example usage of the function:
 924 <screen>
 925 postgres=# SELECT postgres_fdw_disconnect_all();
 926  postgres_fdw_disconnect_all
 927 -----------------------------
 928  t
 929 </screen>
 930      </para>
 931     </listitem>
 932    </varlistentry>
 933    </variablelist>
 934
 935 </sect2>
 936
 937  <sect2 id="postgres-fdw-connection-management">
 938   <title>Connection Management</title>
 939
 940   <para>
 941    <filename>postgres_fdw</filename> establishes a connection to a
 942    foreign server during the first query that uses a foreign table
 943    associated with the foreign server.  By default this connection
 944    is kept and re-used for subsequent queries in the same session.
 945    This behavior can be controlled using
 946    <literal>keep_connections</literal> option for a foreign server. If
 947    multiple user identities (user mappings) are used to access the foreign
 948    server, a connection is established for each user mapping.
 949   </para>
 950
 951   <para>
 952    When changing the definition of or removing a foreign server or
 953    a user mapping, the associated connections are closed.
 954    But note that if any connections are in use in the current local transaction,
 955    they are kept until the end of the transaction.
 956    Closed connections will be re-established when they are necessary
 957    by future queries using a foreign table.
 958   </para>
 959
 960   <para>
 961    Once a connection to a foreign server has been established,
 962    it's by default kept until the local or corresponding remote
 963    session exits.  To disconnect a connection explicitly,
 964    <literal>keep_connections</literal> option for a foreign server
 965    may be disabled, or
 966    <function>postgres_fdw_disconnect</function> and
 967    <function>postgres_fdw_disconnect_all</function> functions
 968    may be used.  For example, these are useful to close
 969    connections that are no longer necessary, thereby releasing
 970    connections on the foreign server.
 971   </para>
 972  </sect2>
 973
 974  <sect2 id="postgres-fdw-transaction-management">
 975   <title>Transaction Management</title>
 976
 977   <para>
 978    During a query that references any remote tables on a foreign server,
 979    <filename>postgres_fdw</filename> opens a transaction on the
 980    remote server if one is not already open corresponding to the current
 981    local transaction.  The remote transaction is committed or aborted when
 982    the local transaction commits or aborts.  Savepoints are similarly
 983    managed by creating corresponding remote savepoints.
 984   </para>
 985
 986   <para>
 987    The remote transaction uses <literal>SERIALIZABLE</literal>
 988    isolation level when the local transaction has <literal>SERIALIZABLE</literal>
 989    isolation level; otherwise it uses <literal>REPEATABLE READ</literal>
 990    isolation level.  This choice ensures that if a query performs multiple
 991    table scans on the remote server, it will get snapshot-consistent results
 992    for all the scans.  A consequence is that successive queries within a
 993    single transaction will see the same data from the remote server, even if
 994    concurrent updates are occurring on the remote server due to other
 995    activities.  That behavior would be expected anyway if the local
 996    transaction uses <literal>SERIALIZABLE</literal> or <literal>REPEATABLE READ</literal>
 997    isolation level, but it might be surprising for a <literal>READ
 998    COMMITTED</literal> local transaction.  A future
 999    <productname>PostgreSQL</productname> release might modify these rules.
1000   </para>
1001
1002   <para>
1003    Note that it is currently not supported by
1004    <filename>postgres_fdw</filename> to prepare the remote transaction for
1005    two-phase commit.
1006   </para>
1007  </sect2>
1008
1009  <sect2 id="postgres-fdw-remote-query-optimization">
1010   <title>Remote Query Optimization</title>
1011
1012   <para>
1013    <filename>postgres_fdw</filename> attempts to optimize remote queries to reduce
1014    the amount of data transferred from foreign servers.  This is done by
1015    sending query <literal>WHERE</literal> clauses to the remote server for
1016    execution, and by not retrieving table columns that are not needed for
1017    the current query.  To reduce the risk of misexecution of queries,
1018    <literal>WHERE</literal> clauses are not sent to the remote server unless they use
1019    only data types, operators, and functions that are built-in or belong to an
1020    extension that's listed in the foreign server's <literal>extensions</literal>
1021    option.  Operators and functions in such clauses must
1022    be <literal>IMMUTABLE</literal> as well.
1023    For an <command>UPDATE</command> or <command>DELETE</command> query,
1024    <filename>postgres_fdw</filename> attempts to optimize the query execution by
1025    sending the whole query to the remote server if there are no query
1026    <literal>WHERE</literal> clauses that cannot be sent to the remote server,
1027    no local joins for the query, no row-level local <literal>BEFORE</literal> or
1028    <literal>AFTER</literal> triggers or stored generated columns on the target
1029    table, and no <literal>CHECK OPTION</literal> constraints from parent
1030    views.  In <command>UPDATE</command>,
1031    expressions to assign to target columns must use only built-in data types,
1032    <literal>IMMUTABLE</literal> operators, or <literal>IMMUTABLE</literal> functions,
1033    to reduce the risk of misexecution of the query.
1034   </para>
1035
1036   <para>
1037    When <filename>postgres_fdw</filename> encounters a join between foreign tables on
1038    the same foreign server, it sends the entire join to the foreign server,
1039    unless for some reason it believes that it will be more efficient to fetch
1040    rows from each table individually, or unless the table references involved
1041    are subject to different user mappings.  While sending the <literal>JOIN</literal>
1042    clauses, it takes the same precautions as mentioned above for the
1043    <literal>WHERE</literal> clauses.
1044   </para>
1045
1046   <para>
1047    The query that is actually sent to the remote server for execution can
1048    be examined using <command>EXPLAIN VERBOSE</command>.
1049   </para>
1050  </sect2>
1051
1052  <sect2 id="postgres-fdw-remote-query-execution-environment">
1053   <title>Remote Query Execution Environment</title>
1054
1055   <para>
1056    In the remote sessions opened by <filename>postgres_fdw</filename>,
1057    the <xref linkend="guc-search-path"/> parameter is set to
1058    just <literal>pg_catalog</literal>, so that only built-in objects are visible
1059    without schema qualification.  This is not an issue for queries
1060    generated by <filename>postgres_fdw</filename> itself, because it always
1061    supplies such qualification.  However, this can pose a hazard for
1062    functions that are executed on the remote server via triggers or rules
1063    on remote tables.  For example, if a remote table is actually a view,
1064    any functions used in that view will be executed with the restricted
1065    search path.  It is recommended to schema-qualify all names in such
1066    functions, or else attach <literal>SET search_path</literal> options
1067    (see <xref linkend="sql-createfunction"/>) to such functions
1068    to establish their expected search path environment.
1069   </para>
1070
1071   <para>
1072    <filename>postgres_fdw</filename> likewise establishes remote session settings
1073    for various parameters:
1074    <itemizedlist spacing="compact">
1075     <listitem>
1076      <para>
1077       <xref linkend="guc-timezone"/> is set to <literal>UTC</literal>
1078      </para>
1079     </listitem>
1080     <listitem>
1081      <para>
1082       <xref linkend="guc-datestyle"/> is set to <literal>ISO</literal>
1083      </para>
1084     </listitem>
1085     <listitem>
1086      <para>
1087       <xref linkend="guc-intervalstyle"/> is set to <literal>postgres</literal>
1088      </para>
1089     </listitem>
1090     <listitem>
1091      <para>
1092       <xref linkend="guc-extra-float-digits"/> is set to <literal>3</literal> for remote
1093       servers 9.0 and newer and is set to <literal>2</literal> for older versions
1094      </para>
1095     </listitem>
1096    </itemizedlist>
1097    These are less likely to be problematic than <varname>search_path</varname>, but
1098    can be handled with function <literal>SET</literal> options if the need arises.
1099   </para>
1100
1101   <para>
1102    It is <emphasis>not</emphasis> recommended that you override this behavior by
1103    changing the session-level settings of these parameters; that is likely
1104    to cause <filename>postgres_fdw</filename> to malfunction.
1105   </para>
1106  </sect2>
1107
1108  <sect2 id="postgres-fdw-cross-version-compatibility">
1109   <title>Cross-Version Compatibility</title>
1110
1111   <para>
1112    <filename>postgres_fdw</filename> can be used with remote servers dating back
1113    to <productname>PostgreSQL</productname> 8.3.  Read-only capability is available
1114    back to 8.1.
1115   </para>
1116   <para>
1117    A limitation however is that <filename>postgres_fdw</filename>
1118    generally assumes that immutable built-in functions and operators are
1119    safe to send to the remote server for execution, if they appear in a
1120    <literal>WHERE</literal> clause for a foreign table.  Thus, a built-in
1121    function that was added since the remote server's release might be sent
1122    to it for execution, resulting in <quote>function does not exist</quote> or
1123    a similar error.  This type of failure can be worked around by
1124    rewriting the query, for example by embedding the foreign table
1125    reference in a sub-<literal>SELECT</literal> with <literal>OFFSET 0</literal> as an
1126    optimization fence, and placing the problematic function or operator
1127    outside the sub-<literal>SELECT</literal>.
1128   </para>
1129   <para>
1130    Another limitation is that when executing <command>INSERT</command>
1131    statements with an <literal>ON CONFLICT DO NOTHING</literal> clause on
1132    a foreign table, the remote server must be running
1133    <productname>PostgreSQL</productname> 9.5 or later,
1134    as earlier versions do not support this feature.
1135   </para>
1136  </sect2>
1137
1138  <sect2 id="postgres-fdw-wait-events">
1139   <title>Wait Events</title>
1140
1141   <para>
1142    <filename>postgres_fdw</filename> can report the following wait events
1143    under the wait event type <literal>Extension</literal>:
1144   </para>
1145
1146   <variablelist>
1147    <varlistentry>
1148     <term><literal>PostgresFdwCleanupResult</literal></term>
1149     <listitem>
1150      <para>
1151       Waiting for transaction abort on remote server.
1152      </para>
1153     </listitem>
1154    </varlistentry>
1155
1156    <varlistentry>
1157     <term><literal>PostgresFdwConnect</literal></term>
1158     <listitem>
1159      <para>
1160       Waiting to establish a connection to a remote server.
1161      </para>
1162     </listitem>
1163    </varlistentry>
1164
1165    <varlistentry>
1166     <term><literal>PostgresFdwGetResult</literal></term>
1167     <listitem>
1168      <para>
1169       Waiting to receive the results of a query from a remote server.
1170      </para>
1171     </listitem>
1172    </varlistentry>
1173   </variablelist>
1174  </sect2>
1175
1176  <sect2 id="postgres-fdw-configuration-parameters">
1177   <title>Configuration Parameters</title>
1178
1179   <variablelist>
1180    <varlistentry id="guc-pgfdw-application-name" xreflabel="postgres_fdw.application_name">
1181     <term>
1182      <varname>postgres_fdw.application_name</varname> (<type>string</type>)
1183      <indexterm>
1184       <primary><varname>postgres_fdw.application_name</varname> configuration parameter</primary>
1185      </indexterm>
1186     </term>
1187     <listitem>
1188      <para>
1189       Specifies a value for <xref linkend="guc-application-name"/>
1190       configuration parameter used when <filename>postgres_fdw</filename>
1191       establishes a connection to a foreign server.  This overrides
1192       <varname>application_name</varname> option of the server object.
1193       Note that change of this parameter doesn't affect any existing
1194       connections until they are re-established.
1195      </para>
1196      <para>
1197       <varname>postgres_fdw.application_name</varname> can be any string
1198       of any length and contain even non-ASCII characters.  However when
1199       it's passed to and used as <varname>application_name</varname>
1200       in a foreign server, note that it will be truncated to less than
1201       <symbol>NAMEDATALEN</symbol> characters.
1202       Anything other than printable ASCII characters are replaced with <link
1203       linkend="sql-syntax-strings-escape">C-style hexadecimal escapes</link>.
1204       See <xref linkend="guc-application-name"/> for details.
1205      </para>
1206
1207      <para>
1208       <literal>%</literal> characters begin <quote>escape sequences</quote>
1209       that are replaced with status information as outlined below.
1210       Unrecognized escapes are ignored. Other characters are copied straight
1211       to the application name. Note that it's not allowed to specify a
1212       plus/minus sign or a numeric literal after the <literal>%</literal>
1213       and before the option, for alignment and padding.
1214      </para>
1215
1216      <informaltable>
1217       <tgroup cols="2">
1218        <thead>
1219         <row>
1220          <entry>Escape</entry>
1221          <entry>Effect</entry>
1222         </row>
1223        </thead>
1224        <tbody>
1225         <row>
1226          <entry><literal>%a</literal></entry>
1227          <entry>Application name on local server</entry>
1228         </row>
1229         <row>
1230          <entry><literal>%c</literal></entry>
1231          <entry>
1232           Session ID on local server
1233           (see <xref linkend="guc-log-line-prefix"/> for details)
1234          </entry>
1235         </row>
1236         <row>
1237          <entry><literal>%C</literal></entry>
1238          <entry>
1239           Cluster name on local server
1240           (see <xref linkend="guc-cluster-name"/> for details)
1241          </entry>
1242         </row>
1243         <row>
1244          <entry><literal>%u</literal></entry>
1245          <entry>User name on local server</entry>
1246         </row>
1247         <row>
1248          <entry><literal>%d</literal></entry>
1249          <entry>Database name on local server</entry>
1250         </row>
1251         <row>
1252          <entry><literal>%p</literal></entry>
1253          <entry>Process ID of backend on local server</entry>
1254         </row>
1255         <row>
1256          <entry><literal>%%</literal></entry>
1257          <entry>Literal %</entry>
1258         </row>
1259        </tbody>
1260       </tgroup>
1261      </informaltable>
1262
1263      <para>
1264       For example, suppose user <literal>local_user</literal> establishes
1265       a connection from database <literal>local_db</literal> to
1266       <literal>foreign_db</literal> as user <literal>foreign_user</literal>,
1267       the setting <literal>'db=%d, user=%u'</literal> is replaced with
1268       <literal>'db=local_db, user=local_user'</literal>.
1269      </para>
1270
1271     </listitem>
1272    </varlistentry>
1273   </variablelist>
1274  </sect2>
1275
1276  <sect2 id="postgres-fdw-examples">
1277   <title>Examples</title>
1278
1279   <para>
1280    Here is an example of creating a foreign table with
1281    <literal>postgres_fdw</literal>. First install the extension:
1282   </para>
1283
1284 <programlisting>
1285 CREATE EXTENSION postgres_fdw;
1286 </programlisting>
1287
1288   <para>
1289    Then create a foreign server using <xref linkend="sql-createserver"/>.
1290    In this example we wish to connect to a <productname>PostgreSQL</productname> server
1291    on host <literal>192.83.123.89</literal> listening on
1292    port <literal>5432</literal>.  The database to which the connection is made
1293    is named <literal>foreign_db</literal> on the remote server:
1294
1295 <programlisting>
1296 CREATE SERVER foreign_server
1297         FOREIGN DATA WRAPPER postgres_fdw
1298         OPTIONS (host '192.83.123.89', port '5432', dbname 'foreign_db');
1299 </programlisting>
1300   </para>
1301
1302   <para>
1303    A user mapping, defined with <xref linkend="sql-createusermapping"/>, is
1304    needed as well to identify the role that will be used on the remote
1305    server:
1306
1307 <programlisting>
1308 CREATE USER MAPPING FOR local_user
1309         SERVER foreign_server
1310         OPTIONS (user 'foreign_user', password 'password');
1311 </programlisting>
1312   </para>
1313
1314   <para>
1315    Now it is possible to create a foreign table with
1316    <xref linkend="sql-createforeigntable"/>.  In this example we
1317    wish to access the table named <structname>some_schema.some_table</structname>
1318    on the remote server.  The local name for it will
1319    be <structname>foreign_table</structname>:
1320
1321 <programlisting>
1322 CREATE FOREIGN TABLE foreign_table (
1323         id integer NOT NULL,
1324         data text
1325 )
1326         SERVER foreign_server
1327         OPTIONS (schema_name 'some_schema', table_name 'some_table');
1328 </programlisting>
1329
1330    It's essential that the data types and other properties of the columns
1331    declared in <command>CREATE FOREIGN TABLE</command> match the actual remote table.
1332    Column names must match as well, unless you attach <literal>column_name</literal>
1333    options to the individual columns to show how they are named in the remote
1334    table.
1335    In many cases, use of <link linkend="sql-importforeignschema"><command>IMPORT FOREIGN SCHEMA</command></link> is
1336    preferable to constructing foreign table definitions manually.
1337   </para>
1338  </sect2>
1339
1340  <sect2 id="postgres-fdw-author">
1341   <title>Author</title>
1342   <para>
1343    Shigeru Hanada <email>shigeru.hanada@gmail.com</email>
1344   </para>
1345  </sect2>
1346
1347 </sect1>