Funcionalidades PostgreSQL 9.xNovembro/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 conhecimentoe 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 paraos 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

ConsultoriaResoluçã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çãoTransferê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

PostgreSQL9.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ódigoFacilita tarefas de administração

Não há necessidade de CREATE/DROP FUNCTIONA estrutura das funções são mantidas:

[ DECLARE ]BEGINEND

PostgreSQL 9.0: Blocos anônimos em PL/pgSQL

banco=> DO $$DECLAREstmt text;BEGINFOR stmt IN SELECT 'ALTER TABLE ' ||tablename|| ' ADD COLUMNultima_modificacao timestamp;' FROM pg_tables WHERE schemaname = 'dextra'AND tablename NOT LIKE 'payment_%'LOOPEXECUTE 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_modified600| 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_modified606| 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 FULLO 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.4pagila=# VACUUM FULL actor;Time: 217613.377 mspagila=# REINDEX TABLE actor;Time: 81567.277 ms

VACUUM FULL 9.0pagila=# 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

PostgreSQL9.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 dadosSET synchronous_commit = on;

Feedback

View pg_stat_replicationSELECT 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 | asyncslave01 | streaming | 0/390000F0 | 0/390000F0 | 0/390000F0 | 0/390000F0 | sync

View pg_stat_database_conflictsSELECT * FROM pg_stat_database_conflicts;

View pg_stat_database, campo conflictsSELECT 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 datawarehousesCREATE 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 INSERTtime psql -f inserts_foo.sql1m40.155stime psql -f inserts_bar.sql0m16.631s100.000 registros com COPYtime psql -f inserts_foo.sql0m2.204stime psql -f inserts_bar.sql0m0.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/dblinkmakemake 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

AntesCREATE 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)DepoisCREATE 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

PostgreSQL9.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 executado a 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 CHECKAlteração de tipos em coluna indexada sem necessidade de REINDEXMelhoria 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 Espanholmatheus.espanhol@dextra.com.br

www.dextra.com.brSão Paulo 11 3051.7711Campinas 19 3256.6722

Treinamento www.facebook.com.br/dextratreinamentos

Sistemas www.facebook.com.br/dextrasisTreinamento @dextracursos

Sistemas @dextrasistemas