serpro_funcionalidade_spostgresql

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