CONSEGI 2010
Oficina PostgreSQL Avanado
Fabrzio de Royes Mello
[email protected]
Apresentao
Fabrzio de Royes Mello ([email protected])
Gerente de Tecnologia
Bacharel em Informtica pela URCAMP
Experincia Profissional5 anos como Gerente de CPD Prefeitura
DBSeller desde 2005
Desenvolvimento de Software desde 1993
Experincia em PostgreSQL desde 1999
Experincia em PHP desde 2002
Desenvolvedor do Software Pblico e-cidade
Agenda
Preparao Servidor
Usurios e Permisses
Backup e Restore
Rotinas de Manuteno
Bsico sobre Monitoramento
Ambiente Oficina
Ubuntu 10.04 Desktop
PostgreSQL 8.4
Fonte: Manoel Pimental Medeiros - visaoagil.wordpress.com
Preparao Servidor
Instalao e Configurao
Usurio postgres sistema Operacional
Entendendo e Trabalhando com Clusters
Iniciando/Parando o Servidor PostgreSQL
Instalao e Configurao
Repositrios Ubuntu 10.04$ su serpro (senha no quadro)$ sudo
apt-get install postgresql-8.4
Verificando se as coisas esto OK!$ pg_lsclusters
Listar Bases de Dados$ psql -U postgres -lOops... erro
autenticao!!!!
Instalao e Configurao
Configurar pg_hba.conf$ sudo vim
/etc/postgresql/8.4/main/pg_hba.confTrocar ident e md5 por trust e
salvar$ sudo /etc/init.d/postgresql-8.4 reload
Listar Bases de Dados (novamente)$ psql -U postgres -l (gravem
isso!!)Agora sim!!!!
Usurio postgres S.O.
Encapsular atividades em um nico usurioUsurio postgres que
executa binrios
Arquivos de configurao e datafiles tem owner e group =
postgres
Uma breve verificao$ ls -al /etc/postgresql/8.4/main$ ls -al
/var/lib/postgresql/8.4$ ps aux | egrep '^postgres'
Entendendo e Trabalhando com Clusters
Cluster = agrupamento de bancos de dados
Gerenciado por um processo servidor"
Listar clusters (debian based - pg_common)$ pg_lsclusters
Criar clusters (debian based - pg_common)$ pg_createcluster
http://www.postgresql.org/docs/8.4/static/app-initdb.html
Apagar clusters (debian based - pg_common)$ pg_dropcluster
Entendendo e Trabalhando com Clusters
ExerccioCriar um cluster chamado consegi
Criar um cluster chamado oficina
Configurar autenticao clusters (pg_hba.conf)
Listar clusters
Apagar cluster oficina
Respostas$ sudo pg_createcluster 8.4 consegi$ sudo
pg_createcluster 8.4 oficina$ sudo vim
/etc/postgresql/8.4/**/pg_hba.conf$ pg_lsclusters$ sudo
pg_dropcluster --stop 8.4 oficina
Iniciando/Parando
Servidor PostgreSQL
Iniciando Servidor$ sudo /etc/init.d/postgresql-8.4 start
Parando Servidor$ sudo /etc/init.d/postgresql-8.4 stop
Reiniciando Servidor$ sudo /etc/init.d/postgresql-8.4
restart
Recarregando Configuraes$ sudo /etc/init.d/postgresql-8.4
reload
http://www.postgresql.org/docs/8.4/static/app-pg-ctl.html
Fonte: Manoel Pimental Medeiros - visaoagil.wordpress.com
Usurios e Permisses
Preparando Ambiente
Criando Usurios
Grupos de Usurios
Definindo Permisses
Autenticao (pg_hba.conf)
Fonte: Manoel Pimental Medeiros - visaoagil.wordpress.com
Preparando Ambiente
wget -c http://bit.ly/aXIywXcreatedb -U postgres
consegi2010bunzip2 -c dump_consegi2010.sql.bz2 | psql -U postgres
consegi2010psql -U postgres -lpsql -U postgres
consegi2010consegi2010=# \d
Fonte: Manoel Pimental Medeiros - visaoagil.wordpress.com
Criando Usurios
ConsideraesUsurios e Grupos no PostgreSQL so chamados
ROLESUsurio = Role Com Login
Grupo = Role Sem Logim
So GLOBAIS para o Cluster inteiro
Podem receber permisses (USAGE, INSERT, UPDATE, DELETE, etc) ou
herdar permisses de outras roles
http://www.postgresql.org/docs/8.4/static/user-manag.html
Fonte: Manoel Pimental Medeiros - visaoagil.wordpress.com
Criando Usurios
Utilitrio createuser$ createuser -U postgres -P consegi
http://www.postgresql.org/docs/8.4/static/app-createuser.html
SQL CREATE ROLE$ psql -U postgres postgres=# CREATE ROLE consegi
LOGIN PASSWORD 'consegi';
http://www.postgresql.org/docs/8.4/static/sql-createrole.html
Fonte: Manoel Pimental Medeiros - visaoagil.wordpress.com
Grupos de Usurios
Utilitrio createuser$ createuser -U postgres -L
grupo_consegi
http://www.postgresql.org/docs/8.4/static/app-createuser.html
SQL CREATE ROLE$ psql -U postgres postgres=# CREATE ROLE
grupo_consegi NOLOGIN;
http://www.postgresql.org/docs/8.4/static/sql-createrole.html
Fonte: Manoel Pimental Medeiros - visaoagil.wordpress.com
Definindo Permisses
GRANT - Concedendo Permisses$ psql -U postgres
consegi2010consegi2010=# GRANT SELECT ON customer TO consegi;
http://www.postgresql.org/docs/8.4/interactive/sql-grant.html
REVOKE - Revogando Permisses$ psql -U postgres
consegi2010consegi2010=# REVOKE SELECT FROM customer TO
consegi;
http://www.postgresql.org/docs/8.4/interactive/sql-revoke.html
Exerccios
Criar 2 Usurios
Criar 1 Grupo
Conceder permisso de SELECT e INSERT para um usurio criado
Conceder permisso de UPDATE para o grupo criado
Conceder permisso do Grupo criado para o outro usurio
Fonte: Manoel Pimental Medeiros - visaoagil.wordpress.com
Autenticao (pg_hba.conf)
Localizao$ psql -U postgres consegi2010consegi2010=# SHOW
hba_file;hba_file
--------------------------------------/etc/postgresql/8.4/main/pg_hba.conf(1
linha)
http://www.postgresql.org/docs/8.4/interactive/client-authentication.html
Fonte: Manoel Pimental Medeiros - visaoagil.wordpress.com
Autenticao (pg_hba.conf)
Editar$ sudo vim /etc/postgresql/8.4/main/pg_hba.conf
OpesType
Database
User
CIDR-ADDRESS
Method
Options
Fonte: Manoel Pimental Medeiros - visaoagil.wordpress.com
Rotinas de Manuteno
A rotina de Vacuum
O processo autovacuum
A rotina de reindex
Manuteno arquivo de log
Fonte: Manoel Pimental Medeiros - visaoagil.wordpress.com
A rotina de Vacuum
Recuperar espao em disco perdido por DELETEs/UPDATEs
Atualizar estatsticas do planejador de consultas (+ANALYZE)
Prevenir falha de ID wraparoundXID de uma transao sempre no
futuro
Valor de 32bits
Erro ocore qdo ocorrer mais de4 bilhes de transaes sem rodar um
VACUUM
Fonte: Manoel Pimental Medeiros - visaoagil.wordpress.com
A rotina de Vacuum
Utilitrio vacuumdb$ vacuumdb -U postgres -v -z consegi2010
http://www.postgresql.org/docs/8.4/interactive/app-vacuumdb.html
SQL VACUUM$ psql -U postgres consegi2010consegi2010=# VACUUM
ANALYZE VERBOSE;
http://www.postgresql.org/docs/8.4/interactive/sql-vacuum.html
Fonte: Manoel Pimental Medeiros - visaoagil.wordpress.com
O processo autovacuum
Realiza manuteno regular de acordo com uso das relaes da base de
dados
Configurao (postgresql.conf)
Como isso ocorre?limiar_vacuum = autovacuum_vacuum_threshold +
autovacuum_vacuum_scale_factor * reltuples
if (pg_stat_*_tables.n_dead_tup > limiar_vacuum)execute o
VACUUM
O mesmo vale para o ANALYZE!!
http://listas.postgresql.org.br/pipermail/pgbr-geral/2010-July/022088.html
Fonte: Manoel Pimental Medeiros - visaoagil.wordpress.com
A rotina reindex
Diminuir inchao (bloat) dos ndices
Melhorar acesso ao ndice
Porque?Uma pgina de disco contm vrias chaves
Se uma chave no for eliminada a pgina inteira continua
alocada
Fonte: Manoel Pimental Medeiros - visaoagil.wordpress.com
A rotina reindex
Utilitrio reindexdb$ reindexdb -U postgres consegi2010
http://www.postgresql.org/docs/8.4/interactive/app-reindexdb.html
SQL REINDEX$ psql -U postgres consegi2010consegi2010=# REINDEX
DATABASE consegi2010;
http://www.postgresql.org/docs/8.4/interactive/sql-reindex.html
Fonte: Manoel Pimental Medeiros - visaoagil.wordpress.com
Manuteno arquivo de log
Onde esto os logs?psql -U postgres consegi2010 -c select name,
setting from pg_settings where name in ( 'data_directory',
'log_directory', 'log_filename')
name | setting
----------------+--------------------------------data_directory |
/var/lib/postgresql/8.4/mainlog_directory | pg_loglog_filename |
postgresql-%Y-%m-%d_%H%M%S.log
http://www.postgresql.org/docs/8.4/interactive/logfile-maintenance.html
Fonte: Manoel Pimental Medeiros - visaoagil.wordpress.com
Backup e Restore
Backup Fsico off-line
Backup Lgico (plain-sql)
Backup Binrio (dump)
Fonte: Manoel Pimental Medeiros - visaoagil.wordpress.com
Backup Fsico off-line
Backup:
$ sudo /etc/init.d/postgresql-8.4 stop
$ time sudo tar cjvf /tmp/pgbkp-fisico-main-20100820.tar.bz2
/var/lib/postgresql/8.4/main /etc/postgresql/8.4/main
ATENO!!! INCLUIR TABLESPACES, SE EXISTIR!!!
Fonte: Manoel Pimental Medeiros - visaoagil.wordpress.com
Backup Fsico off-line
Restore:
$ sudo /etc/init.d/postgresql-8.4 stop$ sudo mv
/etc/postgresql/8.4/main /etc/postgresql/8.4/main.old$ sudo mv
/var/lib/postgresql/8.4/main /var/lib/postgresql/8.4/main.old$ cd
/$ time sudo tar xjvf /tmp/pgbkp-fisico-main-20100820.tar.bz2$ sudo
/etc/init.d/postgresql-8.4 start
Ateno, pode ser necessrio ajustar permisses caso seja uma
instalao diferente!
Fonte: Manoel Pimental Medeiros - visaoagil.wordpress.com
Backup Lgico (plain-sql)
Backup:
$ time pg_dump -U postgres consegi2010 | bzip2 -c >
/tmp/pgbkp-logico-consegi2010-20100820.sql.bz2
Restore:
$ createdb -U postgres consegi2010-restore-logico$ time bunzip2
-c /tmp/pgbkp-logico-consegi2010-20100820.sql.bz2 | psql -U
postgres consegi2010-restore-logico 2> /tmp/erros1.txt
Fonte: Manoel Pimental Medeiros - visaoagil.wordpress.com
Backup Binrio (dump)
Backup:
$ time pg_dump -U postgres -Fc -Z9 consegi2010 -f
/tmp/pgbkp-binario-consegi2010-20100820.backup
Restore:
$ createdb -U postgres consegi2010_restore_bin$ time pg_restore
-U postgres -Fc -v -d consegi2010_restore_bin
/tmp/pgbkp-binario-consegi2010-20100820.backup
Fonte: Manoel Pimental Medeiros - visaoagil.wordpress.com
Bsico sobre Monitoramento
Ferramentas padres *nix
O coletor de estatsticas
Configurando o coletor de estatsticas
Consultando estatsticas coletadas
Uso do disco
Fonte: Manoel Pimental Medeiros - visaoagil.wordpress.com
Ferramentas padres *nix
Verificar espao em Disco
df -hdu
Manipulao de processos
top (ver processos, alterar prioridade, kill)ps, pg_topkill
(NUNCA -9 no postmaster)
Utilitrios
find, grep
Fonte: Manoel Pimental Medeiros - visaoagil.wordpress.com
O coletor de estatsticas
Processo
$ ps aux | grep ^postgres | grep "stats
Configurao
$ sudo vim /etc/postgresql/8.4/main/postgresql.conf
http://www.postgresql.org/docs/8.4/interactive/monitoring-stats.html
http://www.postgresql.org/docs/8.4/interactive/runtime-config-statistics.html
Fonte: Manoel Pimental Medeiros - visaoagil.wordpress.com
Consultando estatsticas coletadas
Vises pg_stat_*
SELECT * FROM pg_stat_activity;SELECT * FROM
pg_stat_user_tables;
http://www.postgresql.org/docs/8.4/interactive/monitoring-stats.html
Fonte: Manoel Pimental Medeiros - visaoagil.wordpress.com
Uso do disco
Sistema Operacional
df -hdu
PostgreSQL
psql -U postgres consegi2010consegi2010=# SELECT
pg_database_size('consegi2010');
consegi2010=# SELECT relname, pg_relation_size(relname) FROM
pg_class WHERE relkind = 'r';
http://www.postgresql.org/docs/8.4/interactive/functions-admin.html#FUNCTIONS-ADMIN-DBSIZE
Algumas Referncias!
Nacionaishttp://www.postgresql.org.br
https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
Internacionaishttp://www.postgresql.org (Site
oficial)http://www.postgresql.org/community/lists/
Dvidas/Crticas/Sugestes!!!
Fabrzio de Royes Mello
[email protected]
http://fabriziomello.blogspot.com
Muokkaa otsikon tekstimuotoa napsauttamalla
Muokkaa jsennyksen tekstimuotoa napsauttamallaToinen
jsennystasoKolmas jsennystasoNeljs jsennystasoViides
jsennystasoKuudes jsennystasoSeitsems jsennystasoKahdeksas
jsennystasoYhdekss jsennystaso
www.dbseller.com.br
