How to do it…

We will first describe the variant that uses dblink, which applies to all supported PostgreSQL versions:

1. First, we need to install the dblink contrib module. The general procedure is explained in the Adding an external module to PostgreSQL recipe of Chapter 3, Configuration.

2. Then, we create some access definitions. The preferred way is to use the following commands, which are SQL standard (SQL/MED):

postgres=# CREATE FOREIGN DATA WRAPPER postgresql 
 VALIDATOR postgresql_fdw_validator;
CREATE FOREIGN DATA WRAPPER

postgres=# CREATE SERVER otherdb
 FOREIGN DATA WRAPPER postgresql
 OPTIONS (host 'foo', dbname 'otherdb', port '5432');
CREATE SERVER

postgres=# CREATE USER MAPPING FOR PUBLIC
SERVER otherdb;
CREATE USER MAPPING

3. You must create FOREIGN DATA WRAPPER only once, though you need one SERVER for each PostgreSQL destination database to which you may wish to connect. This is just the connection definition, not the connection itself.
4. Creating a public user mapping with no options seems strange, though it will mean that we use the libpq default behavior. It will also mean that we will connect the remote database using the value of PGUSER, or if it is not set, use the operating system user. Clearly, if we want to use different credentials, then we must specify them with suitable options, either while creating the mapping or afterwards (ALTER USER MAPPING).

The VALIDATOR clause specifies a function whose purpose is to validate the parameters. That function is a part of the Foreign Data Wrapper and should have been provided by the author, so you need to create it only if you are developing a new type of Foreign Data Wrapper yourself.

5. Now connect using an unnamed connection, as follows:

        SELECT dblink_connect('otherdb');

6. This produces the following output:

        dblink_connect
        ----------------
         OK
        (1 row)

7. We limit ourselves to unnamed connections for simplicity. It is also possible to create a named connection, that is, a connection that is assigned a string so that it can be referred to directly later. This is obviously useful if we want to manage several connections, but it comes at the price of actually having to manage their life cycle (connection and disconnection).

8. Suppose you want to execute the following command:

postgres=#  INSERT  INTO  audit_log  VALUES  (current_user,  now());

9. To do so, run it on the unnamed remote connection like this:

postgres=#  SELECT  dblink_exec('INSERT  INTO  audit_log  VALUES'  ||
     '  (current_user,  now())',  true);

10. This will give the following output:

 dblink_exec
-------------
 INSERT 0 1
(1 row)

11. Notice that the remote command returns the command tag and number of rows processed as the return value of the function. The second option means "fail on error". If you look closely, there's also a subtle error-when the INSERT command is executed locally, we use this server's value of current_user. But when we execute remotely, we use the remote server's value of current_user, which might differ, depending on the user mapping defined previously.
12. Similarly, suppose you want to execute the following query on the unnamed remote connection:

        SELECT generate_series(1,3)

13. We start by typing this:

        SELECT *  
        FROM dblink('SELECT generate_series(1,3)')

14. This will result in the following error:

ERROR:    a  column  definition  list  is  required for functions  returning
"record"
LINE  2:  FROM  dblink('SELECT  generate_series(1,3)');
          ^

15. This error message is telling us that we need to specify the list of output columns and output types that we expect from the dblink() function, because PostgreSQL is unable to determine them automatically at parsing time (that is, without running the query).

16. We can add the missing information by providing an alias in the FROM clause, as in the following example:

        SELECT * 
       FROM dblink('SELECT generate_series(1,3)') 
         AS link(col1 integer);

17. This will succeed, and result in the following output:

        col1  
        ------ 
            1 
            2 
            3 
        (3 rows)

18. To disconnect from the unnamed connection, you can issue the following:

        SELECT dblink_disconnect();

19. You get the following output:

         dblink_connect  
        ---------------- 
         OK 
        (1 row)

Now we will describe the second variant of this recipe, which uses the PostgreSQL Foreign Data Wrapper instead of dblink. 

1. The first step is to install the postgres_fdw contrib module, which is as simple as this:

        postgres=# CREATE EXTENSION postgres_fdw;

2. The result is as follows:

        CREATE EXTENSION

3. This extension automatically creates the corresponding Foreign Data Wrapper, as you can check with psql's \dew meta-command:

postgres=# \dew
                     List of foreign-data wrappers
     Name     | Owner  |       Handler        |       Validator       
--------------+--------+----------------------+------------------------
 postgres_fdw | gianni | postgres_fdw_handler | postgres_fdw_validator
(1 row)

4. We can now define a server:

        postgres=# CREATE SERVER otherdb 
        FOREIGN DATA WRAPPER postgres_fdw 
        OPTIONS (host 'foo', dbname 'otherdb', port '5432');

5. This produces the following output:

CREATE SERVER

6. Then, we can define the user mapping:

postgres=# CREATE USER MAPPING FOR PUBLIC SERVER otherdb;

7. The output is as follows:

        CREATE USER MAPPING

As an example, we will access a portion of a remote table containing (integer, text) pairs:

          postgres=# CREATE FOREIGN TABLE ft ( 
            num int , 
            word text ) 
          SERVER otherdb 
          OPTIONS ( 
            schema_name 'public' , 
            table_name 't' );

The result is quite laconic:

          CREATE FOREIGN TABLE

This table can now be operated almost like any other table. We check whether it is empty:

          postgres=# select * from ft;

This is the output:

           num | word  
          -----+------ 
          (0 rows)

8. We can insert rows as follows:

        postgres=# insert into ft(num,word) values     
        (1,'One'), (2,'Two'),(3,'Three');

9. This query produces the following output:

        INSERT 0 3

10. Then, we can verify that the aforementioned rows have been inserted:

        postgres=# select * from ft;

11. This is confirmed by the output:

         num | word   
        -----+------- 
           1 | One 
           2 | Two 
           3 | Three 
        (3 rows)

Note that you don't have to manage connections or format text strings to assemble your queries. Most of the complexity is handled automatically by the Foreign Data Wrapper.