gruchalski.com

YugabyteDB: Postgres foreign data wrapper

a brief intro to postgres_fdw with YugabyteDB
 1005  5 mins
thumbnail

Mmmm, nearly missed it.

YugabyteDB 2.9.1.0 was released on the 29th of October.

So here’s the thing. Back in August 2021, I contributed foreign data wrapper support to YugabyteDB, and 2.9.1.0 is the first beta release with this feature included. What I’m trying to say: postgres_fdw extension can be used in YugabyteDB starting with version 2.9.1.0.

The [YSQL] Foreign Data Wrapper support pull request contains all the interesting details but the bottom line is:

  • create / alter / drop: foreign data wrapper
  • create / alter / drop: server
  • create / alter / drop: user mapping
  • create / alter / drop: foreign table (with the caveat that not supported alter table features of YugabyteDB will also not work here)
  • import foreign schema: this statement uses collate under the hood and requires YugabyteDB with ICU support to work out of the box

Let’s give it a go.

§the environment

We will need a proper YugabyteDB cluster. This setup will have 3 masters and 9 TServers. The design of this cluster uses 3 always-on TServers and 2 groups of TServers, each with 3 TServers per tenant. In effect, we have:

  • a shared set of masters
  • region: base1a: 3 TServers
  • region: tenant1a: 3 TServers
  • region: tenant2a: 3 TServers
  • each TServer region has its own Envoy proxy in front

I am going to use my reference Docker compose setup which is available here.

§Docker image

Build the referenced Docker image:

1
2
3

cd .docker/yugabyte-db/
docker build -t local/yugabyte:2.9.1.0-b140 .
cd -

And start the cluster:

1
2
3
4
5

docker-compose --env-file "$(pwd)/.env" \
    -f compose-masters.yml \
    -f compose-tservers-shared.yml \
    -f compose-tservers-tenant1.yml \
    -f compose-tservers-tenant2.yml up

This will most likely take some time to settle. On my lab server, this takes about 15 seconds.

Mind you, this cluster needs about 25GB RAM to operate rather reasonably. There are 12 containers, each reserving 2GB RAM and some Envoy proxies.

§what’s the plan

The plan of action goes like this:

  • as yugabyte user: configure two tenant databases,
    • each database is owned by a respective tenant user,
    • each tenant user has a tablespace assigned,
  • as tenant2: create a table,
  • as yugabyte: configure the foreign data wrapper for tenant1,
  • as tenant1, create a foreign table and run some queries on it.

§setting things up

All tooling for this setup is already within the repository. All commands should be executed from the directory where compose files live.

§setup tenants

The password for the yugabyte user is yugabyte (default). Passwords for those new accounts are the same as usernames: tenant1 and tenant2 respectively.

Extension related errors can be ignored.

1
2
3
4
5

docker run --rm \
  --net=yb-dbnet \
  -v "$(pwd)/sql-init-tenant1.sql:/init.sql" \
  -ti postgres:11.2 \
  bash -c 'psql "host=envoy-yb-shared port=35432 user=yugabyte dbname=yugabyte" -f /init.sql'

Password for user yugabyte:
CREATE ROLE
CREATE DATABASE
CREATE TABLESPACE
REVOKE
REVOKE
GRANT
ALTER ROLE
ALTER ROLE
You are now connected to database "tenant1db" as user "yugabyte".
psql:/init.sql:12: ERROR:  could not open extension control file ...

1
2
3
4
5

docker run --rm \
  --net=yb-dbnet \
  -v "$(pwd)/sql-init-tenant2.sql:/init.sql" \
  -ti postgres:11.2 \
  bash -c 'psql "host=envoy-yb-shared port=35432 user=yugabyte dbname=yugabyte" -f /init.sql'

Password for user yugabyte:
CREATE ROLE
CREATE DATABASE
CREATE TABLESPACE
REVOKE
REVOKE
GRANT
ALTER ROLE
ALTER ROLE
You are now connected to database "tenant1db" as user "yugabyte".
psql:/init.sql:12: ERROR:  could not open extension control file ...

§create tenant2 table

Connect as tenant2:

1
2
3
4

docker run --rm \
  --net=yb-dbnet \
  -ti postgres:11.2 \
  bash -c 'psql "host=envoy-yb-tenant2 port=35432 user=tenant2 dbname=tenant2db"'

Password for user tenant2:
psql (11.2 (Debian 11.2-1.pgdg90+1))
Type "help" for help.

tenant2db=>

And create a table:

1

create table sharedtableexample (rowid int, rowval text) split into 3 tablets;

CREATE TABLE

Close the connection:

1

\q

I’ve noticed that all-lower-case names are the easiest to work with.

§configure tenant1 foreign data wrapper

Technically, it does not matter which Envoy proxy is used for this operation, as long as it is executed as the yugabyte user:

1
2
3
4

docker run --rm \
  --net=yb-dbnet \
  -ti postgres:11.2 \
  bash -c 'psql "host=envoy-yb-shared port=35432 user=yugabyte dbname=tenant1db"'

Password for user yugabyte:
psql (11.2 (Debian 11.2-1.pgdg90+1))
Type "help" for help.

tenant1db=#

Run these commands to create the extension, create a server and setup use mapping with permissions:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

create extension postgres_fdw;
create server fdw_tenant2shares foreign data wrapper postgres_fdw options (
  host 'envoy-yb-tenant2',
  port '35432',
  dbname 'tenant2db');
create user mapping for tenant1 server fdw_tenant2shares options (
  user 'tenant2',
  password 'tenant2');
grant usage on foreign server fdw_tenant2shares to tenant1;
\q

postgres_fdw extension needs to be created within the tenant database so we have connected directly to the target database. Alternatively, I could have connected to the yugabyte database and used the \connect tenant1db command.

§create foreign table as tenant1

As tenant1:

1
2
3
4

docker run --rm \
  --net=yb-dbnet \
  -ti postgres:11.2 \
  bash -c 'psql "host=envoy-yb-tenant1 port=35432 user=tenant1 dbname=tenant1db"'

Password for user tenant1:
psql (11.2 (Debian 11.2-1.pgdg90+1))
Type "help" for help.

tenant1db=>

List existing foreign servers and create the foreign table:

1
2
3
4
5
6

\des+
create foreign table sharedtableexample_foreign (
  rowid integer options (column_name 'rowid'),
  rowval text options (column_name 'rowval')
) server fdw_tenant2shares options (
  schema_name 'public', table_name 'sharedtableexample');

§run some queries

tenant1 can now insert data to the foreign table:

1
2
3
4

insert into sharedtableexample_foreign (rowid, rowval) values (1, 'hello, world!');
insert into sharedtableexample_foreign (rowid, rowval) values (2, 'hello, world!');
insert into sharedtableexample_foreign (rowid, rowval) values (3, 'hello, world!');
\q

§verify

Connect back to tenant2db as tenant2:

1
2
3
4

docker run --rm \
  --net=yb-dbnet \
  -ti postgres:11.2 \
  bash -c 'psql "host=envoy-yb-tenant2 port=35432 user=tenant2 dbname=tenant2db"'

Password for user tenant2:
psql (11.2 (Debian 11.2-1.pgdg90+1))
Type "help" for help.

tenant2db=>

And select the data from the original table:

1

select * from sharedtableexample order by rowid;

All records are in:

 rowid |    rowval
-------+---------------
     1 | hello, world!
     2 | hello, world!
     3 | hello, world!
(3 rows)

§voila!

The beauty of YugabyteDB. Because it is PostgreSQL under the hood, postgres_fdw is simply working.

See Also: