Backups are critical in database management, and PostgreSQL offers a powerful tool for this purpose: pg_dump. This guide will walk you through using pg_dump to create efficient and reliable backups.
What Is pg_dump?
pg_dump is a command-line utility for creating backups of a PostgreSQL database. It can export an entire database or specific parts, such as individual tables or schemas. The output can be:
SQL Script: A plain-text file with SQL commands to recreate the database.
Directory-Based Archive: A set of folders designed to be portable across architectures, to be restored using pg_restore.
pg_dump ensures consistent backups even when the database is in use.
pg_dump [options] [dbname]
Dump a Database Into an SQL Script
pg_dump -U admin -d company -f company_backup.sql
Dump a Database With Create Command
pg_dump -U admin -d company -f company_backup.sql –create
Dump a Database Into a Directory-Format Archive
pg_dump -U admin -d company -F d -f company_backup
Export Data Only
pg_dump -U admin -d company -f company_backup.sql –data-only
Export Schema Only
pg_dump -U admin -d company -f company_backup.sql –schema-only
*Include Only Specific Schemas
pg_dump -U admin -d company -n ‘p*’ -f company_backup.sql
Include Only Specific Tables
pg_dump -U admin -d company -t ‘order‘ -f company_backup.sql
pg_dump is a versatile and powerful tool for PostgreSQL backups. Understanding its options and commands allows you to create precise and effective backups tailored to your needs. For more advanced database management, consider tools like DbVisualizer, which simplify the process with a graphical interface.
