Debian Codegraph @

doc/src/sgml/postgres-fdw.sgml

raw
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 id="postgres-fdw-option-keep-connections">
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 <varlistentry id="postgres-fdw-option-use-scram-passthrough">
774 <term><literal>use_scram_passthrough</literal> (<type>boolean</type>)</term>
775 <listitem>
776 <para>
777 This option controls whether <filename>postgres_fdw</filename> will
778 use the SCRAM pass-through authentication to connect to the foreign
779 server. It can be specified for a foreign server or a user mapping.
780 A user mapping setting overrides the foreign server setting.
781 With SCRAM pass-through authentication,
782 <filename>postgres_fdw</filename> uses SCRAM-hashed secrets instead of
783 plain-text user passwords to connect to the remote server. This
784 avoids storing plain-text user passwords in PostgreSQL system
785 catalogs.
786 </para>
787
788 <para>
789 To use SCRAM pass-through authentication:
790 <itemizedlist>
791 <listitem>
792 <para>
793 The remote server must request the <literal>scram-sha-256</literal>
794 authentication method; otherwise, the connection will fail.
795 </para>
796 </listitem>
797
798 <listitem>
799 <para>
800 The remote server can be of any PostgreSQL version that supports
801 SCRAM. Support for <literal>use_scram_passthrough</literal> is
802 only required on the client side (FDW side).
803 </para>
804 </listitem>
805
806 <listitem>
807 <para>
808 The user mapping password is not used.
809 </para>
810 </listitem>
811
812 <listitem>
813 <para>
814 The server running <filename>postgres_fdw</filename> and the remote
815 server must have identical SCRAM secrets (encrypted passwords) for
816 the user being used on <filename>postgres_fdw</filename> to
817 authenticate on the foreign server (same salt and iterations, not
818 merely the same password).
819 </para>
820
821 <para>
822 As a corollary, if FDW connections to multiple hosts are to be
823 made, for example for partitioned foreign tables/sharding, then all
824 hosts must have identical SCRAM secrets for the users involved.
825 </para>
826 </listitem>
827
828 <listitem>
829 <para>
830 The current session on the PostgreSQL instance that makes the
831 outgoing FDW connections also must also use SCRAM authentication
832 for its incoming client connection. (Hence
833 <quote>pass-through</quote>: SCRAM must be used going in and out.)
834 This is a technical requirement of the SCRAM protocol.
835 </para>
836 </listitem>
837 </itemizedlist>
838 </para>
839 </listitem>
840 </varlistentry>
841
842 </variablelist>
843 </sect3>
844 </sect2>
845
846 <sect2 id="postgres-fdw-functions">
847 <title>Functions</title>
848
849 <variablelist>
850 <varlistentry>
851 <term><function>postgres_fdw_get_connections(
852 IN check_conn boolean DEFAULT false, OUT server_name text,
853 OUT user_name text, OUT valid boolean, OUT used_in_xact boolean,
854 OUT closed boolean, OUT remote_backend_pid int4)
855 returns setof record</function></term>
856 <listitem>
857 <para>
858 This function returns information about all open connections postgres_fdw
859 has established from the local session to foreign servers. If there are
860 no open connections, no records are returned.
861 </para>
862 <para>
863 If <literal>check_conn</literal> is set to <literal>true</literal>,
864 the function checks the status of each connection and shows
865 the result in the <literal>closed</literal> column.
866 This feature is currently available only on systems that support
867 the non-standard <symbol>POLLRDHUP</symbol> extension to
868 the <symbol>poll</symbol> system call, including Linux.
869 This is useful to check if all connections used within
870 a transaction are still open. If any connections are closed,
871 the transaction cannot be committed successfully,
872 so it is better to roll back as soon as a closed connection is detected,
873 rather than continuing to the end. Users can roll back the transaction
874 immediately if the function reports connections where both
875 <literal>used_in_xact</literal> and <literal>closed</literal> are
876 <literal>true</literal>.
877 </para>
878 <para>
879 Example usage of the function:
880 <screen>
881 postgres=# SELECT * FROM postgres_fdw_get_connections(true);
882 server_name | user_name | valid | used_in_xact | closed | remote_backend_pid
883 -------------+-----------+-------+--------------+-----------------------------
884 loopback1 | postgres | t | t | f | 1353340
885 loopback2 | public | t | t | f | 1353120
886 loopback3 | | f | t | f | 1353156
887 </screen>
888 The output columns are described in
889 <xref linkend="postgres-fdw-get-connections-columns"/>.
890 </para>
891
892 <table id="postgres-fdw-get-connections-columns">
893 <title><function>postgres_fdw_get_connections</function> Output Columns</title>
894 <tgroup cols="3">
895 <thead>
896 <row>
897 <entry>Column</entry>
898 <entry>Type</entry>
899 <entry>Description</entry>
900 </row>
901 </thead>
902
903 <tbody>
904 <row>
905 <entry><structfield>server_name</structfield></entry>
906 <entry><type>text</type></entry>
907 <entry>
908 The foreign server name of this connection. If the server is
909 dropped but the connection remains open (i.e., marked as
910 invalid), this will be <literal>NULL</literal>.
911 </entry>
912 </row>
913 <row>
914 <entry><structfield>user_name</structfield></entry>
915 <entry><type>text</type></entry>
916 <entry>
917 Name of the local user mapped to the foreign server of this
918 connection, or <literal>public</literal> if a public mapping is used.
919 If the user mapping is dropped but the connection remains open
920 (i.e., marked as invalid), this will be <literal>NULL</literal>.
921 </entry>
922 </row>
923 <row>
924 <entry><structfield>valid</structfield></entry>
925 <entry><type>boolean</type></entry>
926 <entry>
927 False if this connection is invalid, meaning it is used in
928 the current transaction, but its foreign server or
929 user mapping has been changed or dropped.
930 The invalid connection will be closed at the end of
931 the transaction. True is returned otherwise.
932 </entry>
933 </row>
934 <row>
935 <entry><structfield>used_in_xact</structfield></entry>
936 <entry><type>boolean</type></entry>
937 <entry>
938 True if this connection is used in the current transaction.
939 </entry>
940 </row>
941 <row>
942 <entry><structfield>closed</structfield></entry>
943 <entry><type>boolean</type></entry>
944 <entry>
945 True if this connection is closed, false otherwise.
946 <literal>NULL</literal> is returned if <literal>check_conn</literal>
947 is set to <literal>false</literal> or if the connection status check
948 is not available on this platform.
949 </entry>
950 </row>
951 <row>
952 <entry><structfield>remote_backend_pid</structfield></entry>
953 <entry><type>int4</type></entry>
954 <entry>
955 Process ID of the remote backend, on the foreign server,
956 handling the connection. If the remote backend is terminated and
957 the connection is closed (with <literal>closed</literal> set to
958 <literal>true</literal>), this still shows the process ID of
959 the terminated backend.
960 </entry>
961 </row>
962 </tbody>
963 </tgroup>
964 </table>
965
966 </listitem>
967 </varlistentry>
968
969 <varlistentry>
970 <term><function>postgres_fdw_disconnect(server_name text) returns boolean</function></term>
971 <listitem>
972 <para>
973 This function discards the open connections that are established by
974 <filename>postgres_fdw</filename> from the local session to
975 the foreign server with the given name. Note that there can be
976 multiple connections to the given server using different user mappings.
977 If the connections are used in the current local transaction,
978 they are not disconnected and warning messages are reported.
979 This function returns <literal>true</literal> if it disconnects
980 at least one connection, otherwise <literal>false</literal>.
981 If no foreign server with the given name is found, an error is reported.
982 Example usage of the function:
983 <screen>
984 postgres=# SELECT postgres_fdw_disconnect('loopback1');
985 postgres_fdw_disconnect
986 -------------------------
987 t
988 </screen>
989 </para>
990 </listitem>
991 </varlistentry>
992
993 <varlistentry>
994 <term><function>postgres_fdw_disconnect_all() returns boolean</function></term>
995 <listitem>
996 <para>
997 This function discards all the open connections that are established by
998 <filename>postgres_fdw</filename> from the local session to
999 foreign servers. If the connections are used in the current local
1000 transaction, they are not disconnected and warning messages are reported.
1001 This function returns <literal>true</literal> if it disconnects
1002 at least one connection, otherwise <literal>false</literal>.
1003 Example usage of the function:
1004 <screen>
1005 postgres=# SELECT postgres_fdw_disconnect_all();
1006 postgres_fdw_disconnect_all
1007 -----------------------------
1008 t
1009 </screen>
1010 </para>
1011 </listitem>
1012 </varlistentry>
1013 </variablelist>
1014
1015 </sect2>
1016
1017 <sect2 id="postgres-fdw-connection-management">
1018 <title>Connection Management</title>
1019
1020 <para>
1021 <filename>postgres_fdw</filename> establishes a connection to a
1022 foreign server during the first query that uses a foreign table
1023 associated with the foreign server. By default this connection
1024 is kept and re-used for subsequent queries in the same session.
1025 This behavior can be controlled using
1026 <literal>keep_connections</literal> option for a foreign server. If
1027 multiple user identities (user mappings) are used to access the foreign
1028 server, a connection is established for each user mapping.
1029 </para>
1030
1031 <para>
1032 When changing the definition of or removing a foreign server or
1033 a user mapping, the associated connections are closed.
1034 But note that if any connections are in use in the current local transaction,
1035 they are kept until the end of the transaction.
1036 Closed connections will be re-established when they are necessary
1037 by future queries using a foreign table.
1038 </para>
1039
1040 <para>
1041 Once a connection to a foreign server has been established,
1042 it's by default kept until the local or corresponding remote
1043 session exits. To disconnect a connection explicitly,
1044 <literal>keep_connections</literal> option for a foreign server
1045 may be disabled, or
1046 <function>postgres_fdw_disconnect</function> and
1047 <function>postgres_fdw_disconnect_all</function> functions
1048 may be used. For example, these are useful to close
1049 connections that are no longer necessary, thereby releasing
1050 connections on the foreign server.
1051 </para>
1052 </sect2>
1053
1054 <sect2 id="postgres-fdw-transaction-management">
1055 <title>Transaction Management</title>
1056
1057 <para>
1058 During a query that references any remote tables on a foreign server,
1059 <filename>postgres_fdw</filename> opens a transaction on the
1060 remote server if one is not already open corresponding to the current
1061 local transaction. The remote transaction is committed or aborted when
1062 the local transaction commits or aborts. Savepoints are similarly
1063 managed by creating corresponding remote savepoints.
1064 </para>
1065
1066 <para>
1067 The remote transaction uses <literal>SERIALIZABLE</literal>
1068 isolation level when the local transaction has <literal>SERIALIZABLE</literal>
1069 isolation level; otherwise it uses <literal>REPEATABLE READ</literal>
1070 isolation level. This choice ensures that if a query performs multiple
1071 table scans on the remote server, it will get snapshot-consistent results
1072 for all the scans. A consequence is that successive queries within a
1073 single transaction will see the same data from the remote server, even if
1074 concurrent updates are occurring on the remote server due to other
1075 activities. That behavior would be expected anyway if the local
1076 transaction uses <literal>SERIALIZABLE</literal> or <literal>REPEATABLE READ</literal>
1077 isolation level, but it might be surprising for a <literal>READ
1078 COMMITTED</literal> local transaction. A future
1079 <productname>PostgreSQL</productname> release might modify these rules.
1080 </para>
1081
1082 <para>
1083 Note that it is currently not supported by
1084 <filename>postgres_fdw</filename> to prepare the remote transaction for
1085 two-phase commit.
1086 </para>
1087 </sect2>
1088
1089 <sect2 id="postgres-fdw-remote-query-optimization">
1090 <title>Remote Query Optimization</title>
1091
1092 <para>
1093 <filename>postgres_fdw</filename> attempts to optimize remote queries to reduce
1094 the amount of data transferred from foreign servers. This is done by
1095 sending query <literal>WHERE</literal> clauses to the remote server for
1096 execution, and by not retrieving table columns that are not needed for
1097 the current query. To reduce the risk of misexecution of queries,
1098 <literal>WHERE</literal> clauses are not sent to the remote server unless they use
1099 only data types, operators, and functions that are built-in or belong to an
1100 extension that's listed in the foreign server's <literal>extensions</literal>
1101 option. Operators and functions in such clauses must
1102 be <literal>IMMUTABLE</literal> as well.
1103 For an <command>UPDATE</command> or <command>DELETE</command> query,
1104 <filename>postgres_fdw</filename> attempts to optimize the query execution by
1105 sending the whole query to the remote server if there are no query
1106 <literal>WHERE</literal> clauses that cannot be sent to the remote server,
1107 no local joins for the query, no row-level local <literal>BEFORE</literal> or
1108 <literal>AFTER</literal> triggers or stored generated columns on the target
1109 table, and no <literal>CHECK OPTION</literal> constraints from parent
1110 views. In <command>UPDATE</command>,
1111 expressions to assign to target columns must use only built-in data types,
1112 <literal>IMMUTABLE</literal> operators, or <literal>IMMUTABLE</literal> functions,
1113 to reduce the risk of misexecution of the query.
1114 </para>
1115
1116 <para>
1117 When <filename>postgres_fdw</filename> encounters a join between foreign tables on
1118 the same foreign server, it sends the entire join to the foreign server,
1119 unless for some reason it believes that it will be more efficient to fetch
1120 rows from each table individually, or unless the table references involved
1121 are subject to different user mappings. While sending the <literal>JOIN</literal>
1122 clauses, it takes the same precautions as mentioned above for the
1123 <literal>WHERE</literal> clauses.
1124 </para>
1125
1126 <para>
1127 The query that is actually sent to the remote server for execution can
1128 be examined using <command>EXPLAIN VERBOSE</command>.
1129 </para>
1130 </sect2>
1131
1132 <sect2 id="postgres-fdw-remote-query-execution-environment">
1133 <title>Remote Query Execution Environment</title>
1134
1135 <para>
1136 In the remote sessions opened by <filename>postgres_fdw</filename>,
1137 the <xref linkend="guc-search-path"/> parameter is set to
1138 just <literal>pg_catalog</literal>, so that only built-in objects are visible
1139 without schema qualification. This is not an issue for queries
1140 generated by <filename>postgres_fdw</filename> itself, because it always
1141 supplies such qualification. However, this can pose a hazard for
1142 functions that are executed on the remote server via triggers or rules
1143 on remote tables. For example, if a remote table is actually a view,
1144 any functions used in that view will be executed with the restricted
1145 search path. It is recommended to schema-qualify all names in such
1146 functions, or else attach <literal>SET search_path</literal> options
1147 (see <xref linkend="sql-createfunction"/>) to such functions
1148 to establish their expected search path environment.
1149 </para>
1150
1151 <para>
1152 <filename>postgres_fdw</filename> likewise establishes remote session settings
1153 for various parameters:
1154 <itemizedlist spacing="compact">
1155 <listitem>
1156 <para>
1157 <xref linkend="guc-timezone"/> is set to <literal>UTC</literal>
1158 </para>
1159 </listitem>
1160 <listitem>
1161 <para>
1162 <xref linkend="guc-datestyle"/> is set to <literal>ISO</literal>
1163 </para>
1164 </listitem>
1165 <listitem>
1166 <para>
1167 <xref linkend="guc-intervalstyle"/> is set to <literal>postgres</literal>
1168 </para>
1169 </listitem>
1170 <listitem>
1171 <para>
1172 <xref linkend="guc-extra-float-digits"/> is set to <literal>3</literal> for remote
1173 servers 9.0 and newer and is set to <literal>2</literal> for older versions
1174 </para>
1175 </listitem>
1176 </itemizedlist>
1177 These are less likely to be problematic than <varname>search_path</varname>, but
1178 can be handled with function <literal>SET</literal> options if the need arises.
1179 </para>
1180
1181 <para>
1182 It is <emphasis>not</emphasis> recommended that you override this behavior by
1183 changing the session-level settings of these parameters; that is likely
1184 to cause <filename>postgres_fdw</filename> to malfunction.
1185 </para>
1186 </sect2>
1187
1188 <sect2 id="postgres-fdw-cross-version-compatibility">
1189 <title>Cross-Version Compatibility</title>
1190
1191 <para>
1192 <filename>postgres_fdw</filename> can be used with remote servers dating back
1193 to <productname>PostgreSQL</productname> 8.3. Read-only capability is available
1194 back to 8.1.
1195 </para>
1196 <para>
1197 A limitation however is that <filename>postgres_fdw</filename>
1198 generally assumes that immutable built-in functions and operators are
1199 safe to send to the remote server for execution, if they appear in a
1200 <literal>WHERE</literal> clause for a foreign table. Thus, a built-in
1201 function that was added since the remote server's release might be sent
1202 to it for execution, resulting in <quote>function does not exist</quote> or
1203 a similar error. This type of failure can be worked around by
1204 rewriting the query, for example by embedding the foreign table
1205 reference in a sub-<literal>SELECT</literal> with <literal>OFFSET 0</literal> as an
1206 optimization fence, and placing the problematic function or operator
1207 outside the sub-<literal>SELECT</literal>.
1208 </para>
1209 <para>
1210 Another limitation is that when executing <command>INSERT</command>
1211 statements with an <literal>ON CONFLICT DO NOTHING</literal> clause on
1212 a foreign table, the remote server must be running
1213 <productname>PostgreSQL</productname> 9.5 or later,
1214 as earlier versions do not support this feature.
1215 </para>
1216 </sect2>
1217
1218 <sect2 id="postgres-fdw-wait-events">
1219 <title>Wait Events</title>
1220
1221 <para>
1222 <filename>postgres_fdw</filename> can report the following wait events
1223 under the wait event type <literal>Extension</literal>:
1224 </para>
1225
1226 <variablelist>
1227 <varlistentry>
1228 <term><literal>PostgresFdwCleanupResult</literal></term>
1229 <listitem>
1230 <para>
1231 Waiting for transaction abort on remote server.
1232 </para>
1233 </listitem>
1234 </varlistentry>
1235
1236 <varlistentry>
1237 <term><literal>PostgresFdwConnect</literal></term>
1238 <listitem>
1239 <para>
1240 Waiting to establish a connection to a remote server.
1241 </para>
1242 </listitem>
1243 </varlistentry>
1244
1245 <varlistentry>
1246 <term><literal>PostgresFdwGetResult</literal></term>
1247 <listitem>
1248 <para>
1249 Waiting to receive the results of a query from a remote server.
1250 </para>
1251 </listitem>
1252 </varlistentry>
1253 </variablelist>
1254 </sect2>
1255
1256 <sect2 id="postgres-fdw-configuration-parameters">
1257 <title>Configuration Parameters</title>
1258
1259 <variablelist>
1260 <varlistentry id="guc-pgfdw-application-name" xreflabel="postgres_fdw.application_name">
1261 <term>
1262 <varname>postgres_fdw.application_name</varname> (<type>string</type>)
1263 <indexterm>
1264 <primary><varname>postgres_fdw.application_name</varname> configuration parameter</primary>
1265 </indexterm>
1266 </term>
1267 <listitem>
1268 <para>
1269 Specifies a value for <xref linkend="guc-application-name"/>
1270 configuration parameter used when <filename>postgres_fdw</filename>
1271 establishes a connection to a foreign server. This overrides
1272 <varname>application_name</varname> option of the server object.
1273 Note that change of this parameter doesn't affect any existing
1274 connections until they are re-established.
1275 </para>
1276 <para>
1277 <varname>postgres_fdw.application_name</varname> can be any string
1278 of any length and contain even non-ASCII characters. However when
1279 it's passed to and used as <varname>application_name</varname>
1280 in a foreign server, note that it will be truncated to less than
1281 <symbol>NAMEDATALEN</symbol> characters.
1282 Anything other than printable ASCII characters are replaced with <link
1283 linkend="sql-syntax-strings-escape">C-style hexadecimal escapes</link>.
1284 See <xref linkend="guc-application-name"/> for details.
1285 </para>
1286
1287 <para>
1288 <literal>%</literal> characters begin <quote>escape sequences</quote>
1289 that are replaced with status information as outlined below.
1290 Unrecognized escapes are ignored. Other characters are copied straight
1291 to the application name. Note that it's not allowed to specify a
1292 plus/minus sign or a numeric literal after the <literal>%</literal>
1293 and before the option, for alignment and padding.
1294 </para>
1295
1296 <informaltable>
1297 <tgroup cols="2">
1298 <thead>
1299 <row>
1300 <entry>Escape</entry>
1301 <entry>Effect</entry>
1302 </row>
1303 </thead>
1304 <tbody>
1305 <row>
1306 <entry><literal>%a</literal></entry>
1307 <entry>Application name on local server</entry>
1308 </row>
1309 <row>
1310 <entry><literal>%c</literal></entry>
1311 <entry>
1312 Session ID on local server
1313 (see <xref linkend="guc-log-line-prefix"/> for details)
1314 </entry>
1315 </row>
1316 <row>
1317 <entry><literal>%C</literal></entry>
1318 <entry>
1319 Cluster name on local server
1320 (see <xref linkend="guc-cluster-name"/> for details)
1321 </entry>
1322 </row>
1323 <row>
1324 <entry><literal>%u</literal></entry>
1325 <entry>User name on local server</entry>
1326 </row>
1327 <row>
1328 <entry><literal>%d</literal></entry>
1329 <entry>Database name on local server</entry>
1330 </row>
1331 <row>
1332 <entry><literal>%p</literal></entry>
1333 <entry>Process ID of backend on local server</entry>
1334 </row>
1335 <row>
1336 <entry><literal>%%</literal></entry>
1337 <entry>Literal %</entry>
1338 </row>
1339 </tbody>
1340 </tgroup>
1341 </informaltable>
1342
1343 <para>
1344 For example, suppose user <literal>local_user</literal> establishes
1345 a connection from database <literal>local_db</literal> to
1346 <literal>foreign_db</literal> as user <literal>foreign_user</literal>,
1347 the setting <literal>'db=%d, user=%u'</literal> is replaced with
1348 <literal>'db=local_db, user=local_user'</literal>.
1349 </para>
1350
1351 </listitem>
1352 </varlistentry>
1353 </variablelist>
1354 </sect2>
1355
1356 <sect2 id="postgres-fdw-examples">
1357 <title>Examples</title>
1358
1359 <para>
1360 Here is an example of creating a foreign table with
1361 <literal>postgres_fdw</literal>. First install the extension:
1362 </para>
1363
1364 <programlisting>
1365 CREATE EXTENSION postgres_fdw;
1366 </programlisting>
1367
1368 <para>
1369 Then create a foreign server using <xref linkend="sql-createserver"/>.
1370 In this example we wish to connect to a <productname>PostgreSQL</productname> server
1371 on host <literal>192.83.123.89</literal> listening on
1372 port <literal>5432</literal>. The database to which the connection is made
1373 is named <literal>foreign_db</literal> on the remote server:
1374
1375 <programlisting>
1376 CREATE SERVER foreign_server
1377 FOREIGN DATA WRAPPER postgres_fdw
1378 OPTIONS (host '192.83.123.89', port '5432', dbname 'foreign_db');
1379 </programlisting>
1380 </para>
1381
1382 <para>
1383 A user mapping, defined with <xref linkend="sql-createusermapping"/>, is
1384 needed as well to identify the role that will be used on the remote
1385 server:
1386
1387 <programlisting>
1388 CREATE USER MAPPING FOR local_user
1389 SERVER foreign_server
1390 OPTIONS (user 'foreign_user', password 'password');
1391 </programlisting>
1392 </para>
1393
1394 <para>
1395 Now it is possible to create a foreign table with
1396 <xref linkend="sql-createforeigntable"/>. In this example we
1397 wish to access the table named <structname>some_schema.some_table</structname>
1398 on the remote server. The local name for it will
1399 be <structname>foreign_table</structname>:
1400
1401 <programlisting>
1402 CREATE FOREIGN TABLE foreign_table (
1403 id integer NOT NULL,
1404 data text
1405 )
1406 SERVER foreign_server
1407 OPTIONS (schema_name 'some_schema', table_name 'some_table');
1408 </programlisting>
1409
1410 It's essential that the data types and other properties of the columns
1411 declared in <command>CREATE FOREIGN TABLE</command> match the actual remote table.
1412 Column names must match as well, unless you attach <literal>column_name</literal>
1413 options to the individual columns to show how they are named in the remote
1414 table.
1415 In many cases, use of <link linkend="sql-importforeignschema"><command>IMPORT FOREIGN SCHEMA</command></link> is
1416 preferable to constructing foreign table definitions manually.
1417 </para>
1418 </sect2>
1419
1420 <sect2 id="postgres-fdw-author">
1421 <title>Author</title>
1422 <para>
1423 Shigeru Hanada <email>[email protected]</email>
1424 </para>
1425 </sect2>
1426
1427 </sect1>