PostgreSQL High Availability Cookbook - Second 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 this SQL statement to check for the foreign server entry:

        SELECT srvname, srvoptions
          FROM pg_foreign_server;