For most small Ubuntu VPS deployments, use a scheduled pg_dump for each database, a separate pg_dumpall --globals-only export for roles and tablespaces, encryption, and a copy stored off the VPS. If you must recover the entire cluster to a specific moment, logical dumps are not enough: combine pg_basebackup with continuous WAL archiving and a tested point-in-time recovery procedure.
Antihost offers Linux VPS plans with full root access, unlimited traffic on a 1 Gbps port and a 14-day free trial.
Choose the backup method by the failure you need to survive
PostgreSQL has three distinct backup families. A logical dump exports database objects and rows into a portable representation. A file-system-level backup copies the PostgreSQL cluster files. Continuous archiving combines a base backup with the write-ahead log (WAL), allowing recovery to a selected point on the WAL timeline.
As an Amazon Associate I earn from qualifying purchases.
Quick wins for a faster PC:
Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →Repair Windows errors before they cause bigger problemsFix Now →Scan for outdated or missing drivers - takes under a minuteDriver Scan →The right method depends on whether you need one database, the whole cluster, or a recovery point immediately before an incident.
| Need | Recommended approach | What it restores | Main trade-off |
|---|---|---|---|
| Recover one database or migrate data | pg_dump |
A logical copy of one database | Restore can take time; it cannot provide point-in-time recovery (PITR) |
| Preserve roles and cluster-wide globals | pg_dumpall --globals-only alongside database dumps |
Roles, tablespace definitions, and related globals | It is still logical and does not replace database data dumps |
| Recover the complete cluster to a recent or chosen time | pg_basebackup plus archived WAL |
The whole cluster and a selected point on the WAL timeline | Requires more storage, configuration, monitoring, and operational skill |
PostgreSQL’s documentation states that databases should be backed up regularly. A backup is useful only if it is protected from the failure that destroyed the live server and can actually be restored.
#1 Best Overall
- Read Before You Buy — No Video Output: These adapters support charging and USB 2.0 data transfer, but cannot transmit video signals. Except for standard USB webcams (which use USB data only), they are not compatible with HDMI/DisplayPort cables, video-capable USB-C hubs, or docking stations with video output.
- Convert USB-A Ports to USB-C: Designed to connect USB-C earphones, cables, flash drives, card readers, and other USB-C accessories to standard USB-A ports. Plug-and-play with no drivers or software required.
- Aluminum Alloy Housing: Built with a sturdy aluminum alloy shell that aids in heat dissipation and protects against daily wear and scratches. Designed to maintain a stable and secure connection.
- Compact & Travel-Friendly: The ultra-compact design allows the adapter to stay plugged into your device without blocking adjacent ports or adding bulk, reducing wear and tear on your original USB ports.
- 12-Month Warranty: Backed by a 12-month manufacturer warranty for peace of mind. Designed to meet strict quality control standards for reliable everyday performance.
Prepare the Ubuntu VPS before taking a backup
Identify the server and client versions
Use the PostgreSQL client that matches the major version of the server whenever possible. Check the client and server rather than assuming the package version from memory:
psql --version
pg_dump --version
sudo -u postgres psql -c "SHOW server_version;"
Command-line options and behavior can vary between major releases. Confirm the installed version’s manual before putting a command into automation.
Decide which databases and globals exist
List databases and their owners:
sudo -u postgres psql -c "l+"
Roles, tablespace definitions, and other cluster-wide objects are not contained in an individual database dump. Plan a separate globals export even when you intend to dump every database.
Create a protected working directory
Keep temporary dump files away from application directories and restrict access. The following creates a directory readable and writable only by its owner:
sudo install -d -m 0700 /var/backups/postgresql
sudo chown postgres:postgres /var/backups/postgresql
Ensure the destination has enough free space for the largest dump, its compressed or encrypted form, and at least one older generation. A dump can require substantial temporary and final storage at the same time.
Choose an authentication method deliberately
Run local jobs as the PostgreSQL operating-system account when that is compatible with your security model, or use a dedicated database role with only the privileges required to read the intended objects. If a password is needed, use a protected password file with permissions that prevent other users from reading it. Do not place database passwords directly in a world-readable script or command history.
Recommended Free Tools
Make logical backups with pg_dump
pg_dump creates a consistent logical snapshot of one database. It does not block ordinary readers and writers for the duration of the dump, but the resulting file represents a database state rather than a stream of WAL records.
Select a dump format
| Format | Example option | Useful when | Limitation |
|---|---|---|---|
| Custom | -Fc |
You want a compressed archive, selective object restoration, or parallel restore with pg_restore |
It is not human-readable SQL |
| Directory | -Fd |
You need a directory archive and parallel dump or restore workflows | It produces many files that must remain together |
| Plain SQL | No format switch, or -Fp |
You need a portable, inspectable SQL script | Large files can be slower and less flexible to restore selectively |
For a small VPS, custom format is a practical default because it supports selective restoration and works with pg_restore. Plain SQL is useful when a human must inspect or edit the output. Directory format is convenient when parallel operations matter; preserve the entire directory as one backup unit.
Create a dated custom-format dump
This is a pattern, not a production-tested command. Adapt the database name, role, path, retention, and authentication to your installation:
Rank #2
- 5-in-1 USB-C Hub: Experience comprehensive connectivity featuring a Power Delivery input, two USB-A 2.0 ports, a USB-A 3.0 port, and an HDMI port. (Note: The USB-C power delivery input port is only for connecting an external wall charger to power your laptop and cannot power peripheral devices.)
- 90W Pass-Through Charging: Achieve optimal charging with 90W pass-through power to your laptop, supported by a total input of 100W, with the hub reserving 10W for operational efficiency. (Note: Wall charger not included.)
- Quick Data Transfers: Accelerate your productivity with rapid data transfers using a high-speed 5Gbps USB 3.0 port and two 480Mbps USB 2.0 ports.
- 4K HDMI Display: Enhance your visual experience with a hub capable of delivering 4K resolution at 30Hz in both mirror and extend modes. Please note that this hub is compatible with MacBook (macOS 12 and newer), Windows 10 and 11, ChromeOS, and laptops equipped with DP Alt Mode and Power Delivery. Note: This device is not compatible with Linux.
- What You Get: Anker USB-C Hub (5-in-1, 4K HDMI), welcome guide, 18-month warranty, and our friendly customer service.
install -d -m 0700 /var/backups/postgresql
pg_dump -Fc -d appdb
-f /var/backups/postgresql/appdb-$(date +%F).dump
Run it as a role that can read every object you intend to preserve. Check the command’s exit status and verify that the output file is non-empty before marking the job successful.
Free tools Windows power users keep installed
One-click scans. No signup required.
Dump more than one database
pg_dump handles one database per invocation. For a cluster with several application databases, loop over an explicit allow-list rather than blindly dumping template or maintenance databases. Keep each database in its own archive so one restore does not require loading unrelated data.
A full-cluster logical script can be produced with pg_dumpall, but it is one large SQL stream and is less convenient for selective restores. Use it when that simplicity is more valuable than per-database flexibility.
Export roles and other cluster-wide globals
Database dumps do not recreate login roles, memberships, password hashes, or tablespace definitions. Export these objects separately:
pg_dumpall --globals-only
> /var/backups/postgresql/globals-$(date +%F).sql
Protect this file as carefully as the data dumps. It may contain role definitions and authentication-related information. Restore globals before restoring databases when the database objects depend on those roles or tablespaces.
The Tool Desk
Outbyte Driver Updater FREEScan for outdated or missing drivers - takes under a minuteDriver Scan →Outbyte PC Repair FREERepair Windows errors before they cause bigger problemsFix Now →A full pg_dumpall can be suitable for a small cluster when a single SQL script is acceptable. It still remains a logical export: it cannot be combined with WAL replay for PITR, and it does not substitute for a physical base backup.
Compress, encrypt, and move dumps off the VPS
A local file is not a disaster-recovery plan if the VPS disk, account, or hosting instance is lost. After each dump completes, copy it to storage outside the VPS and retain more than one generation. The secondary location should have access controls, encryption at rest or before upload, and a retention policy that prevents an unnoticed corruption from replacing every copy.
Compression reduces transfer and storage costs. Custom and directory archives may already compress data internally, so measure the result before adding another compression layer. Encryption should occur before the file leaves the VPS when the destination does not provide trustworthy encryption controls. Store encryption keys separately from the backup files and document who can retrieve them during an outage.
An external hard drive can hold downloaded dumps or base-backup archives as a second copy, but it does not provide continuous WAL archiving, encryption, monitoring, or geographic redundancy by itself. Disconnect or access-control the drive when it is not being used so ransomware or an account compromise cannot overwrite every generation.
Crashes, 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 minutePC Slower Than It Used to Be?
A free scan shows the junk files, broken settings and background clutter dragging Windows down - then fixes them in one click.Free scan · Windows 10 & 11Rank #3
- Sleek 7-in-1 USB-C Hub: Features an HDMI port, two USB-A 3.0 ports, and a USB-C data port, each providing 5Gbps transfer speeds. It also includes a USB-C PD input port for charging up to 100W and dual SD and TF card slots, all in a compact design.
- Flawless 4K@60Hz Video with HDMI: Delivers exceptional clarity and smoothness with its 4K@60Hz HDMI port, making it ideal for high-definition presentations and entertainment. (Note: Only the HDMI port supports video projection; the USB-C port is for data transfer only.)
- Double Up on Efficiency: The two USB-A 3.0 ports and a USB-C port support a fast 5Gbps data rate, significantly boosting your transfer speeds and improving productivity.
- Fast and Reliable 85W Charging: Offers high-capacity, speedy charging for laptops up to 85W, so you spend less time tethered to an outlet and more time being productive.
- What You Get: Anker USB-C Hub (7-in-1), welcome guide, 18-month warranty, and our friendly customer service.
Use a simple retention model
- Keep several recent daily generations so an accidental deletion is not immediately permanent.
- Keep older weekly or monthly generations for longer-running data corruption or audit needs.
- Keep at least one copy outside the VPS and, where the data requires it, in a separate failure domain.
- Delete files only after verifying that the remaining generations and the off-site copy are readable.
When pg_dump is not enough: base backup plus WAL archiving
Logical dumps cannot replay changes that occurred after the dump started. To recover the entire cluster to a chosen time, take a physical base backup and preserve every required WAL segment from the base backup’s start onward.
WAL is written continuously under pg_wal. Configure archiving with a WAL level of replica or higher, enable archive mode, and provide either an archive_command or an archive_library. The archive destination must survive loss of the VPS and must be protected because WAL contains the database’s changes.
wal_level = replica
archive_mode = on
archive_command = 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f'
This is a documented example pattern, not a universal production configuration. The 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. Replace the local path with durable storage, add encryption and access control, and monitor failures.
PostgreSQL WAL segments are normally 16 MB, although the segment size can be changed when the cluster is initialized. Segment count and archive volume therefore depend on write activity and the cluster’s initialization settings.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
Create a base backup with pg_basebackup
pg_basebackup is the easiest documented way to create a physical base backup. A typical pattern is:
pg_basebackup -D /var/backups/postgresql/base-$(date +%F)
-Fp -Xs -P
Check the installed version’s options, replication permissions, connection settings, and destination space first. Keep the completed base backup together with all archived WAL needed from its start time. A base directory without its WAL history may not be recoverable to the point you need.
Monitor archive health
Track whether archive commands succeed and whether the archive destination is keeping up. When archiving repeatedly fails, PostgreSQL retains WAL in pg_wal. If that filesystem fills, PostgreSQL can perform a PANIC shutdown and remain offline until space is freed. Alert before the disk reaches that condition, and investigate the first archive error rather than deleting WAL files manually.
Restore a logical dump
Prepare a clean target
Restore into a clean database or an intentionally prepared migration target. Do not overwrite a production database until you have verified the dump and the recovery plan. Recreate roles and tablespaces from the globals export first when ownership or tablespace placement requires them.
Restore a custom-format archive
Create the target database with the correct owner, then use pg_restore:
Rank #4
- Dual Converters, Infinite Potential：Includes 2× USB C male to USB A female adapters and 2× USB A male to USB C female adapters. Perfect for a wide range of uses—tablets with Bluetooth keyboards, expand USB ports on macbook, and more. Two different converters for all your daily needs
- Next-Level 10Gbps & 3A Charging: No more slow 480Mbps, this usb to usb c adapter has a transfer speed of up to 10Gbps, allowing you to do more transferring in less time. This usb adapter fits both USB A and USB C charger, supporting up to 3A fast charging
- Upgraded Exquisite Craftsmanship: With an aluminum alloy housing and metal connector, the usbc to usb adapter is extremely durable and sturdy. Rigorously tested to withstand more than 10,000 times of plugging and unplugging, ensuring long-lasting performance
- Broad Compatible: The usb c to usb adapter widely supports all USB C/ USB A devices like laptops, tablets, cellphones, car chargers, and phone chargers. Such as compatible with MacBook Pro/Air 2023/2022, Thunderbolt 4/3 Devices,Apple MagSafe Watch 9/8/7/SE/Ultra, iPad Pro 2022/2021, Samsung Galaxy S23/S20/S10, and iPhone 17/16/15 Pro. Plug and play
- Please Note: To reach 10Gbps speed, keep the cable under 3.3 ft. For USB A Male to USB C adapters, try flipping the USB C connector. USB C Male to USB A adapters support bidirectional 10Gbps transfer within 3.3 ft
createdb -T template0 -O app_owner restored_appdb
pg_restore -d restored_appdb
--exit-on-error
/var/backups/postgresql/appdb-YYYY-MM-DD.dump
Replace the date and names with the actual archive and target. Review the available pg_restore options for your PostgreSQL major version. For a large archive, parallel restore may reduce elapsed time when the storage and CPU can support it; the directory or custom archive must be suitable for that workflow.
Restore a plain SQL file
For a plain script, use the matching client utility against a clean target:
psql -X -d restored_appdb
-f /var/backups/postgresql/appdb-YYYY-MM-DD.sql
Inspect errors rather than assuming a successful process exit means every object was restored. Validate row counts, extensions, ownership, application connectivity, and representative queries before reopening access.
Recover the cluster to a point in time with WAL
PITR is a cluster recovery operation, not a single-table undo. The procedure below is an operational outline; adapt paths, service names, permissions, and recovery targets to the installed PostgreSQL version.
- Stop PostgreSQL and prevent applications from writing while the recovery is prepared.
- Preserve the existing cluster directory if possible. It may contain evidence or unarchived WAL needed for the incident.
- Restore the base backup with ownership and permissions matching the PostgreSQL database user.
- Copy in any required WAL that had not yet reached the archive destination.
- Configure
restore_commandso PostgreSQL can fetch each archived WAL file. The command must report failure when a requested file is unavailable. - Create
recovery.signalin the restored data directory. - Set a recovery target, such as a date/time or a named restore point, according to the target supported by your PostgreSQL version.
- Start PostgreSQL and watch the server logs until recovery reaches the target or reports an error.
- Inspect the recovered data, application invariants, extensions, permissions, and recent transactions before allowing clients to reconnect.
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, including service definitions, TLS material, firewall rules, scheduled jobs, and application configuration that is required to use the database.
Automate safely on Ubuntu
Schedule dumps with a system timer or cron job, but make the job fail loudly. Use an absolute path to each utility, a dedicated log, and an alert for non-zero exit status. A successful process does not prove that the output can be restored; the job should also check that the expected file exists, has plausible size, and was copied to the off-VPS destination.
Run database dumps in a maintenance account with the minimum operating-system and database privileges required. Keep scripts outside directories writable by untrusted application users. Quote database names and paths, use predictable filenames, and avoid exposing passwords through process arguments.
For WAL archiving, alert on archive failures and lag, watch free space in both pg_wal and the archive destination, and verify that the archive command never replaces an existing WAL file with different contents. Retention must be long enough to cover the oldest base backup that you still intend to restore.
Test the restore, not just the backup job
A backup is operationally complete only after a restore rehearsal produces a usable database. Perform the rehearsal on isolated infrastructure so production data and services are not disturbed.
Best Value
- 5-in-1 Connectivity: Equipped with a 4K HDMI port, a 5 Gbps USB-C data port, two 5 Gbps USB-A ports, and a USB C 100W PD-IN port. Note: The USB C 100W PD-IN port supports only charging and does not support data transfer devices such as headphones or speakers.
- Powerful Pass-Through Charging: Supports up to 85W pass-through charging so you can power up your laptop while you use the hub. Note: Pass-through charging requires a charger (not included). Note: To achieve full power for iPad, we recommend using a 45W wall charger.
- Transfer Files in Seconds: Move files to and from your laptop at speeds of up to 5 Gbps via the USB-C and USB-A data ports. Note: The USB C 5Gbps Data port does not support video output.
- HD Display: Connect to the HDMI port to stream or mirror content to an external monitor in resolutions of up to 4K@30Hz. Note: The USB-C ports do not support video output.
- What You Get: Anker 332 USB-C Hub (5-in-1), welcome guide, our worry-free 18-month warranty, and friendly customer service.
- Download a recent off-VPS dump and verify its checksum or transfer integrity.
- Restore globals, then a database dump, into a clean PostgreSQL instance.
- For PITR, restore a base backup and replay archived WAL to a documented target time.
- Run schema checks, representative application queries, row-count or business-invariant checks, and permission tests.
- Measure elapsed restore time and record the last recoverable WAL position or timestamp.
- Document missing prerequisites, then update the runbook and schedule the next rehearsal.
Keep the recovery procedure with the backup metadata: PostgreSQL major version, dump format, creation time, base-backup start time, WAL retention window, encryption-key location, and the owner responsible for approving recovered data.
Do these 3 things before closing this tab:
1Repair Windows errors before they cause bigger problems2Scan for outdated or missing drivers - takes under a minute3Clear out junk files and repair common Windows errorsCommon failure modes and fixes
The dump job reports success but the file is missing
Check shell error handling, destination permissions, available disk space, and whether a pipeline masked the exit status. Write to a temporary filename and rename it only after pg_dump exits successfully, then verify the final file before uploading.
Roles are missing after restore
Restore the pg_dumpall --globals-only export before the database archive. Confirm that the target role can create the required users, memberships, and tablespaces, and treat the globals file as sensitive.
Point-in-time recovery stops because a WAL file is unavailable
Find the first missing segment in the server log. Check archive retention, the restore command’s path and permissions, and whether the base backup’s start time is covered by the retained WAL. Do not delete additional WAL until the dependency window is understood.
The VPS runs out of space while archiving
Resolve the archive destination or transfer failure, provision space, and confirm that PostgreSQL can resume archiving. Do not remove files from pg_wal by hand; premature deletion can make the cluster unrecoverable.
Quick wins for a faster PC:
Scan for outdated or missing drivers - takes under a minuteDriver Scan →Clear out junk files and repair common Windows errorsFree Scan →Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →The restored server starts but applications fail
Check extensions, role ownership, tablespace paths, connection rules, TLS files, and application configuration. Recovery restores database state, not every manually maintained VPS file.
A practical policy for a small Ubuntu VPS
Use nightly or more frequent per-database custom-format dumps when the data-loss window and restore time are acceptable. Export globals in the same job. Encrypt the results, copy them outside the VPS, retain multiple generations, and run scheduled restore rehearsals.
Adopt pg_basebackup with continuous WAL archiving when losing transactions between logical dumps is unacceptable, when the entire cluster must be recovered together, or when the business requires a defined point-in-time target. This adds storage and operational work, so monitor it as a production subsystem rather than treating it as a one-time command.
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.
Do these 3 things before closing this tab:
1Repair Windows errors before they cause bigger problems2Fix the driver behind crashes, sound loss and screen glitches3Clear out junk files and repair common Windows errors
