Administração de um cluster de bases de dados PostgreSQL

Publicado: 21/02/2014 em Programação, Serviços IP
Tags:

Para fazer um bom acompanhamento deste post é recomendável ter um conhecimento geral dos comandos SQL. Um bom guia para isto pode ser o próprio manual do PostgresSQL na sua seção SQL Commands Supported by PostgreSQL.

1. Funções de informação do sistema
O PostgreSQL possui algumas funções de informação do sistema que destinam-se a extrair informações da sessão e do sistema. Exemplos de comandos:

psql=# SELECT version();
psql=# SELECT current_user;
psql=# SELECT current_schema;

2. PostgreSQL Roles Management
O PostgreSQL utiliza o conceito de roles para gerenciar as permissões de acesso ao banco de dados. Um role pode ser um usuário ou um grupo de usuários, dependendo de como se configura. Um role que tem permissão de fazer login é chamado de usuário. Usuários e grupos de usuários são compartilhados por todo “PostgreSQL database cluster”, porém nenhum outro dado é compartilhado entre bancos de dados.

CREATE ROLE adiciona um novo role para o cluster de base de dados do PostgreSQL. Note então que roles são definidos no nível do “database cluster”, sendo válidos para todas as bases de dados do cluster PostgreSQL.

2.1 Login “Roles” (usuários)
No PostgreSQL existe apenas uma espécie de conta: “role”. Alguns roles podem fazer login e são chamadas de “users”.
Exemplos de comandos:
psql=# CREATE ROLE maria LOGIN PASSWORD 'secret' CREATEDB VALID UNTIL 'infinity';
psql=# CREATE ROLE jose LOGIN PASSWORD 'secret' SUPERUSER VALID UNTIL '2014-08-25 18:00';
psql=# ALTER ROLE maria WITH CREATEDB CREATEUSER;
psql=# ALTER ROLE maria WITH CREATEROLE;
psql=# ALTER ROLE aline WITH PASSWORD 'secret';
psql=# DROP ROLE aline;

Veja o manual do PostgreSQL para os comandos SQL CREATE ROLE e ALTER ROLE.

2.2 Group “Roles”
“Roles” podem ser membros de outros roles, onde estes formam um “grupo”. “Group roles” geralmente são roles que não têm atribuições de login e tendo outros roles como membros.
Exemplos de comandos:
psql=# CREATE ROLE group_role INHERIT;
psql=# GRANT group_role TO maria;

3. Tablespaces
Tablespaces no PostgreSQL são diretórios no sistema de arquivos utilizados para armazenamento físico dos dados e objetos (como tabelas e índices) de um banco de dados. Os tablespace divide um banco de dados fisicamente. Apenas super usuários têm permissão de gerenciar tablespaces. Os tablespaces são utilizados para:

  • Gerenciamento de espaço de armazenamento de dados – pode-se armazenar bancos grandes em mídia com tamanho apropriado que esteja disponível na rede.
  • Melhor desempenho – objetos que sofrem mais acessos podem ser alocados em discos mais rápidos, por exemplo, com ganho substancial de desempenho.

Um tablespace pode armazenar dados de diversos bancos de dados, desde que se tenha permissão para acesso aos mesmos. Um banco de dados também pode dividir seus objetos e dados entre vários tablespaces. O nome do tablespace não deve começar com pg_, porque estes nomes são revertidos para o tablespaces do sistema.

Por default, o usuário que executar a chamada SQL CREATE TABLESPACE é o proprietário do tablespace. CREATE TABLESPACE registra um novo tablespace no cluster PostgreSQL. Uma vez que o tablespace esteja criado, pode-se especificar comandos SQL CREATE DATABASE, CREATE TABLE e CREATE INDEX para armazenar arquivos de dados de objetos no tablespace.

Ao se instalar o PostgreSQL, são criados dois tablespaces:

  • PG_DEFAULT – por padrão, quando não é definido explicitamente um tablespace, os dados e objetos de todos os usuários são armazenados no tablespace PG_DEFAULT.
  • PG_GLOBAL – armazena objetos globais, visíveis a todos os bancos

Exemplos de comandos:
shell# mkdir /home/jose/tspace
shell# chown postgres:postgres /home/jose/tspace
psql=# CREATE TABLESPACE dbspace OWNER maria LOCATION '/home/jose/tspace';
psql=# ALTER TABLESPACE dbspace RENAME TO db_data;
psql=# ALTER TABLESPACE db_data OWNER TO jose;
psql=# DROP TABLESPACE db_data;

O diretório deve estar vazio e deve pertencer ao “PostgreSQL system user”. O diretório deve ser especificado através de um caminho absoluto.
Maneiras de visualizar os tablespaces:
psql=# \db
psql=# select * from pg_tablespace;

4. Organizar cada base de dados usando “Esquemas”
Esquemas são uma maneira lógica de particionar uma base de dados em mini-containers. Pode-se dividir os esquemas por funcionalidade, por usuários, ou por qualquer outro atributo. Um banco pode ter dezenas de objetos (tabelas, visões, índices …). Por meio de esquemas é possível organizá-los, atribuindo cada objeto a um esquema. Os esquemas ajudam o gerenciamento de acessos: é possível liberar acesso de usuários a todo um esquema de uma única vez.

Aspectos relevantes sobre os esquemas:

  • cada novo banco de dados criado conterá sempre, por padrão, um esquema chamado “public”;
  • uma base de dados pode conter um ou mais esquemas, que por sua vez conterão as tabelas;
  • um objeto pode ser usado sem conflito em diferentes esquemas. Por exemplo, tanto Esquema1 e Esquema2 poderão conter uma tabela chamada MinhaTabela;
  • para criar ou acessar objetos em um esquema, deve-se escrever um nome qualificado que consiste no nome do esquema e o nome da tabela separados por um ponto: schema.table

CREATE SCHEMA cria em um novo esquema no banco de dados atual. O nome do esquema deve ser distinto do nome de qualquer esquema existente no banco de dados atual.

4.1 Parâmetro de configuração “search_path”
Nomes qualificados são tediosos ​​de escrever, e muitas vezes é melhor não utilizar o nome do esquema no código das aplicações. Portanto tabelas são muitas vezes referidos por nomes não qualificados, que consistem apenas no nome da tabela. O PostgreSQL determina qual tabela está sendo referenciada seguindo um caminho de busca (dado pelo parâmetro de configuração do PostgreSQL “search_path“), que consiste de uma lista de esquemas a procurar. A primeira tabela encontrada no caminho de procura é considerada como sendo a desejada. Se não houver nenhuma correspondência no caminho de procura, um erro é relatado, mesmo que os nomes das tabelas procuradas existirem em outros esquemas no banco de dados.

O primeiro esquema no caminho de procura é chamado de esquema corrente. Além de ser o primeiro esquema a ser pesquisado, também é o esquema em que as novas tabelas serão criadas se o comando CREATE TABLE não especificar um nome de esquema. Este caminho de busca é determinado por um parâmetro de configuração do PostgreSQL chamado search_path. Assim, para visualizar o caminho de busca corrente, use o seguinte comando:
psql=# SHOW search_path;

Quando se cria uma base de dados, o parâmetro “search_path” default é:

search_path="$user",public

4.2 Configuração de sessão – psqlrc
Para automatizar a configuração do ambiente de trabalho do psql deve-se utilizar o arquivo psqlrc (que deve estar na área do sistema indicada por PGSYSCONFDIR) e/ou o arquivo .psqlrc (que deve estar na área raiz do usuário que lançará o psql). Quando o psql inicia, ele lê os comandos SQL de psqlrc e depois do $(HOME)/.psqlrc.

Estes arquivos são utilizados toda vez que o psql for lançado pelo usuário. Será uma configuração válida enquanto existir a sessão de utilização do psql pelo usuário. Ou seja, estes arquivos são válidos a nível de sessão do psql, e não a nível de base de dados ou qualquer outro objeto.

a) Arquivo psqlrc do sistema
No caso do Debian, o arquivo psqlrc deve ser colocado na pasta /etc/postgresql-common/, atentando-se para:

  • caso o usuário lance o psql de sua máquina local, o arquivo psqlrc deverá estar na citada pasta de sua própria máquina local;
  • caso o usuário lance o psql a partir da máquina servidora onde está instalado o PostgreSQL, o arquivo psqlrc deverá estar nesta máquina servidora.

b) Arquivo .psqlrc da área do usuário
Este arquivo deve existir na pasta $HOME do usuário que está lançando o psql.

c) Exemplo de listagem do arquivo para configuração de sessão do psql

-----------------------------------------
-- psqlrc file to set psql preferences --
-- Author : Albuquerque                --
-----------------------------------------
\pset border 2
\echo '\nCurrent Host Server Date Time : ' `date +%x/--/%X` '\n'
\set PROMPT1 '%n@%M:%>%x %/# '
\l
\c teste
SET search_path TO esquema_ti,public

4.3 Exemplos
psql=# CREATE SCHEMA esquema_rh AUTHORIZATION jose;
psql=# ALTER SCHEMA esquema_rh RENAME TO esquema_ti;
psql=# ALTER SCHEMA esquema_ti OWNER TO maria;
psql=# \dn
psql=# \dn+
psql=# \dtvs esquema_ti.*
psql=# SHOW search_path;
psql=# SET search_path TO esquema_ti, public;
psql=# SHOW search_path;
psql=# SELECT current_schema;
psql=# DROP SCHEMA esquema_ti [CASCADE]

5. Tabelas
Por padrão, quando se cria uma tabela sem especificar qualquer nome de esquema, ela é automaticamente colocada em um esquema chamado “public”. Se um nome de esquema for fornecido, então a tabela é criada no esquema especificado. Assim, os dois comandos a seguir são equivalentes:
CREATE TABLE produtos ( ... );
e
CREATE TABLE public.produtos ( ... );

CREATE TABLE irá criar uma nova tabela, inicialmente vazia, no banco de dados atual. A tabela será de propriedade do usuário que emite o comando.

Exemplos de comandos:
psql=# CREATE TABLE esquema_ti.tabela1 (ID INT NOT NULL, NOME VARCHAR(30) NOT NULL, CPF VARCHAR(12) NOT NULL, PRIMARY KEY(ID), UNIQUE (CPF));
psql=# INSERT INTO esquema_ti.tabela1 (ID, NOME, CPF) VALUES (10, 'Justina Silva', '11122233344');
psql=# \d esquema_ti.tabela1;
psql=# SELECT * FROM esquema_ti.tabela1;
psql=# SELECT * FROM tabela1;
psql=# SET search_path TO esquema_ti, public;
psql=# SHOW search_path;
psql=# SELECT * FROM tabela1;
psql=# ALTER TABLE esquema_ti.tabela1 RENAME TO tabela2;
psql=# TRUNCATE TABLE esquema_ti.tabela2;
psql=# DROP TABLE esquema_ti.tabela2 [CASCADE];

Veja também:
1- Tutorial PostgreSQL ( instalar em ambiente Linux – Debian )
2- Documentação do PostgreSQL 8.0.0 (em português)
3- Instalar PostGIS no Linux Debian

Anúncios

Deixe um comentário, pois isto é muito motivante para continuarmos este trabalho

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s