Minicurso PostgreSQL

  • View
    4.599

  • Download
    0

Embed Size (px)

DESCRIPTION

Minicurso minitrado na targettrust com um resumo de diversos tópicos sobre PostgreSQL.

Text of Minicurso PostgreSQL

  • 1. PostgreSQL 8.4 Diogo Biazusdiogob@gmail.com

2. PostgreSQL 7.X Totalmente ACID MVCC Tipos e operadores customizveis Diversas linguagens procedurais Hot backup Cdigo aberto (BSD) Facilidade de extenso PL/pgSQL, PL/Python, PL/Perl, PL/Java, PL/PHP, PL/Ruby, etc. 3. PostgreSQL 8.0 Win32 Pontos de salvamento Recuperao a partir de logs Tablespaces Tratamento de erro em PL/pgSQL Otimizaes 4. PostgreSQL 8.3 Warm-standby Commit em duas etapas Consultas RETURNING FTS integrado ao banco XML integrado Tipos UUID e ENUM Integrao LDAP 5. PostgreSQL 8.4 e Avante 8.4 Window functions CTE e recursividade Permisso de colunas Futuro Hot-standby (rep. assncrona nativa) Replicao sncrona nativa Bitmap index no disco SEPostgres 6. Portes e pacotes Disponibilidade do fonte e de pacotes para: *BSD Linuxpacotes em .rpm e .deb. WindowsPacotes msi (a partir da 8.0) 7. Instalando o servidor no Linux Debian apt-get install postgresql-8.3 Compilao simples Fazer download do fontes em: http://www.postgresql.org/ftp/source/ Descompactar ./configure make install 8. Instalando o servidor no Linux Se no for atravs do sistema de pacotes deve-se iniciar o agrupamento com o initdb necessrio criar a pl/pgsql nos bancos em que ela for utilizada Para instalar mdulos do contrib basta instalar o pacote postgresql-contrib (se for usado sistema de pacotes) ou entrar no diretrio contrib correspondente e digitar make install 9. Instalando o servidor no Linux initdb Usar o flag --locale O diretrio deve pertencer ao usurio que vai rodar o banco. Mscara de permisso devem ser 700 Deve ser executado com o usurio que vai rodar o banco O primeiro usurio do banco ter o mesmo nome do usurio do sistema que executou o initdb 10. Instalando no Windows Fazer o download do msi Descompactar Iniciar o instalador 11. Instalando no Windows Seleo de idioma do instalador 12. Instalando no Windows Seleo de componentes 13. Instalando no Windows Configurao de servio 14. Instalando no Windows initdb locale=pt_BR -D diretorio 15. Instalando no Windows createlang 'plpgsql' template1 16. Instalando no Windows Instalando mdulos do contrib 17. Configuraes bsicas Arquivos dentro do diretrio de dados pg_hba configurao de como os hosts devem se autenticar postgresql.conf demais configuraes do banco 18. Configuraes bsicas pg_hba Regras de como autenticar, lidas sequencialmente do inicio ao fim do arquivo. Tipos de autenticao:trust, reject, password, md5, crypt, krb4, krb5,ident, pam Ex.: TipoBDUsuario IP/RedeMtodo local all allident sameuser hostall all 127.0.0.1/32 md5 19. Configuraes bsicas postgresql.conf listen_addresses = '*'O banco escuta em todas interfaces de rede max_connections = 100Mximo de conexes concorrentes 20. Acessando o banco psql template1 postgres comandos do psql:l lista bancos de dadosc bd conecta no banco bddt lista relaesi executa arquivo contendo comandos sql? mostra help do psqlh mostra referncia do SQLh comando mostra referncia do comando 21. SQL Bsico Nomes Devem ser auto-explicativos Para nomes de objetos ' ' Para literais 22. SQL Bsico DDL CREATE DATABASE nome [ [ WITH ] [ OWNER [=] dono ] [ TEMPLATE [=] modelo ] [ ENCODING [=] codificao ] [ TABLESPACE [=] tablespace ] ] DROP DATABASE nome 23. SQL Bsico DDL CREATE [ TEMPORARY ] TABLE nome_tabela ( { nomecoluna tipo [ DEFAULT expresso ] } [, ... ] ) [ WITH OIDS | WITHOUT OIDS ] [ TABLESPACE tablespace ] DROP TABLE nome [ CASCADE | RESTRICT ] 24. SQL Bsico DDL GRANT permissao ON objeto TO usuario[ WITH GRANT OPTION ] REVOKE permissao ON objeto FROM usuario 25. SQL Bsico CREATE DATABASE minicursos; CREATE TABLE instrutores ( id_instrutor serial PRIMARY KEY, nome varchar(100) NOT NULL ); 26. SQL Bsico CREATE TABLE cursos ( id_curso serial PRIMARY KEY, id_instrutor int REFERENCES instrutores, nome varchar(100) NOT NULL ); 27. SQL Bsico DML SELECT [ ALL | DISTINCT ] * | expresso [ AS nome ] [, ...] [ FROM relacao [, ...] ] [ WHERE condio ] [ GROUP BY expresso [, ...] ] [ HAVING condio [, ...] ] [ ORDER BY expresso [ ASC | DESC | [ LIMIT { quantidade | ALL } ] [ OFFSET inicio ] 28. SQL Bsico DML INSERT INTO tabela [ ( coluna [, ...] ) ]{DEFAULT VALUES| VALUES ( { expresso | DEFAULT } [, ...] )| consulta} 29. SQL Bsico INSERT INTO instrutores VALUES (DEFAULT, 'Diogo Biazus'); INSERT INTO cursos VALUES (DEFAULT, 1, 'Mini-curso de PostgreSQL'); 30. SQL Bsico SELECT * FROM cursos; SELECT * FROM instrutores; SELECT * FROM cursos JOIN instrutores USING (id_instrutor); 31. SQL Bsico DML UPDATE tabela SET coluna = { expresso |DEFAULT } [, ...][ WHERE condio ] 32. SQL Bsico DML DELETE FROM tabela [ WHERE condio ] 33. SQL Bsico DCL BEGIN COMMIT SAVEPOINT nome ROLLBACK TO [ SAVEPOINT ] nome ROLLBACK 34. SQL Bsico BEGIN; UPDATE cursos SET nome = 'teste'; SELECT * FROM cursos; SAVEPOINT atualiza; DELETE FROM cursos; SELECT * FROM cursos; ROLLBACK TO atualiza; SELECT * FROM cursos; ROLLBACK; SELECT * FROM cursos; 35. Manuteno bsica VACUUM Remove verses antigas de registros Deve ser agendado no mnimo diariamente se o autovacuum for desabilitado ANALYZE para atualizar estatsticas 36. Manuteno bsica Backups Podem ser feitos com o banco no ar pg_dumpFormatos:Plain, Custom e Tar pg_restorePode-se usar listas para restaurao 37. Tablespaces rea de dados separada fisicamente CREATE TABLESPACE nome [ OWNER dono ] LOCATION 'diretorio' 38. Funes em PL/pgSQL Porque PL/pgSQL? Ela bonita? No. Ela OO? No. Ela popular? No. Mesmo assim ela tima no que faz! 39. Funes em PL/pgSQL CREATE FUNCTION primeira_funcao() RETURNSVOID AS ' BEGIN RETURN; END; ' LANGUAGE 'plpgsql'; DROP FUNCTION primeira_funcao(); 40. Funes em PL/pgSQL Estrutura bsica [DECLARE nome_da_varivel tipo; ... ] BEGIN comandos; END; 41. Funes em PL/pgSQL CREATE OR REPLACE FUNCTION primeira_funcao()RETURNS VOID AS $body$ BEGIN RAISE NOTICE 'Minha primeira rotina em PL/pgSQL'; RETURN; END; $body$ LANGUAGE 'plpgsql'; 42. Funes em PL/pgSQL Declarando variveis: DECLARE numero int4 NOT NULL DEFAULT 10; Atribuindo valores: numero := 15; 43. Funes em PL/pgSQL Estruturas de controle: IF ... THEN ... ELSIF ... THEN ... ELSE ... END IF; FOR ... LOOP END LOOP; LOOP ... END LOOP; WHILE ... LOOP ... END LOOP; 44. Funes em PL/pgSQL Executando SQL: CREATE OR REPLACE FUNCTION exclui_cliente(pid_cliente int4) RETURNS int4 AS $body$ DECLARE vLinhas int4 DEFAULT 0; BEGIN DELETE FROM clientes WHERE id_cliente = pid_cliente; GET DIAGNOSTICS vLinhas = ROW_COUNT; RETURN vLinhas; END; $body$ LANGUAGE 'plpgsql'; 45. Domnios Podemos fazer validaes mais complexas CPF, CNPJ, email, dependncia de arquivos, etc... E criar domnios para validar de forma consistente 46. Domnios CREATE OR REPLACE FUNCTION valida(p1 varchar) RETURNS boolean AS $$ BEGINRETURN (lower(p1) IN ('a', 'b', 'ab', 'o')); END; $$ LANGUAGE plpgsql; 47. Domnios CREATE DOMAIN tipo_sanguineo AS varchar CHECK(valida(VALUE)); CREATE TABLE pessoa (nome varchar PRIMARY KEY,sangue tipo_sanguineo NOT NULL ); 48. DomniosINSERT INTO pessoa (nome, sangue)VALUES ('Diogo', 'm');INSERT INTO pessoa (nome, sangue)VALUES ('Diogo', 'm'); 49. Gatilhos em PL/pgSQL Tipo de retorno TRIGGER Nvel de linha e nvel de comando Variveis para nvel de linha: NEW OLD TG_OP TG_WHEN 50. Gatilhos em PL/pgSQL CREATE TRIGGER nome { BEFORE |AFTER } { evento [ OR ... ] } ON tabela [ FOR [ EACH ] { ROW |STATEMENT } ] EXECUTE PROCEDUREfuncao( argumentos ) 51. Gatilhos em PL/pgSQL CREATE TABLE usuarios ( nome varchar(30) PRIMARY KEY, senha char(32) ); 52. Gatilhos em PL/pgSQL CREATE FUNCTION md5_senha() RETURNSTRIGGER AS $body$ BEGIN NEW.senha := md5(NEW.senha); RETURN NEW; END; $body$ LANGUAGE 'plpgsql'; 53. Gatilhos em PL/pgSQL CREATE TRIGGER md5_senha BEFORE INSERT ONusuarios FOR EACH ROW EXECUTE PROCEDUREmd5_senha(); INSERT INTO usuarios VALUES ('usuario', 'senha'); SELECT * FROM usuarios; 54. Dicas de segurana O objetivo minimizar os danos Sempre engaiole o seu usurio Conhea bem os comandos GRANT e REVOKE Use funes SECURITY DEFINER como interface para operaes crticas Crie usurios comuns para criao e administrao de bancos Crie usurios comuns para operao de bancos Usurios diferentes para bancos diferentes 55. Extenses Funes em C DBLink: Consultas remotas DBI-Link: Consultas em fontes DBI Tsearch: Indexao de textos no PgSQL PgXML: Manipulao com Xquery PostGis: Dados geogrficos 56. Replicao Slony I PGPool II Warm-standby (hot-standby na 8.5) Skytools 57. Referncias Sobre o PgSQL http://www.postgresql.org.br/ http://www.postgresql.org/