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:
- Create a user mapping for the postgres user with this SQL statement:
CREATE USER MAPPING FOR postgres SERVER primary_db OPTIONS (user 'postgres');
- Drop any existing pgbench_accounts table with this SQL statement:
DROP TABLE IF EXISTS pgbench_accounts;
- 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'); ...