# Backup de PostgreSQL

Este guia mostra como configurar backup automatizado de bancos PostgreSQL.

# Pré-requisitos

• BackupAlly instalado
• Acesso ao servidor PostgreSQL
• Usuário com permissões de backup

# Criar Usuário de Backup

CREATE USER backup WITH PASSWORD 'senha-segura';
GRANT pg_read_all_data TO backup;
GRANT pg_read_all_settings TO backup;

Para PostgreSQL < 14:

CREATE USER backup WITH PASSWORD 'senha-segura';
ALTER USER backup WITH SUPERUSER;

# Armazenar Credenciais

backupally secrets set pg_user "backup"
backupally secrets set pg_password "senha-segura"

# Criar Task de Backup

# Backup Completo

Crie /etc/backupally/tasks.d/postgres-prod.toml:

[task]
id = "postgres-prod"
name = "PostgreSQL Production"
enabled = true
schedule = "0 3 * * *"

[target]
kind = "database"
driver = "postgres"
host = "localhost"
port = 5432
database = "myapp"
user = "${secret:pg_user}"
password = "${secret:pg_password}"
dump_globals = true
clean_dump = true

[packer]
compressor = "zstd"
compression_level = 6

[[storage]]
id = "local"
driver = "local"
path = "/backup/postgres"
retention = "daily 7, weekly 4"

[[storage]]
id = "s3"
driver = "s3"
bucket = "my-backups"
path = "postgres/${hostname}/${date}"
access_key = "${secret:aws_access_key}"
secret_key = "${secret:aws_secret_key}"
retention = "daily 14, weekly 8, monthly 6"

# Backup Apenas Schema

[target]
kind = "database"
driver = "postgres"
database = "myapp"
schema_only = true

# Backup Apenas Dados

[target]
kind = "database"
driver = "postgres"
database = "myapp"
data_only = true

# Opções Disponíveis

# Validar e Executar

backupally task validate postgres-prod
backupally backup --target postgres-prod

# Restaurar Backup

# Listar Snapshots

backupally history --target postgres-prod

# Restaurar Arquivos

backupally restore \
  --target postgres-prod \
  --snapshot snap_20260211_030000 \
  --path /restore/postgres

# Importar no PostgreSQL

Criar banco se necessário:

createdb -U postgres myapp_restored

Restaurar:

psql -U postgres -d myapp_restored -f /restore/postgres/myapp.sql

Com globals (roles):

psql -U postgres -f /restore/postgres/globals.sql
psql -U postgres -d myapp_restored -f /restore/postgres/myapp.sql

# Backup de Múltiplos Bancos

# Usando Wildcard

[target]
database = "*"
excludes = ["postgres", "template0", "template1"]

# Tasks Separadas

Crie uma task por banco para controle granular:

# postgres-app1.toml
[task]
id = "postgres-app1"
[target]
database = "app1"

# postgres-app2.toml
[task]
id = "postgres-app2"
[target]
database = "app2"

# Configuração de Rede

# Conexão SSL

[target]
host = "db.example.com"
ssl_mode = "require"
ssl_cert = "/etc/ssl/client-cert.pem"
ssl_key = "/etc/ssl/client-key.pem"
ssl_root_cert = "/etc/ssl/ca-cert.pem"

# Timeout

[network]
timeout_seconds = 600

# Monitoramento

[notification]
enabled = true
only_on_error = true

[notification.webhook]
url = "https://hooks.slack.com/services/xxx"

# Troubleshooting

# Erro de Autenticação

Verifique pg_hba.conf:

# IPv4 local connections:
host    all    backup    127.0.0.1/32    scram-sha-256

# Permissão Negada em Tabela

Conceda acesso:

GRANT SELECT ON ALL TABLES IN SCHEMA public TO backup;

# Timeout em Tabelas Grandes

[network]
timeout_seconds = 1800