Unificando bases de dados com Schemas

Aqui explico como organizar diversos bancos de dados PostgreSQL utilizando o conceito de Schemas. A idéia é centralizar varias bases de sistemas distintos em um único banco, centralizando todo o seu gerenciamento. Neste processo iremos:

• Criar um novo banco de dados destinado a ser o “Banco de Dados Central”;
• Criar tablespaces para tabelas e índices e fazer com que todos os índices e tabelas de todos os sistemas armazenem seus dados nas suas respectivas tablespaces;
• Centralizar toda a codificação de caracteres para todos os sistemas em UTF8;
• Utilizar apenas um usuário administrador, o postgres para todos os sistemas;
• Utilizar um usuário e um schema com o mesmo nome para cada sistema a ser utilizado pelo desenvolvedor para criar os objetos do sistema;
• Utilizar um ou mais usuários para a aplicação com permissões restritas em cada objeto a ser utilizado;

Para isto, utilizaremos o PostgreSQL 8.1 rodando em Linux. No entanto, a maioria das coisas aqui descritas se aplicam a outros sistemas operacionais no padrão POSIX (linux, BSDs, Solaris, etc) e versões 8.x do PostgreSQL.

Parte I – Preparação

1. Criar tablespaces:

Antes mesmo de criar nosso banco de dados centralizado, criaremos os tablespaces a serem utilizados. Mesmo que você não possua vários discos ou arrays distintos para armazenar os seus dados, é importante já começar a preparar os seus sistemas para isto. Conforme eles crescem você deverá precisar de mais storage. Separar os logs, tabelas e índices e unidades físicas distintas costuma ser a providência número um para aumentar a performance. É claro que sistemas maiores podem utilizar tablespaces separados para cada sistema ou para tabelas e índices com dados históricos (utilizando o particionamento de tabelas) para aumentar ainda mais o desempenho. Como nosso tópico principal aqui não é o tunning, não nos aprofundaremos no assunto aqui.

Criaremos as seguintes tablespaces básicas:
– Uma tablespace para tabelas chamada tbs_tables
– Uma tablespace para índices chamada tbs_indexes

Antes de criar sua tablespace, você deve entender que o PostgreSQL 8.x trata cada tablespace como um diretório no seu sistema de arquivos. Portanto é preciso criar os diretórios no seu sistema operacional antes de criar os tablespaces. Também é preciso garantir a permissão de leitura e gravação nas pastas recém criadas. O local onde você irá criar seus tablespaces depende da forma como você deseja organizar sua estrutura de diretórios. Aqui, criaremos uma pasta específica para isso, na raiz. Na sua linha de comando digite:

$ su postgres
$ cd /
$ mkdir postgresql
$ cd postgresql
$ mkdir tablespaces
$ cd tablespaces
$ mkdir tables
$ mkdir indexes

Feito isto, você pode se conectar ao psql e criar os tablespaces:

$ psql

 CREATE TABLESPACE tbs_tables OWNER postgres LOCATION '/postgresql/tablespaces/tables';
 CREATE TABLESPACE tbs_indexes OWNER postgres LOCATION '/postgresql/tablespaces/indexes';

2. Criar o banco de dados central:

Feito isto, criaremos o banco de dados que servirá para para centralizar todos os seus bancos de dados. Para isto utilizarei a opção ‘-D‘ para definir um tablespace padrão a ser utilizado em todos os objetos que não especificarem um tablespace padrão. Outra opção importante é o ‘-E‘ onde especifico o tipo de codificação de caracteres.

$ createdb -D tbs_tables -E utf-8 nome_do_banco

3. Criar role padrão para desenvolvedores:

O próximo passo é criar um role que será utilizado por todos os usuários que poderão criar objetos no banco. Note que este role não será utilizado para se logar no banco e sim para que novos usuários herdem suas permissões. Concederemos permissão para:
– Criar objetos no banco de dados recém criado;
– Criar objetos nos tablespaces tbs_tables e tbs_indexes.

 CREATE ROLE developer WITH NOLOGIN;
 GRANT CREATE ON DATABASE nome_do_banco TO developer;
 GRANT CREATE ON TABLESPACE tbs_tables TO developer;
 GRANT CREATE ON TABLESPACE tbs_indexes TO developer;

Parte II – Unificando os bancos de dados

 

4. Fazer DUMP do banco de dados a ser migrado

A primeira coisa que você deve fazer agora é exportar cada banco de dados individual. A melhor forma de fazer isto é utilizando o pg_dump através da linha de comando. Você pode fazer alguns testes iniciais sem carregar a estrutura dos dados para ver se está tudo ok. Para isto, utilize a opção ‘-s‘. Depois você pode exportar apenas os dados utilizando a opção ‘-a‘.

$ pg_dump --use-set-session-authorization -E utf-8 -h ip_do_banco -U postgres nome_do_banco_a_ser_migrado > nome_do_banco_a_ser_migrado.sql

a opção ‘–use-set-session-authorization’ é opcional. Ela faz com que seja utilizada o comando SQL ‘SET SESSION AUTHORIZATION‘ ao invés do ‘ALTER … OWNER‘ para cada objeto. A opção ‘-E‘ força a exportação ser realizada na codificação de caracteres desejada. Desta forma não é preciso utilizar o iconv ou mudar o ‘client enconding‘ para importar os dados na codificação correta.

5. Editar o dump:

Agora vem a parte mais importante do processo de migração, editar o dump e corrigir algumas coisas:

• Alterar o tablespace para as tabelas e índices com o comando SQL ‘SET default tablespace‘;

• Criar o usuário que será dono do schema e todos os objetos dentro dele, a ser utilizado pelo desenvolvedor. Colocamos também um limite de conexões para este usuário para impedir que o desenvolvedor tente utilizar este usuário como usuário da aplicação;

• Criar um ou mais usuários a serem utilizados pela aplicação;

• Criar o schema e defini-lo como padrão para que todos os objetos subsequentes sejam criados dentro dele, através da instrução ‘SET search path‘;

• Criar as permissões para os usuários das aplicações concedendo apenas os privilégios realmente necessários para eles.

Alguns cuidados:

• Utilize um editor em modo texto, principalmente se o seu dump for muito grande. Isto irá facilitar o seu trabalho de edição, pois abrir arquivos grandes em modo gráfico pode acabar com a memória do seu computador mais rápido do que em modo texto. Outra alternativa é criar um dump separado para a definição de dados DDL e outro para os dados em DML. A maior parte das alterações dizem respeito apenas a DDL, que são arquivos bem menores.

• Verifique se existe uma linha igual a esta no início do arquivo:
  SET client_encoding = ‘utf-8’;
  ela é importante para garantir que o psql vá utilizar a codificação correta durante a importação. Caso você esteja utilizando outra codificação, você poderá ter problemas durante a importação. Mude a codificação de caractere para UTF8 ou mude o ‘client-encoding‘ para aquele que o arquivo do dump está utilizando.

• Algumas linhas como o ‘SET default tablespace‘ e ‘SET search path‘ já vem no dump padrão realizado pelo pg_dump. Ao invés de reescrever estas linhas, apenas altere a já existente. O risco não fazê-lo é criar um parâmetro que será sobrescrito pré-existente no dump pouco após da linha que você acrescentou.

• Algumas funções em PL são criadas dependendo da versão do PostgreSQL que você está utilizando. Estas funções costumam ser criadas ao se criar o banco de dados ou ao se implementar alguma funcionalidade do diretório contrib do PostgreSQL. Geralmente, estas funções são comuns a todos os sistemas e não precisam ser incluídas novamente. Costuma ser seguro remover estas linhas do dump. Caso ocorra algum problema durante a homologação, você poderá criar um novo dump e recuperar as funções da base de dados antiga.

5.1 Criando usuários:

CREATE ROLE sistema_a WITH LOGIN PASSWORD '123456' INHERIT IN ROLE developer CONNECTION LIMIT 2;
CREATE ROLE sistema_a_client WITH LOGIN PASSWORD '123456';
SET SESSION AUTHORIZATION sistema_a;

Note aqui que o ‘INHERIT IN ROLE developer‘ faz com que o usuário faça parte da role developer e ainda herde as permissões dele. A linha ‘SET SESSION AUTHORIZAION‘ faz com que os objetos subsequentes sejam todos criados com o usuário citado como owner.

5.2 Criando o schema:

Agora criaremos o schema com o mesmo nome do usuário do desenvolvedor. É importante que o usuário tenha o mesmo nome do schema, pois no arquivo postgresql.conf o ‘search_path’ inclui a variável ‘$user‘ por default. Isto significa que o nome do usuário que se conectar procurará automaticamente objetos neste schema sem precisar qualificar seu nome ou utilizar o ‘SET search path‘.

CREATE SCHEMA sistama_a AUTHORIZATION sistema_a;

5.3 Definido os tablespaces

Para definir o tablespace, você deve procurar dois pontos importantes no seu dump: o ponto imediatamente anterior antes de criar as tabelas e o ponto imediatamente anterior a criação dos índices e constraints.

Antes da criação das tabelas coloque a seguinte linha:

SET default_tablespace = 'tbs_tables';

Antes da criação de índices e constraints, coloque a seguinte linha:

SET default_tablespace = 'tbs_indexes';

5.4 Concedendo privilégios aos usuários da aplicação

No final do dump, o pg_dump coloca as permissões inerentes aos objetos criados para cada usuário. Esta parte do trabalho não tem como ser automatizada. É interessante manter o REVOKE para o usuário PUBLIC de forma a zerar as permissões para todos os usuários antes de concedê-las novamente. Evite a todo o custo conceder privilégios do tipo ALL, a fim de não conceder mais privilégio do que o estritamente necessário para cada usuário. Apesar de ser uma tarefa tediosa, esta é uma tarefa importante no trabalho de qualquer bom DBA. Privilégios do tipo CREATE, TEMP, DELETE, RULE, REFERENCES, TRIGGER que só devem existir em usuários de sistema em casos específicos.

6. Importação

O último passo é importar cada dump devidamente alterado no passo anterior para o banco de dados central.

$ psql -h ip_do_banco -U postgres nome_do_banco < nome_do_banco_a_ser_migrado.sql

7. Testes

Por fim, deve-se testar a aplicação para que ver se tudo está funcionando adequadamente com o usuário do sistema e senha nova. Pode ser necessário qualificar o nome dos esquemas para acessar os objetos no local correto. Uma alternativa mais simples é utilizar a instrução ‘SET search path‘ logo após a conexão com o banco de dados. Uma boa idéia é utilizar também a instrução ‘SET client enconding‘ utilizando a codificação da sua aplicação. Como o UTF8 tem a capacidade de ser convertido para a maior parte dos tipos de codificação, ele é ideal para ser utilizado no servidor, enquanto no cliente você pode escolher o tipo de codificação mais adequado para a sua aplicação.

Lembre-se de testar cuidadosamente sua aplicação antes de libera-la para a produção. Um servidor de testes é fundamental para este processo.

Referências:

• Schemas: http://www.postgresql.org/docs/8.1/interactive/ddl-schemas.html
• psql: http://www.postgresql.org/docs/8.1/interactive/app-psql.html
• pg_dump: http://www.postgresql.org/docs/8.1/interactive/app-pgdump.html
• CREATE DATABASE: http://www.postgresql.org/docs/8.1/interactive/sql-createdatabase.html
• CREATE TABLESPACE: http://www.postgresql.org/docs/8.1/interactive/sql-createtablespace.html
• CREATE SCHEMA: http://www.postgresql.org/docs/8.1/interactive/sql-createschema.html
• CREATE ROLE: http://www.postgresql.org/docs/8.1/interactive/sql-createrole.html
• GRANT: http://www.postgresql.org/docs/8.1/interactive/sql-grant.html
• REVOKE: http://www.postgresql.org/docs/8.1/interactive/sql-revoke.html
• SET: http://www.postgresql.org/docs/8.1/interactive/sql-set.html
• SET AUTHORIZATION SESSION: http://www.postgresql.org/docs/8.1/interactive/sql-set-session-authorization.html