1<!-- doc/src/sgml/postgres-fdw.sgml --> 2 3<sect1 id="postgres-fdw" xreflabel="postgres_fdw"> 4 <title>postgres_fdw</title> 5 6 <indexterm zone="postgres-fdw"> 7 <primary>postgres_fdw</primary> 8 </indexterm> 9 10 <para> 11 The <filename>postgres_fdw</filename> module provides the foreign-data wrapper 12 <literal>postgres_fdw</literal>, which can be used to access data 13 stored in external <productname>PostgreSQL</productname> servers. 14 </para> 15 16 <para> 17 The functionality provided by this module overlaps substantially 18 with the functionality of the older <xref linkend="dblink"/> module. 19 But <filename>postgres_fdw</filename> provides more transparent and 20 standards-compliant syntax for accessing remote tables, and can give 21 better performance in many cases. 22 </para> 23 24 <para> 25 To prepare for remote access using <filename>postgres_fdw</filename>: 26 <orderedlist spacing="compact"> 27 <listitem> 28 <para> 29 Install the <filename>postgres_fdw</filename> extension using <xref 30 linkend="sql-createextension"/>. 31 </para> 32 </listitem> 33 <listitem> 34 <para> 35 Create a foreign server object, using <xref linkend="sql-createserver"/>, 36 to represent each remote database you want to connect to. 37 Specify connection information, except <literal>user</literal> and 38 <literal>password</literal>, as options of the server object. 39 </para> 40 </listitem> 41 <listitem> 42 <para> 43 Create a user mapping, using <xref linkend="sql-createusermapping"/>, for 44 each database user you want to allow to access each foreign server. 45 Specify the remote user name and password to use as 46 <literal>user</literal> and <literal>password</literal> options of the 47 user mapping. 48 </para> 49 </listitem> 50 <listitem> 51 <para> 52 Create a foreign table, using <xref linkend="sql-createforeigntable"/> 53 or <xref linkend="sql-importforeignschema"/>, 54 for each remote table you want to access. The columns of the foreign 55 table must match the referenced remote table. You can, however, use 56 table and/or column names different from the remote table's, if you 57 specify the correct remote names as options of the foreign table object. 58 </para> 59 </listitem> 60 </orderedlist> 61 </para> 62 63 <para> 64 Now you need only <command>SELECT</command> from a foreign table to access 65 the data stored in its underlying remote table. You can also modify 66 the remote table using <command>INSERT</command>, <command>UPDATE</command>, or 67 <command>DELETE</command>. (Of course, the remote user you have specified 68 in your user mapping must have privileges to do these things.) 69 </para> 70 71 <para> 72 Note that <filename>postgres_fdw</filename> currently lacks support for 73 <command>INSERT</command> statements with an <literal>ON CONFLICT DO 74 UPDATE</literal> clause. However, the <literal>ON CONFLICT DO NOTHING</literal> 75 clause is supported, provided a unique index inference specification 76 is omitted. 77 Note also that <filename>postgres_fdw</filename> supports row movement 78 invoked by <command>UPDATE</command> statements executed on partitioned 79 tables, but it currently does not handle the case where a remote partition 80 chosen to insert a moved row into is also an <command>UPDATE</command> 81 target partition that will be updated later. 82 </para> 83 84 <para> 85 It is generally recommended that the columns of a foreign table be declared 86 with exactly the same data types, and collations if applicable, as the 87 referenced columns of the remote table. Although <filename>postgres_fdw</filename> 88 is currently rather forgiving about performing data type conversions at 89 need, surprising semantic anomalies may arise when types or collations do 90 not match, due to the remote server interpreting query conditions 91 differently from the local server. 92 </para> 93 94 <para> 95 Note that a foreign table can be declared with fewer columns, or with a 96 different column order, than its underlying remote table has. Matching 97 of columns to the remote table is by name, not position. 98 </para> 99 100 <sect2> 101 <title>FDW Options of postgres_fdw</title> 102 103 <sect3> 104 <title>Connection Options</title> 105 106 <para> 107 A foreign server using the <filename>postgres_fdw</filename> foreign data wrapper 108 can have the same options that <application>libpq</application> accepts in 109 connection strings, as described in <xref linkend="libpq-paramkeywords"/>, 110 except that these options are not allowed or have special handling: 111 112 <itemizedlist spacing="compact"> 113 <listitem> 114 <para> 115 <literal>user</literal>, <literal>password</literal> and <literal>sslpassword</literal> (specify these 116 in a user mapping, instead, or use a service file) 117 </para> 118 </listitem> 119 <listitem> 120 <para> 121 <literal>client_encoding</literal> (this is automatically set from the local 122 server encoding) 123 </para> 124 </listitem> 125 <listitem> 126 <para> 127 <literal>fallback_application_name</literal> (always set to 128 <literal>postgres_fdw</literal>) 129 </para> 130 </listitem> 131 <listitem> 132 <para> 133 <literal>sslkey</literal> and <literal>sslcert</literal> - these may 134 appear in <emphasis>either or both</emphasis> a connection and a user 135 mapping. If both are present, the user mapping setting overrides the 136 connection setting. 137 </para> 138 </listitem> 139 </itemizedlist> 140 </para> 141 142 <para> 143 Only superusers may create or modify user mappings with the 144 <literal>sslcert</literal> or <literal>sslkey</literal> settings. 145 </para> 146 <para> 147 Only superusers may connect to foreign servers without password 148 authentication, so always specify the <literal>password</literal> option 149 for user mappings belonging to non-superusers. 150 </para> 151 <para> 152 A superuser may override this check on a per-user-mapping basis by setting 153 the user mapping option <literal>password_required 'false'</literal>, e.g., 154<programlisting> 155ALTER USER MAPPING FOR some_non_superuser SERVER loopback_nopw 156OPTIONS (ADD password_required 'false'); 157</programlisting> 158 To prevent unprivileged users from exploiting the authentication rights 159 of the unix user the postgres server is running as to escalate to superuser 160 rights, only the superuser may set this option on a user mapping. 161 </para> 162 <para> 163 Care is required to ensure that this does not allow the mapped 164 user the ability to connect as superuser to the mapped database per 165 CVE-2007-3278 and CVE-2007-6601. Don't set 166 <literal>password_required=false</literal> 167 on the <literal>public</literal> role. Keep in mind that the mapped 168 user can potentially use any client certificates, 169 <filename>.pgpass</filename>, 170 <filename>.pg_service.conf</filename> etc in the unix home directory of the 171 system user the postgres server runs as. They can also use any trust 172 relationship granted by authentication modes like <literal>peer</literal> 173 or <literal>ident</literal> authentication. 174 </para> 175 </sect3> 176 177 <sect3> 178 <title>Object Name Options</title> 179 180 <para> 181 These options can be used to control the names used in SQL statements 182 sent to the remote <productname>PostgreSQL</productname> server. These 183 options are needed when a foreign table is created with names different 184 from the underlying remote table's names. 185 </para> 186 187 <variablelist> 188 189 <varlistentry> 190 <term><literal>schema_name</literal></term> 191 <listitem> 192 <para> 193 This option, which can be specified for a foreign table, gives the 194 schema name to use for the foreign table on the remote server. If this 195 option is omitted, the name of the foreign table's schema is used. 196 </para> 197 </listitem> 198 </varlistentry> 199 200 <varlistentry> 201 <term><literal>table_name</literal></term> 202 <listitem> 203 <para> 204 This option, which can be specified for a foreign table, gives the 205 table name to use for the foreign table on the remote server. If this 206 option is omitted, the foreign table's name is used. 207 </para> 208 </listitem> 209 </varlistentry> 210 211 <varlistentry> 212 <term><literal>column_name</literal></term> 213 <listitem> 214 <para> 215 This option, which can be specified for a column of a foreign table, 216 gives the column name to use for the column on the remote server. 217 If this option is omitted, the column's name is used. 218 </para> 219 </listitem> 220 </varlistentry> 221 222 </variablelist> 223 224 </sect3> 225 226 <sect3> 227 <title>Cost Estimation Options</title> 228 229 <para> 230 <filename>postgres_fdw</filename> retrieves remote data by executing queries 231 against remote servers, so ideally the estimated cost of scanning a 232 foreign table should be whatever it costs to be done on the remote 233 server, plus some overhead for communication. The most reliable way to 234 get such an estimate is to ask the remote server and then add something 235 for overhead — but for simple queries, it may not be worth the cost 236 of an additional remote query to get a cost estimate. 237 So <filename>postgres_fdw</filename> provides the following options to control 238 how cost estimation is done: 239 </para> 240 241 <variablelist> 242 243 <varlistentry> 244 <term><literal>use_remote_estimate</literal></term> 245 <listitem> 246 <para> 247 This option, which can be specified for a foreign table or a foreign 248 server, controls whether <filename>postgres_fdw</filename> issues remote 249 <command>EXPLAIN</command> commands to obtain cost estimates. 250 A setting for a foreign table overrides any setting for its server, 251 but only for that table. 252 The default is <literal>false</literal>. 253 </para> 254 </listitem> 255 </varlistentry> 256 257 <varlistentry> 258 <term><literal>fdw_startup_cost</literal></term> 259 <listitem> 260 <para> 261 This option, which can be specified for a foreign server, is a numeric 262 value that is added to the estimated startup cost of any foreign-table 263 scan on that server. This represents the additional overhead of 264 establishing a connection, parsing and planning the query on the 265 remote side, etc. 266 The default value is <literal>100</literal>. 267 </para> 268 </listitem> 269 </varlistentry> 270 271 <varlistentry> 272 <term><literal>fdw_tuple_cost</literal></term> 273 <listitem> 274 <para> 275 This option, which can be specified for a foreign server, is a numeric 276 value that is used as extra cost per-tuple for foreign-table 277 scans on that server. This represents the additional overhead of 278 data transfer between servers. You might increase or decrease this 279 number to reflect higher or lower network delay to the remote server. 280 The default value is <literal>0.01</literal>. 281 </para> 282 </listitem> 283 </varlistentry> 284 285 </variablelist> 286 287 <para> 288 When <literal>use_remote_estimate</literal> is true, 289 <filename>postgres_fdw</filename> obtains row count and cost estimates from the 290 remote server and then adds <literal>fdw_startup_cost</literal> and 291 <literal>fdw_tuple_cost</literal> to the cost estimates. When 292 <literal>use_remote_estimate</literal> is false, 293 <filename>postgres_fdw</filename> performs local row count and cost estimation 294 and then adds <literal>fdw_startup_cost</literal> and 295 <literal>fdw_tuple_cost</literal> to the cost estimates. This local 296 estimation is unlikely to be very accurate unless local copies of the 297 remote table's statistics are available. Running 298 <xref linkend="sql-analyze"/> on the foreign table is the way to update 299 the local statistics; this will perform a scan of the remote table and 300 then calculate and store statistics just as though the table were local. 301 Keeping local statistics can be a useful way to reduce per-query planning 302 overhead for a remote table — but if the remote table is 303 frequently updated, the local statistics will soon be obsolete. 304 </para> 305 306 </sect3> 307 308 <sect3> 309 <title>Remote Execution Options</title> 310 311 <para> 312 By default, only <literal>WHERE</literal> clauses using built-in operators and 313 functions will be considered for execution on the remote server. Clauses 314 involving non-built-in functions are checked locally after rows are 315 fetched. If such functions are available on the remote server and can be 316 relied on to produce the same results as they do locally, performance can 317 be improved by sending such <literal>WHERE</literal> clauses for remote 318 execution. This behavior can be controlled using the following option: 319 </para> 320 321 <variablelist> 322 323 <varlistentry> 324 <term><literal>extensions</literal></term> 325 <listitem> 326 <para> 327 This option is a comma-separated list of names 328 of <productname>PostgreSQL</productname> extensions that are installed, in 329 compatible versions, on both the local and remote servers. Functions 330 and operators that are immutable and belong to a listed extension will 331 be considered shippable to the remote server. 332 This option can only be specified for foreign servers, not per-table. 333 </para> 334 335 <para> 336 When using the <literal>extensions</literal> option, <emphasis>it is the 337 user's responsibility</emphasis> that the listed extensions exist and behave 338 identically on both the local and remote servers. Otherwise, remote 339 queries may fail or behave unexpectedly. 340 </para> 341 </listitem> 342 </varlistentry> 343 344 <varlistentry> 345 <term><literal>fetch_size</literal></term> 346 <listitem> 347 <para> 348 This option specifies the number of rows <filename>postgres_fdw</filename> 349 should get in each fetch operation. It can be specified for a foreign 350 table or a foreign server. The option specified on a table overrides 351 an option specified for the server. 352 The default is <literal>100</literal>. 353 </para> 354 </listitem> 355 </varlistentry> 356 357 </variablelist> 358 359 </sect3> 360 361 <sect3> 362 <title>Updatability Options</title> 363 364 <para> 365 By default all foreign tables using <filename>postgres_fdw</filename> are assumed 366 to be updatable. This may be overridden using the following option: 367 </para> 368 369 <variablelist> 370 371 <varlistentry> 372 <term><literal>updatable</literal></term> 373 <listitem> 374 <para> 375 This option controls whether <filename>postgres_fdw</filename> allows foreign 376 tables to be modified using <command>INSERT</command>, <command>UPDATE</command> and 377 <command>DELETE</command> commands. It can be specified for a foreign table 378 or a foreign server. A table-level option overrides a server-level 379 option. 380 The default is <literal>true</literal>. 381 </para> 382 383 <para> 384 Of course, if the remote table is not in fact updatable, an error 385 would occur anyway. Use of this option primarily allows the error to 386 be thrown locally without querying the remote server. Note however 387 that the <literal>information_schema</literal> views will report a 388 <filename>postgres_fdw</filename> foreign table to be updatable (or not) 389 according to the setting of this option, without any check of the 390 remote server. 391 </para> 392 </listitem> 393 </varlistentry> 394 395 </variablelist> 396 </sect3> 397 398 <sect3> 399 <title>Importing Options</title> 400 401 <para> 402 <filename>postgres_fdw</filename> is able to import foreign table definitions 403 using <xref linkend="sql-importforeignschema"/>. This command creates 404 foreign table definitions on the local server that match tables or 405 views present on the remote server. If the remote tables to be imported 406 have columns of user-defined data types, the local server must have 407 compatible types of the same names. 408 </para> 409 410 <para> 411 Importing behavior can be customized with the following options 412 (given in the <command>IMPORT FOREIGN SCHEMA</command> command): 413 </para> 414 415 <variablelist> 416 <varlistentry> 417 <term><literal>import_collate</literal></term> 418 <listitem> 419 <para> 420 This option controls whether column <literal>COLLATE</literal> options 421 are included in the definitions of foreign tables imported 422 from a foreign server. The default is <literal>true</literal>. You might 423 need to turn this off if the remote server has a different set of 424 collation names than the local server does, which is likely to be the 425 case if it's running on a different operating system. 426 If you do so, however, there is a very severe risk that the imported 427 table columns' collations will not match the underlying data, resulting 428 in anomalous query behavior. 429 </para> 430 431 <para> 432 Even when this parameter is set to <literal>true</literal>, importing 433 columns whose collation is the remote server's default can be risky. 434 They will be imported with <literal>COLLATE "default"</literal>, which 435 will select the local server's default collation, which could be 436 different. 437 </para> 438 </listitem> 439 </varlistentry> 440 <varlistentry> 441 <term><literal>import_default</literal></term> 442 <listitem> 443 <para> 444 This option controls whether column <literal>DEFAULT</literal> expressions 445 are included in the definitions of foreign tables imported 446 from a foreign server. The default is <literal>false</literal>. If you 447 enable this option, be wary of defaults that might get computed 448 differently on the local server than they would be on the remote 449 server; <function>nextval()</function> is a common source of problems. 450 The <command>IMPORT</command> will fail altogether if an imported default 451 expression uses a function or operator that does not exist locally. 452 </para> 453 </listitem> 454 </varlistentry> 455 <varlistentry> 456 <term><literal>import_generated</literal></term> 457 <listitem> 458 <para> 459 This option controls whether column <literal>GENERATED</literal> expressions 460 are included in the definitions of foreign tables imported 461 from a foreign server. The default is <literal>true</literal>. 462 The <command>IMPORT</command> will fail altogether if an imported generated 463 expression uses a function or operator that does not exist locally. 464 </para> 465 </listitem> 466 </varlistentry> 467 <varlistentry> 468 <term><literal>import_not_null</literal></term> 469 <listitem> 470 <para> 471 This option controls whether column <literal>NOT NULL</literal> 472 constraints are included in the definitions of foreign tables imported 473 from a foreign server. The default is <literal>true</literal>. 474 </para> 475 </listitem> 476 </varlistentry> 477 </variablelist> 478 479 <para> 480 Note that constraints other than <literal>NOT NULL</literal> will never be 481 imported from the remote tables. Although <productname>PostgreSQL</productname> 482 does support <literal>CHECK</literal> constraints on foreign tables, there is no 483 provision for importing them automatically, because of the risk that a 484 constraint expression could evaluate differently on the local and remote 485 servers. Any such inconsistency in the behavior of a <literal>CHECK</literal> 486 constraint could lead to hard-to-detect errors in query optimization. 487 So if you wish to import <literal>CHECK</literal> constraints, you must do so 488 manually, and you should verify the semantics of each one carefully. 489 For more detail about the treatment of <literal>CHECK</literal> constraints on 490 foreign tables, see <xref linkend="sql-createforeigntable"/>. 491 </para> 492 493 <para> 494 Tables or foreign tables which are partitions of some other table are 495 automatically excluded. Partitioned tables are imported, unless they 496 are a partition of some other table. Since all data can be accessed 497 through the partitioned table which is the root of the partitioning 498 hierarchy, this approach should allow access to all the data without 499 creating extra objects. 500 </para> 501 502 </sect3> 503 </sect2> 504 505 <sect2> 506 <title>Connection Management</title> 507 508 <para> 509 <filename>postgres_fdw</filename> establishes a connection to a 510 foreign server during the first query that uses a foreign table 511 associated with the foreign server. This connection is kept and 512 re-used for subsequent queries in the same session. However, if 513 multiple user identities (user mappings) are used to access the foreign 514 server, a connection is established for each user mapping. 515 </para> 516 </sect2> 517 518 <sect2> 519 <title>Transaction Management</title> 520 521 <para> 522 During a query that references any remote tables on a foreign server, 523 <filename>postgres_fdw</filename> opens a transaction on the 524 remote server if one is not already open corresponding to the current 525 local transaction. The remote transaction is committed or aborted when 526 the local transaction commits or aborts. Savepoints are similarly 527 managed by creating corresponding remote savepoints. 528 </para> 529 530 <para> 531 The remote transaction uses <literal>SERIALIZABLE</literal> 532 isolation level when the local transaction has <literal>SERIALIZABLE</literal> 533 isolation level; otherwise it uses <literal>REPEATABLE READ</literal> 534 isolation level. This choice ensures that if a query performs multiple 535 table scans on the remote server, it will get snapshot-consistent results 536 for all the scans. A consequence is that successive queries within a 537 single transaction will see the same data from the remote server, even if 538 concurrent updates are occurring on the remote server due to other 539 activities. That behavior would be expected anyway if the local 540 transaction uses <literal>SERIALIZABLE</literal> or <literal>REPEATABLE READ</literal> 541 isolation level, but it might be surprising for a <literal>READ 542 COMMITTED</literal> local transaction. A future 543 <productname>PostgreSQL</productname> release might modify these rules. 544 </para> 545 546 <para> 547 Note that it is currently not supported by 548 <filename>postgres_fdw</filename> to prepare the remote transaction for 549 two-phase commit. 550 </para> 551 </sect2> 552 553 <sect2> 554 <title>Remote Query Optimization</title> 555 556 <para> 557 <filename>postgres_fdw</filename> attempts to optimize remote queries to reduce 558 the amount of data transferred from foreign servers. This is done by 559 sending query <literal>WHERE</literal> clauses to the remote server for 560 execution, and by not retrieving table columns that are not needed for 561 the current query. To reduce the risk of misexecution of queries, 562 <literal>WHERE</literal> clauses are not sent to the remote server unless they use 563 only data types, operators, and functions that are built-in or belong to an 564 extension that's listed in the foreign server's <literal>extensions</literal> 565 option. Operators and functions in such clauses must 566 be <literal>IMMUTABLE</literal> as well. 567 For an <command>UPDATE</command> or <command>DELETE</command> query, 568 <filename>postgres_fdw</filename> attempts to optimize the query execution by 569 sending the whole query to the remote server if there are no query 570 <literal>WHERE</literal> clauses that cannot be sent to the remote server, 571 no local joins for the query, no row-level local <literal>BEFORE</literal> or 572 <literal>AFTER</literal> triggers or stored generated columns on the target 573 table, and no <literal>CHECK OPTION</literal> constraints from parent 574 views. In <command>UPDATE</command>, 575 expressions to assign to target columns must use only built-in data types, 576 <literal>IMMUTABLE</literal> operators, or <literal>IMMUTABLE</literal> functions, 577 to reduce the risk of misexecution of the query. 578 </para> 579 580 <para> 581 When <filename>postgres_fdw</filename> encounters a join between foreign tables on 582 the same foreign server, it sends the entire join to the foreign server, 583 unless for some reason it believes that it will be more efficient to fetch 584 rows from each table individually, or unless the table references involved 585 are subject to different user mappings. While sending the <literal>JOIN</literal> 586 clauses, it takes the same precautions as mentioned above for the 587 <literal>WHERE</literal> clauses. 588 </para> 589 590 <para> 591 The query that is actually sent to the remote server for execution can 592 be examined using <command>EXPLAIN VERBOSE</command>. 593 </para> 594 </sect2> 595 596 <sect2> 597 <title>Remote Query Execution Environment</title> 598 599 <para> 600 In the remote sessions opened by <filename>postgres_fdw</filename>, 601 the <xref linkend="guc-search-path"/> parameter is set to 602 just <literal>pg_catalog</literal>, so that only built-in objects are visible 603 without schema qualification. This is not an issue for queries 604 generated by <filename>postgres_fdw</filename> itself, because it always 605 supplies such qualification. However, this can pose a hazard for 606 functions that are executed on the remote server via triggers or rules 607 on remote tables. For example, if a remote table is actually a view, 608 any functions used in that view will be executed with the restricted 609 search path. It is recommended to schema-qualify all names in such 610 functions, or else attach <literal>SET search_path</literal> options 611 (see <xref linkend="sql-createfunction"/>) to such functions 612 to establish their expected search path environment. 613 </para> 614 615 <para> 616 <filename>postgres_fdw</filename> likewise establishes remote session settings 617 for various parameters: 618 <itemizedlist spacing="compact"> 619 <listitem> 620 <para> 621 <xref linkend="guc-timezone"/> is set to <literal>UTC</literal> 622 </para> 623 </listitem> 624 <listitem> 625 <para> 626 <xref linkend="guc-datestyle"/> is set to <literal>ISO</literal> 627 </para> 628 </listitem> 629 <listitem> 630 <para> 631 <xref linkend="guc-intervalstyle"/> is set to <literal>postgres</literal> 632 </para> 633 </listitem> 634 <listitem> 635 <para> 636 <xref linkend="guc-extra-float-digits"/> is set to <literal>3</literal> for remote 637 servers 9.0 and newer and is set to <literal>2</literal> for older versions 638 </para> 639 </listitem> 640 </itemizedlist> 641 These are less likely to be problematic than <varname>search_path</varname>, but 642 can be handled with function <literal>SET</literal> options if the need arises. 643 </para> 644 645 <para> 646 It is <emphasis>not</emphasis> recommended that you override this behavior by 647 changing the session-level settings of these parameters; that is likely 648 to cause <filename>postgres_fdw</filename> to malfunction. 649 </para> 650 </sect2> 651 652 <sect2> 653 <title>Cross-Version Compatibility</title> 654 655 <para> 656 <filename>postgres_fdw</filename> can be used with remote servers dating back 657 to <productname>PostgreSQL</productname> 8.3. Read-only capability is available 658 back to 8.1. A limitation however is that <filename>postgres_fdw</filename> 659 generally assumes that immutable built-in functions and operators are 660 safe to send to the remote server for execution, if they appear in a 661 <literal>WHERE</literal> clause for a foreign table. Thus, a built-in 662 function that was added since the remote server's release might be sent 663 to it for execution, resulting in <quote>function does not exist</quote> or 664 a similar error. This type of failure can be worked around by 665 rewriting the query, for example by embedding the foreign table 666 reference in a sub-<literal>SELECT</literal> with <literal>OFFSET 0</literal> as an 667 optimization fence, and placing the problematic function or operator 668 outside the sub-<literal>SELECT</literal>. 669 </para> 670 </sect2> 671 672 <sect2> 673 <title>Examples</title> 674 675 <para> 676 Here is an example of creating a foreign table with 677 <literal>postgres_fdw</literal>. First install the extension: 678 </para> 679 680<programlisting> 681CREATE EXTENSION postgres_fdw; 682</programlisting> 683 684 <para> 685 Then create a foreign server using <xref linkend="sql-createserver"/>. 686 In this example we wish to connect to a <productname>PostgreSQL</productname> server 687 on host <literal>192.83.123.89</literal> listening on 688 port <literal>5432</literal>. The database to which the connection is made 689 is named <literal>foreign_db</literal> on the remote server: 690 691<programlisting> 692CREATE SERVER foreign_server 693 FOREIGN DATA WRAPPER postgres_fdw 694 OPTIONS (host '192.83.123.89', port '5432', dbname 'foreign_db'); 695</programlisting> 696 </para> 697 698 <para> 699 A user mapping, defined with <xref linkend="sql-createusermapping"/>, is 700 needed as well to identify the role that will be used on the remote 701 server: 702 703<programlisting> 704CREATE USER MAPPING FOR local_user 705 SERVER foreign_server 706 OPTIONS (user 'foreign_user', password 'password'); 707</programlisting> 708 </para> 709 710 <para> 711 Now it is possible to create a foreign table with 712 <xref linkend="sql-createforeigntable"/>. In this example we 713 wish to access the table named <structname>some_schema.some_table</structname> 714 on the remote server. The local name for it will 715 be <structname>foreign_table</structname>: 716 717<programlisting> 718CREATE FOREIGN TABLE foreign_table ( 719 id integer NOT NULL, 720 data text 721) 722 SERVER foreign_server 723 OPTIONS (schema_name 'some_schema', table_name 'some_table'); 724</programlisting> 725 726 It's essential that the data types and other properties of the columns 727 declared in <command>CREATE FOREIGN TABLE</command> match the actual remote table. 728 Column names must match as well, unless you attach <literal>column_name</literal> 729 options to the individual columns to show how they are named in the remote 730 table. 731 In many cases, use of <xref linkend="sql-importforeignschema"/> is 732 preferable to constructing foreign table definitions manually. 733 </para> 734 </sect2> 735 736 <sect2> 737 <title>Author</title> 738 <para> 739 Shigeru Hanada <email>shigeru.hanada@gmail.com</email> 740 </para> 741 </sect2> 742 743</sect1> 744