PostgreSQL Prático/Administração/Backup e Restore

9.1 - Backup e RestoreEditar

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 (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:

  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


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