# Database Backup & Restore

Automated daily backups and manual procedures for the PostgreSQL databases on staging and production servers.

## Server Reference

| Environment | Server IP     | Database              | DB User           | App Directory                                   |
| ----------- | ------------- | --------------------- | ----------------- | ----------------------------------------------- |
| Staging     | 69.169.111.27 | amsoil_dlp_staging    | amsoil_staging    | /home/amsoildlp/public_html/aimclear.biz        |
| Production  | 69.169.111.27 | amsoil_dlp_production | amsoil_production | /home/amsoildlp/public_html/amsoil.aimclear.com |

Both databases run on the same InterServer VPS.

| Role       | Server IP     | Purpose                 |
| ---------- | ------------- | ----------------------- |
| Production | 69.169.111.27 | App server + databases  |
| Backup     | 67.217.57.194 | Off-site backup storage |

---

## Automated Daily Backups

A cron job on the production server runs `scripts/server/backup-to-remote.sh` daily at 2:00 AM EST (server timezone: America/Toronto). It dumps both databases, copies config files (`.env.local` and `ecosystem.config.js`), pushes everything to the backup server via rclone over SFTP, and prunes local backups older than 30 days. Email alerts are sent via the Resend API on both success and failure.

### How It Works

1. `pg_dump -Fc` creates compressed custom-format dumps of both databases (atomic - dumps to `.tmp` then renames)
2. Config files (`.env.local` and `ecosystem.config.js`) are copied from both app directories (permissions restricted to root-only)
3. Local backup directories older than 30 days are pruned
4. `rclone copy` pushes today's backup to the remote backup server, then `rclone check` verifies integrity
5. All output is logged with timestamps
6. Email alert sent via Resend API - success confirmation or failure details

The remote backup server accumulates backups independently - `rclone copy` only adds files, never deletes. Remote retention should be managed separately on the backup server if needed.

### Directory Structure

On both the production server and backup server:

```
/backups/amsoil-dlp/
└── daily/
    ├── 2026-02-25/
    │   ├── amsoil_dlp_production.dump
    │   ├── amsoil_dlp_staging.dump
    │   ├── production.env.local
    │   ├── production.ecosystem.config.js
    │   ├── staging.env.local
    │   └── staging.ecosystem.config.js
    ├── 2026-02-24/
    │   └── ...
    └── ...  (30 days retained)
```

### Checking Backup Status

```bash
ssh root@69.169.111.27

# View recent log entries
tail -50 /var/log/amsoil-backup.log

# Check today's backup exists
ls -lh /backups/amsoil-dlp/daily/$(date +%Y-%m-%d)/

# Check what's on the remote backup server
rclone ls backupserver:backups/amsoil-dlp/daily/ | tail -20

# Count how many days of backups exist locally
ls -d /backups/amsoil-dlp/daily/*/ | wc -l
```

### Running a Manual Backup

To run the backup immediately outside the cron schedule:

```bash
ssh root@69.169.111.27
bash /home/amsoildlp/scripts/backup-to-remote.sh
```

The script is idempotent - running it multiple times on the same day overwrites that day's directory.

### Restoring from a Remote Backup

If the production server's local backups are lost, pull from the backup server:

```bash
ssh root@69.169.111.27

# List available remote backups
rclone ls backupserver:backups/amsoil-dlp/daily/

# Copy a specific day's backup to the production server
rclone copy backupserver:backups/amsoil-dlp/daily/2026-02-25 /tmp/restore/

# Then follow the standard restore procedure below using:
#   /tmp/restore/amsoil_dlp_production.dump
```

Or copy directly to your local machine from the backup server:

```bash
scp st74729@67.217.57.194:backups/amsoil-dlp/daily/2026-02-25/amsoil_dlp_production.dump /tmp/
```

### Setup Instructions

Complete these steps on the production server (69.169.111.27) to enable automated backups.

#### 1. Generate SSH Key and Copy to Backup Server

```bash
ssh root@69.169.111.27

# Generate a dedicated key for backups (no passphrase)
ssh-keygen -t ed25519 -f /root/.ssh/backup_key -C "amsoil-backup" -N ""

# Copy the public key to the backup server
ssh-copy-id -i /root/.ssh/backup_key.pub st74729@67.217.57.194

# Test the connection
ssh -i /root/.ssh/backup_key st74729@67.217.57.194 "echo 'SSH OK'"
```

#### 2. Create Backup Directories

```bash
# On production server
ssh root@69.169.111.27
mkdir -p /backups/amsoil-dlp/daily
chmod 700 /backups/amsoil-dlp

# On backup server (paths relative to st74729's home dir)
ssh st74729@67.217.57.194
mkdir -p backups/amsoil-dlp/daily
chmod 700 backups/amsoil-dlp
```

#### 3. Install and Configure rclone

```bash
ssh root@69.169.111.27

# Install rclone
apt-get install -y rclone

# Configure the SFTP remote
rclone config

# Follow the prompts:
#   n) New remote
#   name> backupserver
#   Storage> sftp
#   host> 67.217.57.194
#   user> st74729
#   port> (leave default 22)
#   key_file> /root/.ssh/backup_key
#   (accept defaults for everything else)

# Verify the connection
rclone ls backupserver:backups/amsoil-dlp/
```

#### 4. Deploy the Backup Script

The script is in the repo at `scripts/server/backup-to-remote.sh`. Copy it to the production server:

```bash
# From your local machine
scp scripts/server/backup-to-remote.sh root@69.169.111.27:/home/amsoildlp/scripts/
```

The script is invoked via `bash`, so no `chmod +x` is needed.

#### 5. Test the Script

```bash
ssh root@69.169.111.27
bash /home/amsoildlp/scripts/backup-to-remote.sh

# Verify output
tail -20 /var/log/amsoil-backup.log
ls -lh /backups/amsoil-dlp/daily/$(date +%Y-%m-%d)/
rclone ls backupserver:backups/amsoil-dlp/daily/$(date +%Y-%m-%d)/
```

#### 6. Add Cron Entry

```bash
ssh root@69.169.111.27

# Add the cron job (runs daily at 2:00 AM EST — server is America/Toronto)
(crontab -l 2>/dev/null; echo "0 2 * * * bash /home/amsoildlp/scripts/backup-to-remote.sh") | crontab -

# Verify it's installed
crontab -l
```

#### 7. Configure Log Rotation

```bash
ssh root@69.169.111.27

cat > /etc/logrotate.d/amsoil-backup << 'EOF'
/var/log/amsoil-backup.log {
    weekly
    rotate 12
    compress
    missingok
    notifempty
}
EOF
```

#### 8. Email Alerts via Resend

The script sends email alerts via the Resend API (HTTPS, port 443) since outbound SMTP (port 25) is blocked on the VPS. It reads `RESEND_API_KEY` from the production `.env.local` automatically - no extra configuration needed.

- **On success:** Sends a daily confirmation with dump count, total size, and retention count
- **On failure:** Sends failure details including the line number and last 20 log lines
- **Recipient:** `team-amsoil-dlp@aimclear.com`

If `RESEND_API_KEY` is missing from `.env.local`, alerts are silently skipped and the backup still runs normally.

### rclone Configuration Reference

The rclone config file lives at `/root/.config/rclone/rclone.conf` on the production server. It should contain:

```ini
[backupserver]
type = sftp
host = 67.217.57.194
user = st74729
key_file = /root/.ssh/backup_key
```

---

## Manual Backup (pg_dump)

`pg_dump` is a read-only operation. It takes a consistent snapshot using PostgreSQL's MVCC without locking tables or blocking writes. Safe to run on a live production server at any time.

### Quick Backup

```bash
# SSH into the server
ssh root@69.169.111.27

# Production backup (custom format — compressed, supports selective restore)
sudo -u postgres pg_dump -Fc amsoil_dlp_production -f /tmp/amsoil_dlp_production_$(date +%Y-%m-%d).dump

# Staging backup
sudo -u postgres pg_dump -Fc amsoil_dlp_staging -f /tmp/amsoil_dlp_staging_$(date +%Y-%m-%d).dump

# Verify the dump file
ls -lh /tmp/amsoil_dlp_*.dump
```

### Copy Backup Off-Server

From your local machine:

```bash
scp root@69.169.111.27:/tmp/amsoil_dlp_production_2026-02-25.dump ./backups/
```

### Backup Options

| Flag           | Purpose                                                                  |
| -------------- | ------------------------------------------------------------------------ |
| `-Fc`          | Custom format (compressed, supports selective restore with `pg_restore`) |
| `-Fp`          | Plain SQL text (human-readable `.sql` file, larger)                      |
| `-j 4`         | Parallel dump using 4 jobs (faster for large databases)                  |
| `--no-owner`   | Omit ownership commands (useful when restoring to a different user)      |
| `--no-acl`     | Omit access privilege commands                                           |
| `-t tablename` | Dump a single table                                                      |

### Plain SQL Backup (Alternative)

If you want a readable `.sql` file instead:

```bash
sudo -u postgres pg_dump -Fp amsoil_dlp_production > /tmp/amsoil_dlp_production_$(date +%Y-%m-%d).sql
```

---

## Restore

### Pre-Restore Checklist

1. **Stop the application** to prevent writes during restore
2. **Confirm you have a current backup** of the database you're about to overwrite
3. **Notify the team** - restoring drops all current data and replaces it

### Full Restore to Production

This procedure drops the existing production database and replaces it entirely with the backup.

```bash
# SSH into the server
ssh root@69.169.111.27

# 1. Stop the production application
pm2 stop amsoil-dlp-production

# 2. Disconnect all active sessions from the database
sudo -u postgres psql -c "
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = 'amsoil_dlp_production' AND pid <> pg_backend_pid();"

# 3. Drop and recreate the database
sudo -u postgres dropdb amsoil_dlp_production
sudo -u postgres createdb -O amsoil_production amsoil_dlp_production

# 4. Restore from the dump file
sudo -u postgres pg_restore -d amsoil_dlp_production /tmp/amsoil_dlp_production_2026-02-25.dump

# 5. Verify the restore
sudo -u postgres psql -d amsoil_dlp_production -c "SELECT count(*) FROM \"User\";"
sudo -u postgres psql -d amsoil_dlp_production -c "SELECT count(*) FROM \"Dealer\";"

# 6. Restart the application
pm2 start amsoil-dlp-production

# 7. Verify the app is healthy
sleep 5
curl -sI http://localhost:3001 | head -3
pm2 logs amsoil-dlp-production --lines 20 --nostream
```

### Full Restore to Staging

Same process but targeting the staging database:

```bash
ssh root@69.169.111.27

pm2 stop amsoil-dlp-staging

sudo -u postgres psql -c "
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = 'amsoil_dlp_staging' AND pid <> pg_backend_pid();"

sudo -u postgres dropdb amsoil_dlp_staging
sudo -u postgres createdb -O amsoil_staging amsoil_dlp_staging

sudo -u postgres pg_restore -d amsoil_dlp_staging /tmp/amsoil_dlp_production_2026-02-25.dump

# Fix ownership if restoring a production dump to staging
sudo -u postgres psql -d amsoil_dlp_staging -c "REASSIGN OWNED BY amsoil_production TO amsoil_staging;"

pm2 start amsoil-dlp-staging
```

### Restore to Local Development

To pull a production snapshot into your local dev database:

```bash
# Copy the dump to your dev machine
scp root@69.169.111.27:/tmp/amsoil_dlp_production_2026-02-25.dump /tmp/

# Drop and recreate local database
dropdb amsoil_dlp_dev
createdb -O amsoil_dev amsoil_dlp_dev

# Restore with --no-owner so local user owns everything
pg_restore --no-owner -d amsoil_dlp_dev /tmp/amsoil_dlp_production_2026-02-25.dump

# Fix ownership to local dev user
psql -d amsoil_dlp_dev -c "REASSIGN OWNED BY amsoil_production TO amsoil_dev;"
```

---

## Restore a Single Table

If you only need to restore one table (e.g., after accidental data deletion):

```bash
# List tables in the dump file
sudo -u postgres pg_restore -l /tmp/amsoil_dlp_production_2026-02-25.dump | grep TABLE

# Restore just the Dealer table (--clean drops existing data first)
sudo -u postgres pg_restore -d amsoil_dlp_production \
  --clean --if-exists \
  -t Dealer \
  /tmp/amsoil_dlp_production_2026-02-25.dump
```

---

## Troubleshooting

### "database is being accessed by other users"

Active connections prevent dropping the database. Terminate them first:

```bash
sudo -u postgres psql -c "
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = 'amsoil_dlp_production' AND pid <> pg_backend_pid();"
```

### pg_restore warnings about "role does not exist"

If restoring to a different environment (e.g., production dump → local dev), you'll see warnings about the original database user not existing. These are harmless if using `--no-owner`. To suppress them:

```bash
pg_restore --no-owner --no-acl -d amsoil_dlp_dev /tmp/amsoil_dlp_production_2026-02-25.dump
```

### Restore fails with "schema already exists"

The database wasn't fully cleaned before restore. Drop and recreate it:

```bash
sudo -u postgres dropdb amsoil_dlp_production
sudo -u postgres createdb -O amsoil_production amsoil_dlp_production
sudo -u postgres pg_restore -d amsoil_dlp_production /tmp/backup.dump
```

### Verifying Backup Integrity

To check a dump file without restoring:

```bash
# List contents (tables, indexes, etc.)
pg_restore -l /tmp/amsoil_dlp_production_2026-02-25.dump

# Restore to a throwaway database to verify
sudo -u postgres createdb amsoil_verify_test
sudo -u postgres pg_restore -d amsoil_verify_test /tmp/amsoil_dlp_production_2026-02-25.dump
sudo -u postgres psql -d amsoil_verify_test -c "SELECT count(*) FROM \"User\";"
sudo -u postgres dropdb amsoil_verify_test
```