PostgreSQL Prático/Administração/Backup e Restore
9.1 - Backup e Restore
editarAdministraçã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 (lo) por default. Caso desejemos 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 casa 60 segundos. Para alterar descomente e mude a linha:
- 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
No README desta contrib 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.
Backup Automático de Bancos no Windows com o Agendador de Tarefas
Criação do script backuppg.bat:
rem Adaptação de Ribamar FS do original de Ivlison Souza para a lista PostgreSQL Brasil
@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.1\
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.1\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