How to move schema to other tablespace

Question

Postgres 13.2 database contains schema named company2 which contains tables and indexes and is located in g:\Program Files\Postgresql\13\data directory.

Server has also drive I:

How to move tables and indexes from company2 schema to I: drive ?

Using

PostgreSQL 13.2, compiled by Visual C++ build 1914, 64-bit

on windows server.

Answer 1

You'll have to run some dynamic sql execute in a pl/pgsql loop over system catalogs to issue alter...set tablespace for everything inside, individually. For example, tables are in pg_tables: demo at db<>fiddle

select * from pg_tables where schemaname='company2';

do $do_block$
declare record_ record;
begin
for record_ in select schemaname,tablename from pg_tables 
               where coalesce(tablespace,'')<>'new_tablespace'
               and schemaname='company2'
  loop
  execute format ('alter table %I.%I set tablespace new_tablespace',record_.schemaname,record_.tablename);
end loop;
end $do_block$;

select * from pg_tables where schemaname='company2';

schemaname	tablename	tableowner	tablespace	hasindexes	hasrules	hastriggers	rowsecurity
company2	test	postgres	null	t	f	f	f

schemaname	tablename	tableowner	tablespace	hasindexes	hasrules	hastriggers	rowsecurity
company2	test	postgres	new_tablespace	t	f	f	f

Foreign data wrappers allow bulk import of schemas with everything in them, privilege system allows bulk grant/revoke for entire schemas and everything in them, drop can cascade to everything in a schema. By extension, it's sort of reasonable to expect a bulk tablespace switch operation - unfortunately, there isn't one.