FreeBSD

Install pgAdmin4 on FreeBSD 14

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.

Original content from computingforgeeks.com - post 162592

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:

  1. Right-click Servers in the left panel and select Register > Server
  2. On the General tab, give it a name – something like “FreeBSD Local PG17”
  3. 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
  4. 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:

  1. Right-click the database in the tree and select Backup
  2. Choose a filename and location for the backup
  3. Select the format – Custom (.backup) is recommended for most cases since it supports selective restore
  4. Under Dump options, you can choose to include or exclude data, schemas, privileges, and more
  5. Click Backup and monitor progress in the process watcher

To restore a database:

  1. Create a new empty database (or use an existing target)
  2. Right-click the target database and select Restore
  3. Select the backup file
  4. Configure restore options – you can choose specific schemas or tables to restore
  5. 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.

Keep reading

Run Linux VMs on FreeBSD 15 with bhyve FreeBSD Run Linux VMs on FreeBSD 15 with bhyve Install Python 3 and pip on FreeBSD 15 FreeBSD Install Python 3 and pip on FreeBSD 15 How To Install and Configure OPNSense Firewall FreeBSD How To Install and Configure OPNSense Firewall Install Postfix + Dovecot Mail Server on FreeBSD 15 FreeBSD Install Postfix + Dovecot Mail Server on FreeBSD 15 ZFS Snapshots, Send/Recv, and Disaster Recovery on FreeBSD 15 FreeBSD ZFS Snapshots, Send/Recv, and Disaster Recovery on FreeBSD 15 Install Zabbix 7.0 LTS Server on FreeBSD 15 FreeBSD Install Zabbix 7.0 LTS Server on FreeBSD 15

Press ESC to close