Olá a todos, esse é o meu primeiro post na comunidade. Para abrir com chave de ouro, vou detalhar aqui os procedimentos necessários a uma perfeita instalação do banco de dados do e-cidades, visto que em bora já exista muito material disponível aqui mesmo no forum bem como o manual de instalação, ainda persistem erros variados que acontecem com a maioria dos colegas com o PostgreSql, muitos destes erros que "quebra" a nossa cabeça impossibilitando o avanço da instalação. Administração: Com uma boa manutenção o administrador melhora o desempenho do SGBD, garante a integridade dos dados, a sua segurança e os próprios dados. Especialmente quem já teve problemas em HDs e não pode recuperar os dados, sabe da importância dos backups. Para efetuar backup e restore utilizamos o comando pg_dump em conjunto com o psql. Obs.: O pg_dump não faz backup de objetos grandes por default. Caso desejamos também estes objetos no backup devemos utilizar uma saída no formato tar e utilizar a opção -b. pg_dump -Ftb banco > banco.tar Backup local de um único banco: pg_dump -U usuario -d banco > banco.sql pg_dump -Ft banco > banco.tar O script normalmente leva a extensão .sql, por convenção, mas pode ser qualquer extensão e o script terá conteúdo texto puro. Restore de um banco local: psql -U usuario -d banco < banco.sql pg_restore -d banco banco.sql pg_restore -d banco banco.tar Obs.: Cuidado ao restaurar um banco, especialmente se existirem tabelas sem integridade. Corre-se o risco de duplicar os registros. Descompactar e fazer o restore em um só comando: gunzip -c backup.tar.gz | pg_restore -d banco ou cat backup.tar.gz | gunzip | pg_restore -d banco (o cat envia um stream do arquivo para o gunzip que passa para o pg_restore) Backup local de apenas uma tabela de um banco: pg_dump -U nomeusuario -d nomebanco -t nometabela > nomescript Restaurar apenas uma tabela Para conseguir restaurar apenas uma tabela uma forma é gerar o dump do tipo com tar: pg_dump -Ft banco -f arquivo.sql.tar pg_restore -d banco -t tabela banco.sql.tar Backup local de todos os bancos: pg_dumpall -U nomeusuario -d nomebanco > nomescript Backup remoto de um banco: pg_dump -h hostremoto -d nomebanco | psql -h hostlocal -d banco Backup em multivolumes (volumes de 200MB): pg_dump nomebanco | split -m 200 nomearquivo m para 1Mega, k para 1K, b para 512bytes Importando backup de versão anterior do PostgreSQL: Instala-se a nova versão com porta diferente (ex.: 5433) e conectar ambos pg_dumpall -p 5432 | psql -d template1 -p 5433 Visualizar comando atual e PID de todos os processos do servidor: SELECT pg_stat_get_backend_pid(s.backendid) AS procpid, pg_stat_get_backend_activity(s.backendid) AS current_query FROM (SELECT pg_stat_get_backend_idset() AS backendid) AS s; Determinação da utilização em disco pelas Tabelas Tendo um banco com cadastro de CEPs e apenas uma tabela “cep_tabela”, mostrar o uso do disco por esta tabela. Precisamos filtrar as tabelas de sistema, veja: VACUUM ANALYZE; O utilitário VACUUM recupera espaço em disco ocupado pelos registros excluídos e atualizados, atualiza os dados para as estatísticas usadas pelo planejador de consultas e também protege contra perda de dados quando atingir um bilhão de transações. SELECT relname, relfilenode, relpages FROM pg_class WHERE relname LIKE 'cep_%' ORDER BY relname; relname | relfilenode | relpages +-------------+---------- cep_pk | 25140 | 2441 cep_tabela | 16949 | 27540 O daemon do auto-vacuum Iniciando na versão 8.1 é um processo opcional do servidor, chamado de autovacuum daemon, cujo uso é para automatizar a execução dos comandos VACUUM e ANALYZE. Roda periodicamente e checa o uso em baixo nível do coletor de estatísticas. Não pode ser usado enquanto stats_start_collector e stats_row_level forem alterados para true. Portanto o postgresql.conf deve ficar assim: stats_start_collector = on stats_row_level = on autovacuum = on Por default será executado a cada 60 segundos. Para alterar descomente e mude a linha: 1. autovacuum_naptime = 60 Para uma tabela VACUUM ANALYZE tabela; Para todo um banco \c VACUUM FULL ANALYZE; Determinar o uso do disco por tabela SELECT relfinenode, relpages FROM pg_class WHERE relname = 'nometabela' Cada página usa 8kb. Tamanho de Índices SELECT c2.relname, c2.relpages FROM pg_class c, pg_class c2, pg_index i WHERE c.relname = 'customer' AND c.oid = i.indrelid AND c2.oid = i.indexrelid ORDER BY c2.relname; Encontrar as maiores tabelas e índices SELECT relname, relpages FROM pg_class ORDER BY relpages DESC; Veja que no resultado também aparece a tabela de índices, e com uso significativo. Ferramentas Contrib pgbench – testa desempenho do SGBD. dbsize – mostra o tamanho de tabelas e bancos oid2name – retorna OIDs, fileinode e nomes de tabelas D:\ARQUIV~1\POSTGR~1\8.1\bin>oid2name -U postgres -P ******** All databases: Oid Database Name Tablespace 33375 bdcluster ncluster 16948 cep_brasil pg_default 25146 cep_full pg_default 33360 controle_estoque pg_default 16879 municipios pg_default 33340 pgbench pg_default 10793 postgres pg_default 10792 template0 pg_default 33377 template1 pg_default 16898 testes pg_default Existe uma boa sugestão para encontrar o tamanho aproximados dos dados de cada objeto interno do PostgreSQL com: SELECT relpages, relfilenode, relname FROM pg_class ORDER BY relpages DESC; Cada página tem tipicamente 8KB e o relpages é atualizado pelo comando VACUUM. TUTORIAL: Backup Automático de Bancos no Windows com o Agendador de Tarefas Criação do script backuppg.bat: @echo off rem (Nome do Usuário do banco para realizar o backup) REM Dados que precisa alterar: REM PGUSER REM PGPASSWORD REM nome pasta de backup REM nome pasta de instalação do PostgreSQL se diferente de C:\Arquivos de programas\PostgreSQL\8.2\ REM REM (Nome do usuário do PostgreSQL que executará o script) SET PGUSER=postgres rem (Senha do usuário acima) SET PGPASSWORD=****** rem (Indo para a raiz do disco) C: rem (Selecionando a pasta onde será realizada o backup) chdir C:\backup rem (banco.sql é o nome que defini para o meu backup rem (Deletando o backup existente) del banco*.sql echo "Aguarde, realizando o backup do Banco de Dados" rem C:\Arquiv~1\Postgr~1\8.1\bin\pg_dump -i -U postgres -b -o -f "C:\backup\banco.sql" condominio rem Observação: Caso queira colocar o nome do backup seguindo de uma data é só usar: for /f "tokens=1,2,3,4 delims=/ " %%a in ('DATE /T') do set Date=%%b-%%c-%%d rem O comando acima serve para armazenar a data no formato dia-mes-ano na variável Date; C:\Arquiv~1\Postgr~1\8.2\bin\pg_dump -i -U postgres -b -o -f "C:\backup\banco%Date%.sql" condominio rem (sair da tela depois do backup) exit Configuração do Agendador de Tarefas para executar o script diariamente: - Iniciar - Programas - Acessórios - Ferramentas de Sistema - Tarefas agendadas - Adicionar tarefa agendada - Avançar - Clique em procurar e indique o backuppg.bat - Em executar esta tarefa escolha como achar mais adequado (diariamente) e clique em Avançar - Clique em Avançar e OK. Na próxima tela marque "Executar somente se conectado". - Então clique em Concluir - No próximo boot o backup será efetuado a cada dia. Um bom artigo sobre backup e restauração no PostgreSQL encontra-se no site oficial do PostgreSQL do Brasil: https://wiki.postgresql.org.br/wiki/BackupAndRestore Veja também a documentação em inglês: http://www.postgresql.org/docs/8.1/static/app-pgrestore.html http://www.postgresql.org/docs/8.1/static/app-pgdump.html http://www.postgresql.org/docs/8.1/static/app-pg-dumpall.html OBS: TUTORIAL BASEADO NO WINDOWS XP SP3, MAS UTILIZO A MESMA FUNÇÃO NO UNBUTU SERVER COM TAREFAS CRON, mas isso fica pro próximo post!! Até!

Autor: Carlos Jorge