Scaling out Azure Database for PostgreSQL using Foreign Data Wrappers

[ambhatna]

Have you ever come across a scenario in which you need to combine two PostgreSQL databases to explore some data or generate reports?  You want to access an additional PostgreSQL server for various reasons such as reporting and ETL jobs for some of the data. The Foreign Data Wrapper (FDW) (postgres_fdw) feature in PostgreSQL is a great option to access data stored in external PostgreSQL servers. 

Overview of Foreign Data Wrappers

FDWs in PostgreSQL allow you to connect a PostgreSQL server to a range of different remote data stores, from SQL databases to flat files. PostgreSQL provides a variety of officially supported FDWs. One such supported FDW is postgres_fdw, which allows you to create foreign tables in a PostgreSQL database that represent tables in the remote PostgreSQL database. These foreign tables work as proxies for the remote data source, and you can access the foreign tables using regular SQL queries. These foreign table will work as proxies for your remote data source. 

To use the postgres_fdw extension to connect the data between two servers running Azure Database for PostgreSQL, you need to set up the FDW extension. 

Prerequisites 

To prepare for setting up the FDW extension you need to: 

• Create two Azure Database for PostgreSQL servers. 

One server will store your primary database (the receiving server), while the other (the sending server) will host the remote data that you want to access. 

• On the receiving server, enable the Allow access to Azure services setting, which ensures that connections from the sending server have access through its firewall. Enabling this setting effectively whitelists all Azure IP addresses. 

The setting appears on the PostgreSQL server page in the Azure portal, under Connection Security.

            

Set up the postgres_fdw extension 

To set up the postgres_fdw extension, on the sending server, perform the following steps. 

1. Install the postgres_fdw extension by running the following script: 

CREATE EXTENSION postgres_fdw;

     2. Create a foreign server object by running the following script: 

CREATE SERVER demoserver 
FOREIGN DATA WRAPPER postgres_fdw  
OPTIONS (host 'myreceivingserver.postgres.database.azure.com',
         dbname 'postgres', port '5432');

    3. Create a user mapping for the user on the sending server to allow to access receiving server by running the following script: 

CREATE USER MAPPING FOR demouser
      SERVER demoserver 
      OPTIONS (user 'demouser@myreceivingserver', password 'secretpassword');

    4. Create a foreign table for each receiving database table you want to access by running the following script: 

      Important: The columns of the foreign table must match the referenced remote table.

CREATE FOREIGN TABLE inventory ( 
       id serial,
       name VARCHAR(50),
       quantity INTEGER
)
SERVER demoserver; 

After performing above steps successfully, you can query the receiving server table by using SELECT (as shown below) from the sending server. 

SELECT * FROM inventory;

You can also modify the receiving database table if the remote user you specified in the user mapping has the appropriate privileges. 

Having the capability of accessing data from a remote PostgreSQL database on the fly solves many problems and makes the job of reporting and ETL very easy.  However, network latency plays an important role in accessing data using Foreign Data Wrapper and performance can suffer.  Please test your application for performance before setting up FDW.

If you have trouble setting up any extension supported by Azure Database for PostgreSQL, please let us know by contacting us at AskAzureDBforPostgreSQL@service.microsoft.com! 

Thank you! 

Amol Bhatnagar 

Program Manager - Microsoft