As we will be using a foreign server and a user mapping in this recipe, please follow all the previous recipes before proceeding.

For this recipe, we will perform all actions on the pg-report PostgreSQL server in the pgbench database. Follow these steps to create a table in pg-report, which refers to a table on pg-primary within pgbench:

In the first step, we create a user mapping for the postgres user. This is primarily a security step; remote tables should be as locked down as possible under the assumption that their contents are untrusted or otherwise sensitive. This allows us to create the foreign table as the postgres database superuser, preventing any unauthorized use of the remote server.

Next, we drop the local copy of the pgbench_accounts table on the pg-report server. This is both the largest table created by pgbench and the table we identified as a potential candidate for remote access of some kind. We drop it because we are going to replace it with a foreign table that refers to the same table on pg-primary.

To create the foreign table itself, we can look at the table definition of pgbench_accounts and ignore things such as primary keys, indexes, and other types of constraint. By issuing a CREATE FOREIGN TABLE statement instead of CREATE TABLE, PostgreSQL looks for some additional table specification settings. As with user mappings, we set the SERVER to primary_db. For OPTIONS, we simply need to name the remote table that this foreign table represents: pgbench_accounts.

The next step is not strictly necessary but one we strongly recommend. PostgreSQL knows very little about the contents of the remote database or the table we've just created. The PostgreSQL query planner makes much better decisions when it is fully informed of table contents. By running ANALYZE on pgbench_accounts, PostgreSQL fetches enough data to perform statistical analysis and stores that information in pg_stats for query-planning purposes.

Then, the bench_user user mapping we created needs specific access granted before it can use the new table. If we simply granted access locally, the remote bench_user would still not be able to use the table, so we would receive an error by doing so. Any grants for foreign tables must be equivalent on both servers involved.

Finally, we use psql to examine the foreign table structure. This is what PostgreSQL sees when a foreign table is used in a query. Our test server provided this output:

PostgreSQL makes it fairly clear that this is a Foreign table. The FDW Options column lists any column options that we might have attached, though it's empty in our case. We can see that this table resides on the primary_db server and that it corresponds to the pgbench_accounts table on that system. All of this allows us to see that this isn't a regular table; it also allows us to see where its data is actually stored.

PostgreSQL enforces foreign table statements everywhere. For instance, let's try to drop this table using a regular DROP TABLE statement:

DROP TABLE pgbench_accounts;

The server would quickly respond with this output:

Similarly, if we checked the relkind column in the pg_class catalog table, its type would be listed as f for foreign table instead of r for relation. PostgreSQL saves several hints and other bread crumbs so that there is never any question as to the nature of foreign tables. Doing so prevents bugs and can even produce better performance, as remote access is taken into consideration before it selects the most efficient query plan. The more you use foreign tables, the more of these reminders you'll encounter.