Barman: Servidor de Backup (e Restore) para o PostgreSQL

O Barman é um programa para facilitar Backup e Restore do PostgreSQL.
A ideia é que ele seja instalado em outro servidor que não seja o PostgreSQL, e os backups são executados a partir dele, e armazenados nele, que pode centralizar os backups de vários servidores PostgreSQL.

Instalei duas VMs com Debian 8, cada uma com:
– /boot de 1GB, SWAP de 16GB, o restante no /.
– Usuário: Nome Completo “Ricardo Portilho Proni”, Nome “ricardo”.
– SSH e Serviços Padrão de Administração.

Uma destas VMs será o PostgreSQL Server, e a outra será o Barman.

Nas duas VMs, executei a atualização do Debian, habilitei sudo para o meu usuário, e instalei o VirtualBox Guest Additions:

ricardo@PostgreSQL-01:~$ su -
root@PostgreSQL-01:~# apt -y update; apt -y upgrade; apt -y install vim sudo; usermod -aG sudo ricardo
...
root@PostgreSQL-01:~# reboot
...
ricardo@PostgreSQL-01:~$ sudo apt install linux-headers-$(uname -r) gcc make perl
...
ricardo@PostgreSQL-01:~$ sudo mount /dev/sr0 /mnt/
ricardo@PostgreSQL-01:~$ sudo /mnt/VBoxLinuxAdditions.run
...
ricardo@PostgreSQL-01:~$ sudo reboot

Nas duas VMs, configurei os arquivos /etc/hosts de acordo com os IPs que estou utilizando:

ricardo@PostgreSQL-01:~$ sudo cat /etc/hosts
127.0.0.1       localhost
127.0.1.1       PostgreSQL-01.localdomain       PostgreSQL-01
192.168.1.45    PostgreSQL-01.localdomain       PostgreSQL-01
192.168.1.43    Barman.localdomain              Barman

# The following lines are desirable for IPv6 capable hosts
::1     localhost ip6-localhost ip6-loopback
ff02::1 ip6-allnodes
ff02::2 ip6-allrouters
ricardo@PostgreSQL-01:~$
ricardo@Barman:~$ sudo cat /etc/hosts
127.0.0.1       localhost
127.0.1.1       Barman.localdomain              Barman
192.168.1.45    PostgreSQL-01.localdomain       PostgreSQL-01
192.168.1.43    Barman.localdomain              Barman

# The following lines are desirable for IPv6 capable hosts
::1     localhost ip6-localhost ip6-loopback
ff02::1 ip6-allnodes
ff02::2 ip6-allrouters
ricardo@Barman:~$

Nas duas VMs, criei o arquivo abaixo, habilitando o o repositório do PostgreSQL:

ricardo@PostgreSQL-01:~$ sudo cat /etc/apt/sources.list.d/pgdg.list
deb http://apt.postgresql.org/pub/repos/apt/ jessie-pgdg main

ricardo@PostgreSQL-01:~$ wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -                                                      
ricardo@PostgreSQL-01:~$ sudo apt update

Na VM do PostgreSQL, instalei o PostgreSQL Server 9.6 e o rsync:

ricardo@PostgreSQL-01:~$ sudo apt -y install postgresql-9.6
ricardo@PostgreSQL-01:~$ sudo apt -y install rsync

Na VM do Barman, instalei o barman:

ricardo@Barman:~$ sudo apt -y install barman

Na VM do PostgreSQL, criei um usuário de PostgreSQL para o barman:

postgres@PostgreSQL-01:~$ createuser --interactive -P barman
Digite a senha para a nova role:
Digite-a novamente:
A nova role poderá criar um super-usuário? (s/n) s
postgres@PostgreSQL-01:~$

No arquivo de configuração do PostgreSQL (para a versão que usei, /etc/postgresql/9.6/main/postgresql.conf), deixe as entradas abaixo…

#listen_addresses = 'localhost'
#wal_level = minimal
#archive_mode = off
#archive_command = ''

… desta forma:

listen_addresses = '*'
wal_level = replica
archive_mode = on
archive_command = 'rsync -a %p barman@Barman:/var/lib/barman/PostgreSQL-01/incoming/%f'

No arquivo de controle de autenticação do PostgreSQL (para a versão que usei, /etc/postgresql/9.6/main/pg_hba.conf), crie uma linha como abaixo (esta linha permite que qualquer IP consiga se conectar no PostgreSQL desde que tenha um usuário e senha válido – pode ser que você queira restringir os IPs):

host     all             all             0.0.0.0/0               md5

Reinicie o PostgreSQL:

ricardo@PostgreSQL-01:~$ sudo systemctl restart postgresql

Na VM do PostgreSQL, coloque uma senha para o usuário postgres do Linux:

ricardo@PostgreSQL-01:~$ sudo passwd postgres
Digite a nova senha UNIX:
Redigite a nova senha UNIX:
passwd: senha atualizada com sucesso
ricardo@PostgreSQL-01:~$

Na VM do Barman, coloque uma senha para o usuário barman do Linux:

ricardo@Barman:~$ sudo passwd barman
Digite a nova senha UNIX:
Redigite a nova senha UNIX:
passwd: senha atualizada com sucesso
ricardo@Barman:~$

Na VM do Barman, crie um arquivo com a senha do usuário barman do PostgreSQL:

barman@Barman:~$ cat .pgpass
PostgreSQL-01:5432:*:barman:Nerv2020

barman@Barman:~$ chmod 0600 .pgpass
barman@Barman:~$

Na VM do PostgreSQL, gere a chave de ssh do usuário postgres do Linux (dê ENTER em todas as perguntas), e a copie para a VM Barman:

postgres@PostgreSQL-01:~$ ssh-keygen -t rsa
Generating public/private rsa key pair.
Enter file in which to save the key (/var/lib/postgresql/.ssh/id_rsa):
Created directory '/var/lib/postgresql/.ssh'.
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in /var/lib/postgresql/.ssh/id_rsa.
Your public key has been saved in /var/lib/postgresql/.ssh/id_rsa.pub.
The key fingerprint is:
9b:ac:c9:df:2b:a0:aa:6d:4f:79:09:9a:62:d3:0c:a1 postgres@PostgreSQL-01
The key's randomart image is:
+---[RSA 2048]----+
|                 |
|                 |
| .               |
|. .              |
|E.  .   S        |
|  +o o.o o       |
|.oooo.o.+        |
|.oo..o o..       |
|.ooo. +...o.     |
+-----------------+
postgres@PostgreSQL-01:~$ ssh-copy-id barman@Barman
The authenticity of host 'barman (192.168.1.43)' can't be established.
ECDSA key fingerprint is 4f:0e:1a:9c:d3:19:2c:87:c2:13:32:ab:71:15:65:b8.
Are you sure you want to continue connecting (yes/no)? yes
/usr/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any that are already installed
/usr/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed -- if you are prompted now it is to install the new keys
barman@barman's password:

Number of key(s) added: 1

Now try logging into the machine, with:   "ssh 'barman@Barman'"
and check to make sure that only the key(s) you wanted were added.

postgres@PostgreSQL-01:~$

Na VM do Barman, gere a chave de ssh do usuário barman do Linux (dê ENTER em todas as perguntas), e a copie para a VM PostgreSQL:

barman@Barman:~$ ssh-keygen -t rsa
Generating public/private rsa key pair.
Enter file in which to save the key (/var/lib/barman/.ssh/id_rsa):
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in /var/lib/barman/.ssh/id_rsa.
Your public key has been saved in /var/lib/barman/.ssh/id_rsa.pub.
The key fingerprint is:
5b:78:21:a0:1d:29:7a:ef:ee:13:06:91:c1:cf:f3:60 barman@Barman
The key's randomart image is:
+---[RSA 2048]----+
|   ..oo.         |
|    =o.o         |
|   ..=. . .      |
|  . o E  o .     |
|   . + +S o      |
|      + .+       |
|     o ..        |
|      o          |
|     oo.         |
+-----------------+
barman@Barman:~$ ssh-copy-id postgres@PostgreSQL-01
The authenticity of host 'postgresql-01 (192.168.1.45)' can't be established.
ECDSA key fingerprint is 4f:0e:1a:9c:d3:19:2c:87:c2:13:32:ab:71:15:65:b8.
Are you sure you want to continue connecting (yes/no)? yes
/usr/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any that are already installed
/usr/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed -- if you are prompted now it is to install the new keys
postgres@postgresql-01's password:

Number of key(s) added: 1

Now try logging into the machine, with:   "ssh 'postgres@PostgreSQL-01'"
and check to make sure that only the key(s) you wanted were added.

barman@Barman:~$

Na VM do Barman, crie um arquivo como abaixo, com os detalhes do Servidor PostgreSQL:

ricardo@Barman:~$ sudo cat /etc/barman.d/PostgreSQL-01.conf
[PostgreSQL-01]
description =  "Production PostgreSQL Server"
conninfo = host=PostgreSQL-01 user=barman dbname=postgres
ssh_command = ssh postgres@PostgreSQL-01
retention_policy = RECOVERY WINDOW OF 4 WEEKS

ricardo@Barman:~$

Na VM do Barman, verifique se está tudo ok:

barman@Barman:~$ barman check PostgreSQL-01
WARNING: No backup strategy set for server 'PostgreSQL-01' (using default 'exclusive_backup').
WARNING: The default backup strategy will change to 'concurrent_backup' in the future. Explicitly set 'backup_options' to silence this warning.
WARNING: No archiver enabled for server 'PostgreSQL-01'. Please turn on 'archiver', 'streaming_archiver' or both
WARNING: Forcing 'archiver = on'
Server PostgreSQL-01:
        PostgreSQL: OK
        is_superuser: OK
        wal_level: OK
        directories: OK
        retention policy settings: OK
        backup maximum age: OK (no last_backup_maximum_age provided)
        compression settings: OK
        failed backups: OK (there are 0 failed backups)
        minimum redundancy requirements: OK (have 1 backups, expected at least 0)
        ssh: OK (PostgreSQL server)
        not in recovery: OK
        systemid coherence: OK
        archive_mode: OK
        archive_command: OK
        continuous archiving: OK
        archiver errors: OK
barman@Barman:~$

Na VM do Barman, execute um backup:

barman@Barman:~$ barman backup PostgreSQL-01
WARNING: No backup strategy set for server 'PostgreSQL-01' (using default 'exclusive_backup').
WARNING: The default backup strategy will change to 'concurrent_backup' in the future. Explicitly set 'backup_options' to silence this warning.
WARNING: No archiver enabled for server 'PostgreSQL-01'. Please turn on 'archiver', 'streaming_archiver' or both
WARNING: Forcing 'archiver = on'
Starting backup using rsync-exclusive method for server PostgreSQL-01 in /var/lib/barman/PostgreSQL-01/base/20200124T130016
Backup start at LSN: 0/8000028 (000000010000000000000008, 00000028)
Starting backup copy via rsync/SSH for 20200124T130016
Copy done (time: 1 second)
Asking PostgreSQL server to finalize the backup.
Backup size: 20.9 MiB
Backup end at LSN: 0/80000F8 (000000010000000000000008, 000000F8)
Backup completed (start time: 2020-01-24 13:00:16.168447, elapsed time: 3 seconds)
Processing xlog segments from file archival for PostgreSQL-01
        000000010000000000000007
        000000010000000000000008
        000000010000000000000008.00000028.backup
barman@Barman:~$

Na VM do Barman, liste os backups:

barman@Barman:~$ barman list-backup PostgreSQL-01
WARNING: No backup strategy set for server 'PostgreSQL-01' (using default 'exclusive_backup').
WARNING: The default backup strategy will change to 'concurrent_backup' in the future. Explicitly set 'backup_options' to silence this warning.
WARNING: No archiver enabled for server 'PostgreSQL-01'. Please turn on 'archiver', 'streaming_archiver' or both
WARNING: Forcing 'archiver = on'
PostgreSQL-01 20200124T130016 - Fri Jan 24 13:00:17 2020 - Size: 36.9 MiB - WAL Size: 0 B
PostgreSQL-01 20200124T130005 - Fri Jan 24 13:00:07 2020 - Size: 36.9 MiB - WAL Size: 32.0 MiB
barman@Barman:~$

Na VM Barman, verifique os detalhes de um backup:

barman@Barman:~$ barman show-backup PostgreSQL-01 20200124T130016
WARNING: No backup strategy set for server 'PostgreSQL-01' (using default 'exclusive_backup').
WARNING: The default backup strategy will change to 'concurrent_backup' in the future. Explicitly set 'backup_options' to silence this warning.
WARNING: No archiver enabled for server 'PostgreSQL-01'. Please turn on 'archiver', 'streaming_archiver' or both
WARNING: Forcing 'archiver = on'
Backup 20200124T130016:
  Server Name            : PostgreSQL-01
  System Id              : 6785531176219540344
  Status                 : DONE
  PostgreSQL Version     : 90616
  PGDATA directory       : /var/lib/postgresql/9.6/main

  Base backup information:
    Disk usage           : 20.9 MiB (36.9 MiB with WALs)
    Incremental size     : 20.9 MiB (-0.00%)
    Timeline             : 1
    Begin WAL            : 000000010000000000000008
    End WAL              : 000000010000000000000008
    WAL number           : 1
    Begin time           : 2020-01-24 13:00:16.083923-03:00
    End time             : 2020-01-24 13:00:17.798204-03:00
    Copy time            : 1 second
    Estimated throughput : 19.0 MiB/s
    Begin Offset         : 40
    End Offset           : 248
    Begin LSN           : 0/8000028
    End LSN             : 0/80000F8

  WAL information:
    No of files          : 0
    Disk usage           : 0 B
    Last available       : 000000010000000000000008

  Catalog information:
    Retention Policy     : VALID
    Previous Backup      : 20200124T130005
    Next Backup          : - (this is the latest base backup)
Você tem mensagem nova de correio em /var/mail/barman
barman@Barman:~$

4 comments

    1. Muito obrigado Clayton.
      Eu escrevi este Post exatamente porque achei o que encontrei um pouco confuso, e apanhei um pouco até conseguir entender e fazer funcionar.

  1. Oi Portilho,

    Pelo que entendi, o barman vai centralizar e também armazenar os backups. Para bases pequenas, acho que vai ajudar, mas no caso de uma base PostgreSQL de 3Tb, acredito que isso será um problema.
    Fazer backups full com certa frequência e transferir toda essa informação pela rede.

    Sabe me dizer se é possível apenas catalogar os backups sem realizar a transferência dos dados pela rede?

    Obrigado!

    1. Oi Thiago.
      O Barman pode ser instalado no próprio servidor PostgreSQL.
      Mas não faz sentido manter o Backup de um banco apenas no próprio servidor, certo?
      Mesmo que exista um Standby, este não substitui o Backup. E este Backup pode ser feito semanalmente, e Backups incrementais feitos durante a semana.

Deixe uma resposta para Clayton Xavier Cancelar resposta

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *

Esse site utiliza o Akismet para reduzir spam. Aprenda como seus dados de comentários são processados.