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.

Feature flag article image

Create a feature flag in your IDE in 5 minutes with LaunchDarkly’s MCP server ⏰

How to create, evaluate, and modify flags from within your IDE or AI client using natural language with LaunchDarkly's new MCP server. Follow along with this tutorial for step by step instructions.

Read full post

Top comments (0)

Feature flag article image

Create a feature flag in your IDE in 5 minutes with LaunchDarkly’s MCP server 🏁

How to create, evaluate, and modify flags from within your IDE or AI client using natural language with LaunchDarkly's new MCP server. Follow along with this tutorial for step by step instructions.

Read full post

👋 Kindness is contagious

Dive into this thoughtful piece, beloved in the supportive DEV Community. Coders of every background are invited to share and elevate our collective know-how.

A sincere "thank you" can brighten someone's day—leave your appreciation below!

On DEV, sharing knowledge smooths our journey and tightens our community bonds. Enjoyed this? A quick thank you to the author is hugely appreciated.

Okay