Fdw Setup Postgres

In your PostgreSQL instance:

1. If not installed, create a postgres_fdw extension in your database:

   mzsqlCopy

   CREATE EXTENSION postgres_fdw;
   

2. Create a foreign server to your Materialize, substitute your Materialize connection details.

   mzsqlCopy

   CREATE SERVER remote_mz_server
      FOREIGN DATA WRAPPER postgres_fdw
      OPTIONS (host '<host>', dbname '<db_name>', port '6875');
   

3. Create a user mapping between your PostgreSQL user and the Materialize fdw_svc_account:

   mzsqlCopy

   CREATE USER MAPPING FOR <postgres_user>
      SERVER remote_mz_server
      OPTIONS (user 'fdw_svc_account', password '<service_account_password>');
   

4. For each view/materialized view you want to access, create the foreign table mapping (you can use the data explorer to get the column detials)

   mzsqlCopy

   CREATE FOREIGN TABLE <local_view_name_in_postgres> (
            <column> <type>,
            ...
        )
   SERVER remote_mz_server
   OPTIONS (schema_name '<schema>', table_name '<view_name_in_Materialize>');
   

5. Once created, you can select from within PostgreSQL:

   mzsqlCopy

   SELECT * from <local_view_name_in_postgres>;