Cross Database Querying in Postgres SQL

Blogs

Seamless SSISDB Migration: A Detailed Walkthrough
August 24, 2024
ONELAKE: The core component of Microsoft Fabric
August 26, 2024

Cross Database Querying in Postgres SQL

What is cross Database Querying?

Consider you want to compare and analyze data which are sitting two different databases in your environment. To do that, you need multiple tables from both the databases and replicating tables in either of the DB is not possible. Neither can you move data from one DB to another. How to solve this problem?

Cross Database Querying is the solution to your problem. Cross database querying is a feature which allows one to query data from tables sitting in one database from another database. For example, consider, you are working in the Finance database which consists of all the financial tables, and you want to query Employee table present in the HR database, this can be done with the help of cross database querying.

How to use cross database Querying in Postgres?

Postgres SQL helps leverage cross database querying with the help of an extension called postgres_fdw (Postgres foreign data wrapper).

Let’s understand the steps to enable this feature and use it for cross database querying.

Before we get into the steps, lets create sample tables for our understanding in one of the databases. We are creating these tables in database test1 and query these tables in database test2.

  • CREATE TABLE public.table1(a int, b int, c text);

INSERT INTO public.table1 VALUES(generate_series(1,10),generate_series(11,20),’t1′);

  • CREATE TABLE public.table2(a int, b int, c text);

INSERT INTO public.table2 VALUES(generate_series(1,10),generate_series(11,20),’t2′);

STEPS:

  1. CREATE EXTENION FOR FOREIGN DATABASE WRANGLER

One should create the extension in the database they want to query in. This extension will help create a connector between databases.

  • Create extension postgres_fdw <extension name>

 

  1. CREATE A SERVER

The next step is to create a server to set up a connection to the other database, from which the data should be pulled. Provide the host, dbname and port information even if the database where you installed the foreign data wrapper is on the same host as the database you’re connecting to.

  • create server test2 <Server_name>

foreign data wrapper postgres_fdw

options(host ‘localhost’,dbname ‘test2’,port ‘5432’)

 

  1. CREATE A MAPPING

Mapping is created for the user in the current database. The mapping of the user is done for the server created before. This gives the user in the current database permissions to query the data from the foreign db.

NOTE: The default user in Postgres does not have permission to query foreign data, one should create another user with password and grant superuser access to the new user.

  • create user mapping for testuser <username>

server test2

options (user ‘testuser’, password ‘abczxy@123’)

  1. IMPORT THE DATA.

After the connection between the two databases have been created, lets import the required data from database test1 to test2. There are 2 ways to import the data:

a) Create a foreign table for the source table in the database test2.

  • CREATE FOREIGN TABLE public.t1(a int, b int)

server test2 options(schema_name ‘public’, table_name ‘t1’);

This method has the following advantages and disadvantages:

Advantages: You can specify a custom DDL structure for foreign tables. For example, the t1 table in the db02 database contains the a, b, and c fields, but the test2 database contains only the a and b fields. By using this method, you can specify fields when you create a foreign table.

Disadvantages: You must know the DDL structure of each table. An extended period is required to import multiple foreign tables at a time.

 

b)Import all tables from the schema of the source database. You can either import all the tables or limit it to the required tables.

  • IMPORT FOREIGN SCHEMA public <Schema_name>

LIMIT TO ( t2) <table_name>

FROM SERVER test2 INTO public;

This method has the following advantages and disadvantages:

Advantages: You can import foreign tables in a short period of time. You do not need to know the DDL structure of each table.

Disadvantages: Foreign tables must have the same names and fields as the tables in the source database.

 

  1. QUERY THE DATA OF TEST1 FROM TEST2.

Once the data is imported in the desired schema of the foreign database (test2) we can now query the data. You can perform all kinds of operations (select, insert, update, delete) on these tables.

And that’s it, quite simple right? You didn’t have to move your data, neither copy it and yet all the analysis could be done. Hope this was helpful and an interesting learning experience.

Until next time!!

 

Yatika Sheth


Yatika Sheth

Leave a Reply

Your email address will not be published. Required fields are marked *