Coordenação de Operações e Processos Procedimentos

Coordenação de Operações e Processos
Procedimentos Operacionais
Área: Banco de Dados
Categoria: PostgreSQL
Sub-Categoria: Scripts
Elaborador: Clesio Braga - Inovar
CR: COP
Data: 11/29/2006
Título: Script para efetuar o backup dos bancos de dados separadamente
Palavras Chaves: backup, banco de dados, pg_dump, pg_database, psql, postgres
1- Objetivo:
O objetivo deste documento é registrar um importante procedimento criado para realizar backups dos bancos de dados do Postgre banco por
banco, ou seja, através do script em anexo é possível efetuar backups dos bancos de dados em arquivos separados. Isto é muito interessante quando
existem diversos bancos de dados com funções ou características distintas, assim, pode-se copiar o arquivo de backup e restaurar facilmente sem
maiores complicações, ou mesmo, enviar para o cliente quando necessário.
2- Definições:
pg_dump
Nome
pg_dump -- salva um banco de dados do PostgreSQL em um arquivo de script ou de outro tipo
Sinopse
pg_dump [opção...] [nome_do_banco_de_dados]
Descrição
O pg_dump é um utilitário 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 concorrentemente. O pg_dump não bloqueia os outros usuários que
estão acessando o banco de dados (leitura ou escrita).
As cópias de segurança podem ser feitas no formato de script ou em outros formatos. As cópias de segurança no formato de script são
arquivos no formato texto puro, contendo os comandos SQL necessários para reconstruir o banco de dados no estado em que este se
encontrava quando foi salvo. Para restaurar a partir destes scripts, deve ser utilizado o 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.
Os formatos de arquivo de cópia de segurança alternativos devem ser utilizados com o 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. Os
formatos alternativos de cópia de segurança 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. As formatos de cópia de segurança alternativos também são projetados para serem portáveis
entre arquiteturas diferentes.
Quando usado com um dos formatos de cópia de segurança alternativos, e combinado com o pg_restore, o pg_dump fornece um
mecanismo flexível para cópias de segurança e transferência. O pg_dump pode ser usado para fazer a cópia de segurança de todo o
banco de dados e, posteriormente, o pg_restore pode ser usado para examinar a cópia de segurança e/ou selecionar as partes do
banco de dados a serem restauradas. O formato de arquivo de saída mais flexível é o "personalizado" (custom, -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 a restaurar, mas por outro lado é bastante flexível; além disso, pode ser manipulado pelas ferramentas
padrão do Unix, como o tar.
Ao executar o pg_dump a saída deve ser examinada à procura de advertências (escritas na saída de erro padrão), com atenção
especial às limitações mostradas abaixo.
Opções
As seguintes opções de linha de comando controlam o conteúdo e o formato da saída:
nome_do_banco_de_dados
Especifica o nome do banco de dados a ser salvo. Se não for especificado, é utilizada a variável de ambiente
PGDATABASE. Caso esta variável não esteja definida, é utilizado o nome do usuário especificado para a conexão.
-a
--data-only
Salva somente os dados, não salva o esquema (definições de dado). Esta opção só faz sentido para o formato textopuro. Para os formatos alternativos esta opção pode ser especificada ao chamar o pg_restore.
-b
--blobs
Inclui os objetos grandes na cópia de segurança. Deve ser selecionado um formato de saída não-texto.
-c
--clean
Inclui comandos para remover (drop) os objetos do banco de dados antes dos comandos para criá-los. Esta opção só
faz sentido para o formato texto-puro. Para os formatos alternativos esta opção pode ser especificada ao chamar o
pg_restore.
-C
--create
Inicia a saída por um comando para criar o banco de dados e conectar ao banco de dados criado (Com um script assim
não importa qual banco de dados se está conectado antes de executar o script). Esta opção só faz sentido para o
formato texto-puro. Para os formatos alternativos a opção pode ser especificada ao chamar o pg_restore.
-d
--inserts
Salva os dados como comandos INSERT, em vez de COPY. Torna a restauração muito lenta; sua utilização principal é
para fazer cópias de segurança que possam ser carregadas em outros bancos de dados que não o PostgreSQL. Deve
ser observado que a restauração pode falhar inteiramente se a ordem das colunas tiver sido modificada. A opção -D é
mais segura, mas ainda mais lenta.
-D
--column-inserts
--attribute-inserts
Salva os dados como comandos INSERT explicitando os nomes das colunas (INSERT INTO tabela (coluna, ...) VALUES
...). Torna a restauração muito lenta; sua utilização principal é para fazer cópias de segurança que possam ser
carregadas em outros bancos de dados que não o PostgreSQL.
-f arquivo
--file=arquivo
Envia a saída para o arquivo especificado. Se for omitido é usada a saída padrão.
-F formato
--format=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 (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. Também é possível limitar
os dados a serem recarregados 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.
Também, este formato é comprimido por padrão.
-i
--ignore-version
Ignora a diferença de versão entre o pg_dump e o servidor de banco de dados. O pg_dump pode tratar bancos de dados
de versões anteriores do PostgreSQL, mas as versões muito antigas não são mais suportadas (atualmente as anteriores
a 7.0). Esta opção deve ser utilizada se for necessário desconsiderar a verificação de versão (mas se o pg_dump não
for bem-sucedido, não diga que não foi avisado).
-n esquema
--schema=esquema
Salva apenas o conteúdo do esquema. Se esta opção não for especificada, todos os esquemas no banco de dados
especificado (fora os do sistema) são salvos.
Nota: Neste modo, o pg_dump não tenta salvar os demais objetos de banco de dados que os objetos no
esquema selecionado possam depender. Portanto, não existe nenhuma garantia que o resultado de salvar um
único esquema possa, por si próprio, ser bem-sucedido quando restaurado em um banco de dados vazio.
-o
--oids
Salva os identificadores de objeto (OIDs) de todas as tabelas como parte dos dados. Esta opção deve ser usada quando
a coluna OID é referenciada de alguma maneira (por exemplo, em uma restrição de chave estrangeira). Caso contrário,
esta opção não deve ser usada.
-O
--no-owner
Não gera comandos para definir o dono dos objetos correspondendo ao do banco de dados original. Por padrão, o
pg_dump emite os comandos ALTER OWNER ou SET SESSION AUTHORIZATION para definir o dono dos objetos de
bancos de dados criados. Estes comandos não são bem-sucedidos quando o script é executado, a menos que o script
seja executado por um superusuário (ou o mesmo usuário que possui todos os objetos presentes no script). Para gerar
um script que possa ser restaurado por qualquer usuário, mas que torna este usuário o dono de todos os objetos, deve
ser especificada a opção -O. Esta opção só faz sentido para o formato texto-puro. Para os formatos alternativos a opção
pode ser especificada ao chamar o pg_restore.
-R
--no-reconnect
Esta opção está obsoleta, mas ainda é aceita para manter compatibilidade com as versões anteriores.
-s
--schema-only
Salva somente o esquema (definições dos dados), não os dados.
-S nome_do_usuário
--superuser=nome_do_usuário
Especifica o nome de usuário do superusuário a ser usado para desabilitar os gatilhos. Somente é relevante quando é
usada a opção --disable-triggers (Geralmente é melhor não utilizar esta opção e, em vez disso, executar o script
produzido como um superusuário).
-t tabela
--table=tabela
Salva somente os dados da tabela. É possível existirem várias tabelas com o mesmo nome em esquemas diferentes; se
este for o caso, todas as tabelas correspondentes serão salvas. Deve ser especificado tanto --schema quanto --table
para selecionar apenas uma tabela.
Nota: Neste modo, o pg_dump não tenta salvar os demais objetos de banco de dados que a tabela
selecionada possa depender. Portanto, não existe nenhuma garantia que o resultado de salvar uma única
tabela possa, por si próprio, ser bem-sucedido quando restaurado em um banco de dados vazio.
-v
--verbose
Especifica o modo verboso, fazendo o pg_dump colocar comentários detalhados sobre os objetos e os tempos de
início/fim no arquivo de cópia de segurança, e mensagens de progresso na saída de erro padrão.
-x
--no-privileges
--no-acl
Impede salvar os privilégios de acessos (comandos GRANT/REVOKE).
-X disable-dollar-quoting
--disable-dollar-quoting
Esta opção desabilita a utilização do caractere cifrão ($) para delimitar o corpo das funções, obrigando a utilização da
sintaxe para cadeia de caracteres do padrão SQL.
-X disable-triggers
--disable-triggers
Esta opção somente é relevante ao criar um arquivo de cópia de segurança somente de dados. Faz o pg_dump incluir
comandos para desabilitar, temporariamente, os gatilhos das tabelas de destino enquanto os dados são recarregados.
Deve ser utilizado quando existem verificações de integridade referencial, ou outros gatilhos nas tabelas, que não se
deseja que sejam chamados durante a recarga dos dados. Atualmente, os comandos emitidos para a opção --disabletriggers devem ser executados por superusuários. Portanto, também deve ser especificado o nome de um superusuário
com a opção -S ou, de preferência, executar, com cuidado, o script produzido como um superusuário. Esta opção só faz
sentido para o formato texto-puro. Para os formatos alternativos esta opção pode ser especificada ao chamar o
pg_restore.
-X use-set-session-authorization
--use-set-session-authorization
Gera comandos SET SESSION AUTHORIZATION do padrão SQL em vez dos comandos OWNER TO. Isto torna a
cópia de segurança mais compatível com o padrão, mas dependendo da disposição dos objetos na cópia de segurança
pode não restaurar de forma apropriada.
-Z 0..9
--compress=0..9
Especifica o nível de compressão a ser usado nas cópias de segurança com formatos que suportam compressão
(atualmente somente o formato personalizado suporta compressão).
As seguintes opções de linha de comando controlam os parâmetros de conexão com o servidor de banco de dados:
-h hospedeiro
--host=hospedeiro
Especifica o nome de hospedeiro da máquina onde o servidor está executando. Se o nome iniciar por uma barra (/) é
usado como o diretório do soquete do domínio Unix. O padrão é obter o nome a partir da variável de ambiente
PGHOST, se esta estiver definida, senão tentar uma conexão pelo soquete do domínio Unix.
-p porta
--port=porta
Especifica a porta TCP, ou a extensão do arquivo de soquete do domínio Unix local, onde o servidor está atendendo as
conexões. O padrão é obter o valor a partir da variável de ambiente PGPORT, se esta estiver definida, senão usar o
valor padrão compilado.
-U nome_do_usuário
Conectar como o usuário especificado.
-W
Força a solicitação da senha, o que deve acontecer automaticamente quando o servidor requer autenticação por senha.
Ambiente
PGDATABASE
PGHOST
PGPORT
PGUSER
Parâmetros de conexão padrão.
Diagnósticos
O pg_dump executa internamente comandos SELECT. Se acontecerem problemas ao executar o pg_dump, deve-se ter certeza que é
possível selecionar as informações no banco de dados utilizando, por exemplo, o utilitário psql .
Observações
Se o agrupamento de bancos de dados tiver alguma adição local ao banco de dados template1, deve-se ter o cuidado de restaurar a
saída do pg_dump em um banco de dados totalmente vazio; senão, podem acontecer erros devido à duplicidade de definição dos
objetos adicionados. Para criar um banco de dados vazio, sem nenhuma adição local, deve-se fazê-lo partir de template0, e não de
template1 como, por exemplo:
CREATE DATABASE foo WITH TEMPLATE template0;
O pg_dump possui algumas poucas limitações:
Ao salvar uma única tabela, ou no formato texto-puro, o pg_dump não trata os objetos grandes. Os objetos grandes devem
ser salvos juntamente com todo o banco de dados usando um dos formatos de cópia de segurança não-texto.
Quando é escolhido salvar apenas os dados, e se utiliza a opção --disable-triggers, o pg_dump emite comandos para
desabilitar os gatilhos das tabelas do usuário antes de inserir os dados, e comandos para reabilitá-los após os dados terem
sido inseridos. Se a restauração for interrompida antes do fim, os catálogos do sistema podem ser deixados em um estado
errado.
Os membros de arquivos tar estão limitados a um tamanho inferior a 8 GB (esta limitação é inerente ao formato dos arquivos tar).
Portanto, este formato não pode ser utilizado se a representação textual de uma tabela exceder este tamanho. O tamanho total do
arquivo tar, e dos outros formatos de saída, não possui limitação exceto, talvez, pelo sistema operacional.
Os arquivos de cópia de segurança produzidos pelo pg_dump não contêm as estatísticas utilizadas pelo otimizador para fazer as
decisões de planejamento dos comandos. Portanto, é aconselhável executar o ANALYZE após restaurar de uma cópia de segurança
para garantir um bom desempenho.
Exemplos
Para salvar um banco de dados:
$ pg_dump meu_bd > db.out
Para recarregar este banco de dados:
$ psql -d banco_de_dados -f db.out
Para salvar o banco de dados chamado meu_bd contendo objetos grandes em um arquivo tar:
$ pg_dump -Ft -b meu_bd > db.tar
Para recarregar este banco de dados (com os objetos grandes) em um banco de dados existente chamado novo_bd:
$ pg_restore -d novo_bd db.tar
Histórico
O pg_dump apareceu pela primeira vez no Postgres95 versão 0.02. Os formatos de saída não-texto-puro foram introduzidos no
PostgreSQL versão 7.1.
Consulte também
pg_dumpall , pg_restore , psql
Scheduled Tasks
You can use Task Scheduler to schedule commands, programs, or scripts to run at specific times. (Recurso do sistema operacional Windows)
Schedule 1
Criar a batch que irá realizar o backup dos bancos de dados.
Name:
Run:
Start in:
Run as:
Recorrente:
Start time:
Preparar Batch Backup PostgreSQL
letradriver:\backupdb\schebkppost.bat
letradriver:\backupdb
<Domínio>\<user administrator>
Diário.
02:00 AM
Schedule 2
Executar o backup dos bancos de dados. É necessário que o Schedule 1 execute primeiro para geração correta da batch. Outro
detalhe, é que a batch será criada no caminho onde se encontra o pg_dump, ou seja, PostgreSQL\8.1\bin.
Name:
Run:
Start in:
Run as:
Recorrente:
Start time:
Executar Batch Backup PostgreSQL
letradriver:\PostgreSQL\8.1\bin\execbkppost.bat
letradriver:\PostgreSQL\8.1\bin\
<Domínio>\<user administrator>
Diário.
02:05 AM
3- Descrição:
Nota: schebkppost.bat
A batch schebkppost deve ser criada ou mesmo copiada para letradriver:\backupdb\, depois de feito isto, deve-se ainda criar o schedule para
a chamada da batch conforme apresentado nas
definições acima.
Os destaques em azul devem ser substituídos de acordo com o servidor que será implementado o procedimento.
É necessário que as pastas estejam de acordo com os padrões adotados. O código da batch está em anexo abaixo, assim é possível utilizálo para criar o arquivo schebkppost.bat se preferir, mas deve-se observar os caminhos dos diretórios.
Arquivo batch: schebkppost.bat
A batch schebkppost se posiciona no diretório do PostgreSQL para submeter o comando psql e gerar a batch que fará os backups, que por
sua vez, também se posiciona no diretório do PostgreSQL\8.1\Bin devido ao comando pg_dump.
letradriverpostgre:
CD \
CD PostgreSQL\8.1\Bin
psql -U postgres -t -f letradriver:\Backupdb\listdbbackup.sql >letradriverpostgre:\PostgreSQL\8.1\Bin\execbkppost.bat
Arquivo sql: listdbbackup.sql
A batch schebkppost utiliza o arquivo listdbbackup.sql que obtem como resultado o comando para proceder com o pg_dump, que é o
recurso utilizado para submeter backups no PostgreSQL. O conteúdo do arquivo é mostrado abaixo.
SELECT 'pg_dump -i -h localhost -p 5432 -U postgres -F c' || REPLACE(TRIM(' -b -v -f letradriver:/Backupdb/Postgre/ '),
'/', chr(92)) || datname || '_db_' || to_char(current_timestamp, 'YYYYMMDDHH24MI') || '.BAK ' || datname AS col1 FROM
pg_database WHERE datname not in ('template1', 'template0')
Resultado do arquivo sql: listdbbackup.sql
Um exemplo do resultado obtido com a execução do script listdbbackup.sql pode ser observado abaixo.
pg_dump -i -h localhost -p 5432 -U postgres -F c-b -v -f D:\Backupdb\Postgre\postgres_db_200612041722.BAK postgres
pg_dump -i -h localhost -p 5432 -U postgres -F c-b -v -f D:\Backupdb\Postgre\bd123_db_200612041722.BAK bd123
pg_dump -i -h localhost -p 5432 -U postgres -F c-b -v -f D:\Backupdb\Postgre\mob123_db_200612041722.BAK mob123
Nota: execbkppost.bat
A batch execbkppost é simplesmente o resultado do script listdbbackup.sql.
A execbkppost será criada a partir do Schedule 1 através da batch schebkppost e não é preciso se preocupar com esse arquivo, deve-se
sim, criar o schedule para a chamada da batch conforme apresentado nas definições acima do Schedule 2.
Segue abaixo apenas para demonstração que o conteúdo da batch execbkppost.bat.
pg_dump -i -h localhost -p 5432 -U postgres -F c-b -v -f D:\Backupdb\Postgre\postgres_db_200612041722.BAK postgres
pg_dump -i -h localhost -p 5432 -U postgres -F c-b -v -f D:\Backupdb\Postgre\bd123_db_200612041722.BAK bd123
pg_dump -i -h localhost -p 5432 -U postgres -F c-b -v -f D:\Backupdb\Postgre\mob123_db_200612041722.BAK mob123