PostgreSQL 12 High Availability Cookbook - Third Edition

How to do it...

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

1. Create a user mapping for the postgres user with the following SQL statement:

CREATE USER MAPPING FOR postgres
    SERVER primary_db
    OPTIONS (user 'postgres', password 'changeme');

2. Drop any existing pgbench_accounts table with the following SQL statement:

DROP TABLE IF EXISTS pgbench_accounts;

3. Execute the following SQL statement to create the foreign table:

CREATE FOREIGN TABLE pgbench_accounts ( aid INTEGER NOT NULL, bid INTEGER, abalance INTEGER, filler CHAR(84) ) SERVER primary_db OPTIONS (table_name ...