Backing up your PostgreSQL database should be simple and repeatable. This guide walks you through creating a clean SQL dump using DBeaver, with settings that make restores smooth across different servers. Follow along step by step.
What You’ll Need
- DBeaver installed on your machine
- Access to your PostgreSQL database
- The
pg_dumpclient installed and accessible on your system
Step 1: Install DBeaver
- Download DBeaver from the official website and install it.
- Linux users: If you installed DBeaver via Snap, remove the Snap version first. Snap apps are sandboxed and may block access to root-level directories outside the app’s installation folder.
Step 2: Open the Backup Tool
- Launch DBeaver and connect to your PostgreSQL database.
- In the Database Navigator, right-click your database.
- Select Tools > Backup.
Step 3: Point DBeaver to pg_dump
- In the Backup dialog, click Load Client.
- Browse to your
pg_dumpexecutable.- Linux tip: It’s typically located in
/usr/bin/.
- Linux tip: It’s typically located in
Step 4: Choose What to Back Up
- Select the schemas you want to include in the backup.
- Click Next.
Step 5: Use a Plain SQL Format
- Set Format to Plain.
Optional: Make the Dump Easier to Restore Elsewhere
If you plan to restore on a different server or under a different user, consider creating a dump without ownership and privilege statements. This keeps the SQL portable and avoids permission conflicts during restore.
Note: The user who runs the restore will become the owner of the restored objects.
Step 6: Adjust Ownership and Privilege Options
- Enable the following options:
- Do not backup privileges (GRANT/REVOKE)
- Discard objects owner
Step 7: Start the Backup
- Click Start.
- Wait for the process to complete. You should find the SQL dump file in your selected output directory.
Where to Find Your Dump
After the backup finishes, DBeaver saves the .sql file to the directory you chose in the Backup dialog. Keep this file safe and consider versioning it if you are managing multiple environments.
Why These Settings?
- Plain format: Produces human-readable SQL that you can inspect, version-control, and run with standard PostgreSQL tools.
- Without privileges and ownership: Avoids restore-time permission errors and makes it easier to import into a fresh database or under a different user.
Quick Restore Tip
To restore a Plain SQL dump, you can use psql from the command line:
psql -h <host> -U <user> -d <database> -f /path/to/backup.sql
Final Thoughts
This setup gives you a portable, clean backup that is easy to restore in development, staging, or production. Run backups regularly and store them in a secure location. If you automate this process, keep the same options to ensure consistency.
