PostgreSQL 12 High Availability Cookbook - Third Edition

How to do it...

For this recipe, we have two servers: pg-primary as our main data source and pg-report as a reporting server. As with the previous recipe, we will use pgbench as our sample database. Follow these steps to create a connection from pg-report to pg-primary within pgbench.

1. Connect to pgbench on the pg-report PostgreSQL server as the postgres user.
2. Execute the following SQL statement:

CREATE SERVER primary_db
    FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (host 'pg-primary', dbname 'pgbench');

3. Execute the following SQL statement to check for the foreign server entry:

SELECT srvname, srvoptions
  FROM pg_foreign_server;