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