Fazendo um backup contínuo do PostgreSQL (Point-In-Time-Recovery)

Eu separei o post de Backup do PostgreSQL  agora em dois posts para ficar mais fácil de entender a diferença dos dois tipos de backup.


Quando os dados de um banco ficam grandes demais/ou a missão é crítica para fazer o dump da database, é necessário o backup contínuo da database.

O backup contínuo é o backup da qual é feito um backup físico da base (backup de base), as alterações, a cada X minutos, o PostgreSQL salvará em arquivos de segmento incrementais.

Um script ou uma linha de comando copiará o arquivo de segmento para o destino de backup, que pode ser um servidor de arquivos, uma unidade de fita, etc.

É preciso modificar algumas linhas  do postgresql.conf:

archive_command = 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f'  # Unix
archive_command = 'copy "%p" "C:\\server\\archivedir\\%f"'  # Windows

wal_level = archive

archive_mode = on

checkpoint_timeout = 5min 

O banco expandirá o %p para o caminho do arquivo e o %f para o 

nome do arquivo apenas. 

O nome do arquivo é importante na sua restauração.

Aqui eu também dimninuí o tempo máximo de checkpoint (checkpoint_timeout) para 5 minutos. 

 1-) Fazendo o backup de base

O backup de base é o backup físico de toda a base inicial. Depois virão os backup incrementais para fazer as atualizações seguintes a este backup.

ATENÇÃO: Fazer um dump com pg_dump (isso é backup lógico), não vai funcionar. É preciso que o backup seja físico copiando os arquivos fisicamente.

Chame a linha de comando e:

psql -U postgres -c "select pg_start_backup('nomedolabel')"

No Windows geralmente a pasta da database/cluster é a pasta data\ da instalação.

No Unix ela costuma ser /var/lib/pgsql/XXXX/data.
Copie a pasta da database, mesmo com o banco ligado e funcionando.

Depois páre o modo de backup do banco:

psql -U postgres -c "select pg_stop_backup()"

2-) Recuperando um backup contínuo

• Páre o servidor.
• Mova a pasta da database danificada para outro lugar.
• Restaure o backup de base, feito no item 1. Verifique as permissões de arquivos.
• Remova todos os arquivos presentes em pg_xlog/ do backup de base, recém restaurado do item 1.
• Se você tiver os arquivos da pasta pg_xlog/ do cluster danificado, copie para o pg_xlog/ do restore.
• Crie um arquivo recovery.conf no cluster restaurado para indicar o banco a fazer uma restauração. Ele possui várias opções, você deve pelo menos criar a linha restore_command:
  

  restore_command = 'cp /mnt/server/archivedir/%f "%p"' 

• Modifique o pg_hba.conf para  os usuários não poderem se conectar enquanto o servidor se restaura.
• Ligue o servidor. Ele vai entrar em modo de recovery, quando terminar vai renomear o recovery.conf para recovery.done. 
• Depois veja se está tudo correto com o banco. Caso OK modifique o pg_hba.conf novamente,  e dê select pg_reload_conf()  para os usuários se logarem.

O arquivo recovery.conf possui muitas opções. Ele pode, inclusive, restaurar somente até determinada data. Isto é util se alguém dropou uma tabela acidentalmente, por exemplo.
Por isso leia tb:
http://www.postgresql.org/docs/9.4/static/recovery-target-settings.html
http://www.postgresql.org/docs/9.4/static/continuous-archiving.html