PostgreSQL. Bruno Hideo Casillo

PostgreSQL

Bruno Hideo Casillo

Objetivos do Curso

„ Arquitetura do PostgreSQL

„ Configuração de ambiente

„ Gerenciamento de banco de dados

„ Autenticação de clientes

„ Administração de segurança lógica e física

„ Manutenção do banco de dados

„ Write Ahead Log

„ Monitorando o PostgreSQL

„ Instalação e administração do software

Arquitetura do PostgreSQL

Introdução

„ O servidor PostgreSQL pode manipular múltiplas conexões simultâneas de

clientes .Para cada conexão, um novo

processo servidor é iniciado para tratar

as requisições sem a interferência do

processo postmaster original.

Introdução

„ O processo postmaster está sempre rodando e esperando novas conexões de clientes, enquanto os processos

clientes e seus processos servidores associados são finalizados após o

encerramento da execução.

Conexões ao PostgreSQL

Arquitetura do PostgreSQL

„ A biblioteca LIBPQ é responsável por

tratar da comunicação entre o processo cliente e o postmaster. Ele passa

adiante, ao processo postmaster

dedicado, os comandos recebidos do

processo cliente.

Arquitetura do PostgreSQL

„ O lado servidor é composto por dois processos: o postmaster e o processo postgres dedicado. O postmaster é

responsável por realizar a autenticação das novas conexões e iniciar um processo

postgres dedicado ‘a nova conexão. O processo postgres trata as queries e

comandos enviados pelos clientes. Existe um

processo postgres para cada cliente.

Configuração de Ambiente

Codificação

„ O PostgreSQL é um servidor de banco de dados que suporta vários character set, permitindo uma internacionalização simples de aplicações.

„ Num mesmo servidor é possível ter

vários bancos de dados com diferentes

character sets. O character set padrão é

o SQL_ASCII

Definindo um character set

„ Pode ser feito na configuração do initdb

# initdb –E LATIN1

„ Na criação do banco

# createdb –E LATIN1 banco1

Exemplos de character set

„ SQL_ASCII

„ UNICODE

„ LATIN1

„ ISO_8859_5

„ EUC_JP

„ EUC_CN

Variáveis de ambiente

„ PGDATA

mostra o diretório onde estão os dados

„ PGUSER

„ PGPASSWORD

mantém o user e o password para facilitar a conexão com o banco

„ PGHOST

„ PGPORT

„ PGOPTIONS

O arquivo postgresql.conf

„ As configurações do servidor PostgreSQL estão definidas e armazenadas no arquivo

$PGDATA/postgresql.conf

Opções

„ AUTOCOMMIT

„ AUTHENTICATION_TIMEOUT

„ CLIENT ENCODING

„ DATESTYLE

„ DEADLOCK_TIMEOUT

„ DEFAULT_TRANSACTION_ISOLATION

„ FSYNC

„ MAX_CONNECTIONS

„ PASSWORD_ENCRYPTIONS

Opções

„ SEARCH_PATH

„ STATEMENT_TIMEOUT

„ SHARED_BUFFERS

„ SORT_MEM

„ SSL

„ SUPERUSER_RESERVED_CONNECTIONS

„ TCPIP_SOCKET

„ VACUUM_MEM

Gerenciamento do banco de

dados

Iniciando um cluster PostgreSQL

„ Antes de criar um banco de dados, os arquivos de sistemas devem ser

configurados no diretório $PGDATA

# initdb –D /postgres

Colocar o diretório /usr/local/pgsql/bin

no path para que os scripts funcionem em

qualquer diretório.

Iniciando o servidor PostgreSQL

„ posmaster –i –S –D /postgres

„ pg_ctl start –D /postgres

Criando um banco de dados

„ Para criar um banco de dados utilizando template0, use:

„ CREATE DATABASE dbname TEMPLATE template0;

„ do ambiente SQL, ou

„ createdb -T template0 dbname

„ pelo shell.

opções

„ -O especifica o usuário que será o proprietário do banco

„ -E especifica o conjunto de caracteres de codificação

„ -T especifica que o banco será utilizado

como template

Deletando um banco de dados

„ DROP DATABASE dbname

„ do ambiente SQL, ou

„ dropdb dbname

„ do shell.

Banco de dados template

„ É possível especificar qual banco será usado como template , caso não seja especificado , o banco template1 é utilizado

„ Se adicionarmos objetos ao banco template1, os bancos criados posteriormente serão

criados com estes objetos

„ Existe um segundo banco de dados padrão chamado template0. Não são permitidos

conexões neste banco e ele pode ser utilizado

para criação de banco de dados zerados

Catálogo de Dados

Tabelas de Sistema

„ O PostgreSQL utiliza intensamente o recurso de dicionário de dados, conhecida como

catalog, para armazenar e controlar as atividades sobre o banco de dados

„ As tabelas e views do dicionário de dados são

prefixadas com pg_ e em sua maior parte ,

são acessíveis a todos os usuários.

Principais Tabelas de Sistema

„ pg_attribute

„ Armazena as informações sobre as colunas das tabelas

„ Attrelid: oid da tabela que a coluna pertence

„ Attname: nome da coluna

„ Attypid: oid do tipo de dado da coluna

„ Attnum: posição da coluna na tabela

„ Attnotnull: valor true se a coluna for not null

„ Pg_class

„ Armazena definições de tabelas, índices, views e seqüências

„

Relname: nome do objeto

„

Relnamespace: nome do schema onde se encontra o objeto

„

Relowner: proprietário da tabela

„

Relpages: numero de blocos ocupados no disco pela tabela

„

Reltuples: numero de linhas da tabela

„

Relhasindex: true se a tabela possui índices

„

Relkind: tipo do objeto(r=tabela, i=indice, s= seqüência, v=view)

„

Relnatts: numero de colunas na tabela

„

Reltriggers: numero de triggers na tabela

„

Relacl: array com permissões de acesso

„ Pg_constraint

„ Armazena definições das restrições de tabelas e colunas

„

Conname: nome da constraint

„

Connamespace: nome do schema onde se encontra o objeto

„

Contype: tipo da constraint

„

Conrelid: oid da tabela que possui a constraint

„

Confupdtype: ação da foreign key no UPDATE

„

Confdeltype: ação da foreign key no DELETE

„

Consrc: código fonte da constraint check

„ Pg_database

„ Armazena as informações sobre todos os bancos de dados

„ Datname: nome do banco de dados

„ Encoding: código da codificação

„ Datpath: determina o local alternativo de armazenamento do banco

„ Datacl: lista de acesso ao banco

„ Pg_group

„ Armazena as informações sobre os grupos de usuários

„ Groname: nome do grupo

„ Grosysid: código do grupo

„ Grolist: array com os usuários que pertencem

ao grupo

„ Pg_shadow

„ Armazena as informações dos usuarios.

Existe uma view chamada pg_user sem informações de senha

„ Usename: nome do usuário

„ Usesysid: código do usuário

„ Usecreatedb: true se o usuário pode criar bancos

„ Usesuper: true se super-usuario

„ Passwd: senha do usuário

Autenticação de clientes

Pg_hba.conf

„ A autenticação de clientes é configurada no arquivo pg_hba.conf. Este arquivo se localiza no diretorio $PG_DATA

„ O formato geral do pg_hba.conf é um

conjunto de registros, um por linha, com as regras de acesso. Linhas em branco ou

iniciando com # são ignoradas

„ O primeiro registro que casa com os dados da

requisição é utilizado para a autenticação

Administração de Segurança

Lógica

Gerenciando Schemas

„ Um schema é um espaço onde podem ser criados objetos de banco de dados como tabelas, funções, etc.

„ Com o uso de schemas é possível ter mais de uma tabela ou objeto com o mesmo nome

„ Todos os bancos possuem um schema chamado public

„ Existem muitas razões para utilizar schemas

„ Possibilita que vários usuarios utilizem o mesmo banco sem interferência

„ Facilita a organização lógica dos objetos no banco

„ Aplicações podem ser colocadas em outros schemas sem

problemas de colisão de nomes com outros objetos

„ Create schema nome [AUTHORIZATION usuário];

„ Onde nome é o nome do schema

„ Onde usuário é o proprietário do schema

„ Ex: create schema dbo;

„ Create schema produção AUTHORIZATION

postgres;

Gerenciando schemas

„ Criando uma tabela dentro de um schema

„ Create table dbo.cadastro(codigo integer);

„ Para acessar um objeto dentro de um

schema, o usuário necessita ter privilégios de acesso no schema e no objeto. Também é

preciso mencionar o nome do schema antes do objeto

„ Select * from dbo.cadastro;

Gerenciando schemas

„ Para facilitar o acesso aos objetos sem a utilização do prefixo com o nome schema, podemos utilizar a variável SEARCH_PATH com uma lista de schemas

„ A variável SEARCH_PATH pode ser configurada no arquivo postgresql.conf

„ Quando um objeto é especificado sem qualificação , busca-se o objeto seguindo a ordem do

search_path

Removendo um Schema

„ Drop schema nome;

„ Drop schema nome cascade;

Gerenciando usuarios e permissões

„ Create user nome password ‘ senha ’;

„ Createuser [opções]…{ nome }

„ -a pode criar novos usuarios

„ -A não pode criar novos usuarios

„ -d pode criar novos bancos

„ -D Não pode criar novos bancos

„ -P pede senha

„ -E armazena a senha criptografada

„ -e mostra os comandos sql gerados

„ -q modo silencioso

Visualizando usuários

„ Os usuarios do sistema são

armazenados na tabela pg_shadow

„ Existe uma view chamada pg_user que é visível para todos os usuários

„ A tabela pg_shadow somente é visível para super-usuarios

„ Select * from pg_user;

„ Select * from pg_shadow;

Removendo usuários

„ Dropuser nome (shell)

„ Drop user nome (psql)

„ A única restrição é que o usuário não pode

ser removido se ele possuir algum banco

de dados. Neste caso, o banco de dados

deve ser removido primeiro

Grupos de Usuários

„ Utilizamos grupos para simplificar a atribuição de direitos de acesso a bancos de dados e tabelas.

„ Create group devel with user bruno, patrícia;

„ Alter group devel add user mariana;

„ Alter group devel drop user mariana;

„ Drop group devel;

Visualizando grupos

„ Select * from pg_group where

groname= ‘devel’ ;

Controle de Acesso

„ Existem vários tipos de privilégios que podem ser concedidos aos usuarios

„ Select

„ Insert

„ Update

„ Delete

„ References

„ All privileges

„ Etc…

„ Grant select on emp to bruno;

„ Grant select on emp to group devel;

„ Grant select on emp to public;

„ Existe um usuário especial chamado public que é utilizado quando queremos conceder um privilegio a todos os usuarios.

„ Revoke select on emp to bruno;

Views para controle de acesso

„ Não é possível conceder privilégios de SELECT ao nível de coluna de tabela. É

possível contornar essa limitação através do uso de views para esconder determinadas colunas.

„ O grant é concedido para a view e não para a tabela

„ Create view empregados as select n_emp, nome_emp, cargo from emp;

„ Grant select on empregados to public;

Administração de segurança

lógica e física

Pg_hba.conf

„ Um registro pode ter uma das 3 formas abaixo:

„ local database user authentication-method [ authentication-option ]

„ host database user IP-address IP-mask

authentication-method [ authenticationoption ]

„ hostssl database user IP-address IP-mask

authentication-method [ authenticationoption ]

Backup

„ Existem 2 maneiras de se realizar o backup de um servidor de banco de dados PostgreSQL:

„ Utilizando o comando pg_dump

„ Fazendo um backup de Filesystem

„ A operação de backup (via pg_dump) não bloqueia a operação normal do banco e é internamente consistente, isto é, enquanto um banco de dados sofre backup, as

atualizações não serão incluídas no arquivo resultante.

„ O backup pode reduzir bastante a performance do sistema

„ Pode demorar dependendo do tamanho do

banco

Pg_dump

„ A idéia por traz do pg_dump é gerar um arquivo com todos os comandos SQL

necessários para recriar o estado atual do banco de dados.

„ O pg_dump é uma aplicação cliente do

postgreSQL , o que permite a execução

de backups em servidores remotos

„ Pg_dump [opções] [dbname]

„ -a somente dados

„ -s somente estrutura

„ -b inclui blobs

„ -c inclui comandos de remoção, antes de criá-los

„ -C coloca no inicio um comando para criar o banco de dados

„ -d gera no formato INSERT

„ -f envia a saída pra um arquivo

„ -o envia também OIDs

„ -O cria os objetos com no owner

„ -t envia somente a tabela especificada

„ -x não envia comandos grant

„ -F formato de saída

„ Backup simples

„ Pg_dump –f backup1.dump curso

„ Backup somente dos schemas, sem dados

„ Pg_dump –s –f backup2.dump curso

„ Backup no formato insert

„ Pg_dump –d –c –f backup3.dump curso

„ Backup no formato binário

„ Pg_dump –Ft –f backup5.dump curso

„ Backup no formato binário comprimido e somente dados

„ Pg_dump –a –Fc –f backup6.dump curso

Pg_dumpall

„ Para realizar o backup de todos os bancos num único comando

„ Não é possível realizar backups de BLOBs

„ O usuário deve ter privilégios suficientes

em todos os bancos

Restaurando um dump

„ Texto

„ Psql dbname < arquivo.dump

„ Binário

„ Pg_restore [opções] [arquivo-bkp]

„

-a somente os dados

„

-s somente estrutura

„

-d nome do banco de dados

„

-c executa os comandos de remoção

„

-C faz que o CREATE DATABASE seja executado

„

-v verbose

Pg_restore

„ Restauração simples

„ Pg_restore –d dbname backup.dump

„ Restauração com os comandos de remoção e a opção verbose

„ Pg_restore –v –d dbname backup.dump

„ Restauração somente com a recriação dos objetos de banco de dados

„ Pg_restore –s –d dbname backup.dump

Backup de Grandes Bancos de Dados

„ Dado que o PostgreSQL pode ter tabelas maiores que o máximo do tamanho de

arquivos do sistema operacional, pode ser complicado para gerar um backup que o

tamanho de saída seja maior que o admitido pelo S.O.

„ Uma solução para contornar esse problema é utilizar o recurso de pipe do Linux e unix para esta situação

„ Pg_dump dbname | gzip > filename.gz

„ Gunzip –c filename.gz | psql dbname

Backup de filesystem

„ Não pode ser feito online

„ Cópia literal do banco de dados

„ Tar czv psql.bjp.tar.gz data

„ Tar xzf psql.bkp.tar.gz

Manutenção de banco de

dados

Manutenção

„ Existem algumas tarefas que devem ser executadas regularmente para que o

servidor PostgreSQL funcione eficazmente.

„ Uma dessas tarefas, é a rotina de

backup e a outra tarefa é o vacuum do

banco de dados.

Vacuum

„ O vacuum deve ser executado por 3 razões:

„ Liberar espaço em disco utilizado por linhas que foram apagadas

„ Atualizar as estatísticas utilizadas pelo otimizador

„ Para evitar o transaction ID wraparound

Vacuum

„ A freqüência do vacuum dependerá das características e necessidades de cada banco de dados.

„ O vacuum pode ser executado em

paralelo com a operação normal do

banco de dados

Vacuum

„ vacuumdb [Opção]… [dbname]

„ Opções:

„ -a faz o vacuum em todos os bancos

„ -d especifica o banco de dados

„ -t especifica tabela e coluna

„ -f faz um vacuum full

„ -z atualiza as estatísticas do otimizador

„ -e mostra os comandos sendo executados

„ -v gera informações mais detalhadas

Liberando espaço em disco

„ O postgreSQL não remove imediatamente as linhas removidas por um comando

delete.Este espaço pode ser liberado pelo

comando vacuum para ser utilizado por novas linhas

„ A operação de um vacuum libera espaço

dentro de um banco de dados , mas não para o sistema operacional

„ Para liberar o espaço para o sistema

operacional, é necessário utilizar o opção

FULL

Atualização das estatísticas

„ O otimizador de queries do PostgreSQL utiliza estatísticas de distribuição de

dados em tabelas e índices para construir planos de execução

adequados

„ Estas estatísticas podem ser colhidas

através do comando analyse ou através

da opção analyse do vacuum

Prevenindo Falhas nos IDs de Transação

„ O mecanismo de transação do

PostgreSQL, utiliza a comparação do ID da transação(XID) para estabelecer a consistência entre as transações

„ Ex: Uma linha com XID de inserção maior que o XID da transação corrente deve

estar no futuro e não deve ser visível pela

transação.

Prevenindo Falhas nos IDs de Transação

„ Dado que os IDs tem um tamanho de 32 bits, um servidor que rode por um período longo(

mais de 4 bilhões de transações) sofrerão de transaction ID wraparound. Ou seja o XID

volta para zero e todas as transações que estavam no passado parecerão estar no futuro(ficando invisível)

„ A solução para este problema é realizar um vacuum no mínimo a cada bilhão de

transações

Verificando os IDs de transação

„ A tabela pg_database armazena informações estatísticas sobre a rotina de vacuum. A coluna datfrozenxid é atualizada ao final desta rotina.

„ É possível sabermos quantas transações já foram realizadas após o ultimo vacuum utilizando a função AGE

„ Select datname, datfrozenxid, AGE(datfrozenxid) from pg_database;

„ Um banco de dados com AGE em 1 bilhão acabou de sofrer

um vacuum.

Write Ahead Log

Write Ahead Log

„ O mecanismo padrão de log de transação é conhecido como write ahead log (WAL)

„ O WAL faz que uma transação somente seja escrita nos arquivos de banco de dados depois que ela foi escrita nos arquivos de log localizados em

dispositivos de armazenamento permanente

„ Desta forma, não é preciso sincronizar a área de cache em memória do banco de dados com o disco todas as vezes que uma transação é efetivada

„ Caso haja um crash de banco de dados, é possível

recuperar as transações registradas.

Monitorando o PostgreSQL

Monitorando o tamanho dos bancos

„ Para saber o tamanho de um banco no sistema de arquivos podemos utilizar o seguinte mecanismo:

„ Banco=# select datname, oid from pg_database;

„ O oid mostra o valor do subdiretório onde

o banco se encontra.

Monitorando o tamanho das tabelas

„ Select relfilenode, relpages from

pg_class where relname = ‘tablename’

„ O resultado obtido é em paginas do banco de dados. O valor padrão de um página é de 8k.

„ É necessário executar um vacuum analyse para ter as informações atualizadas

„ Ex: select relfilenode, relpages from

pg_class where relname=‘emp’;

Estatísticas de Uso do Banco

„ O PostgreSQL possui views que organizam informações estatísticas de utilização de banco, de sessões e de tabelas do banco:

„ Pg_stat_activity

„

Mostra uma linha para cada processo servidor com as informações do banco de dados , usuário, texto da query, etc…

„ Pg_stat_database

„

Uma linha por banco de dados com as informações do

numero de conexões ativas, total de blocos lidos em

memória, etc…

Monitoração do Log do Banco

„ Para gerar um arquivo de log, é

necessário especificá-lo no momento de iniciar o processo postmaster

„ Pg_ctl start –l $PGDATA/postgresql.log

Instalação e administração do

software

Instalação - Requisitos

„ Software de instalação

„ Gnu make (versão 3.76.1)

#gmake –version

„ ISO/ANSI C compiler (gcc versão 2.95)

#gcc --version

„ Gnu zip (gzip)

„ Gnu tar

„ Opcionais(Readline, OpenSSL, Tcl/TK, Ant/JDK)

Instalação dos Fontes

„ Adicionar o usuário postgres para ser o administrador do PostgreSQL

#adduser postgres

#passwd postgres

„ Descompactar o software

#tar –xzvf postgresql-7.3.4.tar.gz

(o local default para a instalação é o diretório /usr/local/pgsql )

Instalação dos Fontes

„ Antes de compilar o código fonte deve ser executado o script configure, que checa as dependências de software necessárias para a instalação do PostgreSQL

#./configure

„ Depois de finalizada a execução do configure, é feita a compilação do PostgreSQL através do comando gmake:

#gmake

(O tempo de compilação depende do seu computador, e pode

demorar de 10 minutos até mais de 1 hora)

Instalação dos Fontes

„ Depois de tudo compilado, podemos fazer o teste de regressão, que não é necessário, mas é recomendável, pois ele faz uma verificação se o PostgreSQL irá funcionar corretamente após a compilação.

#gmake check

„ Após o teste de regressão é hora de fazer a instalação dos binários com o seguinte comando.

#gmake install

(O PostgreSQL estará instalado no local /usr/local/pgsql)

Instalação

„ #./configure

„ #gmake

„ #su

„ #gmake install

„ #adduser postgres

„ #mkdir /usr/local/pgsql/data

„ #chown postgres /usr/local/pgsql/data

„ #su - postgres

„ #/usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data

„ #/usr/local/pgsql/bin/postmaster -D /usr/local/pgsql/data

„ #/usr/local/pgsql/bin/createdb test

„ #/usr/local/pgsql/bin/psql test

Instalação Windows

„ NTFS

„ A partir da versao 8.0 ( nativo )

„ Qualquer sistema de arquivos

„ Através de um emulador de Unix (Cygwin)

„ Bruno Hideo Casillo

„ [email protected]