Backing Up Postgres Without a Managed Service

The Script That Actually Runs

A postgres backup script vps setup is the difference between a recoverable system and a lost one. On a single VPS where multiple applications share resources, you cannot rely on the cloud provider’s snapshot features alone; you need a local script that executes, verifies, and moves data before the disk fills up. The core of this setup is a shell script triggered by a systemd timer. It runs pg_dump with the -Fc flag to create a compressed, custom-format archive. This format allows for parallel restores later if you ever need to bring the database back quickly. The script writes the output to a temporary directory, checks the exit code of the dump command, and only then moves the file to the final storage location. If the dump fails, the script exits non-zero, which triggers a systemd alert. This is critical because a silent failure means you believe you have a backup when you do not.

Retention and Disk Space

The VPS has a 193GB disk, which sounds large until you account for the operating system, the Next.js build artifacts for the four sites, and the static files for the other products. With 12GB of RAM, memory pressure is less of a concern for the database itself, but disk space is the hard limit. The script implements a simple retention policy: keep the last seven daily dumps and the last four weekly dumps. After moving the new backup into place, the script deletes any files older than this window. This prevents the accumulation of stale backups that consume space without providing new safety. Because the database is shared by the Next.js app and Instalaz, a corruption event could affect both services simultaneously. Having multiple versions from different days means you can restore to a point before a bad migration or a runaway query, rather than being stuck with the last known good state from three days ago.

Offsite Copies

A backup that sits on the same hard drive as the live database is not a backup; it is a copy. If the disk controller fails, or if the VPS is compromised and the attacker deletes your data, the local backup is gone too. The script includes a step to upload the fresh dump to an offsite storage provider using rclone or aws s3 cp. This transfer happens after the local file is verified. The offsite copy is encrypted at rest. This is not just good practice; it is necessary because the database contains user data for the film publication and the scheduling tool. If the storage bucket were ever misconfigured or accessed by an unauthorized party, the data would be unreadable without the key. The key is stored in a separate, secure location, not on the VPS itself. This separation ensures that a compromise of the server does not automatically grant access to the backup data.

The Restore Test

The most important part of any backup strategy is proving that the restore works. A backup that cannot be restored is a fiction. The script does not just create files; it periodically performs a test restore into a temporary, isolated PostgreSQL instance. This test restore uses a different port and a temporary data directory. It verifies that the dump file is not corrupted and that the schema and data can be re-imported successfully. If the restore fails, the script flags the backup as invalid and keeps the previous known-good version. This step catches issues that only appear during restoration, such as permission errors or incompatible format changes. It turns the backup from a passive file into an active, verified safety net. For a solo developer running multiple live products, this automated verification is the only way to ensure that when something does go wrong, the recovery process is immediate and reliable. You do not want to be learning how to restore your database for the first time during an outage.