Topic: Cross-database query between Google SQL instances PostgreSQL
Cross-database query between Google SQL instances PostgreSQL | GCP SQL Tutorial 2022, in this article we are going to learn Cross database query between Google SQL instances PostgreSQL.
Script used in demo:
create database sales_asiacreate database sales_europe
create table public.AsiaSale(id int, name varchar(100), region varchar(100))
insert into public.AsiaSale values(1,'aamir','Asia')
Select * From public.AsiaSale create table public.EuropeSale(id int, name varchar(100), region varchar(100))
insert into public.EuropeSale values(2,'lisa','Europe')
Select * From public.europesale -- we want to execute union query in sales_asia database that should get data from sales_europe.public.EuropeSale table.
Select * From public.europesale
union all
select * from public.AsiaSale 1) Set up a Foreign User-- Do this on DB from which you would like to read the tables
CREATE USER fdwuser WITH PASSWORD 'test123$';
GRANT USAGE ON SCHEMA PUBLIC TO fdwuser;
GRANT SELECT ON europesale TO fdwuser; --Check the list of Tables
select * from information_schema.tables where table_name like '%sale%' 2) Create the Extension
CREATE EXTENSION postgres_fdw;
select * from pg_extension; 3) Create the Foreign Server
CREATE SERVER secondrydb_srv FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host '34.67.244.181', port '5432', dbname 'sales_europe');
Select * From pg_foreign_server; 4) Create User Mapping
CREATE USER MAPPING FOR postgres SERVER secondrydb_srv OPTIONS(user 'fdwuser',password 'test123$');
Select * From pg_user_mappings 5) Grant the Local User Access to the Foreign Data Wrapper
GRANT USAGE ON FOREIGN SERVER secondrydb_srv TO postgres; 6) Import the Foreign Schema or Tables
IMPORT FOREIGN SCHEMA public LIMIT TO (europesale) FROM SERVER secondrydb_srv INTO public; Select * From public.europesale
union all
select * from public.AsiaSale