Running pgweb on Amazon EC2

Pgweb is a simple lightweight web-based client for PostgreSQL.

This is a guide on installing and setting up pgweb on Amazon EC2 (Ubuntu 20.04.4 LTS machine).

Requirement:

1. An Amazon EC2 instance

Installation

SSH into your instance and update your system packages. sudo apt update && sudo apt upgrade -y

Download and install pgweb.

curl -s https://api.github.com/repos/sosedoff/pgweb/releases/latest \
  | grep linux_amd64.zip \
  | grep download \
  | cut -d '"' -f 4 \
  | wget -qi - \
  && unzip pgweb_linux_amd64.zip \
  && rm pgweb_linux_amd64.zip \
  && mv pgweb_linux_amd64 /usr/local/bin/pgweb

Check installation by running;

pgweb -v

  Output
  Pgweb v0.11.11 (git: db2a7a8aa5bc449e4efa78cada9c76c3fe33bc39) (go: go1.17.6) (build time: 2022-03-30T04:36:12Z)

Usage

Start server with pgweb.

By default pgweb runs on 127.0.0.1, localhost on port 8081. For Amazon EC2, bind pgweb to 0.0.0.0 to be able to access pgweb via the instance's public IP/DNS.

pgweb --bind 0.0.0.0

Open your-instance-public-ip:8081 in your browser to access pgweb.

To connect to a database at start of pgweb you can add the connection flags;

pgweb --bind 0.0.0.0 --host db-host-endpoint --user dbuser --db dbname

pgweb also supports url scheme connection;

pgweb --bind 0.0.0.0 --url postgres://dbuser:password@host:port/database

See more CLI options/flags.

Extend into a service(daemon)

This lets systemd manage pgweb and can be monitored via systemctl.

Create a file pgweb.service in /etc/systemd/system folder.

sudo vi /etc/systemd/system/pgweb.service

Copy and paste the code below;

[Unit]
Description=pgweb
Wants=
After=network.target

[Service]
User=ubuntu
Type=simple
ExecStart=/usr/local/bin/pgweb --bind 0.0.0.0 --sessions
Restart=on-failure
RestartSec=5s

[Install]
WantedBy=multi-user.target

Note the --sessions flag on ExecStart=/usr/local/bin/pgweb --bind 0.0.0.0 --sessions is to enable multiple database sessions.

You can also get/clone this repo and move pgweb.service file to /etc/systemd/system folder.

sudo mv pgweb.service /etc/systemd/system

Reload services to include the new service.

sudo systemctl daemon-reload

Start the service and check the status.

sudo systemctl start pgweb.service
sudo systemctl status pgweb.service

If all goes through, enable the service to start at boot;

sudo systemctl enable pgweb.service