Baixe o app para aproveitar ainda mais
Prévia do material em texto
Funcionalidades PostgreSQL 9.x Novembro/2011 Agenda A Dextra e o PostgreSQL Histórico PostgreSQL Recursos avançados Por que utilizar PostgreSQL? Funcionalidades da versão 9.0 Funcionalidades da versão 9.1 Funcionalidade da versão 9.2 (devel) A Dextra Soluções de Software Transferência de conhecimento e aprimoramento de competências Resolução de problemas tecnologicamente desafiadores e implementação de melhorias de forma prática Projeto e Sustentação de software complexos, com alta criticidade para os negócios Clientes A Dextra oferece serviços no banco de dados PostgreSQL desde 1999 Gerência de Serviços PostgreSQL, focada na garantia de qualidade dos serviços oferecidos Projetos de grande porte com empresas e governo Equipe especializada de DBAs PostgreSQL Dextra e PostgreSQL Consultoria Resolução efetiva de problemas desafiadores e complexos Os serviços oferecidos englobam: Instalação e configuração de servidores PostgreSQL para aplicações críticas Migração de sistemas de outros bancos de dados (Oracle, SQL Server, Informix, MySQL entre outros) para PostgreSQL Modelagem de banco de dados Administração preventiva Soluções de monitoramento Ajustes de performance Replicação de bancos de dados Soluções de alta disponibilidade e desempenho Desenvolvimento de aplicações com PostgreSQL Suporte Técnico Segurança na implantação e administração de ambientes críticos Gestão voltada à garantia da qualidade dos serviços – Acordos de Nível de Serviço (SLA) – Aumento do nível de satisfação dos usuários Gerenciamento de ambientes PostgreSQL Monitoramento eficiente do banco de dados Administração preventiva Transferência de conhecimento Modelo flexível: 24 x 7 ou 8 x 5 Casos de Sucesso – Consultoria e Suporte Capacitação Transferência de conhecimento e aprimoramento de competências Treinamentos com profissionais que vivenciam o dia-a-dia do desenvolvimento de software e das rotinas do banco de dados PostgreSQL Turmas abertas ou In-Company Customização de conteúdos Mais de 15 mil alunos treinados – PostgreSQL Essencial – Linguagem Procedural PL/pgSQL – Administração (DBA) – Performance Tuning – PostgreSQL Alta Disponibilidade – PostGIS – PostgreSQL para BI e Datawarehouse Casos de Sucesso – Treinamento PostgreSQL Sistema Gerenciador de Banco de Dados (SGBD) opensource Banco de dados padrão para aplicações do governo brasileiro Projeto dirigido pela comunidade de software livre – Desenvolvedores ao redor do mundo – Controle de código pelo PGDG (PostgreSQL Global Development Group) – Não há empresas proprietárias do código Comunidade formada por desenvolvedores e usuários – Desenvolvedores fulltime, colaboradores de empresas que investem no PostgreSQL Liberdade total para o negócio – Licença BSD – Sem restrições para uso, alteração e venda PostgreSQL ~90%PostgreSQL Bancos de dadosproprietários PostgreSQL - História 1986-1995: Berkeley 1996-1998: Bugs, bugs, bugs, ... 1999-2001: Padrão SQL 2002-2005: Suporte nativo para Windows 2005-2011: Funcionalidades Avançadas Derivados do PostgreSQL Robustez – Escalável para altas necessidades de processamento – Estabilidade Confiabilidade – Mecanismo de log de transação avançado – Dados efetivados não são perdidos após catástrofes Segurança – Níveis de configuração (Servidor, banco, usuário e sessão) – SSL – Controle de acesso refinado – Integração com LDAP, Active Directory, PAM entre outros – Rápida divulgação e correção de bugs de segurança Por que utilizar PostgreSQL? Alto desempenho – Velocidade em sistemas com grande concorrência – Particionamento de tabelas – Escalabilidade horizontal com PL/Proxy – Ferramentas de pool de conexões e balanceamento de carga (Pgbouncer e Pgpool) Alta disponibilidade – Arquivamento de logs de transação – Backup quente e frio – Restauração PITR (Point in Time Recovery) – Replicação por fluxo (Streaming Replication) – Replicação nativa Master-Slave síncrona e assíncrona Por que utilizar PostgreSQL? Consistência – Mecanismo MVCC (Multiversion Concurrency Control) – Transações ACID (Atomicidade, Consistência, Isolamento e Durabilidade) Rapidez – Diversos algoritmos de junções e índices – Recursos avançados para manutenção em ambientes concorrentes – Parâmetros de tuning para adaptação aos diversos ambientes Modelo Extensível – Possibilita o desenvolvimento de funções em C e novas linguagens procedurais – Desenvolvimento de extensões como PostGIS e Dblink – Plugins FDW (Foreign Data Wrapper) – Diversas ferramentas opensource disponíveis Por que utilizar PostgreSQL? Compatibilidade – Acompanha o padrão ANSI SQL, atualmente SQL:2008 – Suporte a XML – Expressões regulares Versatilidade – Diversidade de linguagens procedurais (PL/pgSQL, PL/Perl, PL/Java, PL/PHP …) – Diversidade de drivers de acesso (JDBC, ODBC, PHP, .Net, Perl ...) Facilidade de administração – Arquitetura enxuta – Facilidade de configuração e manutenção – Ferramentas de monitoramento (Nagios, Zabbix, Munin, Cacti …) – Ferramentas opensource e proprietárias de administração Por que utilizar PostgreSQL? Redução de TCO (Total Cost of Ownership) – Investir no conhecimento – Investir em hardware Por que utilizar PostgreSQL? Buscas textuais com Full Text Search nativo Consultas recursivas Window functions para facilitar o desenvolvimento de relatórios complexos Arrays Herança entre tabelas Suporte a BLOBs (Binary Large Objects) Tipos de dados customizados Compressão transparente de dados com tipo texto Extensão para dados espaciais COMMIT em 2 fases para replicação Master-Master Recursos avançados OLTP (Online Transaction Processing) OLAP (Online Analytical Processing) Web GIS (Geographical Information Systems) E-commerce Telecomunicações Redes sociais Monitoramento de dispositivos eletrônicos Sistemas de busca ... Adequado para diversos ambientes 2010/Setembro – Versão 9.0 lançada – Release intermediária atual 9.0.4 2011/Setembro – Versão 9.1 lançada Em média 200 melhorias a cada nova versão Releases intermediárias para correções de bugs 5 versões em 5 anos Releases PostgreSQL PostgreSQL 9.0 Transferência de fragmentos de logs de transação. Servidor slave em modo read-only Failover através da criação de arquivo gatilho PostgreSQL 9.0: Hot standby Servidor standby disponível em modo read-only: UPDATE actor SET last_update = now(); ERROR: cannot execute UPDATE in a read-only transaction Restauração ocorre paralelamente as consultas Em caso de failover: – Conexões ativas são mantidas e autorizadas a alterar a base de dados sem a necessidade de reconexão PostgreSQL 9.0: Hot standby PostgreSQL 9.0: Streaming Replication Criação de um canal de comunicação via TCP/IP Comunicação constante entre os servidores master e slaves, agilizando a transferência de fragmentos dos logs de transação Criação de processos walsender e walreceiver, iniciados nos servidores master e slaves respectivamente File-based Replication Record-based Replication Capacidade de executar funcões sem a necessidade de criá-las Todas as linguagens procedurais podem ser utilizadas em linha de comando Sintaxe: DO [ LANGUAGE nome_linguagem ] código Facilita tarefas de administração Não há necessidade de CREATE/DROP FUNCTION A estrutura das funções são mantidas: [ DECLARE ] BEGIN END PostgreSQL 9.0: Blocos anônimos em PL/pgSQL banco=> DO $$ DECLARE stmt text; BEGIN FOR stmt IN SELECT 'ALTER TABLE ' ||tablename|| ' ADD COLUMNultima_modificacao timestamp;' FROM pg_tables WHERE schemaname = 'dextra' AND tablename NOT LIKE 'payment_%' LOOP EXECUTE stmt; END LOOP; END $$ LANGUAGE plpgsql; PostgreSQL 9.0: Blocos anônimos em PL/pgSQL Triggers disparadas com eventos de UPDATE em colunas Evita condições lógicas e comparação de valores no código da função Não dispara em caso de atualização da coluna para o valor padrão CREATE TRIGGER tg_log_ativo BEFORE UPDATE OF activebool ON customer FOR EACH ROW EXECUTE PROCEDURE log_ativo(); UPDATE customer SET activebool = false WHERE customer_id = 599; SELECT * FROM log_ativo WHERE customer_id = 599; id | customer_id | first_name | activebool | last_modified 600| 599 | AUSTIN | f | 2010-08-28 PostgreSQL 9.0: Triggers por colunas Comparação que define se a trigger sera executada Reduz o número de execuções das funções de trigger Elimina estruturas de condição do código da função CREATE TRIGGER tg_log_ativo BEFORE UPDATE OF activebool ON customer FOR EACH ROW WHEN (OLD.activebool IS DISTINCT FROM NEW.activebool) EXECUTE PROCEDURE log_ativo(); UPDATE customer SET activebool = true WHERE customer_id = 599; UPDATE customer SET activebool = true WHERE customer_id = 599; SELECT * FROM log_ativo WHERE customer_id = 599; id | customer_id | first_name | activebool | last_modified 606| 599 | AUSTIN | t | 2010-08-28 PostgreSQL 9.0: Triggers condicionais Facilidade para alterações de privilégios em massa Concedendo permissão de consulta para todas as tabelas do schema dextra ao usuário foobar: GRANT SELECT ON ALL TABLES IN SCHEMA dextra TO foobar; Definição de privilégios padrões para futuros objeto Exemplo: Concedendo permissão de consulta para todas as futuras tabelas do schema dextra ao usuário foobar: ALTER DEFAULT PRIVILEGES FOR ROLE dextra IN SCHEMA dextra GRANT SELECT ON TABLES TO foobar; PostgreSQL 9.0: Privilégios de usuários PostgreSQL 9.0: Novo VACUUM FULL O processo de VACUUM FULL é mais rápido, pois duplica a tabela, eliminando a original e recriando os índices – Não é mais necessária a execução do REINDEX após o VACUUM FULL – Mais espaço em disco VACUUM FULL 8.4 pagila=# VACUUM FULL actor; Time: 217613.377 ms pagila=# REINDEX TABLE actor; Time: 81567.277 ms VACUUM FULL 9.0 pagila=# VACUUM FULL actor; Time: 98295.479 ms A ferramenta pg_migrator esta presente no contrib com o nome de pg_upgrade – Permite a migração através dos datafiles, sem a necessidade dump/restore – Torna o processo de migração muito rápido, reduzindo a indisponibilidade do sistema export OLDDATADIR=/dados/postgresql9.0/ export NEWDATADIR=/dados/postgresql9.1/ export OLDBINDIR=/usr/local/pgsql/bin/ export NEWBINDIR=/usr/local/bin/ pg_upgrade > upgrade.log PostgreSQL 9.0: Ferramenta de migração Restrições UNIQUE postergáveis EXPLAIN – Visualização das informações de uso de buffer pela consulta – Definição de outros formatos para o resultado Suporte completo para Windows 64bits Suporte para autenticação com RADIUS (Remote Authentication Dial In User Service) Contrib pg_archivecleanup para limpeza de arquivos de log de transação PostgreSQL 9.0: Outras melhorias PostgreSQL 9.1 PostgreSQL 9.1: Replicação síncrona Este recurso permite a atualização dos servidores slaves de forma sincronizada com o servidor master – Simples configuração – Pode ser controlado por sessão – Consistência de dados entre servidor master e slaves – Possibilidade de definição de prioridades para replicação dos dados SET synchronous_commit = on; Feedback View pg_stat_replication SELECT application_name AS appname,state,sent_location,write_location,flush_location, replay_location,sync_priority,sync_state FROM pg_stat_replication; appname|state|sent_location|write_location|flush_location|replay_location|sync_state --------+-----------+------------+------------+------------+------------+------ slave02 | streaming | 0/390000F0 | 0/390000F0 | 0/390000F0 | 0/390000F0 | async slave01 | streaming | 0/390000F0 | 0/390000F0 | 0/390000F0 | 0/390000F0 | sync View pg_stat_database_conflicts SELECT * FROM pg_stat_database_conflicts; View pg_stat_database, campo conflicts SELECT datname, conflicts FROM pg_stat_database; PostgreSQL 9.1: Monitoramento da replicação Funcionalidade que possibilita o desenvolvimento de plugins para acessar qualquer fonte de dados a partir do PostgreSQL – Padrão SQL/MED – Possibilita a integração de sistemas – Importação de dados facilitada para datawarehouses CREATE EXTENSION mysql_fdw; CREATE SERVER mysql_svr FOREIGN DATA WRAPPER mysql_fdw OPTIONS (address '127.0.0.1', port '3306'); CREATE USER MAPPING FOR PUBLIC SERVER mysql_svr OPTIONS (username 'mysql_user', password 'senha'); CREATE FOREIGN TABLE customer_mysql ( customer_id SMALLINT, store_id BIGINT, first_name VARCHAR(45), last_name VARCHAR(45), SERVER mysql_svr OPTIONS (table 'sakila.customer'); SELECT * FROM customer JOIN customer_mysql USING (customer_id); PostgreSQL 9.1: Fontes de dados externas Funcionalidade que possibilita a alteração de tabelas sem a gravação nos logs de transação – Maior performance para operações de escrita na tabela – Não são recuperadas após um crash e não são replicadas CREATE TABLE foo(id int); CREATE UNLOGGED TABLE bar(id int); 100.000 registros com INSERT time psql -f inserts_foo.sql 1m40.155s time psql -f inserts_bar.sql 0m16.631s 100.000 registros com COPY time psql -f inserts_foo.sql 0m2.204s time psql -f inserts_bar.sql 0m0.738s PostgreSQL 9.1: Tabelas não logadas Facilita o gerenciamento de ferramentas do diretório contrib do PostgreSQL – Comando CREATE EXTENSION e ALTER EXTENSION Instalação cd /usr/local/src/postgresql-9.1.1/contrib/dblink make make install Criação da extensão postgres=# CREATE EXTENSION dblink ; CREATE EXTENSION Visualização das extensões instaladas postgres=# SELECT extname,extversion FROM pg_extension ; extname | extversion ---------+------------ plpgsql | 1.0 dblink | 1.0 PostgreSQL 9.1: Extensões Extensão para novas classes de operadores Antes CREATE INDEX foobar_btree_idx ON foobar(nome); EXPLAIN SELECT count(*) FROM foobar WHERE nome LIKE '%MAT%'; QUERY PLAN ---------------------------------------------------------------- Aggregate (cost=1741.03..1741.04 rows=1 width=0) -> Seq Scan on foobar (cost=0.00..1741.00 rows=10 width=0) Filter: (nome ~~ '%MAT%'::text) Depois CREATE EXTENSION pg_trgm ; CREATE INDEX foobar_gin_idx ON foobar USING gin (nome gin_trgm_ops); EXPLAIN SELECT count(*) FROM foobar WHERE nome LIKE '%MAT%'; QUERY PLAN ---------------------------------------------------------------- Aggregate (cost=51.95..51.96 rows=1 width=0) -> Bitmap Heap Scan on foobar (cost=16.08..51.92 rows=10 width=0) Recheck Cond: (nome ~~ '%MAT%'::text) -> Bitmap Index Scan on foobar_gin_idx (cost=0.00..16.07 rows=10 width=0) Index Cond: (nome ~~ '%MAT%'::text) PostgreSQL 9.1: Otimização de busca (LIKE/ILIKE) Triggers em visões Utilitário para executar backup online (pg_basebackup) Definição de collation por coluna Implementação do verdadeiro nível de isolamento serializable Implementação do algoritmo nearest-neighbor para índices GiST Processo de validação para chaves estrangeiras Recursividade para operações de escrita SEPostgreSQL PostgreSQL 9.1: Outras melhorias PostgreSQL 9.2 (devel) PostgreSQL 9.2: Replicação em cascata Possibilita que servidores slaves sejam replicados a partir de outro slave – Redução do impacto de vários processos wal sender em produção O backup base online poderá ser executadoa partir de servidores read-only PostgreSQL 9.2: Dados diretamente de índices Consulta poderá varrer apenas o índice – Não será necessário buscar dados na tabela – Planos de execuções melhores para consultas que retornam as colunas disponíveis em algum índice CREATE INDEX foobar_idx ON foobar(id,nome); EXPLAIN SELECT id, nome FROM foobar WHERE id = 10; QUERY PLAN ---------------------------------------------------------------------- Index Only Scan using foobar_idx on foobar (cost=0.00..8.28 rows=1 width=11) Index Cond: (id = 10) Suporte a tipos de dados temporais (range types) Melhoria de performance para COUNT(*) Processo de validação para restrições CHECK Alteração de tipos em coluna indexada sem necessidade de REINDEX Melhoria no tratamento de erros em PL/pgSQL PostgreSQL 9.2: Outras melhorias Conclusão Funcionalidades importantes para ambientes de missão crítica Evolução constante Comunidade ativa O banco de dados opensource mais avançado do mundo Fale conosco Matheus Ricardo Espanhol matheus.espanhol@dextra.com.br www.dextra.com.br São Paulo 11 3051.7711 Campinas 19 3256.6722 Treinamento www.facebook.com.br/dextratreinamentos Sistemas www.facebook.com.br/dextrasis Treinamento @dextracursos Sistemas @dextrasistemas Slide 1 Slide 2 Slide 3 Slide 4 Slide 5 Slide 6 Slide 7 Slide 8 Slide 9 Slide 10 Slide 11 Slide 12 Slide 13 Slide 14 Slide 15 Slide 16 Slide 17 Slide 18 Slide 19 Slide 20 Slide 21 Slide 22 Slide 23 Slide 24 Slide 25 Slide 26 Slide 27 Slide 28 Slide 29 Slide 30 Slide 31 Slide 32 Slide 33 Slide 34 Slide 35 Slide 36 Slide 37 Slide 38 Slide 39 Slide 40 Slide 41 Slide 42 Slide 43 Slide 44 Slide 45 Slide 46 Slide 47 Slide 48 Slide 49
Compartilhar