ECS Docker PostgreSQL Backup And Restore

Current production database: ECS Docker PostgreSQL container biof3-pg. Data directory bind mount: /opt/biof3-platform/pgdata -> /var/lib/postgresql/data.

Backup Policy

Script:

/usr/local/bin/biof3-backup-ecs-pg.sh

Source in repo:

scripts/backup-ecs-pg.sh

Default local backup directory:

/opt/biof3-platform/backups/pg/
  daily/
  weekly/
  monthly/
  latest.sql.gz -> daily/latest-file.sql.gz

Default retention:

• daily: 14 days
• weekly: 35 days
• monthly: 120 days

If /etc/biof3.env contains OSS_AK and OSS_SK, the script uploads dumps to OSS. Default OSS prefix:

backups/postgresql/

Cron

Recommended ECS root cron:

10 3 * * * /usr/local/bin/biof3-backup-ecs-pg.sh >> /var/log/biof3-pg-backup.log 2>&1

Installed on production ECS on 2026-06-12.

Check:

ssh aliyun 'crontab -l | grep biof3-backup-ecs-pg'
ssh aliyun 'tail -80 /var/log/biof3-pg-backup.log'

Manual Backup

ssh aliyun '/usr/local/bin/biof3-backup-ecs-pg.sh'
ssh aliyun 'ls -lh /opt/biof3-platform/backups/pg/daily | tail'

The admin console uses the same PostgreSQL backup path in platform mode:

curl -sk -H 'X-API-Token: ...' https://biof3.com/api/platform/admin/backup/list
curl -sk -X POST -H 'X-API-Token: ...' https://biof3.com/api/platform/admin/backup/db

Expected response details:

• engine:"postgres"
• key:"backups/postgresql/biof3-pg.<timestamp>.sql.gz"
• signed downloads from /api/platform/admin/backup/download/:name

Health Checks

ssh aliyun 'docker ps --format "table {{.Names}}\t{{.Status}}\t{{.Ports}}" | grep biof3-pg'
ssh aliyun 'docker inspect biof3-pg --format "{{range .Mounts}}{{println .Type .Source \"->\" .Destination}}{{end}}"'
ssh aliyun 'docker exec biof3-pg psql -U postgres -d biof3 -Atc "select current_database(), pg_size_pretty(pg_database_size(current_database()))"'

Restore Drill

Use a disposable container and a recent dump. This does not touch production.

ssh aliyun '
set -euo pipefail
DUMP=/opt/biof3-platform/backups/pg/latest.sql.gz
docker rm -f biof3-pg-restore-drill >/dev/null 2>&1 || true
docker run -d --name biof3-pg-restore-drill \
  -e POSTGRES_PASSWORD=restore_drill \
  -e POSTGRES_DB=biof3_restore \
  postgres:16-alpine >/dev/null
sleep 5
gzip -dc "$DUMP" | docker exec -i biof3-pg-restore-drill psql -U postgres -d biof3_restore >/tmp/biof3-pg-restore-drill.log
docker exec biof3-pg-restore-drill psql -U postgres -d biof3_restore -Atc "select count(*) from users;"
docker rm -f biof3-pg-restore-drill >/dev/null
'

Success criteria:

• restore exits with code 0
• users count returns a number
• no production container is stopped or modified

Last drill:

• 2026-06-12: restored latest.sql.gz into disposable container biof3-pg-restore-drill; verified users=8, projects=3, tool_jobs=1; removed disposable container.

Production Restore Outline

Do this only in a maintenance window.

1. Stop platform writers or put site in maintenance mode.
2. Take one final copy of /opt/biof3-platform/pgdata if possible.
3. Create a fresh PostgreSQL container or empty database.
4. Restore:

gzip -dc /path/to/backup.sql.gz | docker exec -i biof3-pg psql -U postgres -d biof3

5. Restart platform, r-worker, and py-worker.
6. Verify login, project listing, admin user count, and one tool job status.

Do Not

• Do not run docker compose down -v against the PostgreSQL volume.
• Do not delete /opt/biof3-platform/pgdata.
• Do not assume Dell SQLite is a current source of truth.