[6 Methods] How to Backup and Restore PostgreSQL Databases

Why PostgreSQL Backup and Recovery are Essential?

PostgreSQL is popular open-source object-relational database management system (RDBMS). For any business driven by PostgreSQL, the database can be the most critical asset.  

Whether you are a developer managing a local project or a DBA overseeing a production cluster, a reliable strategy to backup PostgreSQL database important for protecting data from human error, system failure, ransomware attack and ensuring compliance.

• Human Error: The “accidental delete.” A single poorly written DROP TABLE or DELETE command without a WHERE clause can wipe out years of data in milliseconds.
• Hardware & System Failures: Even the best SSDs fail, and cloud regions can go offline. Without a backup stored in a separate location, your data dies with the hardware.
• Ransomware & Cyber Attacks: Databases are prime targets for hackers. If your data is encrypted by a malicious actor, an off-site backup is often your only way to recover without paying a ransom.
• Compliance Requirements: Laws like GDPR, HIPAA, and SOC2 often mandate that you maintain verifiable data retention and recovery procedures.

In this guide, we will walk you through the most reliable methods to backup and restore your PostgreSQL database, from the command line and pgAdmin to modern Docker environments and automation.

Method 1. Backup PostgreSQL with Command Line

The command-line interface (CLI) is the common way to manage PostgreSQL backups.It uses two native utilities: pg_dump for creating the backup and psql or pg_restore for bringing the data back. This method support backup specific tables, and placed into scripts for scheduling. but it doesn’t support point-in-time recovery.

Here are detailed steps:

Step 1. Navigate to PostgreSQL bin folder. Usually it locates: C:\Program Files\PostgreSQL\16\.

Step 2. Find the “bin” folder and right-click on it, and choose “Open in Terminal“.

Step 3. To create a standard backup of a single database, use the following structure:

pg_dump -U [username] -W -F t [database] > c:\[backup location]

To restore postgreSQL, you can use the following scripts.

1. Restoring a Plain Text (.sql) File:

psql -U [username] -d [target_database] < [backup_file].sql

2. Restoring a Custom/Compressed (.dump) File:

Required if you used the -Fc flag.

pg_restore -U [username] -d [target_database] [backup_file].dump

Method 2. Backup PostgreSQL Database using pgAdmin 4 (GUI)

pgAdmin 4 provides a powerful graphical way to manage backups. This method is ideal for one-off backups, beginners, or developers who want to visually select specific database objects. But pgAdmin is harder to automate; slower for very large databases

Follow the steps below:

Step 1. Open pgAdmin, expand the “Servers” tree, and find the database you wish to backup.

Step 2. Right-click the database name and select “Backup…” from the context menu.

Step 3. Then configure the setting:

• Filename: Click the folder icon to choose your storage path and give the file a name (e.g., my_db_backup.dump).
• Format: Select Custom (recommended for better compression and flexible restores) or Plain (for a readable SQL script).

Step 4. In the Options tab, you can choose to “Only Data” or “Only Schema” if you don’t need a full backup.

Step 5. Click Backup to execute. You can monitor the progress in the “Processes” dashboard at the bottom right.

To restore database from pgadmin4, follow the steps below:

Step 1. Create a fresh, empty target database

Step 2. Right-click the target database and select “Restore…“.

Step 3. Select the File:

• Format: Choose the format that matches your backup (e.g., Custom or Tar).
• Filename: Browse to find your .dump or .sql file.

Step 4. Under the Options tab, enabling “Clean before restore” is useful if you want to drop existing objects before recreating them.

Step 5. Click “Restore”. A notification will appear once the process is complete.

Method 4. Using DBeaver for Quick Dumps

DBeaver is a favorite among developers because it is a universal tool that works with almost any database engine. While it provides a slick interface for “Quick Dumps,” it relies on your local PostgreSQL installation to do the heavy lifting.

DBeaver comes with intuitive UI and useful for quick data export, but it is not the best option for scheduling protection and is heavywright since it is Java-based.

Then below steps are how to use DBeaver.

Preparation:Before you can back up, DBeaver needs to know where your PostgreSQL tools (pg_dump) are located on your computer.

Right-click your connection in the database navigator and select Edit Connection.

Go to Connection Settings > Local Client.

LCLick Browse and point it to the bin folder of your PostgreSQL installation (e.g, C:\Program Files\PostgreSQL\15\bin on Windows or /usr/bin on Linux).

If you don’t have Postgres installed locally, DBeaver may offer to download the native client binaries for you.

After the prepration, following the steps below.

Step 1. Right-click on your database name, navigate to “Tools“, and select “Backup“.

Step 2. Choose the specific schemas or tables you want to include.

Step 3. Choose a formation – Plain or Custom

• Plain: Best for readable SQL scripts.
• Custom: Best for compressed, professional-grade backups.

Step 4. Choose your destination (local disk or external drive) and click “Start“.

Method 5. Backup PostgreSQL Automatically (Cron Jobs)

Manual backups are fine for one-off tasks, but production databases require automation. On Linux and macOS, the most reliable way to schedule backups is using Cron Jobs. But if you are not familier with Cron Jobs, it may different to operate for you.

Part 1. Handling Passwords

Cron jobs run in the background, so they cannot “type” a password when prompted. You have two professional options to handle this:

• The .pgpass file): Create a file in your home directory named .pgpass with the format hostname:port:database:username:password. Set permissions to 600 (chmod 600 ~/.pgpass) so only you can read it.
• Option B (Environment Variables): Define PGPASSWORD directly inside your backup script (best for simple setups).

Part 2. The Script and Schedule

Instead of putting a long command directly in the crontab, it is better to create a simple bash script (backup_db.sh). This allows you to add features like date-stamping and automatic cleanup.

#!/bin/bash

# Configuration

DB_NAME=”my_database”

BACKUP_DIR=”/home/user/backups”

DATE=$(date +%Y-%m-%d_%H-%M)

# Create the backup (compressed)

export PGPASSWORD=’your_secure_password’

pg_dump -U postgres -d $DB_NAME | gzip > $BACKUP_DIR/db_backup_$DATE.sql.gz

# Optional: Delete backups older than 30 days to save space

find $BACKUP_DIR -type f -name “*.sql.gz” -mtime +30 -delete

Part 3. Scheduling the Cron Job

The Crontab is the schedule where you tell the system when to run your script.

Step 1. Open the editor: crontab -e

Step 2. Add a line at the bottom to schedule the backup. For example, to run every night at 2:00 AM:

00 02 * * * /home/user/scripts/backup_db.sh >> /home/user/logs/backup.log 2>&1

Breakdown of the Cron Schedule:

• >00 02: Run at minute 0 of hour 2 (2:00 AM).
• >* * *: Every day of the month, every month, and every day of the week.
• >>> … 2>&1: This captures any errors and saves them to a log file—crucial for troubleshooting!

Method 6. i2Backup: Enterprise-Grade Automation and Easiness

While manual scripts and open-source GUIs are effective for smaller projects, enterprise environments with massive datasets and strict uptime requirements often need a more robust, “hands-off” solution.

For organizations that cannot afford data loss or manual configuration errors, i2Backup from Info2soft provides a professional and easy way to create scheduled backups for all critical PostgreSQL databases. It support multiple backup storage, like local/external disk, ZFS, NAS, deduplicate stoage, AWS S3 storage, and so on.

Key Features and advantages of i2Backup for PostgreSQL:

• Centralized Management: Manage backups for hundreds of PostgreSQL instances across physical servers, virtual machines, and cloud environments from a single dashboard.
• Automated Workflow: Eliminate human error with intelligent scheduling. The solution automatically implements backups based on your schedule.
• Incremental Backup: Instead of dumping the entire database every time (which consumes massive bandwidth and storage), i2Backup captures only the data that has changed. This results in faster windows and significantly reduced storage costs.
• Deduplication: Configure backup retention rules, it can automatically remove outdated backup version to optimize your storage.
• Scalable Backup: Built to grow with your infrastructure. Whether you are managing a single local instance or thousands of PostgreSQL nodes, i2Backup provides a centralized console to manage them all.
• Point-in-Time Recovery (PITR): Easily roll back your database to the exact second before a corruption event or accidental deletion occurred.

Comparison: i2Backup vs. Manual Methods

Conclusion

Selecting the right method to backup PostgreSQL databases depends entirely on your environment and the value of your data. For developers and small projects, native command-line tools and GUIs like pgAdmin offer a reliable, free way to manage manual snapshots and simple automation. These methods provide the flexibility needed for local development and minor production tasks without additional costs.

However, as data scales, manual scripts often become a liability due to “invisible” failures and high storage demands. For mission-critical environments, an enterprise solution like Information2’s i2Backup is the most efficient choice, offering automated, incremental, and scalable protection that manual methods can’t match. Besides backup PostgreSQL, it also support backup many other platforms, like SQL Server data backup.