PostgreSQL 12 High Availability Cookbook - Third Edition

How it works...

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, ...