Criando os diretórios (lembre do sudo se for o caso):

# mkdir /home/usuario/bkpBD

(crie onde ficar melhor para você e dê o nome que quiser)

# chmod 755 /home/usuario/bkpBD
# mkdir /home/usuario/logs
# chmod 755 /home/usuario/logs

# crontab -e

Coloque no final do arquivo a seguinte linha adaptando para o teu usuário, banco de dados, etc:


Salve e saia do arquivo.

Na linha acima estamos dizendo para fazer um backup todo dia às 02:40 horas da madrugada no diretório /home/usuario/bkpBD salvando o arquivo de backup com o nome perdido.sql.

Os comandos após os sinais de ">>" dizem para salvar um arquivo de log em /home/usuario/bkpBD com o nome de perdido.sql.log e, caso o backup falhar, os dois pipes (||) dizem para executar o próximo comando somente se o anterior falhar, então será acrescentada uma linha (tail -1) com o log do erro no mesmo arquivo e será enviada a mensagem entre aspas para o e-mail indicado.

Caso o backup der certo não será enviada mensagem nenhuma, pois não é necessário.

Para fazer um backup no terminal basta o seguinte comando:

# /usr/bin/pg_dump -U postgres -f /home/usuario/bkpBD/perdido.sql perdido

Será pedida a senha do usuário postgres.

Na linha acima estamos criando um backup completo do banco inteiro com schemas, tabelas, dados, etc, no formato de texto simples/plano.

Prefira esse formato e prefira fazer backups e restores pela linha de comando, pois caso você trabalhar também com o PgAdmin4 evitará o problema de versões (uma versão do PgAdmin não faz o restore feito pelo backup de outra, dependendo da versão).

Caso quiser colocar no Cron somente a linha:


o backup também será executado, mas não fará log em caso de erro. Você pode também criar seu próprio script de backup e chamá-lo no Cron.

Para fazer um restore desse backup do comando acima no terminal:

# psql -U postgres perdido < perdido.sql

Restore no Windows:

C:> psql -U postgres -d perdido -f C:\Users\Usuario\Desktop\perdido.sql

Na linha acima você coloca o caminho onde salvou o arquivo de backup.

psql: use para restaurar a partir de um arquivo de script SQL de texto simples criado usando pg_dump com formato de texto simples, veja os formatos abaixo.

pg_restore: use para restaurar de um arquivo .tar, diretório ou formato personalizado criado usando pg_dump.

Exemplo com pg_dump e pg_restore:

# pg_dump -U postgres -f perdido.dump perdido -Fc
# pg_restore -d perdido /home/usuario/bkpBD/perdido.dump -c -U postgres

# pg_dump -U postgres -f perdido.tar perdido -Ft
# pg_restore -d perdido /home/usuario/bkpBD/perdido.tar -c -U postgres

Formatos possíveis de serem usados com pg_dump (tirado da documentação oficial do PostgreSQL):

-F format --format=format

p plain Produz um arquivo de script SQL de texto simples (o padrão). Backups com esse formato (-Fp ou --format=plain ou sem indicação nenhuma de formato) devem ser restaurados somente com psql.

Veja bem, não confunda o -f (minúsculo; file, arquivo) da linha "/usr/bin/pg_dump -U postgres -f /home/usuario/bkpBD/perdido.sql perdido" com o -F (maiúsculo) que indica o formato.

c custom Produz um arquivo em formato personalizado adequado para entrada em pg_restore. Juntamente com o formato de saída do diretório, este é o formato de saída mais flexível, pois permite a seleção manual e a reordenação de itens arquivados durante a restauração. Este formato é compactado por padrão.

d directory Produz um arquivo em formato de diretório adequado para entrada em pg_restore. Criará um diretório com um arquivo para cada tabela e objeto grande sendo despejado, além de um arquivo de índice que descreve os objetos despejados em um formato legível para o pg_restore.

Um arquivo em formato de diretório pode ser manipulado com ferramentas Unix padrão; por exemplo, os arquivos em um arquivo descompactado podem ser compactados com as ferramentas gzip, lz4 ou zstd. Este formato é compactado usando o padrão gzip e também oferece suporte a dumps paralelos.

t tar Produz um arquivo em formato .tar adequado para entrada em pg_restore. O formato tar é compatível com o formato de diretório: extrair um arquivo no formato tar produz um arquivo válido no formato de diretório. No entanto, o formato tar não oferece suporte à compactação. Além disso, ao usar o formato tar, a ordem relativa dos itens de dados da tabela não pode ser alterada durante a restauração.

Outras explicações

localhost:5432:perdido:postgres:123456 "localhost" é a própria máquina (127.0.0.1), "5432" é a porta onde o Postgres está escutando, "perdido" é o nome do banco de dados e "123456" é a senha, adapte para as suas necessidades. Nesses arquivos .pgpass você pode colocar várias linhas caso tiver vários usuários e/ou bancos de dados, uma linha para cada usuário e/ou banco de dados e portas diferentes, se for o caso.

Pode colocar até uma linha assim:


Essa linha acima diz que o usuário sergtsop está liberado para conectar em localhost em qualquer banco e qualquer porta. Caso for conexão remota é só alterar para o IP correspondente, por exemplo:

RESUMÃO

su -
# vim ~/.pgpass

Coloque dentro a seguinte linha adaptando para tuas necessidades:

localhost:5432:perdido:postgres:123456

Salve e saia do arquivo.

# chmod 600 ~/.pgpass

Veja bem, a permissão 600 é estritamente necessária, senão o arquivo será ignorado.

# mkdir /home/usuario/bkpBD
(crie onde ficar melhor para você e dê o nome que quiser)

# chmod 755 /home/usuario/bkpBD
# mkdir /home/usuario/logs
# chmod 755 /home/usuario/logs
# crontab -e


Salve e saia do arquivo.

Teste mudando o horário, na linha acima, de "40 2" para um horário próximo ao que você está (veja com o comando date no terminal) e aguarde para ver no diretório indicado se o backup foi criado, depois deixe o horário de backup que ficar melhor para você.

O método com o .pgpass é bastante seguro, pois se alguém conseguir invadir teu servidor para ver a senha dentro do .pgpass é porque a vaca já foi para o brejo, mas recomendo utilizar .pgpass somente para backups no próprio servidor (localhost). É fortemente recomendado fazer backups em HDs/SSDs/Storages diferentes.

Outra solução para evitar o problema de senha no Cron é criar um usuário só para fazer dump com o método de autenticação no mínimo md5 no arquivo pg_hba.conf. Porém, esse método não é recomendado por questões de segurança, por questões físicas e envolve bastante conhecimento.

Faça sempre o backup no próprio servidor, compacte (com proteção) e envie para onde precisar usando sftp ou outro meio criptografado. O dump compactado certamente fica muito menor e ainda terá proteção, uma vez que é texto puro.

Evite fazer backups remotamente, mas caso for imprescindível, automatize o backup local, crie um script para compactar o backup e enviá-lo por sftp, ssh, etc. Banco de dados não é para ficar circulando por aí.

Não abordarei este método, pois é fortemente não recomendado, mas para fins didáticos darei umas dicas.

No link abaixo você encontra como alterar o pg_hba.conf, sugiro fazer o que está no link e depois criar o novo usuário e acrescentá-lo no pg_hba.conf para agendar no Cron com o comando crontab -e:

• Configurar Postgresql para conectar com senha criptografada [Dica]

Não me responsabilizo se, por acaso, você não saiba o que está fazendo.

Sugiro você dar uma estudada antes, mas como eu recomendei: prefira o método com o arquivo .pgpass e backups locais.

Criando um novo usuário no PostgreSQL (para versão 12 e acima):

# createuser -D -P -R -S backdump

(dê o nome da sua preferência)

Opções: -D não pode criar novos bancos de dados -P atribui uma senha -R não pode criar novas regras -S não será superusuário (root) O usuário deverá ser criado somente para o fim específico de fazer backup por questões de segurança, inclusive backup remoto.

E depois altere o pg_hba.conf.

# find / | grep pg_hba.conf
(encontrando o caminho do arquivo)

# cd /etc/postgresql/15/main
# vim pg_hba.conf

(usei o vim, use teu editor de texto preferido)

O backup remoto ainda tem o problema de rotas, firewall, etc.

Depois, dependendo, você terá de criar umas regras como usuário postgres (ou outro superusuário do banco) permitindo o usuário backdump fazer backup, algo como:


Não me responsabilizo se, por acaso, você não saiba o que está fazendo. Sugiro você dar uma estudada antes, mas como eu recomendei: prefira o método com o arquivo .pgpass e backups locais. É mais simples de configurar e mais seguro em vários aspectos.

Referências

• PostgreSQL: Documentation: 16: 21.3. Authentication Methods
• PostgreSQL: Documentation: 16: pg_dump
• PostgreSQL: Documentation: 16: 21.1. The pg_hba.conf File
• PostgreSQL: Documentation: 16: createuser
• Configurar Postgresql para conectar com senha criptografada [Dica]
• database - Restore a postgres backup file using the command line? - Stack Overflow
• Entendendo o script de backup do PostgreSQL - Artigos
• CodeGeo: Automatizando backup de uma base PostgreSQL utilizando Cron
• 34.16. O arquivo de senhas - PostgreSQL 14.5