# SiteGround SFTP Operations — Reference

SiteGround offers SFTP access (port 18765) but **blocks SSH shell execution** (`exec_command` in paramiko, any remote `tar`/`rsync`/`find` via SSH). Only pure SFTP operations work: listdir, stat, open, read, write.

## Directory Layout

All sites live at `/<domain>/<domain>/public_html` — **not** `/home/<domain>/public_html`.

```
/815bistro.com/815bistro.com/
├── logs/
├── public_html/       ← site files (WordPress, etc.)
└── webstats/
```

Each domain has a subdirectory with the same domain name. Within that, `public_html/` contains the actual website files.

## Programmatic Backup to S3 (Wasabi)

When backing up all 21+ sites to S3 programmatically, the approach must use **pure SFTP** — no remote tar/rsync:

### Correct Approach (Pure SFTP)

1. Connect via paramiko SSHClient → `client.open_sftp()`
2. Recursively list all files under `/<domain>/<domain>/public_html` using `sftp.listdir_attr()` + `stat_module.S_ISDIR`/`S_ISREG`
3. Collect a list of `(full_path, relative_path)` tuples
4. Build a local `.tar.gz` by reading each file via `sftp.open(entry_path, "rb")` and adding to `tarfile.TarInfo`
5. Upload to Wasabi S3 with `aws s3 cp` (via awscli venv)

### What Does NOT Work

```python
# ❌ SSH exec_command is blocked — "Channel closed" or "Timeout opening channel"
stdin, stdout, stderr = client.exec_command("tar czf - -C /path .")

# ❌ Remote rsync (requires SSH shell)
# ❌ Remote find | wc -l for file counting
```

### What Does Work

```python
# ✅ Pure SFTP recursive listing
import stat
all_files = []
dirs_to_check = [working_dir]
while dirs_to_check:
    current = dirs_to_check.pop()
    for entry in sftp.listdir_attr(current):
        entry_path = f"{current}/{entry.filename}"
        if stat.S_ISDIR(entry.st_mode):
            dirs_to_check.append(entry_path)
        elif stat.S_ISREG(entry.st_mode):
            all_files.append((entry_path, rel_path))

# ✅ Local tarball from SFTP streams
with tarfile.open(tar_path, "w:gz") as tar:
    for entry_path, rel_path in all_files:
        file_obj = sftp.open(entry_path, "rb")
        info = sftp.stat(entry_path)
        tarinfo = tarfile.TarInfo(name=rel_path)
        tarinfo.size = info.st_size
        tarinfo.mtime = info.st_mtime
        tarinfo.mode = info.st_mode & 0o777
        tar.addfile(tarinfo, file_obj)
```

### Performance Note

The pure-SFTP approach is **slow** for large WordPress sites (1000s of files) because each file requires a separate SFTP round-trip. A full backup of 21 sites may take 30–60+ minutes. If an alternative method ever becomes available (e.g., SiteGround adds SSH shell), prefer remote tar streaming.

## SFTP Connection Details

| Parameter | Value |
|-----------|-------|
| Host | `sftp.siteground.net` |
| Port | `18765` |
| Auth | SSH public key (RSA) |
| Key path | `/root/.ssh/siteground.key` |

## Manual Migration (When Moving Sites)

### Files
```bash
# Full site download via SFTP CLI
sftp -P 18765 user@sftp.siteground.net
get -r /<domain>/<domain>/public_html ./backup-$(date +%F)
```

Or via lftp for large sites:
```bash
lftp -u user,password sftp://sftp.siteground.net:18765
mirror /<domain>/<domain>/public_html ./backup/
```

### Database
1. Log into SiteGround cPanel → phpMyAdmin
2. Select the database → Export → Quick method
3. Download the .sql file
4. Import on new host: `mysql -u user -p dbname < /tmp/database.sql`

### Installation on new host
- WordPress files + database export are sufficient for a full migration
- Update wp-config.php with new DB credentials
- Search-replace old domain URL if changing
- Import DB: `mysql -u user -p dbname < /tmp/database.sql`

## Python Environment

On the infrastructure, paramiko and awscli live in `/opt/awscli-venv/` — not system Python.
Always run backup scripts with `/opt/awscli-venv/bin/python3`, not `python3`.

```bash
# Run backup
/opt/awscli-venv/bin/python3 /root/backup_sites.py

# Test connectivity
/opt/awscli-venv/bin/python3 -c "import paramiko; print('OK')"
```
