pgAdmin 4 vs. pgAdmin 3: Key Differences and Migration Steps
Overview
pgAdmin 4 is a complete redesign of pgAdmin 3, built as a web-based application (with optional desktop wrappers) to provide a modern, extensible GUI for PostgreSQL. pgAdmin 3 is a legacy desktop-only app that reached end-of-life and lacks support for newer PostgreSQL features. This article compares the two and gives step-by-step migration guidance.
Key differences
-
Architecture
- pgAdmin 3: Native desktop application (C++/wxWidgets).
- pgAdmin 4: Web application (Python/Flask + JavaScript). Can run as a server or bundled desktop app.
-
Compatibility and support
- pgAdmin 3: No longer actively maintained; limited support for recent PostgreSQL features (e.g., newer data types, extensions).
- pgAdmin 4: Actively maintained with regular releases, compatibility with current PostgreSQL versions and features.
-
User interface and experience
- pgAdmin 3: Traditional desktop UI — lightweight and fast for simple tasks.
- pgAdmin 4: Modern web UI — more flexible, responsive, and supports richer workflows (dashboards, panels, integrated query tool with explain/analyze visualizations).
-
Extensibility and features
- pgAdmin 3: Limited plugin/extension capability.
- pgAdmin 4: Extensible via plugins and configurable server settings; supports roles, preferences per user, multi-user deployments, and REST endpoints for programmatic access.
-
Security and multi-user support
- pgAdmin 3: Single-user desktop app; security depends on local OS controls.
- pgAdmin 4: Can be deployed as a central server with authentication, role separation, TLS, and session handling for multiple users.
-
Resource usage
- pgAdmin 3: Lower memory/CPU for basic operations.
- pgAdmin 4: Higher overhead due to web server and browser, but gains in functionality and remote access.
When to migrate
- You should migrate if you need support for modern PostgreSQL versions, multi-user centralized access, browser-based access, active maintenance, or newer features (debugging, dashboards, plugins). If you only need a lightweight local GUI and no recent PostgreSQL features, pgAdmin 3 may still work but is not recommended.
Migration checklist (high-level)
- Inventory servers and connections used in pgAdmin 3.
- Export connection and server configuration details.
- Install pgAdmin 4 (server mode or desktop).
- Recreate servers and connections in pgAdmin 4 or import where possible.
- Verify queries, saved scripts, and dashboards.
- Update workflows (backups, scheduled tasks, user access).
- Decommission pgAdmin 3 after validation.
Step-by-step migration (presumptive, practical steps)
1. Prepare and back up
- Backup critical data: Use pg_dump/pg_dumpall for databases and pgbasebackup for clusters as appropriate.
- Export saved queries/scripts: Locate saved SQL files and export them from pgAdmin 3 (copy .sql or saved project files).
- Document connection details: Hostnames/IPs, ports, database names, usernames, SSL settings, SSH tunnel info.
2. Install pgAdmin 4
- On Linux (example, Debian/Ubuntu):
Code
curl https://www.pgadmin.org/static/packages_pgadmin_org.pub | sudo apt-key add - sudo sh -c ‘echo “deb https://ftp.postgresql.org/pub/pgadmin/pgadmin4/apt/ $(lsb_release -cs) pgadmin4” > /etc/apt/sources.list.d/pgadmin4.list’ sudo apt update sudo apt install pgadmin4-desktop# or pgadmin4-web for server mode - On Windows/macOS: Download installer from pgadmin.org and run the installer; choose Desktop or Server mode per needs.
- Server mode: Configure web server, set up initial admin user and enable HTTPS/TLS if exposing to network.
3. Recreate connections and servers
- Open pgAdmin 4.
- For each server from your inventory:
- Right-click “Servers” → Create → Server.
- Enter General name, Connection details (host, port, maintenance DB, username, password).
- Configure SSL and SSH tunnel (SSH tunnel settings available under Connection → SSH Tunnel).
- Optionally store passwords in pgAdmin 4’s saved connections or use external credential managers.
4. Import or migrate saved objects
- Saved SQL files: Use Query Tool → Open file → Run or save into pgAdmin 4’s Query Tool history/projects.
- Maintenance jobs: Recreate scheduled tasks outside pgAdmin (cron, Task Scheduler) or use pgAgent if used previously.
- Dashboards & layout: Rebuild custom dashboards using pgAdmin 4’s dashboards and graphs.
5. Verify and test
- Run representative queries and administrative tasks: backups, restores, VACUUM, pg_stat_activity inspection.
- Test SSL and SSH connections.
- Confirm performance and resource usage on target host.
6. Configure multi-user server (if applicable)
- Create user accounts in pgAdmin 4 (web server mode uses internal authentication or configured OAuth/LDAP).
- Configure roles and permissions for shared servers.
- Enable HTTPS, configure reverse proxy (nginx/apache), and enforce strong authentication.
7. Decommission pgAdmin 3
- After validation and user training, uninstall pgAdmin 3 or remove from workstations.
- Archive old configs and saved scripts.
Migration tips and pitfalls
- No direct automatic import: There’s no official one-click import from pgAdmin 3 server definitions; manual recreation is usually required.
- SSH tunneling differences: pgAdmin 4 stores SSH settings differently; test each tunneled connection.
- Saved layouts and UI habits: Expect a learning curve—some workflows moved or renamed.
- Performance tuning: For large deployments, run pgAdmin 4 in server mode behind a reverse proxy with caching and sufficient CPU/RAM.
- Use pgAdmin docs: Refer to official pgAdmin documentation for version-specific instructions and advanced configuration.
Example quick checklist for a small setup (3 servers)
- Backup DBs (pg_dumpall).
- Copy saved SQL scripts to a folder.
- Install pgAdmin 4 desktop.
- Create three Server entries with same credentials and test.
- Run sample backups/restores and verify.
- Remove pgAdmin 3 after 24–48 hours of successful parallel use.
Conclusion
pgAdmin 4 is the supported, modern replacement for pgAdmin 3 with web-based access, active updates, and richer features. Migration involves inventorying connections, installing pgAdmin 4, recreating servers, importing scripts, testing, and optionally configuring multi-user server mode. Plan backups and testing to ensure a smooth transition.
Leave a Reply