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.
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.
To prepare for setting up the FDW extension you need to:
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.
The setting appears on the PostgreSQL server page in the Azure portal, under Connection Security.
To set up the postgres_fdw extension, on the sending server, perform the following steps.
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!
Program Manager - Microsoft
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.