Conceito de Schema no PostgreSQL

Muitas vezes sentimos a necessidade de usar o conceito de cross-database, isto é, criar consultas SQL cruzando informações entre campos de tabelas que estão em Banco de Dados distintos. Exemplo, a tabela pessoa tem o campo id do banco database1, vamos cruzar informações com a tabela pessoa com o campo id do banco database2 gerando uma consulta SQL como esta:

SELECT db1.id, db2.id
FROM database1.pessoa db1, Database2.pessoa db2

Logicamente, esta é uma query ilustrativa, mas serve como exemplo para demonstrar o que é um Cross-Database. Fazendo uma referência como esta, é possível acessar diversos objetos do banco, por exemplo, db2 (tables, views, indexes, functions etc.).

Apenas com a query ilustrativa podemos imaginar o quanto isso é útil. Nossas aplicações agora podem ser divididas em diversos bancos por diversos motivos: Segurança e organização são apenas dois exemplos.

A partir da versão 7.3, o PostgreSQL iniciou o suporte a Schema, no qual é possível criar um espaço lógico (namespace) dentro do banco de dados para armazenar os objetos: Dados, tabelas, funções, sequence, etc.

Este conceito – SCHEMA - é semelhante ao cross-database, a diferença é que o cross-database relaciona objetos (Tabelas, índices, sequence, etc) de banco de dados distintos, já o Schema relaciona objetos (Tabelas, índices, sequence, etc) que estão no MESMO BANCO DE DADOS, mas em estruturas lógicas (namespace) distintas – schema.

Exemplo

Vejamos uma demonstração básica do Esquema, dentro do banco teste:

teste_carlos=# CREATE SCHEMA sch_sqlmagazine1;
CREATE SCHEMA
 teste_carlos=# CREATE TABLE sch_sqlmagazine1.info (id INT, txt TEXT);
CREATE TABLE
teste_carlos=# INSERT INTO sch_sqlmagazine1.info VALUES(1, 'Este é o Schema sch_sqlmagazine1');
INSERT 149312822 1
teste_carlos=# CREATE SCHEMA sch_sqlmagazine2;
CREATE SCHEMA
teste_carlos=# CREATE TABLE sch_sqlmagazine2.info (id INT, txt TEXT);
CREATE TABLE
teste_carlos=# INSERT INTO sch_sqlmagazine2.info VALUES(1, 'Este é o Schema sch_sqlmagazine2');
INSERT 149312829 1
 teste_carlos=# SELECT sch_sqlmagazine1.info.txt,
teste_carlos=# sch_sqlmagazine2.info.txt
teste_carlos-# FROM sch_sqlmagazine1.info, sch_sqlmagazine2.info
teste_carlos-# WHERE 
teste_carlos-# sch_sqlmagazine1.info.id=sch_sqlmagazine2.info.id;

SELECT sch_sqlmagazine1.info.txt
 Sch_sqlmagazine1 = Nossa Schema 
Info = Tabela
Txt = Coluna.

Logo, estou buscando os dados da coluna txt que está na tabela info do Schema sch_sqlmagazine1.

O Schema Público

Quando criamos um novo Banco de Dados, o schema “public” é criado por padrão e qualquer objeto que criarmos neste banco, por padrão será criado no schema public:

teste_carlos=# create table tabela_exemplo(id int4);
 teste_carlos=# \dt

Uso Prático do Schema

Como foi comentado, qualquer objeto criado sem especificar um SCHEMA é criado por padrão no public. No entanto, é possível definirmos um OUTRO schema como padrão através de uma variável interna do PostgreSQL chamado search_path. Esta variável informa ao banco PostgreSQL qual é o Usuário Padrão e o Schema padrão para manipular os objetos.

teste_carlos=# SHOW search_path;
 search_path 
 --------------
 $user,public
 (1 row)

Nestas configurações-padrão, o $user é o nome do usuário corrente, ou seja, o usuário no qual logamos no Banco de Dados:

Podemos mudar o Schema padrão alterando nosso Search_Path:

teste_carlos=# SET search_path TO sch_sqlmagazine1;
SET
teste_carlos=# SHOW search_path;
  search_path 
------------------
sch_sqlmagazine1
(1 row)
 teste_carlos=# SET search_path TO sch_sqlmagazine1;
teste_carlos=# \dv
No relations found.
teste_carlos=# \dt

Veja como alterar o Schema padrão de forma permanente:

ALTER Database teste_carlos SET search_path TO $user,sch_sqlmagazine1;
teste=# SET search_path TO sch_sqlmagazine2, sch_sqlmagazine1;
SET

Como o Schema sch_sqlmagazine1 é agora o padrão:

teste_carlos=# SELECT txt FROM info;
                          Txt
----------------------------------
Este é o Schema sch_sqlmagazine1
(1 row)
 teste_carlos-# \dt

Permissão e Segurança

Um schema só pode ser criado por usuários com permissão para isso (geralmente o usuário ”postgres”).

Para criar um Schema no qual queremos que OUTRO usuário (que não seja o usuário “postgres”) acesse o Schema (Tarzan), fazemos o seguinte:

CREATE SCHEMA tarzan AUTHORIZATION tarzan;

ou

CREATE SCHEMA AUTHORIZATION tarzan;

No caso, criamos o schema tarzan que será acessado pelo usuário tarzan.

Schemas Especiais

Todo Banco de Dados PostgreSQL contém schemas especiais que são necessários para o Backend (processo postgreSQL que estabelece a conexão com o cliente) por isso, eles não podem ser removidos ou renomeados.

Os Schemas especiais:

• pg_catalog: Contém informações sobre as tabelas, funções, views e metainformações sobre o banco;
• pg_temp_x: É um schema usado na criação e armazenamento de tabelas temporárias;
• pg_toast: Relações internas do PostgreSQL usadas para várias atividades, como a reindexão (vacuum), por exemplo.

Limitações

Não é possível transferir objetos entre Schemas. Para solucionar isso, precisamos contornar o problema usando SQL. Por exemplo: com a query a seguir, iremos criar um novo schema com uma nova tabela e preencher esta tabela com dados do schema antigo:

CREATE TABLE new_schema.tabela AS SELECT * FROM old_schema.tabela

Na versão 7.4 é possível renomear um schema através do seguinte comando:

ALTER SCHEMA .. RENAME TO ...

Abraços e até logo.