Setup the clusters (May not be needed during the actual demo.) ============================================================== 1. initdb both the clusters from the demo root directory $BuildDir/bin/initdb -D ls_data $BuildDir/bin/initdb -D fs_data 2. Modify postgres.conf to change ports to 15322 and 15323 ls_data/postgresql.conf uses port 15431 fs_data/postgresql.conf uses port 15432 Boot the local and foreign server and setup =========================================== 1. Boot both the servers $BuildDir/bin/pg_ctl start -D ls_data -l /tmp/ls_data.log echo -ne "\033]2;local server running\007" -- to change the name of terminal In a different terminal $BuildDir/bin/pg_ctl start -D fs_data -l /tmp/fs_data.log echo -ne "\033]2;foreign server running\007" -- to change the name of terminal 2. Build postgres_fdw from code and install 4. On local server create postgres_fdw extension create extension postgres_fdw; -- shows all the foreign data wrappers available select * from pg_foreign_data_wrapper; 5. Create foreign server, user mapping -- no user and password specified since a local connection by superuser. create user mapping for public server foreign_pg; -- public user mapping create user mapping for CURRENT_USER server foreign_pg; -- private UM -- show the contents of pg_foreign 6. Create a local table on the foreign server create schema frgn_schema; create table frgn_schema.t1 (a int, b int); 7. Create foreign table on the local server create foreign table ft1 (val int options(column_name 'a'), val2 int options(column_name 'b')) server foreign_pg options (schema_name 'frgn_schema', table_name 't1'); 5. Create another local and foreign table (for join) On foreign server: create table frgn_schema.t2 (a int, b int); On local server: create foreign table ft2 (val int options(column_name 'a'), val2 int options(column_name 'b')) server foreign_pg options (schema_name 'frgn_schema', table_name 't2'); On local server: insert into ft2 select i * 3, i * 3 + 1 from generate_series(1, 6) i; Reset Demo ========== 1. On foreign server truncate the table referenced as foreign tables. truncate t1; ****** Actual demo start from here ****************** Open connections and demo various pushdown features =================================================== Try explain and explain analyze to show various pushdown features and explain cost model. 1. Open two separate connections to local and foreign server in separate terminals. PS1="[\u@\h \W]" echo -ne "\033]2;local server connection\007" psql -d postgres -p 15431 echo -ne "\033]2;foreign server connection\007" psql -d postgres -p 15432 Show foreign server, user mapping and foreign table entries =========================================================== 1. shows all the foreign data wrappers available \dew+ 2. Foreign servers and user mapping \des+ \deu+ 3. Foreign tables \det+ Demo various pushdown features etc. ====================================================================== 1. Simple table scan on local server: select * from ft1; On foreign server: select * from t1; 2. Populate and simple table scan On foreign server: insert into frgn_schema.t1 select i, i + 1 from generate_series(1, 20) i; On local server: select * from ft1; On local server: select * from ft1 where val < 10; -- WHERE clause pushdown 3. DML pushdown (Show the effects on the foreign server as well.) On local server: delete from ft1 where val % 2 = 0; -- DML pushdown On local server: update ft1 set val2 = val + 2; 4. Sort pushdown On local server: select * from ft1 order by val; select * from ft1 order by val + val2; 6. Join pushdown On local server: select * from ft1 join ft2 on (ft1.val = ft2.val) where ft1.val2 < 15; On local server: select * from ft1 left join ft2 on (ft1.val = ft2.val) where ft1.val2 < 15; 7. Effect of analyze On local server: select * from pg_statistic where starelid = 'ft2'::regclass; On local server: analyze ft2; On local server: select * from pg_statistic where starelid = 'ft2'::regclass; 8. Create view, materialized view On local server: create view ft_join as select ft1.val ft1_val, ft1.val2 ft1_val2, ft2.val2 ft2_val2 from ft1 left join ft2 on (ft1.val = ft2.val); On local server: create materialized view mv_ft_join as select ft1.val ft1_val, ft1.val2 ft1_val2, ft2.val2 ft2_val2 from ft1 left join ft2 on (ft1.val = ft2.val); select * from ft_join; select * from mv_ft_join;