Backup and Recovery of PostgreSQL Databases: A Complete Guide for AlexHost Users

Why PostgreSQL Backup Strategy Matters More Than You Think

Data loss is not a hypothetical risk — it is an operational certainty that every database administrator will face at some point. Hardware failures, accidental deletions, corrupted transactions, and ransomware attacks can all bring a production environment to its knees within seconds. For PostgreSQL users, having a robust, tested, and automated backup strategy is the difference between a minor incident and a catastrophic business failure.

AlexHost Dedicated Servers provide an ideal foundation for hosting and protecting PostgreSQL databases. With enterprise-grade NVMe SSD storage delivering exceptional I/O throughput, full root access for complete configuration control, and built-in DDoS protection, AlexHost gives you the infrastructure performance and security posture that serious database workloads demand.

Whether you are running a high-traffic e-commerce platform, a SaaS application, a WordPress installation backed by a relational database, or a custom enterprise system, this guide walks you through every major PostgreSQL backup and recovery method — from simple SQL dumps to advanced Point-in-Time Recovery (PITR) — all optimized for production environments.

1. Understanding PostgreSQL Backup Options

PostgreSQL ships with several mature, well-documented backup mechanisms. Choosing the right one depends on your database size, recovery time objectives (RTO), recovery point objectives (RPO), and operational complexity tolerance.

Understanding these trade-offs before you begin is essential. Most production environments benefit from combining at least two approaches — for example, nightly custom format dumps alongside continuous WAL archiving for granular recovery capability.

2. Prerequisites and Privilege Requirements

Before executing any backup operation, confirm the following prerequisites are in place:

User Privileges:

• You must be a PostgreSQL superuser or the owner of the target database to perform a full backup.
• For pg_dumpall, superuser privileges are mandatory.

Verify your PostgreSQL version:

psql --version

Check available disk space before backing up:

df -h /var/lib/postgresql/

Ensure your backup destination has sufficient free space — at minimum 1.5× the size of the database being backed up to account for temporary files and compression overhead.

Connect to your server via SSH:

ssh root@your-server-ip

If you are using a VPS Hosting plan, you will have full SSH access and the ability to install, configure, and manage PostgreSQL without restriction.

3. Method 1 — SQL Dump with pg_dump

The pg_dump utility is the most commonly used PostgreSQL backup tool. It produces a consistent snapshot of a single database, even while the database is actively being used. The output is a plain-text SQL script that can be reviewed, edited, and replayed on any compatible PostgreSQL installation.

Step 1: Open a Terminal and Access Your Server

ssh root@your-alexhost-server-ip

Step 2: Run the pg_dump Command

pg_dump -U username -W -F p database_name > /backups/backup_file.sql

Parameter breakdown:

Practical example:

pg_dump -U postgres -W -F p my_production_db > /backups/my_production_db_$(date +%Y%m%d_%H%M%S).sql

> Pro Tip: Appending a timestamp using $(date +%Y%m%d_%H%M%S) to your backup filename ensures you never accidentally overwrite a previous backup and creates a natural chronological archive.

Step 3: Verify the Backup File

ls -lh /backups/
head -50 /backups/my_production_db_*.sql

The file should begin with PostgreSQL header comments and SET statements, confirming a valid dump was created.

4. Method 2 — Backing Up All Databases with pg_dumpall

When you need to back up every database in a PostgreSQL instance — including global objects such as roles and tablespaces — pg_dumpall is the correct tool.

pg_dumpall -U postgres -W > /backups/all_databases_$(date +%Y%m%d).sql

This command exports:

• All databases
• All roles (users and groups)
• All tablespaces
• All global configuration

Important: The output file from pg_dumpall can be very large on busy servers. Ensure your backup partition has adequate space and consider compressing the output immediately:

pg_dumpall -U postgres | gzip > /backups/all_databases_$(date +%Y%m%d).sql.gz

5. Method 3 — Custom Format Backups for Large Databases

For production databases exceeding a few gigabytes, the custom format (-F c) is strongly recommended over plain SQL dumps. Custom format backups are:

• Compressed by default — significantly reducing storage requirements
• Faster to restore — supporting parallel restore operations with -j flag
• Selectively restorable — allowing you to restore individual tables or schemas

Creating a Custom Format Backup

pg_dump -U postgres -W -F c my_production_db > /backups/my_production_db_$(date +%Y%m%d).dump

Creating a Compressed Directory Format Backup (Supports Parallelism)

pg_dump -U postgres -W -F d -j 4 -f /backups/my_production_db_dir my_production_db

This approach dramatically reduces backup duration on multi-core servers, making it ideal for the high-performance dedicated server environments available at AlexHost.

6. Restoring from SQL Dumps

Restore a Single Database from a Plain SQL Dump

First, ensure the target database exists. If it does not, create it:

psql -U postgres -c "CREATE DATABASE my_restored_db;"

Then restore:

psql -U postgres -d my_restored_db -f /backups/my_production_db_backup.sql

Parameter breakdown:

Monitor Restoration Progress

For large SQL files, you can monitor progress using pv:

pv /backups/my_production_db_backup.sql | psql -U postgres -d my_restored_db

7. Restoring from Custom Format Dumps

Custom format dumps require the pg_restore utility rather than psql.

Basic Restore

pg_restore -U postgres -d my_restored_db /backups/my_production_db.dump

Restore and Create the Database Automatically

Use the -C flag to instruct pg_restore to create the database before populating it:

pg_restore -U postgres -C -d postgres /backups/my_production_db.dump

Parallel Restore for Faster Recovery

pg_restore -U postgres -d my_restored_db -j 4 /backups/my_production_db_dir/

Using -j 4 with a directory format backup can reduce restore time by up to 75% on a quad-core server — a significant advantage when minimizing downtime during disaster recovery.

Restore a Specific Table Only

pg_restore -U postgres -d my_restored_db -t orders /backups/my_production_db.dump

This granular capability is one of the key advantages of the custom format over plain SQL dumps.

8. Method 4 — Continuous Archiving and Point-in-Time Recovery (PITR)

PITR is the gold standard for mission-critical PostgreSQL deployments. It allows you to restore your database to any specific moment in time — not just the last backup — by replaying Write-Ahead Log (WAL) segments on top of a base backup. This is essential for scenarios where you need to recover from a logical error (such as an accidental DROP TABLE) that occurred at a known timestamp.

Step 1: Enable WAL Archiving in postgresql.conf

Locate and edit your PostgreSQL configuration file:

nano /etc/postgresql/15/main/postgresql.conf

Add or modify the following directives:

# Enable WAL archiving
wal_level = replica
archive_mode = on
archive_command = 'cp %p /var/lib/postgresql/wal_archive/%f'

Parameter explanation:

Create the archive directory and set correct permissions:

mkdir -p /var/lib/postgresql/wal_archive
chown postgres:postgres /var/lib/postgresql/wal_archive
chmod 700 /var/lib/postgresql/wal_archive

Restart PostgreSQL to apply changes:

systemctl restart postgresql

Step 2: Take a Base Backup with pg_basebackup

pg_basebackup -U postgres -D /backups/base_backup -Ft -z -P -Xs

Step 3: Restore to a Specific Point in Time

To restore from a base backup and WAL archives:

1. Stop PostgreSQL:

systemctl stop postgresql

1. Clear the existing data directory:

rm -rf /var/lib/postgresql/15/main/*

1. Extract the base backup:

tar -xzf /backups/base_backup/base.tar.gz -C /var/lib/postgresql/15/main/

1. Create a recovery.conf (PostgreSQL 11 and earlier) or configure postgresql.conf and create a recovery.signal file (PostgreSQL 12+):

# For PostgreSQL 12+
touch /var/lib/postgresql/15/main/recovery.signal

Add to postgresql.conf:

restore_command = 'cp /var/lib/postgresql/wal_archive/%f %p'
recovery_target_time = '2025-01-15 14:30:00'
recovery_target_action = 'promote'

1. Set correct ownership and start PostgreSQL:

chown -R postgres:postgres /var/lib/postgresql/15/main/
systemctl start postgresql

PostgreSQL will replay WAL segments up to the specified timestamp and then promote to a normal read-write state.

9. Automating Backups with Cron

Manual backups are unreliable. Automating your backup schedule with cron ensures consistency and removes the human error factor.

Create a Backup Script

nano /usr/local/bin/pg_backup.sh

#!/bin/bash

# PostgreSQL Automated Backup Script
BACKUP_DIR="/backups/postgresql"
TIMESTAMP=$(date +%Y%m%d_%H%M%S)
DB_USER="postgres"
DB_NAME="my_production_db"
RETENTION_DAYS=14

# Create backup directory if it doesn't exist
mkdir -p "$BACKUP_DIR"

# Perform the backup
pg_dump -U "$DB_USER" -F c "$DB_NAME" > "$BACKUP_DIR/${DB_NAME}_${TIMESTAMP}.dump"

# Remove backups older than retention period
find "$BACKUP_DIR" -name "*.dump" -mtime +"$RETENTION_DAYS" -delete

# Log completion
echo "[$TIMESTAMP] Backup of $DB_NAME completed successfully." >> /var/log/pg_backup.log

Make the script executable:

chmod +x /usr/local/bin/pg_backup.sh

Schedule with Cron

crontab -e

Add the following line to run a backup every night at 2:00 AM:

0 2 * * * /usr/local/bin/pg_backup.sh

For weekly full backups plus daily incremental WAL archiving, combine this with the PITR setup described in the previous section.

10. Securing and Storing Backups Offsite

A backup stored on the same server as your production database is not a real backup — it is a single point of failure. Implement the following security and offsite storage practices:

Encrypt Backups Before Transfer

gpg --symmetric --cipher-algo AES256 /backups/my_production_db.dump

Transfer Backups to a Remote Location with rsync

rsync -avz --progress /backups/postgresql/ user@remote-backup-server:/remote/backups/postgresql/

Use pg_dump with SSH Pipe for Direct Remote Backup

pg_dump -U postgres my_production_db | gzip | ssh user@remote-server "cat > /backups/my_production_db_$(date +%Y%m%d).sql.gz"

Firewall Rules for PostgreSQL (UFW)

Restrict PostgreSQL port access to trusted IPs only:

ufw allow from 192.168.1.0/24 to any port 5432
ufw deny 5432
ufw enable

For teams managing multiple projects across different hosting tiers, AlexHost Shared Web Hosting plans also support database management tools that can complement your backup workflows for smaller projects.

11. Best Practices Summary

Implementing PostgreSQL backups correctly requires discipline and a layered approach. Follow these best practices to ensure your data is always protected:

> Critical reminder: A backup that has never been tested is not a backup — it is an assumption. Schedule regular restore drills and document the results.

Conclusion: Protect Your PostgreSQL Data with Confidence on AlexHost

PostgreSQL offers one of the most comprehensive backup and recovery toolsets of any open-source database system. From the simplicity of pg_dump for rapid SQL snapshots to the surgical precision of PITR for granular point-in-time recovery, you have everything you need to build a bulletproof data protection strategy.

The key is execution: automate your backups, verify them regularly, encrypt them before transfer, and store them offsite. Combined with the performance and reliability of AlexHost Dedicated Servers — featuring NVMe storage, full root access, and enterprise-grade DDoS protection — your PostgreSQL databases will be both fast and resilient.

For teams that need scalable infrastructure without the overhead of managing bare metal, AlexHost VPS Hosting offers a flexible, cost-effective alternative with the same commitment to performance and uptime. And if you need to secure your database-backed web applications end-to-end, pairing your hosting with AlexHost SSL Certificates ensures encrypted communication between your application layer and your users.

Start implementing these backup strategies today. Your future self — facing a 3 AM incident with a corrupted production database — will thank you for it.