backup e restore no PostgreSQL

BANCO DE DADOS WEB
AULA 8
backup e restore no PostgreSQL
professor Luciano Roberto Rocha
www.lrocha.com
Procedimentos de backup/restore
Como tudo que contém dados importantes, devem ser
feitas cópias de segurança dos bancos de dados do
PostgreSQL regularmente.
Embora o procedimento seja essencialmente simples, é
importante possuir uma compreensão básica das técnicas e
princípios subjacentes.
O PostgreSQL nos disponibiliza três ferramentas para
realização de cópias de segurança: pg_dump, pg_dumpall e
pg_restore.
As ferramentas acima descritas são programas executáveis
externos (não comandos) e podem ser encontrados na pasta
bin dentro do diretório de instalação do PostgreSQL.
2
O utilitário pg_dump
O pg_dump é um aplicativo para fazer cópia de
segurança de um banco de dados do PostgreSQL. São
feitas cópias de segurança consistentes, mesmo que o
banco de dados esteja sendo utilizado ao mesmo tempo.
O pg_dump não bloqueia os outros usuários que estão
acessando o banco de dados (leitura ou escrita).
A idéia por trás do Método SQL-dump é gerar um arquivo
texto contendo comandos SQL que, ao serem processados
pelo servidor, recriam o banco de dados no mesmo estado
em que este se encontrava quando o arquivo foi gerado.
A forma básica de utilização deste programa é:
pg_dump nome_do_banco_de_dados > arquivo_de_saída
3
O utilitário pg_dump
As cópias de segurança geradas pelo pg_dump podem ser
feitas no formato de script, personalizado ou tar.
As cópias de segurança no formato de script são arquivos de
texto puro, contendo os comandos SQL necessários para
reconstruir o banco de dados no estado em que este se
encontrava quando foi salvo.
Como estes scripts contém um conjunto de instruções SQL,
para restaurar os dados a partir destes scripts, deve ser
utilizado o terminal psql .
Os arquivos de script podem ser utilizados para reconstruir o
banco de dados até em outras máquinas com outras
arquiteturas; com algumas modificações, até mesmo em
outros produtos gerenciadores de banco de dados SQL.
4
Realizar cópias de segurança
Exemplos:
Para salvar um banco de dados:
$ pg_dump -U nome_do_usuário meu_bd > db.out
Para restaurar este banco de dados:
$ psql -U nome_do_usuário -d banco_de_dados -f db.out
Parâmetros:
-d nome_do_banco_de_dados
nome do banco de dados a se conectar.
-f nome_do_arquivo
o arquivo de backup que contém os comandos SQL (script)
-U nome_do_usuário
o usuário dono do banco a ser acessado ou um superusuário.
5
Utilizar formatos personalizados
Os formatos personalizado e tar devem ser utilizados com o
aplicativo pg_restore para reconstruir o banco de dados.
Estes formatos permitem que o pg_restore selecione o que será
restaurado, ou mesmo reordene os itens antes de restaurá-los, e
também permitem salvar e restaurar os "objetos grandes", o que
não é possível com a cópia de segurança em arquivo de script.
O formato de arquivo de saída mais flexível é o "personalizado"
(-Fc) permite a seleção e a reordenação de todos os itens da
cópia de segurança, e é comprimido por padrão.
O formato “tar” (-Ft) não é comprimido e não permite
reordenar os dados ao restaurar, mas por outro lado pode ser
manipulado pelas ferramentas padrão do Unix, como o tar ou
mesmo o winrar.
6
Utilizar formatos personalizados
O comando abaixo gera a cópia de segurança do banco de
dados utilizando o formato personalizado (custom dump format):
pg_dump -U usuário –Fc nome_do_banco_de_dados > nome_do_arquivo
Parâmetros:
F formato
Seleciona o formato da saída. O formato pode ser um dos seguintes:
p
Gera um arquivo de script SQL no formato texto-puro (o padrão)
t
Gera um arquivo tar adequado para servir de entrada para o pg_restore. A utilização deste
formato de arquivo permite reordenar e/ou excluir objetos do banco de dados ao fazer a
restauração.
c
Gera um arquivo personalizado adequado para servir de entrada para o pg_restore.
Este é o formato mais flexível, porque permite a reordenação da restauração dos dados,
assim como das definições dos objetos. Além disso, este formato é comprimido por padrão.
7
Utilizar formatos personalizados
O formato de cópia de segurança personalizado não é um script
para o psql, devendo ser restaurado pelo pg_restore.
Para restaurar o backup do banco de dados criado
anteriormente deve-se usar o seguinte comando:
pg_restore -U usuário -d
novo_banco_de_dados nome_do_arquivo
Parâmetros:
-d novo_banco_de_dados
Seleciona o banco de dados no qual os dados serão restaurados.
nome_do_arquivo
O arquivo que contém a cópia de segurança personalizada.
-U usuário
O nome do usuário do PostgreSQL dono do novo banco de dados ou um
superusuário.
8
Restauração seletiva de dados
Para reordenar os itens do banco de dados, primeiro é necessário criar um
arquivo contendo a lista de conteúdo (índice) da cópia de segurança:
pg_restore -l backup.arquivo > backup.list
O arquivo de listagem consiste de um cabeçalho e uma linha para cada
item iniciado por um número que corresponde ao seu identificador interno
de arquivamento:
; Archive created at Fri Jul 28 22:28:36 2000
;
dbname: birds
;
TOC Entries: 74
;
Compression: 0
;
Dump Version: 1.4-0
;
Format: CUSTOM
;
; Selected TOC Entries:
;
2; 145344 TABLE species postgres
3; 145344 ACL species
6; 145402 TABLE species_records postgres
8; 145416 TABLE ss_old postgres
9; 145416 ACL ss_old
10; 145433 TABLE map_resolutions postgres
11; 145433 ACL map_resolutions
12; 145443 TABLE hs_old postgres
13; 145443 ACL hs_old
9
Restauração seletiva de dados
Cada linha do arquivo pode ser transformada em comentário,
excluída ou reordenadas. Por exemplo:
10; 145433 TABLE map_resolutions postgres
;2; 145344 TABLE species postgres
;4; 145359 TABLE nt_header postgres
6; 145402 TABLE species_records postgres
;8; 145416 TABLE ss_old postgres
Se o arquivo backup.list for usado como entrada do pg_restore,
somente serão restaurados os itens 10 e 6, nesta ordem:
pg_restore -d banco -L backup.list
backup.arquivo
Onde:
-L arquivo_da_listagem
Restaura apenas os elementos presentes no arquivo_da_listagem, e na ordem
que aparecem neste arquivo.
10
Backup de bancos grandes
Como o PostgreSQL permite a existência de tabelas maiores
do que o tamanho máximo de arquivo do sistema
operacional, pode ser problemático fazer a cópia de
segurança de uma tabela como esta em um arquivo, uma vez
que o arquivo resultante provavelmente terá um tamanho
maior que o máximo permitido pelo sistema operacional.
Para superar esses possíveis problemas podemos realizar
cópias de segurança comprimidas, usando qualquer
programa de compressão disponível em modo texto.
Uma boa opção é utilizar o programa de compressão
padrão do sistema Unix chamado gzip que também está
disponível para Windows.
11
Backup de bancos grandes
Exemplo:
Para realizar um backup comprimido passamos o nome do programa
compactador como parâmetro por meio do operador | (pipe):
pg_dump -U postgres empresa
|gzip > c:\backup\empresa_bkp.gz
Para restaurar utilizamos o utilitário gunzip:
gunzip -U postgres -c c:\backup\empresa_bkp.gz |psql empresa
No Windows o aplicativo gunzip.exe nada mais é do que uma cópia
de gzip.exe renomeada, para isso execute o seguinte comando no
prompt:
copy gzip.exe gunzip.exe
12
Salvar todos os bancos de dados
O aplicativo pg_dumpall permite salvar todos os bancos de
dados de um agrupamento do PostgreSQL em um arquivo de
script.
O pg_dumpall também salva os objetos globais, comuns a
todos os bancos de dados (O pg_dump não salva estes
objetos).
Como o pg_dumpall lê tabelas de todos os bancos de
dados, muito provavelmente será necessário se conectar
como um superusuário para poder gerar uma cópia
completa.
Também será necessário o privilégio de superusuário para
executar o script produzido, para poder criar usuários e
grupos, e para poder criar os bancos de dados.
13
Salvar todos os bancos de dados
O script SQL é escrito na saída padrão. Devem ser
usados operadores de linha de comando para
redirecionar para um arquivo:
Exemplos:
Para salvar todos bancos de dados:
$ pg_dumpall > db.out
Para recarregar este banco de dados deve ser utilizado, por
exemplo:
$ psql -f db.out template1
Neste caso o banco de dados a se conectar não tem importância,
porque o arquivo de script criado pelo pg_dumpall contém os comandos
apropriados para criar e conectar aos bancos de dados salvos.
14
Salvar todos os bancos de dados
O pg_dumpall precisa conectar várias vezes ao servidor
PostgreSQL (uma vez para cada banco de dados). Se for
utilizada autenticação por senha, provavelmente será solicitada
a senha cada uma destas vezes. Neste caso é conveniente criar
o arquivo .pgpass (Unix) ou pgpass.conf (Windows)
O arquivo .pgpass, armazenado na pasta base (home) do
usuário, é um arquivo que contém senhas a serem utilizadas se a
conexão requisitar uma senha (e a senha não tiver sido
especificada de outra maneira). No Microsoft Windows o
arquivo se chama %APPDATA%\postgresql\pgpass.conf (onde
%APPDATA% se refere ao subdiretório de dados do aplicativo no perfil
do usuário).
Exemplo:
C:\Users\Marcio\APPDATA\roaming\posgresql\pgpass.conf
15
Salvar todos os bancos de dados
O arquivo pgpass deve conter linhas com o seguinte formato:
nome_do_host:porta:nome_do_banco:nome_do_usuário:senha
Os quatro primeiros valores podem ser um texto, ou * para
corresponder a qualquer coisa. Ex.:
localhost:5432:*:postgres:12345
As permissões de acesso ao arquivo .pgpass não devem
permitir o acesso por todos os usuários ou para grupos; isto é
conseguido pelo comando chmod 0600 ~/.pgpass. Se as
permissões forem mais rígidas que esta, o arquivo será ignorado
(Entretanto,atualmente as permissões não são
verificadas no Microsoft Windows).
16