How to Backup and Restore PostgreSQL Databases with pgAdmin

If you are working with PostgreSQL databases, relying solely on command-line tools like pg_dump can be intimidating. Fortunately, pgAdmin 4 provides a robust Graphical User Interface (GUI) to handle backups seamlessly.

In this guide, we will cover not just the basic clicks, but the critical configuration settings (like Binary Paths and Format types) that usually cause errors for beginners.

---

Critical Prerequisite: Binary Path Configuration

Before you start: The most common error users face is "Utility file not found." This happens because pgAdmin doesn't know where your PostgreSQL installation is.

1. Go to File > Preferences.
2. Navigate to Paths > Binary paths.
3. Scroll to "PostgreSQL Binary Path".
4. Set it to your bin folder (e.g., C:\Program Files\PostgreSQL\16\bin).

---

Step 1: Creating a Backup

The backup process creates a snapshot of your schema and data.

1. Right-click your database name in the browser tree.
2. Select Backup...
3. Filename: Click the folder icon. Important: You must manually type the extension (e.g., my_backup.sql or my_backup.backup).
4. Format Selection:
   • Custom/Tar: Best for restoring into pgAdmin later. Creates a compressed binary file.
   • Plain: Creates a readable SQL script. Choose this if you want to open the code in a text editor.
5. Click Backup. You should see a green notification box with "Process completed."

---

Step 2: Restoring a Database

Restoring overwrites existing data, so ensure you are targeting the correct database.

1. Create a Blank DB: You cannot restore a full backup into a database that doesn't exist. Right-click Databases > Create > Database.
2. Right-click the new (empty) database and select Restore....
3. Filename: Select the file you created in Step 1.
4. Role Name: In the "Restore options" tab, ensure the "Role name" matches your current user (usually postgres) to avoid permission errors.
5. Click Restore.

---

Troubleshooting Common Errors

If your process fails with "Exit Code 1", check these issues:

• Version Mismatch: You cannot restore a backup from a newer version of PostgreSQL (e.g., v16) into an older server (e.g., v14).
• Active Connections: If users are connected to the database, the restore might fail. Disconnect the database first via the Dashboard.

---

Watch the Live Demo

To see the "Binary Path" configuration and the backup process in real-time, watch the full tutorial below:

---

Found this helpful? Subscribe to the channel for more Data Engineering guides.