PostgreSQL 12 High Availability Cookbook - Third Edition

How to do it...

For this recipe, we will continue to use two servers: pg-primary as our main data source and pg-report as a reporting server. We will keep pgbench as our sample database. Follow these steps to create and map a user from pg-report to pg-primary within pgbench:

1. Execute the following SQL statement on both PostgreSQL servers as the postgres user:

CREATE USER bench_user WITH PASSWORD 'testing'; 

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

CREATE USER MAPPING FOR bench_user 
    SERVER primary_db 
    OPTIONS (user 'bench_user', password 'testing'); 

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

SELECT u.rolname AS ...