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!