PostgreSQL with pgAdmin 4 on AWS

This guide covers connecting to PostgreSQL and using the pgAdmin 4 web interface on an EC2 instance launched from the cloudimg AWS Marketplace AMI. The AMI ships PostgreSQL 18 and pgAdmin 4 preinstalled. On the first boot a one-shot service generates a unique set of credentials for that instance.

Prerequisites

• An EC2 instance launched from the cloudimg PostgreSQL with pgAdmin 4 Marketplace AMI.
• The instance's security group allows inbound TCP 22 (SSH) and TCP 80 (HTTP).
• Your .pem key file downloaded at launch time.

Connecting to your instance

ssh -i /path/to/key.pem ec2-user@<instance-public-ip>

Retrieving generated credentials

On first boot a systemd service generates a unique PostgreSQL superuser password and pgAdmin 4 admin password, then writes them to /root/postgresql-credentials.txt. Only root can read the file.

sudo cat /root/postgresql-credentials.txt

Expected output:

# PostgreSQL 18 + pgAdmin 4 — generated on first boot
# These credentials are unique to this VM. Store them somewhere safe.

# PostgreSQL 18 superuser
postgresql.superuser=postgres
postgresql.superuser.password=x8rTLN1UQfUFEVi5C6rWduTJPofE2IO7

# pgAdmin 4 web console
pgadmin.admin.email=admin@example.com
pgadmin.admin.password=sHntLKWILHOHCzs1EV99A1!
pgadmin.url=http://172.31.90.184/pgadmin4

# Quick check:
#   PGPASSWORD=x8rTLN1UQfUFEVi5C6rWduTJPofE2IO7 psql -U postgres -h 127.0.0.1 -c 'SELECT version();'

Checking service status

/usr/pgsql-18/bin/psql --version

psql (PostgreSQL) 18.4

systemctl status postgresql-18 httpd

* postgresql-18.service - PostgreSQL 18 database server
     Loaded: loaded (/usr/lib/systemd/system/postgresql-18.service; enabled)
     Active: active (running) since Wed 2026-05-27 19:54:28 UTC
   Main PID: 3108 (/usr/pgsql-18/bin/postgres -D /var/lib/pgsql/18/data/)

* httpd.service - The Apache HTTP Server
     Loaded: loaded (/usr/lib/systemd/system/httpd.service; enabled)
     Active: active (running) since Wed 2026-05-27 19:55:23 UTC
     Status: "Total requests: 86; Idle/Busy workers 100/0"

pgAdmin 4 web interface

pgAdmin 4 is served by Apache httpd at /pgadmin4/. Browse to:

http://<instance-public-ip>/pgadmin4/

You will be redirected from the instance root URL automatically.

Sign in with the pgadmin.admin.email and pgadmin.admin.password values from /root/postgresql-credentials.txt.

The local PostgreSQL 18 server is pre-registered in pgAdmin. Expand Servers > PostgreSQL 18 (localhost) in the object browser on the left and enter the postgresql.superuser.password when prompted. From there you can browse schemas, run queries in the Query Tool, and monitor server activity in the Dashboard.

Connecting with psql

Use the PostgreSQL superuser password from the credentials file:

PGPASSWORD=<postgresql.superuser.password> psql -U postgres -h 127.0.0.1 -c 'SELECT version();'

                                                  version                                                  
-----------------------------------------------------------------------------------------------------------
 PostgreSQL 18.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 11.5.0 20240719 (Red Hat 11.5.0-14), 64-bit
(1 row)

You can also switch to the postgres OS user for local peer authentication (opens an interactive psql session):

sudo -u postgres psql

Creating a database and user

Connect to psql as the postgres superuser and create an application database and a dedicated user:

sudo -u postgres /usr/pgsql-18/bin/psql -c "CREATE DATABASE myapp;"
sudo -u postgres /usr/pgsql-18/bin/psql -c "CREATE USER appuser WITH PASSWORD 'securepass123';"
sudo -u postgres /usr/pgsql-18/bin/psql -c "GRANT ALL PRIVILEGES ON DATABASE myapp TO appuser;"
sudo -u postgres /usr/pgsql-18/bin/psql -l

                                                     List of databases
   Name    |  Owner   | Encoding | Locale Provider |   Collate   |    Ctype    | Locale | ICU Rules |   Access privileges   
-----------+----------+----------+-----------------+-------------+-------------+--------+-----------+-----------------------
 myapp     | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |        |           | =Tc/postgres         +
           |          |          |                 |             |             |        |           | postgres=CTc/postgres+
           |          |          |                 |             |             |        |           | appuser=CTc/postgres
 postgres  | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |        |           | 
 template0 | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |        |           | =c/postgres          +
           |          |          |                 |             |             |        |           | postgres=CTc/postgres
 template1 | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |        |           | =c/postgres          +
           |          |          |                 |             |             |        |           | postgres=CTc/postgres
(4 rows)

Backing up a database with pg_dump

Create a compressed binary dump of a database:

sudo -u postgres /usr/pgsql-18/bin/pg_dump postgres -Fc -f /tmp/postgres_backup.dump

Restore with pg_restore:

sudo -u postgres /usr/pgsql-18/bin/pg_restore -d myapp -Fc /tmp/myapp_backup.dump

For a plain SQL dump:

sudo -u postgres /usr/pgsql-18/bin/pg_dump myapp > /tmp/myapp_backup.sql

Data volume

The PostgreSQL data directory /var/lib/pgsql/18/data lives on a dedicated 30 GiB gp3 EBS volume mounted at /var/lib/pgsql. This volume is independently resizable — to expand it, increase the EBS volume size in the AWS console, then:

sudo xfs_growfs /var/lib/pgsql

or for ext4 (replace vol-YOURVOLID with your EBS volume ID from the AWS console):

sudo resize2fs /dev/$(lsblk -dno NAME,SERIAL | awk -v s=$(echo vol-YOURVOLID | tr -d -) '$2==s{print $1}')

Enabling HTTPS

To serve pgAdmin over HTTPS, install Certbot and configure an SSL virtual host in Apache, or place an Application Load Balancer with an ACM certificate in front of the instance. Update /etc/httpd/conf.d/000-cloudimg-redirect.conf to redirect HTTP to HTTPS after obtaining a certificate.

Screenshots

The pgAdmin 4 web interface login page served by Apache httpd, accessible at the instance IP address with no manual setup.

The pgAdmin 4 object browser showing the pre-registered local PostgreSQL 18 server and database tree.

Terminal output showing psql version and the running status of postgresql-18 and httpd systemd services.

Support

24/7 technical support is available from cloudimg for this image. Contact details are on the cloudimg website.