A foreign data wrapper(fdw) is an extension available in PostgreSQL that allows you to access a table or schema in one database from another.
In this repo, we are using the Kubernetes to deploy the Postgresql instances.
- Rancher Desktop:
1.4.1 - Kubernetes:
v1.24.3 - kubectl
v1.23.3 - Helm:
v3.9.0
tl;dr: ./scripts/up.sh
kubectl create namespace pg-dfw-demo --dry-run=client -o yaml | kubectl apply -f -follow the bitnami postgresql chart to install postgresql
helm repo add bitnami https://charts.bitnami.com/bitnami
helm repo update bitnamihelm upgrade --install local-postgresql bitnami/postgresql -n pg-dfw-demo -f postgresql/local.yaml
helm upgrade --install foreign-postgresql bitnami/postgresql -n pg-dfw-demo -f postgresql/foreign.yamllogin to the postgresql
kubectl run local-postgresql-client --rm --tty -i --restart='Never' --namespace pg-dfw-demo --image docker.io/bitnami/postgresql:14.5.0-debian-11-r21 --env="PGPASSWORD=demo_password" --command -- psql --host local-postgresql -U postgres -d postgres -p 5432create a FDW extension and a postgresql foreign server
CREATE EXTENSION postgres_fdw;
CREATE SERVER foreign_pg_svr FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'foreign-postgresql', port '5432', dbname 'postgres');create the user mapping
CREATE USER MAPPING FOR CURRENT_USER SERVER foreign_pg_svr OPTIONS (user 'foreign_user', password 'foreign_user_password');create the schema
CREATE SCHEMA foreign_pg;import the foreign schema
IMPORT FOREIGN SCHEMA public
LIMIT TO (test_table, users)
FROM SERVER foreign_pg_svr
INTO foreign_pg;login to the postgresql
kubectl run local-postgresql-client --rm --tty -i --restart='Never' --namespace pg-dfw-demo --image docker.io/bitnami/postgresql:14.5.0-debian-11-r21 --env="PGPASSWORD=demo_password" --command -- psql --host local-postgresql -U postgres -d postgres -p 5432list all foreign tables
SET search_path=foreign_pg;
\det;
List of foreign tables
Schema | Table | Server
------------+------------+----------------
foreign_pg | test_table | foreign_pg_svr
foreign_pg | users | foreign_pg_svr
(2 rows)read from foreign tables
select count(*) from foreign_pg.users;
count
-------
10000
(1 row)write into the foreign tables
insert into foreign_pg.test_table(id) values (42);tl;dr: ./scripts/down.sh
helm uninstall local-postgresql -n pg-dfw-demo
helm uninstall foreign-postgresql -n pg-dfw-demo
kubectl delete pvc --all -n pg-dfw-demo
kubectl delete namespace pg-dfw-demo
React
Typescript
Django
Laravel
Facebook
Microsoft
Google
Alibaba
D3
Tencent