Setting Up Reliable Off-Site Backups on Ubuntu with Hetzner Backup Box and PostgreSQL

In this technical guide, we document the complete process of structuring, automating, and securing daily, weekly, and monthly backups for a production Ubuntu server. The setup combines local snapshot management, PostgreSQL data protection, and a remote Hetzner Backup Box mounted via CIFS for off-site redundancy.

1. Understanding the Environment

The system in this case study runs several services including PostgreSQL 9.5, Apache, and various application directories such as /var/www and /home/admin. Local data and backups were initially spread across multiple drives (/dev/sdc and /dev/sdd), with the PostgreSQL database located at /media/sdd/postgresql/9.5/main.

The objective was to consolidate backup logic, reduce disk space usage, and integrate a remote backup target (Hetzner Backup Box) for off-site data protection while maintaining clear daily, weekly, and monthly retention.

2. Mounting and Managing Local Drives

The server contained multiple SSDs mounted under /media/sdc and /media/sdd. The /dev/sdd device was a 1.8 TB Micron SSD used for both active PostgreSQL data and local backups. Meanwhile, /dev/sdc was a smaller Samsung drive previously used for secondary backups.

Using df -h and lsblk, the structure was verified to ensure correct mounts and available capacity. Old redundant backup directories on /media/sdc were cleaned up to reclaim space and avoid confusion.

3. Analyzing Log Growth and Logrotate Configuration

The /var/log directory had grown excessively due to PostgreSQL query logs (~77 GB), large system logs (~10 GB), and other service logs. logrotate was reconfigured with shorter retention and proper compression:

PostgreSQL: daily rotation, keep 1 or 2 compressed logs
Syslog and Rsyslog: daily rotation, keep 3 compressed logs
Apache / Elasticsearch: weekly rotation or limited by size

PostgreSQL's internal logging parameters (log_statement, log_min_duration_statement) were also tuned to prevent logging every SQL query. Combined with logrotate automation, this brought total log usage from ~90 GB down to a few gigabytes.

4. Locating and Backing Up PostgreSQL Data

The active PostgreSQL cluster was confirmed via:

sudo -u postgres psql -At -c "SHOW data_directory;"

The output revealed the main data directory under /media/sdd/postgresql/9.5/main. It is essential to avoid copying this directory live with rsync while PostgreSQL is running, as this can lead to inconsistent or corrupted backups.

Instead, several safe methods were discussed:

pg_basebackup for physical, consistent online backups.
pg_dump or pg_dumpall for logical, schema-level backups.
Cold rsync after stopping PostgreSQL (complete filesystem copy).

For daily automation, pg_basebackup was selected as the most practical solution, optionally combined with a short retention policy (e.g., keeping 7 days of backups).

5. Mounting the Hetzner Backup Box (CIFS / SMB)

The Hetzner Backup Box was configured as a 2 TB remote share accessible via the SMB protocol. SSHFS and FTP(S) were initially tested, but the correct method was CIFS (Samba).

Installation and mount steps:

sudo apt install -y cifs-utils
sudo mkdir -p /mnt/backupbox
sudo bash -c 'cat > /root/.smbcred_backupbox <<EOF
username=YOUR_USERNAME
password=YOUR_PASSWORD
EOF'
sudo chmod 600 /root/.smbcred_backupbox

sudo mount -t cifs //YOUR-SERVER.your-backup.de/backup /mnt/backupbox \
  -o vers=3.0,iocharset=utf8,sec=ntlmssp,credentials=/root/.smbcred_backupbox,uid=0,gid=0,file_mode=0660,dir_mode=0770

The mount was then verified with:

df -h | grep backupbox

Result: 2.0T total, 328G used, 1.7T free — confirming successful connection and available capacity.

6. Designing the Backup Structure

A clear directory hierarchy was implemented on the backup box:

/database — PostgreSQL backups and dumps
/daily — recent daily snapshot links
/weekly — weekly backups (usually Sunday)
/monthly — monthly backups (1st of each month)

The retention policy was defined as:

Keep 3 daily versions
Keep 1 weekly version
Keep 1 monthly version

Old backups outside this window are automatically pruned to free space.

7. Automation with Rsync and Snapshot Retention

The core automation script uses rsync to synchronize directories such as /home/admin, /var/www, /etc, and database dumps to the mounted backup volume. The script also creates daily snapshot folders with timestamps (e.g., snapshots/2025-10-14/) and updates symlinks for daily, weekly, and monthly retention.

Each run checks if the mount is active, prevents concurrent executions using flock, and writes logs to /var/log/backup_snapshots_to_backupbox.log.

Example cron job:

30 2 * * * /root/scripts/backup_snapshots_to_backupbox.sh

This job performs all synchronization automatically every night at 02:30.

8. Mount and Health Checks

To avoid stale mounts, the script verifies that /mnt/backupbox is an active CIFS mount before starting any rsync operations. If the share is disconnected, the backup is aborted gracefully to prevent writing to an empty local folder.

System logs and cron logs help track every run and identify potential failures or connection issues early.

9. Cleanup and Housekeeping

Old, temporary Chromium session files under /tmp/.org.chromium.* were automatically removed as part of the backup maintenance routine. The same script also performs cleanup of outdated snapshot folders that are no longer referenced by any daily, weekly, or monthly symlink.

This ensures the remote backup space remains organized and efficient, even over long periods.

10. Result and Recommendations

After completing these steps, the system now maintains:

Local PostgreSQL data safely stored on an enterprise SSD.
Automated off-site backups mirrored to Hetzner’s 2 TB Backup Box via CIFS.
Clean log management with daily rotation and compression.
Daily snapshots with 3-day retention and automatic pruning.
Weekly and monthly snapshots for longer-term reference.

In production, this provides a balanced trade-off between storage efficiency, safety, and restore flexibility.

For future improvements, consider upgrading PostgreSQL (9.5 is end-of-life), enabling WAL archiving for point-in-time recovery, and testing restore procedures regularly on a separate host or container.

Search This Blog