DEV Community

Eddie Gulay
Eddie Gulay

Posted on

PostgreSQL Backup the quick Way

#postgres #tutorial #devops #database

PostgreSQL is one of the most reliable open-source databases, but backups are often handled in overly complex ways — involving filesystem snapshots, replication configurations, or manual tar archives.
If you just need a clean, portable, and consistent backup of all your databases, PostgreSQL already includes a simple tool for that: pg_dumpall.

This post shows how to create a full backup of your PostgreSQL server in one line — and how to move it safely to your local machine.

Why “Logical” Backups Are Enough

There are two kinds of PostgreSQL backups:

  1. Physical backups — copy all data files on disk; good for point-in-time recovery but heavy and less portable.
  2. Logical backups — dump all SQL definitions and data; lighter, portable, and perfect for everyday snapshots or migrations.

If your goal is to have a restorable .sql file containing every database, user, and permission, a logical backup is the right choice.

Step 1 — Create the Backup

Run this command directly on your server:

sudo -u postgres pg_dumpall > /tmp/october_4_2025_backup.sql
Enter fullscreen mode Exit fullscreen mode

This command:

  • Connects as the postgres superuser
  • Dumps all databases, roles, and grants
  • Produces one plain-text SQL file ready for restoration

You can name the file however you prefer — for example, include the date:

/tmp/october_4_2025_backup.sql
Enter fullscreen mode Exit fullscreen mode

Step 2 — Verify the Backup

Check that the backup file exists and has a reasonable size:

ls -lh /tmp/october_4_2025_backup.sql
Enter fullscreen mode Exit fullscreen mode

Optional: look at the first few lines:

head /tmp/october_4_2025_backup.sql
Enter fullscreen mode Exit fullscreen mode

Step 3 — Copy the Backup to Your Local Machine

Use scp (secure copy) to transfer the backup:

scp user@your_server_ip:/tmp/october_4_2025_backup.sql ~/Downloads/
Enter fullscreen mode Exit fullscreen mode

Replace:

  • user → your SSH username
  • your_server_ip → your server’s IP address

Now your backup is safely stored on your local machine.

Optional — Compress the Backup

If the database is large:

gzip /tmp/october_4_2025_backup.sql
scp user@your_server_ip:/tmp/october_4_2025_backup.sql.gz ~/Downloads/
Enter fullscreen mode Exit fullscreen mode

This typically reduces file size by 70–90%.

Step 4 — Restore the Backup (When Needed)

To restore everything to a new PostgreSQL instance:

psql -U postgres -f october_4_2025_backup.sql
Enter fullscreen mode Exit fullscreen mode

If compressed:

gunzip -c october_4_2025_backup.sql.gz | psql -U postgres
Enter fullscreen mode Exit fullscreen mode

This recreates all databases, roles, and data as they were during the backup.

Automation Tip

To schedule a daily or weekly backup automatically, create a cron job:

sudo crontab -u postgres -e
Enter fullscreen mode Exit fullscreen mode

Add this line:

0 2 * * * /usr/bin/pg_dumpall > /var/backups/pg_backup_$(date +\%F).sql
Enter fullscreen mode Exit fullscreen mode

That takes a nightly snapshot at 2 a.m. — hands-free.

Top comments (0)