Recommended Free Tools
The safest backup plan for a PostgreSQL database on an Ubuntu VPS uses two layers: scheduled logical dumps for convenient database-level recovery, plus a physical base backup with continuous WAL archiving when you need whole-cluster recovery or point-in-time recovery (PITR). Store encrypted copies outside the VPS and regularly prove that they restore.
Antihost offers Linux VPS plans with full root access, unlimited traffic on a 1 Gbps port and a 14-day free trial.
As with everything that contains valuable data, PostgreSQL databases should be backed up regularly.
— PostgreSQL Global Development Group, PostgreSQL 18 documentation.
As an Amazon Associate I earn from qualifying purchases.
Free tools Windows power users keep installed
One-click scans. No signup required.
Choose the backup method that matches the failure you must survive
PostgreSQL has three backup families: logical SQL dumps, file-system-level backups, and continuous archiving. They solve different problems, so a single command is not a complete disaster-recovery plan.
| Need | Recommended method | What it restores | Main trade-off |
|---|---|---|---|
| Recover one database, a few objects, or migrate data | pg_dump |
A logical copy of one database | Restore may take time and cannot replay WAL for PITR |
| Preserve roles and cluster-wide definitions | pg_dumpall --globals-only alongside each database dump |
Roles, tablespace definitions, and related global objects | Logical export; it does not contain table data |
| Recover the complete cluster to a recent or selected time | pg_basebackup plus archived WAL |
The whole cluster and a point on its WAL timeline | Requires more storage, configuration, monitoring, and operational discipline |
pg_dump and pg_dumpall are logical tools. They do not create file-system-level backups and cannot take part in WAL replay. A logical dump is therefore not a substitute for a base backup and an uninterrupted WAL archive when your requirement is PITR.
#1 Best Overall
- Easily store and access 2TB to content on the go with the Seagate Portable Drive, a USB external hard drive
- Designed to work with Windows or Mac computers, this external hard drive makes backup a snap just drag and drop
- To get set up, connect the portable hard drive to a computer for automatic recognition no software required
- This USB drive provides plug and play simplicity with the included 18 inch USB 3.0 cable
- The available storage capacity may vary.
Prepare the Ubuntu VPS before taking backups
Identify the PostgreSQL instance and client version
Ubuntu can host several PostgreSQL major versions at once. Run pg_lsclusters to identify the active cluster, port, status, data directory, and major version. Check the client utilities with:
pg_dump --version
pg_dumpall --version
pg_basebackup --versionUse a client version supported by the server and verify compatibility before automating. Do not assume that the first pg_dump found in PATH belongs to the cluster you intend to protect. Record the server version from an authenticated psql session with SELECT version();.
Create a protected working directory
Keep temporary dump files away from application directories and restrict access to the database administrator account:
sudo install -d -o postgres -g postgres -m 0700 /var/backups/postgresqlCheck free space before every large dump. A dump can require substantial temporary capacity, and a failed job must not fill the VPS disk needed by PostgreSQL itself.
Use deliberate authentication
For a local job running as the postgres operating-system user, peer authentication commonly permits a socket connection without a password:
sudo -u postgres psql -d postgres -c 'SELECT current_database(), current_user;'For a remote or application role, specify the host, port, database, and password method explicitly. A .pgpass file must be owned by the account running the job and have mode 0600; never place a database password directly in a world-readable script or command line. The role needs permission to read every object that the dump is expected to contain.
Take a logical backup with pg_dump
pg_dump backs up one database at a time. It produces a consistent logical snapshot while PostgreSQL remains online, making it the practical first layer for most small VPS deployments.
Choose a dump format
| Format | Example option | Best use | Restore utility |
|---|---|---|---|
| Custom | -Fc |
Compact archive, selective object restore, and flexible ordering | pg_restore |
| Directory | -Fd |
Parallel dump or restore and handling a large database as multiple files | pg_restore |
| Plain SQL | -Fp (the default) |
Human-readable, portable SQL script | psql |
Custom and directory formats are generally more useful for operational recovery. Plain SQL is convenient when a person must inspect or edit the script, but it is a single stream and offers fewer selective-restore features.
Run a database dump
This pattern creates a custom-format archive with a date in its name:
sudo -u postgres pg_dump
-Fc
-d appdb
-f /var/backups/postgresql/appdb-$(date +%F).dumpAdapt the database name, destination, ownership, authentication, and retention policy. The command is an example pattern, not a guarantee for every Ubuntu or PostgreSQL release. Confirm the installed utility’s options with pg_dump --help.
Windows Errors? Fix Them Before They Spread
Repair common Windows errors and clear accumulated junk for a smoother, more stable PC - no reinstall needed.Free scan · no reinstallCrashes, No Sound, or Screen Glitches?
Random freezes, missing sound and display glitches usually trace back to one bad driver. Find and replace yours safely.Free scan · under a minuteRank #2
- Easily store and access 5TB of content on the go with the Seagate portable drive, a USB external hard Drive
- Designed to work with Windows or Mac computers, this external hard drive makes backup a snap just drag and drop
- To get set up, connect the portable hard drive to a computer for automatic recognition software required
- This USB drive provides plug and play simplicity with the included 18 inch USB 3.0 cable
- The available storage capacity may vary.
For a directory-format dump that can be restored in parallel, use a separate directory as the output target:
sudo -u postgres pg_dump
-Fd
-j 4
-d appdb
-f /var/backups/postgresql/appdb-$(date +%F).dirParallel jobs can reduce elapsed time but increase CPU, disk, and I/O pressure. Choose a job count that leaves capacity for the production workload.
Back up roles and other global objects
A database dump does not include cluster-wide roles or tablespace definitions. Export those separately:
sudo -u postgres pg_dumpall
--globals-only
> /var/backups/postgresql/globals-$(date +%F).sqlRestore this file before restoring databases when the target cluster does not already contain the required roles. Protect it as sensitive data: role definitions can include password verifiers and privilege information.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
For a very small cluster, a complete pg_dumpall can produce one SQL script containing every database and the globals:
sudo -u postgres pg_dumpall
> /var/backups/postgresql/cluster-$(date +%F).sqlThis is simple but creates one large, less-flexible script. It is inconvenient when you need to restore only one database and can take a long time to process.
Compress and encrypt dumps
Custom and directory formats support compression. Plain SQL can be compressed after creation, for example with gzip. Encryption is separate from compression: use an approved encryption tool and key-management process before transferring files off the VPS. Keep decryption keys independent of the VPS and document who can use them. Test decryption as part of a restore rehearsal; an encrypted file that nobody can unlock is not a usable backup.
Automate logical backups without hiding failures
Run dumps from a root-owned wrapper or a dedicated service account, but execute PostgreSQL commands with the least-privileged database identity that can complete the export. A wrapper should stop on errors, create uniquely named output, record start and finish times, and return a non-zero status when any command fails. A minimal shell pattern is:
#!/bin/sh
set -eu
backup_dir=/var/backups/postgresql
today=$(date +%F)
install -d -o postgres -g postgres -m 0700 "$backup_dir"
sudo -u postgres pg_dump -Fc -d appdb
-f "$backup_dir/appdb-$today.dump"
sudo -u postgres pg_dumpall --globals-only
> "$backup_dir/globals-$today.sql"Schedule the wrapper with cron or a systemd timer at a time that fits the workload. Capture standard output and error in a protected log, alert on a non-zero exit code, and verify that the expected files are non-empty and have the correct owner and mode. A scheduler that silently misses jobs is not a backup system.
Retain multiple generations rather than deleting the previous file immediately after a new one appears. Keep at least one copy outside the VPS. Define retention by recovery needs—for example, how many daily versions and how far back a business can request a restore—rather than by an arbitrary file count.
Rank #3
- Easily store and access 1TB to content on the go with the Seagate Portable Drive, a USB external hard drive.Specific uses: Personal
- Designed to work with Windows or Mac computers, this external hard drive makes backup a snap just drag and drop. Reformatting may be required for Mac
- To get set up, connect the portable hard drive to a computer for automatic recognition no software required
- This USB drive provides plug and play simplicity with the included 18 inch USB 3.0 cable
- The available storage capacity may vary.
Copy backups away from the VPS
A VPS can be lost through a provider outage, account error, disk failure, compromise, or an operator mistake. A second copy on the same virtual machine does not address those events. Transfer encrypted dumps and, when used, base-backup and WAL archives to storage with separate failure domains and restricted credentials.
- Use a destination that remains available if the VPS is destroyed.
- Encrypt before transfer or use storage encryption with keys managed separately from the server.
- Restrict archive and dump permissions so other users cannot read database contents.
- Keep more than one generation and protect older versions from accidental deletion.
- Monitor transfer completion, object counts, age of the newest copy, and available destination capacity.
An external hard drive can be secondary storage for downloaded dumps or base-backup archives, but by itself it does not provide continuous WAL archiving, encryption, monitoring, or geographic redundancy. Disconnect or otherwise protect offline copies from ransomware and operator mistakes.
Use a base backup and WAL archiving for PITR
PostgreSQL writes write-ahead log (WAL) records continuously under pg_wal. A physical base backup captures the cluster files; an uninterrupted archive of the WAL generated from the base backup onward lets PostgreSQL rebuild the cluster and stop at a selected time. This is the method to choose when losing every change since yesterday’s dump is unacceptable.
Configure WAL archiving
Set these parameters for the cluster:
wal_level = replica
archive_mode = on
archive_command = 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f'wal_level must be replica or higher. archive_mode requires a restart when enabled. The archive command runs as the PostgreSQL server user. It must return success only after the WAL file is safely stored, and it must not overwrite a different file that happens to have the same WAL name. The command above is a documented example pattern, not a universal production configuration; replace the destination and copy mechanism with one that survives VPS loss and provides encryption, access control, retention, and monitoring.
You can use archive_library instead of archive_command when an appropriate library and deployment process are available. Whichever mechanism you choose, protect the archive because WAL contains the database’s changes.
Quick wins for a faster PC:
Clear out junk files and repair common Windows errorsFree Scan →Scan for outdated or missing drivers - takes under a minuteDriver Scan →Repair Windows errors before they cause bigger problemsFix Now →After changing settings, reload or restart as required by each parameter and verify them from SQL:
sudo -u postgres psql -d postgres -c
"SELECT name, setting FROM pg_settings WHERE name IN ('wal_level','archive_mode','archive_command');"Take the base backup
pg_basebackup is the easiest documented way to create a physical base backup. It requires a connection that has the necessary replication privileges and a pg_hba.conf rule permitting it. A plain-format example using streamed WAL is:
sudo -u postgres pg_basebackup
-D /var/backups/postgresql/base-$(date +%F)
-Fp
-Xs
-PEnsure the destination has enough capacity and is not inside the live data directory. Keep the base backup and every WAL segment needed from the backup’s start time through the end of the recovery window. PostgreSQL WAL segments are normally 16 MB each, although the segment size can be changed when the cluster is initialized.
Copy the completed base backup and its associated metadata to the off-VPS destination. Do not delete archived WAL merely because a base backup finished; calculate retention from the oldest base backup you still intend to restore.
Do these 3 things before closing this tab:
1Scan for outdated or missing drivers - takes under a minute2Clear out junk files and repair common Windows errors3Fix the driver behind crashes, sound loss and screen glitchesRestore a logical dump
Restore roles and create a clean target
Restore globals first when roles or tablespaces are required:
Rank #4
- Easily store and access 4TB of content on the go with the Seagate Portable Drive, a USB external hard drive.Specific uses: Personal
- Designed to work with Windows or Mac computers, this external hard drive makes backup a snap just drag and drop
- To get set up, connect the portable hard drive to a computer for automatic recognition no software required
- This USB drive provides plug and play simplicity with the included 18 inch USB 3.0 cable
- The available storage capacity may vary.
sudo -u postgres psql -d postgres
-f /path/to/globals-2026-10-14.sqlCreate a new, empty target database owned by the intended role. Restoring into a clean database avoids collisions with existing tables and makes the result easier to validate. Preserve the original database until inspection is complete if disk space and operational policy allow it.
Restore custom or directory format
For a custom-format archive:
sudo -u postgres createdb -O appowner recovered_appdb
sudo -u postgres pg_restore
--dbname=recovered_appdb
--clean
--if-exists
/path/to/appdb-2026-10-14.dump--clean drops objects before recreating them and is most appropriate for a disposable, empty recovery target. Omit destructive options when restoring into a database that contains data you must preserve. For a directory archive, point pg_restore at the directory and use -j only when the target has enough CPU and I/O capacity.
Restore plain SQL
sudo -u postgres createdb -O appowner recovered_appdb
sudo -u postgres psql
--dbname=recovered_appdb
--file=/path/to/appdb-2026-10-14.sqlReview errors rather than assuming a successful process exit means every statement applied. Check row counts, key application queries, extensions, sequences, permissions, and any external files the application expects.
The Tool Desk
Outbyte PC Repair FREEClear out junk files and repair common Windows errorsFree Scan →Outbyte Driver Updater FREEFix the driver behind crashes, sound loss and screen glitchesFind Drivers →Recover the cluster to a point in time
PITR is a cluster recovery operation, not a per-table undo mechanism. Plan a maintenance window and preserve the failed cluster before replacing it whenever storage permits.
- Stop PostgreSQL and isolate the service. Prevent applications from writing while recovery is prepared.
- Preserve the existing data directory. Rename or snapshot it so logs, configuration, and forensic evidence are not destroyed.
- Restore the base backup. Copy the physical backup into the cluster data directory with ownership and permissions expected by the PostgreSQL service account.
- Recover local WAL if necessary. Include any WAL that had not yet reached the archive destination when the failure occurred.
- Configure WAL retrieval. Set a
restore_commandthat retrieves each requested segment, for examplerestore_command = 'cp /mnt/server/archivedir/%f %p'. Use your secure archive retrieval mechanism instead of a local copy when the archive is remote. - Choose the recovery target. Set a target timestamp such as
recovery_target_time = '2026-10-14 12:34:56+00', or use a named restore point created before the incident. - Create the recovery signal. On current PostgreSQL releases, create an empty
recovery.signalfile in the restored data directory. - Start PostgreSQL and watch the logs. Confirm that the server is fetching WAL, reaches the target, and stops or promotes according to the chosen settings.
- Inspect before reopening access. Validate critical tables, recent transactions, constraints, roles, extensions, and application behavior. Promote the recovered cluster only after the result is accepted.
WAL recovery does not restore manually edited postgresql.conf, pg_hba.conf, or pg_ident.conf. Back up those files and the rest of the VPS configuration separately, then reapply only the settings appropriate for the recovered server.
Monitor and troubleshoot backup failures
Archive command failures
If an archive command repeatedly fails, PostgreSQL retains WAL in pg_wal while it waits for a successful archive. Monitor archive status and lag with SQL and system monitoring. A growing pg_wal directory is an urgent capacity warning; if the file system fills, PostgreSQL can enter a PANIC shutdown and remain offline until space is freed.
Check destination permissions, available space, network reachability, encryption-key availability, and whether a previous file with the same WAL name contains different contents. Never “fix” the symptom by deleting unknown WAL files from the live pg_wal directory.
Dump failures
- Authentication errors: verify the operating-system user, database role, socket or host, port,
pg_hba.conf, and password-file permissions. - Permission errors: grant the dump role access to the objects it must read, or run the export with an appropriately privileged role.
- No space left on device: check both the dump directory and PostgreSQL’s data volume; remove only verified, expired generations.
- Unexpectedly small output: inspect the exit status and logs, confirm the database name, and verify that the destination file belongs to the backup job.
- Restore conflicts: restore into a clean database, load globals first, and use a matching restore tool for the archive format.
Prove that backups work
A successful backup job is only evidence that bytes were written. Periodically restore a representative dump to an isolated PostgreSQL instance and perform application-level checks. For PITR, restore a base backup, replay WAL to a known target, and verify that the expected transaction boundary is present. Record the elapsed backup and restore times, the oldest recoverable point, and any manual steps so the procedure is usable during an outage.
A practical backup policy for a small Ubuntu VPS
- Run a daily
pg_dumpfor each important database in custom or directory format. - Run
pg_dumpall --globals-onlywith the same schedule and retain it with the database dumps. - Encrypt and transfer completed files to storage outside the VPS.
- Keep multiple generations and at least one copy in a separate failure domain.
- If the recovery objective requires changes made between dumps, configure
wal_level = replica,archive_mode = on, and a monitored WAL archive, then take regularpg_basebackupimages. - Back up PostgreSQL configuration, service definitions, firewall rules, deployment files, and application secrets through their own protected process.
- Rehearse both logical restoration and PITR, and update the runbook whenever versions, paths, credentials, or storage destinations change.
The design is intentionally layered: logical dumps provide selective recovery and migration flexibility, while a base backup plus WAL preserves the complete cluster’s timeline. Neither layer is complete until copies are protected outside the VPS and a real restore has been inspected.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
