PostgreSQL High Availability Cookbook - Second 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, which refers to a table on pg-primary within pgbench:

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

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

2. Drop any existing pgbench_accounts table with this 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 'pgbench_accounts'); ...