DEV Community

Cover image for PostgreSQL Backups Simplified with pg_dump
DbVisualizer
DbVisualizer

Posted on

1

PostgreSQL Backups Simplified with pg_dump

pg_dump is an essential tool for creating PostgreSQL backups. This guide highlights key features and examples to streamline your backup process.

SQL Script Backup

pg_dump -U admin -d company -f company_backup.sql
Enter fullscreen mode Exit fullscreen mode

Restore using:

psql -d new_company -f company_backup.sql
Enter fullscreen mode Exit fullscreen mode

Directory-Format Archive

pg_dump -U admin -d company -F d -f company_backup
Enter fullscreen mode Exit fullscreen mode

Produces a directory with .dat.gz files.

Export Data Only

pg_dump -U admin -d company -f company_backup.sql --data-only
Enter fullscreen mode Exit fullscreen mode

Export Specific Schemas

pg_dump -U admin -d company -n 'p*' -f company_backup.sql
Enter fullscreen mode Exit fullscreen mode

FAQ

Where does pg_dump output?
Outputs to stdout; redirect with >.

Path to pg_dump.exe?
Found in C:\Program Files\PostgreSQL\<version>\bin\pg_dump.exe.

Can pg_dump be run remotely?
Yes, using -h, -U, and -d options with remote access enabled.

pg_dump vs. pg_dumpall?
pg_dump backs up single databases; pg_dumpall backs up all databases on the server.

Conclusion

pg_dump simplifies PostgreSQL backups. For more detailed guidance, read the article A Complete Guide to pg_dump With Examples, Tips, and Tricks.

If this post was valuable to you

Please leave your appreciation by commenting!

Everyone is welcome

Top comments (0)

A Workflow Copilot. Tailored to You.

Pieces.app image

Our desktop app, with its intelligent copilot, streamlines coding by generating snippets, extracting code from screenshots, and accelerating problem-solving.

Read the docs

👋 Kindness is contagious

Explore a sea of insights with this enlightening post, highly esteemed within the nurturing DEV Community. Coders of all stripes are invited to participate and contribute to our shared knowledge.

Expressing gratitude with a simple "thank you" can make a big impact. Leave your thanks in the comments!

On DEV, exchanging ideas smooths our way and strengthens our community bonds. Found this useful? A quick note of thanks to the author can mean a lot.

Okay