Query Data Between Databases In the Same Universe

Just as a single Postgres server process can manage multiple databases at the same time, Yugabyte allows you to create and manage multiple YSQL databases residing in the same Yugabyte Universe. 

However, if you try to query data from one database while connected to another, you’d be greeted with this disappointing error message:

				
					d1=# SELECT * FROM d2.public.t_database_2;
ERROR:  cross-database references are not implemented: "d2.public.t_database_2"
LINE 1: SELECT * FROM d2.public.t_database_2;
                      ^
				
			

But wait, there’s a solution for this, and it’s called the Postgres Foreign Data Wrapper Extension!

The documentation explains that the postgres_fdw, which comes pre-bundled with Yugabyte, can be used to access data stored in external PostgreSQL servers.

Does this apply to databases within the same Yugabyte Universe? Let’s find out…

First we need to do a little set up.

				
					yugabyte=# SELECT current_database();
 current_database
------------------
 yugabyte
(1 row)

yugabyte=# CREATE DATABASE d1;
CREATE DATABASE

yugabyte=# CREATE DATABASE d2;
CREATE DATABASE

yugabyte=# \c d1
You are now connected to database "d1" as user "yugabyte".

d1=# CREATE TABLE t_database_d1 (c1 VARCHAR);
CREATE TABLE

d1=# INSERT INTO t_database_d1 SELECT 'Data from database D1!';
INSERT 0 1

d1=# \c d2
You are now connected to database "d2" as user "yugabyte".

d2=# CREATE TABLE t_database_d2 (c1 VARCHAR);
CREATE TABLE

d2=# INSERT INTO t_database_d2 SELECT 'Data from database D2!';
INSERT 0 1

				
			

Next we need to install the postgres_fdw extension on the D1 Database because in this example,  we want to read data from the D2 Database while connected to the D1 Database. 

				
					d2=# \c d1
You are now connected to database "d1" as user "yugabyte".

d1=# CREATE EXTENSION postgres_fdw;
CREATE EXTENSION

d1=# CREATE SERVER my_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'yugabytedb.tech', dbname 'd2', port '5433');
CREATE SERVER

d1=# CREATE USER MAPPING FOR yugabyte SERVER my_server OPTIONS (user 'yugabyte', password '');
CREATE USER MAPPING

d1=# CREATE FOREIGN TABLE t_database_d2 (c1 VARCHAR) SERVER my_server OPTIONS (schema_name 'public', table_name 't_database_d2');
CREATE FOREIGN TABLE

				
			

Now we can query data from the t_database_d2 table in the Database D2 from the Database D1!

				
					d1=# SELECT current_database();
 current_database
------------------
 d1
(1 row)

d1=# SELECT * FROM t_database_d1; -- Internal
           c1
------------------------
 Data from database D1!
(1 row)

d1=# SELECT * FROM t_database_d2; --External
           c1
------------------------
 Data from database D2!
(1 row)
				
			

Have Fun!