Restaurar backup do PostgreSQL

# Cenário 1: Restaurar em banco existente (sobrescrever dados)
# ATENÇÃO: isso apaga os dados atuais do banco!

# Opção A: Dropar e recriar o banco antes do restore
sudo -u postgres psql << 'EOF'
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = 'minha_db' AND pid <> pg_backend_pid();

DROP DATABASE IF EXISTS minha_db;
CREATE DATABASE minha_db OWNER minha_app;
EOF

# Restaurar o backup (arquivo .sql ou .sql.gz)
gunzip -c /var/backups/minha_db_20260607.sql.gz \
  | sudo -u postgres psql minha_db

# Cenário 2: Restaurar em banco de teste (sem afetar produção)
sudo -u postgres createdb -O minha_app minha_db_restore_test
gunzip -c /var/backups/minha_db_20260607.sql.gz \
  | sudo -u postgres psql minha_db_restore_test

# Verificar restore:
sudo -u postgres psql minha_db_restore_test -c "
SELECT tablename, n_live_tup
FROM pg_stat_user_tables
ORDER BY n_live_tup DESC;
"

# Backup custom (gerado com pg_dump -Fc):
pg_dump -Fc minha_db > backup.dump

# Restaurar todo o banco:
pg_restore -d minha_db -U minha_app -h localhost backup.dump

# Restaurar apenas tabelas específicas:
pg_restore -d minha_db -t usuarios -t pedidos backup.dump

# Restaurar apenas o schema (sem dados):
pg_restore -d minha_db --schema-only backup.dump

# Restaurar apenas os dados (schema já existe):
pg_restore -d minha_db --data-only backup.dump

# Restaurar em paralelo (mais rápido para bancos grandes):
pg_restore -d minha_db -j 4 backup.dump  # 4 workers paralelos

# Ver conteúdo do backup sem restaurar:
pg_restore -l backup.dump | head -50

# Verbose para debug:
pg_restore -d minha_db -v backup.dump 2>&1 | tail -30

# Migração em pipeline (sem arquivo intermediário) — mais rápido
# Fonte: VPS_ANTIGA | Destino: VPS_NOVA

# No servidor de destino (VPS nova):
# 1. Criar banco e usuário destino
sudo -u postgres psql << 'EOF'
CREATE USER minha_app WITH PASSWORD 'senha_app';
CREATE DATABASE minha_db OWNER minha_app;
EOF

# 2. Executar migração em pipeline (SSH + pg_dump + psql):
ssh usuario@VPS_ANTIGA \
  "PGPASSWORD=senha pg_dump -U minha_app minha_db" \
  | PGPASSWORD=senha psql -U minha_app -h localhost minha_db

# Alternativa com compressão (para conexões lentas):
ssh usuario@VPS_ANTIGA \
  "PGPASSWORD=senha pg_dump -U minha_app minha_db | gzip" \
  | gunzip | PGPASSWORD=senha psql -U minha_app minha_db

# Verificar integridade após migração:
# No destino:
psql -U minha_app minha_db -c "SELECT count(*) FROM usuarios;"
# Comparar com a fonte:
ssh usuario@VPS_ANTIGA "psql -U minha_app minha_db -c 'SELECT count(*) FROM usuarios;'"

# Cenário: DELETE acidental na tabela pedidos
# Backup disponível: /var/backups/minha_db_20260607.sql.gz

# 1. Extrair apenas a tabela pedidos do backup para arquivo separado
pg_restore -t pedidos -f /tmp/pedidos_only.sql /var/backups/minha_db.dump
# (apenas para backups formato custom -Fc)

# Para backup formato plain (SQL):
# Extrair manualmente com grep/sed entre COPY pedidos e \.
# Ou: restaurar em banco temporário e copiar de lá

# 2. Restaurar em banco temporário
sudo -u postgres createdb minha_db_temp
gunzip -c /var/backups/minha_db_20260607.sql.gz | sudo -u postgres psql minha_db_temp

# 3. Copiar a tabela do backup para produção usando INSERT
sudo -u postgres psql << 'EOF'
-- Truncar tabela atual (se corrompida)
TRUNCATE TABLE minha_db.public.pedidos;

-- Copiar do backup
INSERT INTO minha_db.public.pedidos
SELECT * FROM minha_db_temp.public.pedidos;

-- Verificar
SELECT count(*) FROM minha_db.public.pedidos;
EOF

# 4. Limpar banco temporário
sudo -u postgres dropdb minha_db_temp

# PITR requer configuração prévia de WAL archiving:
# postgresql.conf:
# archive_mode = on
# archive_command = 'aws s3 cp %p s3://meu-bucket/wal/%f'
# restore_command = 'aws s3 cp s3://meu-bucket/wal/%f %p'

# Para fazer PITR após DELETE acidental às 14:32:

# 1. Parar PostgreSQL
sudo systemctl stop postgresql

# 2. Restaurar pg_basebackup (snapshot mais recente antes do incidente)
sudo rm -rf /var/lib/postgresql/17/main/
sudo -u postgres pg_basebackup -D /var/lib/postgresql/17/main -Fp

# 3. Configurar recovery
sudo -u postgres cat > /var/lib/postgresql/17/main/postgresql.auto.conf << EOF
restore_command = 'aws s3 cp s3://meu-bucket/wal/%f %p'
recovery_target_time = '2026-06-07 14:30:00 America/Sao_Paulo'
recovery_target_action = 'promote'
EOF
sudo -u postgres touch /var/lib/postgresql/17/main/recovery.signal

# 4. Iniciar PostgreSQL — ele aplica WAL até o momento especificado
sudo systemctl start postgresql