Movendo objetos no Postgres

Quando você cria um objeto seja ele uma tabela, visão, função, etc, a não ser que você especifique o contrário, ele vai ser criado no esquema ‘public’, no tablespace ‘pg_default’ e o dono do objeto será o ‘postgres’. Dependendo da aplicação, não há nenhum problema nisso. Sério, existem aplicações que funcionam muito bem com este arranjo padrão. Mas você pode querer organizar melhor os seus objetos em diferentes esquemas, pode querer melhorar a segurança e criar donos diferentes para os objetos e pode também querer armazenar seus objetos em discos ou partições diferentes.

Note no padrão ISO esquema e dono de objeto se confundem. Isto pode ou não ocorrer no PostgreSQL dependendo de como você organizar as coisas. Note que por padrão, a variável ‘search_path’ sempre aponta para ‘$user’, public’. Ou seja, se você criar um esquema com o nome de um usuário, os objetos vão ser sempre procurados neste esquema em primeiro lugar. Se não achar lá, vai procurar no esquema public.

Bom, por algum motivo você resolveu mudar as coisas… você pode se surpreender como é simples mudar um único objeto de lugar. Imagine uma tabela ‘foo’, uma sequência ‘foo_seq’ e uma visão ‘foo_vw’ que vão ser migrados para o esquema ‘bar’, usuário ‘bar’ e tablespace ‘novo_tablespace’:

• Para mudar um objeto de esquema, basta utilizar o SET SCHEMA:

  ALTER TABLE foo SET SCHEMA bar;
  ALTER SEQUENCE foo_set SET SCHEMA bar;
  ALTER VIEW foo_vw SET SCHEMA bar;

• Para mudar o dono de um objeto, basta utilizar o OWNER TO

  ALTER TABLE foo OWNER TO bar;
  ALTER SEQUENCE foo_seq OWNER TO bar;
  ALTER VIEW foo_vw OWNER TO bar;

• Para mudar o tablespace de um objeto (no caso só se aplica para tabelas e índices, pois os demais objetos são armazenados apenas no catálogo):

  ALTER TABLE foo SET TABLESPACE novo_tablespace;
  ALTER INDEX foo_idx SET TABLESPACE novo_tablespace;

Automatizando

Claro que se você quiser migrar um conjunto enorme de objetos, você vai querer automatizar um pouco este trabalho. Aqui vou demonstrar apenas um exemplo de migração de tabelas, sequências, visões e funções para um novo esquema. Você pode migrar outros objetos como tipos, domínios, agregações, extensões, tabelas externas, operadores e outros bichos avançados do Postgres. Aqui eu coloquei apenas os objetos mais utilizados para não complicar:

-- Script para mover tabelas, sequências, visões e funções do esquema 'PUBLIC' para o esquema 'bar'

-- Cria o esquema novo
CREATE SCHEMA AUTHORIZATION bar;

-- Os comandos t e o são interpretados apenas pelo psql.
-- São utilizados para exportar o resultado das consultas abaixo
t
o move.sql
SELECT 'ALTER TABLE ' || table_schema || '.' || table_name || ' SET SCHEMA bar;'
    FROM information_schema.tables
    WHERE table_schema = 'public'
    ORDER BY table_name;

SELECT 'ALTER SEQUENCE ' || sequence_schema || '.' || sequence_name || ' SET SCHEMA bar;'
    FROM information_schema.sequences
    WHERE sequence_schema = 'public'
    ORDER BY sequence_name;

SELECT 'ALTER VIEW ' || table_schema || '.' || table_name || ' SET SCHEMA bar;'
    FROM information_schema.views
    WHERE table_schema = 'public'
    ORDER BY table_name;

SELECT 'ALTER FUNCTION ' || n.nspname || '.' || p.proname || ' (' || pg_catalog.pg_get_function_arguments(p.oid) || ') SET SCHEMA bar;'
    FROM
                  pg_catalog.pg_proc p
        LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
    WHERE n.nspname = 'public'
    ORDER BY p.proname;

o
t
-- O comando i é interpretado pelo psql apenas.
-- Ele é utilizado para executar o script indicado.
i move.sql

Navegando pelo psql

Os usuários do pgAdmin III muitas vezes ficam perdidos ao utilizarem o psql. Na verdade é mais fácil e rápido do que parece. Utilize os comandos dn para ver os esquemas existentes na sua base, o dp para ver as permissões nos objetos e o db para ver os tablespaces.

Cuidado ao mover objetos grandes para um novo tablespace

Uma observação importante é que ao alterar o esquema e o dono de um objeto, apenas o registro no catálogo do sistema é alterado (mais especificamente na pg_catalog.pg_class). Então esta alteração é bem rápida. Para alterar o tablespace, além de alterar o catálogo, o postgres ainda tem de copiar o objeto fisicamente para o novo tablespace. Então, um objeto grande pode levar um bom tempo para mover.