Banco de Dados PosgtreSQL, Notas de estudo de Análise de Sistemas de Engenharia

Baixe Banco de Dados PosgtreSQL e outras Notas de estudo em PDF para Análise de Sistemas de Engenharia, somente na Docsity!

PostgreSQL Prático

(versão 8.1.4)

Ribamar FS – [email protected] – http://ribafs.tk 17 de setembro de 2006

• Revisado em 03/10/

13.3 Com PHP 13.4 Exemplos de conexão com PHP, Java e VB

14 Ferramentas.......... 108 14.1 psql 14.2 phpPgAdmin 14.3 PgAdmin 14.4 EMS PostgreSQL 14.5 Azzurry Clay (modelagem com o Eclipse) 14.6 dbVisualizer 14.7 OpenOffice Base 15 – Apêndices.......... 124 15.1 – Planejamento e Projeto de Bancos de Dados 15.2 – Implementação de Banco de Dados com o PostgreSQL 15.3 Integridade Referencial PostgreSQL 15.4 – Dicas Práticas de uso do SQL 15.5 – Dicas sobre Desempenho e Otimizações do PostgreSQL 16 – Exercícios.......... 149 17 Referências.......... 154

organização. Projetos de mesma categoria, que precisem acessar uns aos outros devem ficar em um mesmo banco, podendo ficar em esquemas separados. Tabelas – são subdivisões de um esquema, nelas realmente ficam armazenados os dados de um banco. Uma tabela parece realmente com uma tabela em papel, tipo planilha, com linhas e colunas. Cada linha representa um registro de banco de dados e cada cruzamento de coluna com linha representa um campo de tabela. Tipo de Dados de um campo restringe o conjunto de valores (domínio) que pode ser atribuído ao campo e atribui semântica aos dados armazenados. Um campo do tipo numérico não aceita dados do tipo texto ou similar. Citação da Introdução do documento sobre otimização do PostgreSQL POSTGRESQL é um SGBD objetor elational (SGBDOR) desenvolvido via Internet por um grupo de desenvolvedores espalhados pelo globo. É uma alternativa de código fontea berta para SGBDs comerciais como Oracle e Informix. O POSTGRESQL foi desenvolvido originalmente na Universidade de Califórnia em Berkeley. Em 1996, um grupo começou o desenvolvimento do SGBD na Internet. Eles usam ema il para compartilhar idéias e servidores de arquivos para compartilhar código. POSTGRESQL é agora comparável à SGBDs comerciais em termos de características, desempenho e confiança. Hoje tem transações, views, procedimentos armazenados, e constranints de integridade referencial. Apóia um número grande de interfaces de programação, como ODBC, Java (JDBC), TCL/TK, PHP, Perl e Python, entre outros. POSTGRESQL continua avançando a um tremendo passo, graças a um grupo talentoso de desenvolvedores via Internet. (Bruce Momjian - 16th January 2003) Projeto POSTGRES (19861 994): Partiu do projeto do SGBD Ingres de Berkeley. Projetista: Michael Stonebraker. Em 1995 dois estudantes de Berkeley (Jolly Chen e Andrew Yu) adicionam suporte a SQL. Seu novo nome: Postgres95. Foi totalmente reescrito em C e também adotou a SQL. Foi originalmente patrocinado pelo DARPA, ARO, NSF e ESL Inc. Em 1996: Disponibilizado na Internet sob o nome de PostgreSQL. O PostgreSQL aniversariou no dia 08/07/2006, quando completou 10 anos (08/07/1996). Seu décimo aniversário foi comemorado nos dias 08 e 09 de julho próximo, em Toronto, Canadá, com algumas conferências sobre o mesmo. Atualmente está na versão 8.1.4 (14/09/2006). Para saber mais sobre a história do PostgreSQL visite o site oficial em: http://www.postgresql.org/docs/current/interactive/history.html Ou em português em: http://pgdocptbr.sourceforge.net/pg80/history.html Características:

• O PostgreSQL suporta grande parte do SQL ANSI, inclusive do SQL 2003, além de oferecer

outros recursos importantes, como:

• Comandos complexos
• Chaves estrangeiras (Foreign Key)
• Gatilhos (Triggers)
• Visões (views)
• Integridade de Transações
• Controle de Simultaneidade Multiversão (MVCC)
• Suporta múltiplas transações online concorrentes entre usuários.
• Suporte a Rules (sistema de regras que reescreve diretivas SQL)
• Criação de tabelas temporárias (CREATE TEMP TABLE nome(listadecampos tipos);) Traz também opções de extensão pelo usuário para:
• Tipos de dados
• Funções
• Operadores
• Funções de Agregação (Agrupamento)
• Métodos de Índice
• Linguagens Procedurais (Stored Procedures) Licença Sua licença é BSD, portanto pode ser utilizado, modificado e distribuído por qualquer pessoa ou empresa para qualquer finalidade, sem encargo, em quaisquer dos sistemas operacionais suportados. Algumas Empresas que Utilizam PostgreSQL BASF (PDF format) Fujitsu Apple RedHat Sun Pervasive Mohawk Software Proximity Radio Paradise Shannon Medical Center Spiros Louis Stadium The Dravis Group OSS Report Vanten Inc. SRA Rambler Netezza VA Software Travel Post National Weather Service Aplicações Corporativas de Alto Volume: Uma Solução com o PostgreSQL A utilização da dupla PostgreSQL+Linux nas empresas cresce rapidamente e é um exemplo de como produtos Open Source podem ajudar empresas a racionalizar os custos de TI. Uma

Publicado sob a licença Creative Commons [http://creativecommons.org/worldwide/br/]

2 – Instalação Instalação no Windows XP Lembrar que: Precisa instalar em sistema de arquivos NTFS e não instala no XP Start Edition (onde falta suporte a redes). Fazer download do site oficial (www.postgresql.org) (hoje postgresql8 .1.41 .zip) Executar o arquivo postgresql8 .1.msi Selecionar idioma e Start. Depois em Próximo. Na tela "Informações de Instalação" existem muitas informações importantes: Sugere a leitura da FAQ Fala das licenças dos diversos softwares a serem instalados As versões 95, 98 e Me do Windows não são suportadas pelo PostgreSQL Usar obrigatoriamente em sistema de arquivos NTFS Instalar como serviço (mesmo que deixe como manual) O PostgreSQL não executa com usuário que tenha privilégios de administrador Os drivers jdbc estão no subdiretório \jdbc, que deve ser adicionada ao CLASSPATH Na Tela "Opções de Instalação" marque: Suporte para idioma nativo (importante para ter as mensagens em pt_BR) E outros que considere importantes e clique em Próximo Na tela "Configuração do Serviço": Poderá optar por instalar como serviço ou não. Como serviço é mais prático. Clique em Próximo (ele criará uma senha) Obs.: Caso já tenha instalado o PostgreSQL antes nesta máquina deverá remover o usuário "postgres" antes de continuar: Painel de controle Ferramentas administrativas Gerenciamento do computador Usuários e grupos locais Usuários. Remova o "postgres" Agora clique em Próximo e Sim Na tela "Inicializar o agrupamento de bancos de dados": Caso precise acessar sua máquina de outra remota marque Endereços Em Locale selecione Português Brasil Em Codificação selecione LATIN Entre com uma senha e repita. Altere o usuário se for o caso e Próximo. Na tela "Habilitar Linguagens Procedurais" deixe marcada PL/pgsql (caso pretenda utilizar) e Próximo Na tela "Habilitar Módulos Contrib" marque os desejados e Próximo Na tela "Habilitar PostGIS em templae1" marque somente se precisar que todos os bancos tragam o PostGIS e Próximo e Próximo novamente. Após instalar, na tela "Instalação concluída" recomendase que você se cadastrar na lista pgsqla nnounce, que envia informações semanais sobre novas versões e correções de erros. Basta clicar no botão, fazer o cadastro e Concluir.

/usr/local/pgsql/bin/psql test

Copiar o script de inicialização “linux” para o /etc/init.d (Nos Debians): De /usr/local/src/postgresql8 .1.4/contrib/startscri pt/linux para /etc/init.d/postgresql Dar permissão de execução: chmod u+x /etc/init.d/postgresql Se no Ubuntu ou outro Debian: su postgres gedit .bash_profile (e adicione a linha): PATH=/usr/local/pgsql/bin:$PATH Pós Instalação (sh,bash,ksh e zsh): LD_LIBRARY_PATH=/usr/local/pgsql/lib export LD_LIBRARY_PATH Ou no ~/.bash_profile do usuário postgres initdb – inicializa o cluster, cria os scripts de configuração default. postmaster – inicia o processo do servidor responsável por escutar por pedidos de conexão. Para suporte aos locales do Brasil usar: /usr/local/pgsql/bin/initdb l ocale=pt_BR D /usr/local/pgsql/data A instalação via fontes (sources) em algumas distribuições muito enxutas, voltadas para para desktop, pode não funcionar da primeira vez, pois faltarão algumas bibliotecas, compiladores, etc. Após a instalação está criado o agrupamento principal (cluster main) de bancos de dados do PostgreSQL. Caso não se tenha confiança nos usuários locais é recomendável utilizar a opção W, pwprompt ou –pwfile do initdb, que atribuirá uma senha ao superusuário. No arquivo pg_hba.conf utilizar autenticação tipo md5, password ou cript, antes de iniciar o servidor pela primeira vez. Quando o programa que inicia o servidor (postmaster) está em execução, é criado um PID e armazenado dentro do arquivo postmaster.pid, dentro do subdiretório data. Ele impede que mais de um processo postmaster seja executado usando o mesmo cluster e diretório de dados. Baixar PostgreSQL via Anonymous CVS: Baixar CVS de http://www.nongnu.org/cvs/ Instalar e Logar com qualquer senha: cvs d :pserver:[email protected]:/projects/cvsroot login Baixar fontes: cvs z3 d :pserver:[email protected]:/projects/cvsroot co P pgsql Isto irá instalar o PostgreSQL num subdiretório pgsql do diretório atual.

versão 8 podem ficar em diretório diferente do PGDATA.

Sugestão de Padrão

• Nomes de bancos no plural
• Nomes de tabelas no singular
• Exemplo:
  • banco – clientes
  • tabela cliente Criar Novo Cluster Caso sinta necessidade pode criar outros clusters, especialmente indicado para grupos de tabelas com muito acesso. O comando para criar um novo cluster na versão atual (8.1.3) do PostgreSQL é: banco=# \h create tablespace Comando: CREATE TABLESPACE Descrição: define uma nova tablespace Sintaxe: CREATE TABLESPACE nome_tablespace [ OWNER usuário ] LOCATION 'diretório' Exemplo: CREATE TABLESPACE ncluster OWNER usuário LOCATION '/usr/local/pgsql/nc'; CREATE TABLESPACE ncluster [OWNER postgres] LOCATION 'c:\ncluster'; O diretório deve estar vazio e pertencer ao usuário. Criando um banco no novo cluster: CREATE DATABASE bdcluster TABLESPACE = ncluster; Obs: Podem existir numa mesma máquina vários agrupamentos de bancos de dados (cluster) gerenciados por um mesmo ou por diferentes postmasters. Se usando tablespace o gerenciamento será de um mesmo postmaster, se inicializados por outro initdb será por outro. Setar o Tablespace default: SET default_tablespace = tablespace1; Listar os Tablespaces existentes: \db SELECT spcname FROM pg_tablespace; Detalhes extras no item 14.5 do manual oficial.

Exemplo Gráfico de Consultas (Tabela, com campos C1, C2) (Adaptação de exemplo da Wikipedia (http://pt.wikipedia.org) Tabela T Consulta Resultado C1 C2 SELECT * FROM T C1 C 1 a 1 a 2 b 2 b C1 C2 SELECT C1 FROM T C 1 a 1 2 b 2 C1 C2 SELECT * FROM T WHERE C1=1 C1 C 1 a 1 a 2 b C1 C2 SELECT C1 FROM T WHERE C2=b C 1 A 2 2 B Criar Banco banco=# \h create database Comando: CREATE DATABASE Descrição: cria um novo banco de dados Sintaxe: CREATE DATABASE nome [ [ WITH ] [ OWNER [=] dono_bd ] [ TEMPLATE [=] modelo ] [ ENCODING [=] codificação ] [ TABLESPACE [=] tablespace ] ] [ CONNECTION LIMIT [=] limite_con ] ] CREATE DATABASE nomebanco; Excluindo Um Banco DROP DATABASE nomebanco; Listar os bancos existentes: \l No psql

psql l (no prompt) SELECT datname FROM pg_database; Quando se cria um novo banco de dados sem indicar o modelo, o que de fato estamos fazendo é clonar o banco de dados template1. Criar um banco para outro usuário: CREATE DATABASE nomebanco OWNER nomeuser; createdb O nomeusuario nomebanco Obs.: requer ser superusuário para poder criar banco para outro usuário. Criar Tabela postgres=# \h create table Comando: CREATE TABLE Descrição: define uma nova tabela Sintaxe: CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE nome_tabela ( [ { nome_coluna tipo_dado [ DEFAULT expressão_padrão ] [ restrição_coluna [ ... ] ] | restrição_tabela | LIKE tabela_pai [ { INCLUDING | EXCLUDING } DEFAULTS ] } [, ... ] ] ) [ INHERITS ( tabela_pai [, ... ] ) ] [ WITH OIDS | WITHOUT OIDS ] [ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ] [ TABLESPACE tablespace ] onde restrição_coluna é: [ CONSTRAINT nome_restrição ] { NOT NULL | NULL | UNIQUE [ USING INDEX TABLESPACE tablespace ] | PRIMARY KEY [ USING INDEX TABLESPACE tablespace ] | CHECK (expressão) | REFERENCES tabela_ref [ ( coluna_ref ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE ação ] [ ON UPDATE ação ] } [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ] e restrição_tabela é: [ CONSTRAINT nome_restrição ] { UNIQUE ( nome_coluna [, ... ] ) [ USING INDEX TABLESPACE tablespace ] | PRIMARY KEY ( nome_coluna [, ... ] ) [ USING INDEX TABLESPACE tablespace ] | CHECK ( expressão ) | FOREIGN KEY ( nome_coluna [, ... ] ) REFERENCES tabela_ref [ ( coluna_ref [, ... ] ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE ação ] [ ON UPDATE