pgAdmin4 is the leading open-source web-based management tool for PostgreSQL databases. It gives you a graphical interface to create, manage, and monitor your PostgreSQL instances without wrestling with psql commands for every operation. If you are running PostgreSQL 17 on FreeBSD 14 and want a proper management UI, pgAdmin4 is the standard choice. This guide walks through a full production setup – installing pgAdmin4 in a Python virtual environment, configuring it in server mode behind Nginx with SSL, and setting up an rc.d service so it starts on boot.
Prerequisites
Before starting, make sure you have the following in place:
- FreeBSD 14 system with root or sudo access
- PostgreSQL 17 installed and running
- Python 3.9+ installed
- Nginx installed (for reverse proxy with SSL)
- A valid domain name pointed to your server (for SSL certificates)
Update your package repository and install base dependencies first:
sudo pkg update && sudo pkg upgrade -y
Install the required packages for building pgAdmin4 and its Python dependencies:
sudo pkg install -y python39 py39-pip py39-virtualenv nginx postgresql17-client rust cargo libffi
Confirm Python is available:
python3.9 --version
Step 1 – Install pgAdmin4 in a Python Virtual Environment
The cleanest way to install pgAdmin4 on FreeBSD 14 is inside a Python virtual environment. This avoids conflicts with system packages and gives you full control over the version you run.
Create a dedicated user and directory for pgAdmin4:
sudo pw useradd -n pgadmin -d /opt/pgadmin4 -s /usr/sbin/nologin -c "pgAdmin4 Service User"
sudo mkdir -p /opt/pgadmin4
sudo chown pgadmin:pgadmin /opt/pgadmin4
Create the virtual environment and install pgAdmin4:
sudo -u pgadmin python3.9 -m venv /opt/pgadmin4/venv
sudo -u pgadmin /opt/pgadmin4/venv/bin/pip install --upgrade pip
sudo -u pgadmin /opt/pgadmin4/venv/bin/pip install pgadmin4
This takes a few minutes since pgAdmin4 pulls in a fair number of Python dependencies. Once it finishes, verify the installation:
/opt/pgadmin4/venv/bin/python -c "import pgadmin4; print('pgAdmin4 installed successfully')"
Step 2 – Configure pgAdmin4 for Server Mode
pgAdmin4 ships with a default configuration, but for a production server you need a local config file to override the defaults. Find the pgAdmin4 package directory first:
PGADMIN_DIR=$(/opt/pgadmin4/venv/bin/python -c "import os, pgadmin4; print(os.path.dirname(pgadmin4.__file__))")
echo $PGADMIN_DIR
Create the data and log directories:
sudo mkdir -p /var/lib/pgadmin4/data
sudo mkdir -p /var/lib/pgadmin4/sessions
sudo mkdir -p /var/lib/pgadmin4/storage
sudo mkdir -p /var/log/pgadmin4
sudo chown -R pgadmin:pgadmin /var/lib/pgadmin4 /var/log/pgadmin4
Now create the local configuration file:
sudo tee ${PGADMIN_DIR}/config_local.py <<'EOF'
import os
SERVER_MODE = True
DEFAULT_SERVER = '0.0.0.0'
DEFAULT_SERVER_PORT = 5050
DATA_DIR = '/var/lib/pgadmin4/data'
SESSION_DB_PATH = '/var/lib/pgadmin4/sessions'
STORAGE_DIR = '/var/lib/pgadmin4/storage'
SQLITE_PATH = '/var/lib/pgadmin4/data/pgadmin4.db'
LOG_FILE = '/var/log/pgadmin4/pgadmin4.log'
LOG_ROTATION_SIZE = 10 # In MB
LOG_ROTATION_AGE = 1440 # In minutes (1 day)
LOG_LEVEL = 20 # INFO level
MASTER_PASSWORD_REQUIRED = True
# Mail server settings for password resets
MAIL_SERVER = 'localhost'
MAIL_PORT = 25
MAIL_USE_SSL = False
MAIL_USE_TLS = False
# Default admin email - change this
PGADMIN_SETUP_EMAIL = '[email protected]'
EOF
Set proper ownership on the config file:
sudo chown pgadmin:pgadmin ${PGADMIN_DIR}/config_local.py
Run the setup script to initialize the database and create the admin account. You will be prompted for an email and password:
sudo -u pgadmin /opt/pgadmin4/venv/bin/python ${PGADMIN_DIR}/setup.py
Verify the database was created:
ls -la /var/lib/pgadmin4/data/pgadmin4.db
Step 3 – Create an rc.d Service for pgAdmin4
FreeBSD uses rc.d for service management (not systemd). Create a startup script so pgAdmin4 runs as a proper service:
sudo tee /usr/local/etc/rc.d/pgadmin4 <<'EOF'
#!/bin/sh
# PROVIDE: pgadmin4
# REQUIRE: LOGIN postgresql
# KEYWORD: shutdown
. /etc/rc.subr
name="pgadmin4"
rcvar="pgadmin4_enable"
load_rc_config $name
: ${pgadmin4_enable:="NO"}
: ${pgadmin4_user:="pgadmin"}
: ${pgadmin4_group:="pgadmin"}
pidfile="/var/run/${name}.pid"
command="/opt/pgadmin4/venv/bin/gunicorn"
command_args="--bind 127.0.0.1:5050 --workers 3 --daemon --pid ${pidfile} --user ${pgadmin4_user} --group ${pgadmin4_group} --access-logfile /var/log/pgadmin4/access.log --error-logfile /var/log/pgadmin4/error.log pgadmin4.app:create_app()"
start_cmd="${name}_start"
stop_cmd="${name}_stop"
status_cmd="${name}_status"
pgadmin4_start() {
echo "Starting ${name}."
/opt/pgadmin4/venv/bin/gunicorn \
--bind 127.0.0.1:5050 \
--workers 3 \
--daemon \
--pid ${pidfile} \
--user ${pgadmin4_user} \
--group ${pgadmin4_group} \
--access-logfile /var/log/pgadmin4/access.log \
--error-logfile /var/log/pgadmin4/error.log \
"pgadmin4.app:create_app()"
}
pgadmin4_stop() {
if [ -f ${pidfile} ]; then
echo "Stopping ${name}."
kill $(cat ${pidfile})
rm -f ${pidfile}
else
echo "${name} is not running."
fi
}
pgadmin4_status() {
if [ -f ${pidfile} ] && kill -0 $(cat ${pidfile}) 2>/dev/null; then
echo "${name} is running as pid $(cat ${pidfile})."
else
echo "${name} is not running."
fi
}
run_rc_command "$1"
EOF
sudo chmod +x /usr/local/etc/rc.d/pgadmin4
Install gunicorn inside the virtual environment to serve pgAdmin4 as a WSGI application:
sudo -u pgadmin /opt/pgadmin4/venv/bin/pip install gunicorn
Enable and start the service:
sudo sysrc pgadmin4_enable="YES"
sudo service pgadmin4 start
Confirm it is running:
sudo service pgadmin4 status
sockstat -4 -l | grep 5050
Step 4 – Set Up Nginx as a Reverse Proxy with SSL
Running pgAdmin4 behind Nginx gives you SSL termination and better performance for static assets. First, obtain an SSL certificate. Install certbot and get a Let’s Encrypt certificate:
sudo pkg install -y py39-certbot py39-certbot-nginx
sudo certbot certonly --standalone -d pgadmin.yourdomain.com
Create the Nginx configuration for pgAdmin4:
sudo tee /usr/local/etc/nginx/conf.d/pgadmin4.conf <<'EOF'
server {
listen 80;
server_name pgadmin.yourdomain.com;
return 301 https://$server_name$request_uri;
}
server {
listen 443 ssl http2;
server_name pgadmin.yourdomain.com;
ssl_certificate /usr/local/etc/letsencrypt/live/pgadmin.yourdomain.com/fullchain.pem;
ssl_certificate_key /usr/local/etc/letsencrypt/live/pgadmin.yourdomain.com/privkey.pem;
ssl_protocols TLSv1.2 TLSv1.3;
ssl_ciphers HIGH:!aNULL:!MD5;
ssl_prefer_server_ciphers on;
client_max_body_size 200M;
location / {
proxy_pass http://127.0.0.1:5050;
proxy_set_header Host $host;
proxy_set_header X-Real-IP $remote_addr;
proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
proxy_set_header X-Forwarded-Proto $scheme;
proxy_redirect off;
}
location /static/ {
alias /opt/pgadmin4/venv/lib/python3.9/site-packages/pgadmin4/static/;
expires 30d;
add_header Cache-Control "public, immutable";
}
}
EOF
Make sure the conf.d directory is included in your main nginx.conf. Then test and reload Nginx:
sudo mkdir -p /usr/local/etc/nginx/conf.d
echo 'include /usr/local/etc/nginx/conf.d/*.conf;' | sudo tee -a /usr/local/etc/nginx/nginx.conf
sudo nginx -t
sudo service nginx restart
Verify Nginx is proxying correctly:
curl -I https://pgadmin.yourdomain.com
Step 5 – Access the pgAdmin4 Web Interface
Open your browser and navigate to https://pgadmin.yourdomain.com. You will see the pgAdmin4 login screen. Enter the email and password you set during the setup step.
After logging in, you land on the pgAdmin4 dashboard. The left panel shows the server browser tree, and the main area shows system statistics and activity.
Step 6 – Connect to Your PostgreSQL Server
To add your local PostgreSQL 17 instance:
- Right-click Servers in the left panel and select Register > Server
- On the General tab, give it a name – something like “FreeBSD Local PG17”
- On the Connection tab, fill in:
- Host: 127.0.0.1 (or /tmp for socket connections)
- Port: 5432
- Maintenance database: postgres
- Username: postgres (or your admin user)
- Password: your PostgreSQL password
- Check Save password if you want it remembered
- Click Save
The server appears in the tree. Expand it to see databases, roles, and tablespaces. Verify the connection by expanding Databases > postgres > Schemas > public – you should see the standard system catalog.
Step 7 – Manage Databases, Tables, and Queries
With pgAdmin4 connected, you can handle all your day-to-day PostgreSQL work from the browser:
Create a database: Right-click Databases, select Create > Database. Enter the database name, select an owner, and configure encoding and collation as needed. Click Save.
Create tables: Expand your new database, navigate to Schemas > public > Tables. Right-click and choose Create > Table. Add columns, set data types, define primary keys, and configure constraints all from the dialog.
Run queries: Select a database and click the Query Tool icon (or use Tools > Query Tool). This opens a SQL editor with syntax highlighting, auto-completion, and result grids. Write your SQL, hit F5 or click the play button, and results appear below. You can save queries and export results to CSV.
View and edit data: Right-click any table and select View/Edit Data. You can filter, sort, and directly edit cell values in the grid. Changes are committed when you save.
Step 8 – Backup and Restore from pgAdmin4
pgAdmin4 wraps pg_dump and pg_restore with a graphical interface, which makes backup and restore operations straightforward.
To back up a database:
- Right-click the database in the tree and select Backup
- Choose a filename and location for the backup
- Select the format – Custom (.backup) is recommended for most cases since it supports selective restore
- Under Dump options, you can choose to include or exclude data, schemas, privileges, and more
- Click Backup and monitor progress in the process watcher
To restore a database:
- Create a new empty database (or use an existing target)
- Right-click the target database and select Restore
- Select the backup file
- Configure restore options – you can choose specific schemas or tables to restore
- Click Restore and watch the progress
Make sure the pg_dump and pg_restore binaries are in the pgAdmin4 user’s PATH. You can set the binary paths in pgAdmin4 under File > Preferences > Paths > Binary paths. Set the PostgreSQL 17 binary path to /usr/local/bin.
Step 9 – Configure pgAdmin4 for Remote PostgreSQL Servers
pgAdmin4 can manage multiple PostgreSQL servers across your infrastructure. To connect to a remote server, the process is the same as Step 6 – just enter the remote host’s IP or hostname instead of 127.0.0.1.
Before connecting, make sure the remote PostgreSQL server is configured to accept connections:
On the remote PostgreSQL server, edit postgresql.conf:
listen_addresses = '*'
Edit pg_hba.conf to allow your pgAdmin4 server’s IP:
# Allow pgAdmin4 server
host all all 10.0.0.5/32 scram-sha-256
Replace 10.0.0.5 with the actual IP of your pgAdmin4 host. Restart PostgreSQL on the remote server for changes to take effect:
sudo service postgresql restart
For connections over the internet, you can enable SSL on the connection tab in pgAdmin4. Set SSL mode to require or verify-full depending on your certificate setup. This encrypts traffic between pgAdmin4 and the remote PostgreSQL server.
You can also organize servers into groups. Right-click in the server browser and create server groups like “Production”, “Staging”, and “Development” to keep things organized when managing dozens of instances.
Troubleshooting
Here are common issues you may hit and how to fix them:
pgAdmin4 fails to start – “Permission denied” errors
Check ownership on the data and log directories:
ls -la /var/lib/pgadmin4/
ls -la /var/log/pgadmin4/
sudo chown -R pgadmin:pgadmin /var/lib/pgadmin4 /var/log/pgadmin4
502 Bad Gateway from Nginx
This means Nginx cannot reach the gunicorn backend. Verify gunicorn is actually listening:
sockstat -4 -l | grep 5050
If nothing shows up, check the gunicorn error log:
tail -50 /var/log/pgadmin4/error.log
Cannot connect to PostgreSQL – “connection refused”
Verify PostgreSQL is running and listening on the expected port:
sockstat -4 -l | grep 5432
sudo service postgresql status
Check pg_hba.conf allows connections from the method you are using (password, md5, scram-sha-256). After editing pg_hba.conf, reload PostgreSQL:
sudo service postgresql reload
Backup/Restore fails – “pg_dump not found”
pgAdmin4 needs to know where the PostgreSQL binaries live. Go to File > Preferences > Paths > Binary paths and set the PostgreSQL 17 path:
/usr/local/bin
Verify the binaries exist:
ls -la /usr/local/bin/pg_dump /usr/local/bin/pg_restore
SSL certificate renewal
Let’s Encrypt certificates expire every 90 days. Set up automatic renewal with a cron job:
echo '0 3 * * 0 root certbot renew --quiet --deploy-hook "service nginx reload"' | sudo tee -a /etc/crontab
High memory usage
If pgAdmin4 consumes too much memory, reduce the number of gunicorn workers. Edit the rc.d script and change --workers 3 to --workers 1 on systems with limited RAM. Then restart:
sudo service pgadmin4 restart
Conclusion
You now have pgAdmin4 running on FreeBSD 14 in a production-ready setup – served through gunicorn, proxied behind Nginx with SSL, and managed by an rc.d service. From here you can manage all your PostgreSQL 17 databases, run queries, handle backups and restores, and connect to remote servers across your infrastructure. pgAdmin4 handles the bulk of daily DBA tasks through its web interface, which saves time compared to working strictly from the command line. Keep an eye on the pgAdmin4 logs at /var/log/pgadmin4/ and update regularly with pip install --upgrade pgadmin4 inside the virtual environment to stay current with security patches and new features.