Loading...

中文 日本語
By: Dylan

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]

pgdump Backup postgreSQL

Step 4. You will be asked the password. Type your password and press “Enter“. When the backup is finished, go to the backup location and check if the backup files.

Note:

1. If you want to backup all PostgreSQL, please use the ./pg_dumall. If you want to only backup schema, please use ./pg_dumpall.

2. If you are using Docker, to backup PostgreSQL databases in Docker:

  • Find your container name using docker ps
  • >Run the dump command to backup PostgreSQL through Docker:
  • Ensure the backup.sql file appears on your local folder.

3. If you want to backup PostgreSQL to AWS S3, please configure the AWS CLI:

  • Run aws configure to enter your Access Key ID and Secret Access Key.
  • Specify your preferred AWS Region and set the default output format to json.
  • Ensure your IAM user has the S3:PutObject permission.
  • Then set the AWS as the 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.Backup postgreSQL pgadmin4

    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.

    Tip: If you choose the Plain format in pgAdmin, it creates a standard SQL file. To restore this, you should use the Query Tool to run the script or the psql command line, as the “Restore” dialog in pgAdmin is primarily designed for Custom and Tar formats.

    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“.

      backup postgresql dbeaver

      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

      Important: After saving the script, make it executable by running: chmod +x backup_db.sh.

      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

      Feature Manual Scripts (Cron/CLI) i2Backup (Enterprise)
      Setup High manual effort per server Fast, policy-based deployment
      Data Transfer Usually full dumps only  True Incremental support 
      Monitoring Manual log checking  Automated alerts and reporting 
      Scalability Hard to manage at scale Scalable backup node for growth 

      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.

      {{ author_info.name }}
      {{author_info.introduction || "No brief introduction for now"}}

      More Related Articles

      {{item.title}}
      {{item.excerpt}}
      Read Article
      Table of Contents:
      Stay Updated on Latest Tips
      Subscribe to our newsletter for the latest insights, news, exclusive content. You can unsubscribe at any time.
      Subscribe
      Ready to Enhance Business Data Security?
      Start a 60-day free trial or view demo to see how Info2Soft protects enterprise data.
      Free Trial Contact Sales
      Table of Contents:
      {{ country.name }}
      Please fill out the form and submit it, our customer service representative will contact you soon.
      By submitting this form, I confirm that I have read and agree to the Privacy Notice.
      {{ isSubmitting ? 'Submitting...' : 'Submit' }}