---
title: "HeliosDB Backup & Recovery Guide"
---

# HeliosDB Backup & Recovery Guide

---

## Backup Strategies

### Full Backup

```sql
-- One-time full backup
SELECT pg_basebackup(
  directory => '/backups/full_backup',
  format => 'tar',
  compression => 'gzip'
);

-- Scheduled backups
CREATE BACKUP SCHEDULE daily_backup AS
  FREQUENCY = '1 day'
  START TIME = '02:00'
  RETENTION = '30 days'
  STORAGE LOCATION = '/backups/daily';
```

### Incremental Backup

```sql
-- Incremental backups (faster)
ALTER BACKUP SCHEDULE daily_backup SET
  TYPE = 'INCREMENTAL',
  FULL_BACKUP_FREQUENCY = '7 days';
```

### PITR (Point-in-Time Recovery)

```sql
-- Enable WAL archiving for PITR
ALTER SYSTEM SET
  wal_level = replica,
  archive_mode = on,
  archive_command = 'cp %p /archive/%f';

SELECT pg_reload_conf();
```

---

## Restore Operations

### Full Restore

```bash
# Stop the server
systemctl stop postgresql

# Restore from backup
pg_basebackup -D /var/lib/postgresql/data -r /backups/full_backup

# Start server
systemctl start postgresql
```

### Point-in-Time Restore

```sql
-- Restore to specific time
SELECT restore_to_point_in_time(
  target_timeline => 'latest',
  target_time => '2025-01-15 14:30:00'::TIMESTAMP,
  recovery_target_action => 'promote'
);

-- Restart with recovery
pg_ctl restart -D /data -c recovery_target_timeline=latest
```

### Selective Restore

```sql
-- Restore specific table
SELECT restore_table(
  table_name => 'important_data',
  from_backup => '/backups/before_incident.tar',
  to_timestamp => '2025-01-15 10:00:00'::TIMESTAMP
);
```

---

## Monitoring Backups

```sql
-- Backup history
SELECT
  backup_id,
  backup_type,
  backup_size_gb,
  backup_time,
  backup_duration_minutes,
  status,
  retention_until
FROM backup_history
ORDER BY backup_time DESC;

-- Backup health check
SELECT * FROM backup_health_check;
```

---

## Testing Backups

```sql
-- Verify backup integrity
SELECT verify_backup('/backups/full_backup');

-- Test restore (non-destructive)
SELECT test_restore_backup('/backups/full_backup');
```

---

## Best Practices

1. Regular full backups (daily)
2. Incremental backups (hourly)
3. Test restores monthly
4. Store backups off-site
5. Monitor backup size
6. Verify backup integrity
7. Document restore procedures

---

**Related Documentation:**
- [High Availability Guide](https://www.heliosdb.com/docs/full/guides/user/high_availability_operational_procedures/)