PostgreSQL High Availability Cookbook - Second Edition

Creating all tables for a foreign schema

This recipe provides an example of creating a single foreign table, although in an actual production system, this process could be quite cumbersome. Do we really want to create dozens or even hundreds of tables one by one? In PostgreSQL 9.5 and more, we can actually import the entire foreign schema.

The test data we're using is the default set of tables created by the pgbench tool. This means all of the tables exist in the public schema. With this knowledge, we could substitute this command for the CREATE FOREIGN TABLE step in our recipe:

IMPORT FOREIGN SCHEMA public
  FROM SERVER primary_db
  INTO public;

Of course, importing the public schema is not a recommended practice. Yet it's clear we can utilize ...