Criação e restauração de cópias de segurança no PostgreSQL

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.

Existem duas abordagens fundamentalmente diferentes para fazer cópia de segurança dos dados do PostgreSQL:
· Método SQL-dump;
· Cópia de segurança no nível de sistema operacional.

1. Método SQL-dump

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. O PostgreSQL disponibiliza o programa utilitário pg_dump para esta finalidade. A forma básica de utilização deste programa é:

pg_dump nome_do_banco_de_dados > arquivo_de_saída

Conforme pode ser visto, o programa pg_dump escreve o seu resultado na saída padrão. Será visto abaixo como isto pode ser útil.

O pg_dump é uma aplicação cliente normal do PostgreSQL (embora seja particularmente astuta). Isto significa que o procedimento de cópia de segurança pode ser realizado a partir de qualquer hospedeiro remoto que possua acesso ao banco de dados. Porém, deve ser lembrado que o pg_dump não opera com permissão especial. Em particular, é necessário possuir acesso de leitura a todas as tabelas que se deseja fazer cópia de segurança. Portanto, na prática, quase sempre é necessário ser um superusuário do banco de dados.

Para especificar qual servidor de banco de dados o pg_dump deve se conectar, devem ser utilizadas as opções de linha de comando -h hospedeiro e -p porta. O hospedeiro padrão é o hospedeiro local, ou o que estiver especificado na variável de ambiente PGHOST. De maneira semelhante, a porta padrão é indicada pela variável de ambiente PGPORT ou, na falta desta, pelo padrão de compilação (Por conveniência, o servidor normalmente é compilado usando o mesmo padrão).

Assim como qualquer outra aplicação cliente do PostgreSQL, o pg_dump se conecta por padrão ao banco de dados com o usuário cujo nome é igual ao nome do usuário corrente do sistema operacional. Para que seja outro, deve ser especificada a opção -U, ou definida a variável de ambiente PGUSER. Não deve ser esquecido que as conexões do pg_dump estão sujeitas aos mecanismos normais de autenticação de cliente.

As cópias de segurança criadas pelo pg_dump são consistentes internamente, ou seja, as atualizações feitas no banco de dados enquanto o pg_dump está executando não estão resentes na cópia de segurança. O pg_dump não bloqueia outras operações no banco de dados enquanto está executando (Exceto as operações que necessitam operar com modo de bloqueio exclusivo, como o VACUUM FULL).

Importante: Quando o esquema do banco de dados é dependente dos OIDs (como chaves estrangeiras, por exemplo) deve-se instruir o pg_dump para que também inclua os OIDs. Para que isto seja feito, deve ser utilizada a opção de linha de comando -o. Também, não são feitas cópias de segurança dos "objetos grandes" por padrão. Se forem utilizados objetos grandes, deve ser vista a página de referência do programa pg_dump.

1.1. Restauração da cópia de segurança

Os arquivos texto criados pelo programa pg_dump são feitos para serem lidos pelo programa psql. A forma geral do comando para restaurar uma cópia de segurança é:

psql nome_do_banco_de_dados < arquivo_de_entrada

onde o arquivo_de_entrada é o que foi utilizado como arquivo_de_saída pelo programa pg_dump. O banco de dados nome_do_banco_de_dados não será criado por este comando, devendo ser criado a partir de template0 antes de executar o psql (por exemplo, usando createdb -T template0 nome_do_banco_de_dados). O psql possui opções semelhantes às do pg_dump para controlar a identificação do servidor de banco de dados e o nome do usuário. Para obter mais informações deve ser vista a página de referência do programa psql.

Se no banco de dados original os objetos pertencem a usuários diferentes, então a cópia de segurança instrui o psql a se conectar como cada usuário afetado por vez, e depois criar os objetos relevantes. Desta forma o dono original é preservado. Entretanto, isto também significa que todos estes usuários devem existir, e que é possível se conectar como cada um deles. Portanto, pode ser necessário fazer um relaxamento temporário das definições de autenticação de clientes.

Uma vez feita a restauração, é sensato executar o comando ANALYZE em cada um dos bancos de dados, para que o otimizador possua estatísticas úteis. Uma forma fácil de se fazer é executando vacuumdb -a -z para efetuar o VACUUM ANALYZE de todos os bancos de dados; equivale a executar VACUUM ANALYZE manualmente.

A capacidade do pg_dump e do psql de escrever e ler de pipes torna possível replicar um banco de dados de um servidor para outro diretamente; por exemplo:

pg_dump -h hospedeiro1 nome_do_banco_de_dados | psql -h hospedeiro2 nome_do_banco_de_dados

Importante: As cópias de segurança produzidas pelo pg_dump são relativas a template0. Isto significa que todas as linguagens, procedimentos, etc. adicionados a template1 também serão incluídos na cópia de segurança feita pelo pg_dump. Como resultado, se estiver sendo utilizado um banco de dados template1 personalizado, ao ser feita a restauração deve ser criado um banco de dados vazio a partir de template0, conforme mostrado no exemplo acima.

Dica: O desempenho da restauração pode ser melhorado pelo aumento do parâmetro de configuração sort_mem.

1.2. Utilização do pg_dumpall

O mecanismo mostrado acima não é cômodo nem apropriado para fazer a cópia de segurança de um agrupamento de bancos de dados. Por este motivo é fornecido o programa pg_dumpall. O pg_dumpall faz a cópia de segurança de todos os bancos de dados de um agrupamento, e também salva dados de todo o agrupamento, como os usuários e grupos. A forma básica de utilização deste programa é:

pg_dumpall > arquivo_de_saída

A cópia de segurança gerada pode ser restaurada pelo psql usando:

psql template1 < arquivo_de_entrada

(Na verdade, pode ser especificado qualquer nome de banco de dados existente para começar, mas se estiver sendo feita a restauração em um agrupamento vazio, então template1 é a única escolha disponível). É sempre necessário possuir acesso de superusuário do banco de dados para fazer a restauração de uma cópia de segurança gerada pelo pg_dumpall, para poder restaurar as informações de usuário e de grupo.

1.3. Tratamento de bancos de dados grandes

Uma vez que 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 será maior do que o tamanho máximo permitido pelo sistema operacional. Como o pg_dump pode escrever na saída padrão, podem ser utilizadas as ferramentas padrão do Unix para superar este possível problema.

Utilização de cópias de segurança comprimidas: Pode ser utilizado o programa de compressão favorito como, por exemplo, o gzip.

pg_dump nome_do_banco_de_dados | gzip > nome_do_arquivo.gz

Restaurar com

createdb nome_do_banco_de_dados

gunzip -c nome_do_arquivo.gz | psql nome_do_banco_de_dados

ou

cat nome_do_arquivo.gz | gunzip | psql nome_do_banco_de_dados

Utilização do comando split: O comando split permite dividir a saída em blocos de tamanho aceitável para o sistema de arquivos subjacente. Por exemplo, para fazer blocos de 1 megabyte:

pg_dump nome_do_banco_de_dados | split -b 1m - nome_do_arquivo

Restaurar com

createdb nome_do_banco_de_dados
cat nome_do_arquivo | psql nome_do_banco_de_dados

Utilização de formatos de cópia de segurança personalizados. Se o PostgreSQL foi construído em um sistema com a biblioteca de compressão zlib instalada, o formato de cópia de segurança personalizado comprime os dados ao escrever o arquivo de saída. Produz cópias de segurança com tamanhos semelhantes às produzidas utilizando o gzip, mas tem a vantagem adicional de permitir a restauração seletiva das tabelas. O comando abaixo gera a cópia de segurança do banco de dados utilizando o formato de cópia de segurança personalizado (custom dump format):

pg_dump -Fc nome_do_banco_de_dados > nome_do_arquivo

O formato de cópia de segurança personalizado não é um script para o psql, devendo ser restaurado pelo pg_restore. Para obter detalhes devem ser vistas as referências do pg_dump e do pg_restore.

1.4. Precauções

O pg_dump (e consequentemente o pg_dumpall) possui algumas poucas limitações causadas pela dificuldade de reconstruir certas informações a partir dos catálogos do sistema.

Especificamente, a ordem utilizada pelo pg_dump para escrever os objetos não é muito sofisticada. Isto pode causar problemas como, por exemplo, quando são utilizadas funções como valor padrão de colunas. A única solução é reorganizar manualmente a cópia de segurança. Se forem criadas dependências circulares no esquema, então haverá mais trabalho a ser feito.

Por motivo de compatibilidade com as versões anteriores, o pg_dump não faz cópia de segurança dos objetos grandes por padrão. Para fazer cópia de segurança dos objetos grandes deve ser utilizado o formato de saída personalizado ou o formato tar, e utilizada a opção -b do pg_dump. Para obter detalhes deve ser vista a referência do pg_dump. O diretório contrib/pg_dumplo, da árvore do código fonte do PostgreSQL, também contém um programa que pode ser utilizado para fazer cópias de segurança dos objetos grandes.

Por favor, se familiarize com a referência do pg_dump.