Skip to content

Instantly share code, notes, and snippets.

@NikolayS
Last active October 1, 2018 00:23
Show Gist options
  • Save NikolayS/cb028d6c49e6f9e3ea8e1944452d9ff5 to your computer and use it in GitHub Desktop.
Save NikolayS/cb028d6c49e6f9e3ea8e1944452d9ff5 to your computer and use it in GitHub Desktop.
PostgreSQL: move all objects from one tablespace to default one
-- The following code will generate a set of ALTER queries
-- to move tables and indexes from one tablespace to another.
-- WARNING: Running such ALTERs in production means huge stress for DB.
-- Use pg_repack with "-s" option (http://reorg.github.io/pg_repack/)
-- TODO: generate pg_repack call, smth like:
-- pg_repack [connection creds] -s TABLESPACENAME \
-- -t tbl1 \
-- ...
-- -i idx1 \
-- ...
set postgres_dba.tablespace_from to 'hdd';
set postgres_dba.tablespace_to to 'pg_default';
do $$
declare
rec record;
sql text;
begin
sql := '';
for rec in select * from pg_indexes where tablespace = current_setting('postgres_dba.tablespace_from') loop
sql := sql || format(
e'\nalter index %I.%I set tablespace %I;',
rec.schemaname,
rec.indexname,
current_setting('postgres_dba.tablespace_to')
);
end loop;
for rec in select * from pg_tables where tablespace = current_setting('postgres_dba.tablespace_from') loop
sql := sql || format(
e'\nalter table %I.%I set tablespace %I;',
rec.schemaname,
rec.tablename,
current_setting('postgres_dba.tablespace_to')
);
end loop;
raise info '%', sql;
end;
$$ language plpgsql;
reset postgres_dba.tablespace_from;
reset postgres_dba.tablespace_to;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment