Cross Database Query in PostgreSQL

 

I have been using Postgres for the last few years and recently came across the use case of cross-database query.  I am sharing the same and if you have the same kind of requirement you might it interesting.


Before going into more details, let's go through a few basic differences between MySQL and PostgreSQL:

A schema for both the DBMSs doesn't mean the same thing.

MySQL: 

  1. Synonymous to the database.
  2. Allows across database queries. For example, 
    1. SELECT * FROM db1.table1 tb1 inner join db2.table2 tb2 on tb1.column1 = tb2.column

PostgreSQL: 

  1. A namespace within the database. By default, a database contains three schemas namely information_schema, pg_catalog, and public. In the public schema, all tables and views created by a user reside.
  2. Doesn't allow across database queries out of the box. Foreign data wrapper does the same job. 



It provides a way to allow across database queries. 



NOTE: The following set of commands have experimented on docker containers. It might differ for other PostgreSQL service providers such as RDS on AWS.


PostgreSQL provides across database queries with the help of the extension "postgres_fdw".

This extension is PostgreSQL to PostgreSQL connector, where databases can reside on the same host of different hosts.

We will use the term Current Database(where you will need tables from another database) to refer current database and Remote Database to refer another database to which we create link.
  
Use the following steps to enable the same:
  1. Activate the Wrapper
    1. Log into the postgres docker and run following commands(docker exec -it <pg_docker> bash)
      2. psql -U root-pg
    2. \c <Current Database>;
    3. CREATE EXTENSION postgres_fdw; 
    4. GRANT CONNECT ON DATABASE <Remote Database> TO <CURRENT_DB_USER>;
    5. GRANT USAGE ON SCHEMA public TO <CURRENT_DB_USER>;
    6. GRANT SELECT ON ALL TABLES IN SCHEMA public TO <CURRENT_DB_USER>;
    7. ALTER DEFAULT PRIVILEDGES FOR ROLE <CURRENT_DB_USER> IN SCHEMA public GRANT SELECT ON TABLES TO <CURRENT_DB_USER>;
  2. Create Server Connection:
    1. Now, connect to current database : 
      2. psql -h <current_db_host> -d <current_database> -p <port> -U <current_db_user> -W
    2. CREATE SERVER <remote_server_connection_name> 
    3. FOREIGN DATA WRAPPER postgres_fdw 
    4. OPTIONS(host '<remote_host_address>', dbname '<remote_database_name>', port '<port_number>');
  3. Create User Mapping:
    1. With the same database connection as in step 2, run following commands:
      2. CREATE USER MAPPING FOR <remote_server_connection_name>
    2. OPTIONS(user '<remote_database_user>', password '<remote_database_password>'); 
    3. CREATE USER MAPPING FOR <remote_server_connection_name>
    4. OPTIONS(user '"root-pg"', password '<remote_database_password>');
  4. Import Data
    1. Now that remote server connection has been created and user mappings are also done, its time to import remote schema. Though all tables can be imported, it should be limited to required tables only.
    2. IMPORT FOREIGN SCHEMA public 
    3. LIMIT TO (table1, table2, table3) 
    4. FROM SERVER <remote_server_connection_name> INTO public;
  5. Verify Setup
    1. The following two queries can be run to find out remote server connection and availabe tables from remote database.
      2. SELECT * FROM pg_foreing_server;
srvname | srvowner | srvfdw | srvtype | srvversion | srcacl | srvoptions

    2. -------------------------------------------------------------------------------
    3. --------------------------------------------------------------
    4. <remote_server_connection_name> | 12321 | 23432 |      |      |        |
    5. {host=<host_address>, dbname=<db_name>, port=<port_number>}

    7. SELECT * FROM pg_foreign_table;
    8. ftrelid | ftserver | ftoption
    9. ------------------------------------------------------------------------
    10. 12343     | 12345 | {schema_name=public, table)name=<remote_table_name>}
  6. Query
    1. Now, that remote tables are imported and availbe in the current database, any query can be run on the same. For example,
      2. SELECT *
    2. FROM <remote_table_name>;


Note:- Please write comment for any queries.

Comments

Post a Comment