PostgreSQL 18 pgAdmin

In the Azure Portal, go to Marketplace, search for PostgreSQL 18 pgAdmin, and select the SecureAnyCloud publisher entry. Click Create.

On the Basics tab, choose your subscription, target resource group and region, and set the virtual machine name. Choose SSH public key as the authentication type, enter a username, and paste your SSH public key. Standard_D2s_v3 is the recommended minimum size for PostgreSQL 18 with pgAdmin. For production workloads with significant query load or many concurrent connections, start with Standard_D4s_v3 or larger.

On the Disks tab, select Standard SSD for the OS disk. PostgreSQL data lives under /var/lib/postgresql/18/main. If you expect to store more than 20 GB of data, attach a separate Premium SSD data disk now, and configure the tablespace to use it after the server is running.

On the Networking tab, select your existing virtual network and subnet. Attach a network security group that opens TCP 22 from your management IP range and TCP 80 from the client IPs that need pgAdmin access. Do not expose port 80 to the entire public internet unless you have added TLS termination as described in Step 14.

On the Management, Monitoring and Advanced tabs, the defaults are appropriate. Click Review + create, wait for validation to pass, and then click Create. Deployment takes around two to three minutes.

To deploy from the command line, accept the Marketplace terms once, and then deploy from the Marketplace image URN:

RG="postgresql-prod" LOCATION="eastus" VM_NAME="postgresql-01" ADMIN_USER="azureuser" IMAGE_URN="sac1647283583153:postgresql-18-pgadmin-ubuntu-24-04:standard:latest" SSH_KEY="$(cat ~/.ssh/id_rsa.pub)" az group create --name "$RG" --location "$LOCATION" az network vnet create \
  --resource-group "$RG" \
  --name "${VM_NAME}-vnet" \
  --address-prefix "10.0.0.0/16" \
  --subnet-name default \
  --subnet-prefix "10.0.1.0/24" az network nsg create \
  --resource-group "$RG" \
  --name "${VM_NAME}-nsg" az network nsg rule create \
  --resource-group "$RG" \
  --nsg-name "${VM_NAME}-nsg" \
  --name AllowSSH \
  --priority 100 \
  --protocol Tcp \
  --destination-port-ranges 22 az network nsg rule create \
  --resource-group "$RG" \
  --nsg-name "${VM_NAME}-nsg" \
  --name AllowHTTP \
  --priority 110 \
  --protocol Tcp \
  --destination-port-ranges 80
# Accept the Marketplace terms once per subscription (required before the first CLI deploy)
az vm image terms accept --urn sac1647283583153:postgresql-18-pgadmin-ubuntu-24-04:standard:latest
az vm create \
  --resource-group "$RG" \
  --name "$VM_NAME" \
  --image "$IMAGE_URN" \
  --size Standard_D2s_v3 \
  --admin-username "$ADMIN_USER" \
  --ssh-key-value "$SSH_KEY" \
  --vnet-name "${VM_NAME}-vnet" \
  --subnet default \
  --nsg "${VM_NAME}-nsg" \
  --storage-sku StandardSSD_LRS \
  --public-ip-sku Standard

The --storage-sku StandardSSD_LRS flag selects a Standard SSD OS disk. This tier is the right choice when the database itself is on an attached data disk, because the default Premium SSD costs more than the OS disk needs.

After deployment finishes, note the public IP address from the Azure Portal or from az vm show. Connect to the virtual machine over SSH:

ssh azureuser@<your-vm-public-ip>

On first boot, a one-time initialisation service named postgresql-firstboot.service runs automatically. It generates unique passwords for the PostgreSQL postgres role and the pgAdmin 4 admin account, and writes them to a credentials file. The service typically completes within 15 seconds of the virtual machine reaching the login prompt. Read the credentials file:

sudo cat /root/postgresql-credentials.txt

The output looks like this:

# PostgreSQL 18 + pgAdmin 4 — generated on first boot
# These credentials are unique to this VM. Store them somewhere safe.
# pgAdmin 4 web console (use this to manage PostgreSQL via a browser) pgadmin.url=http://10.0.1.10/pgadmin4/ pgadmin.email=admin@example.com pgadmin.password=<unique-password>
# PostgreSQL 18 (use this from psql or any client driver) postgres.host=127.0.0.1 postgres.port=5432 postgres.role=postgres postgres.password=<unique-password> postgres.database=postgres

Save these credentials. Only root can read the credentials file (0600), and it never leaves the virtual machine.

Verify that PostgreSQL 18 is running and confirm the installed version. The pg_lsclusters command lists the cluster status:

sudo -u postgres psql -c 'SELECT version();' pg_lsclusters systemctl is-active postgresql apache2

The output confirms that PostgreSQL 18.3 is running on port 5432, the cluster is online, and both the postgresql and apache2 services are active.

Open http://<your-vm-public-ip>/pgadmin4/ in a browser. Apache redirects the root URL, so http://<your-vm-public-ip>/ also lands on the pgAdmin 4 login page.

Enter the Email Address and Password from the credentials file (pgadmin.email and pgadmin.password), and click Login.

After you log in, the pgAdmin 4 dashboard opens. The Object Explorer panel on the left shows a Servers group. The local PostgreSQL 18 instance is pre-registered in it as PostgreSQL 18 (localhost).

In the Object Explorer, click the expand arrow next to Servers. pgAdmin prompts for the PostgreSQL role password to connect to the pre-registered server.

Enter the postgres.password value from your credentials file and click OK.

pgAdmin connects to the PostgreSQL 18 instance and expands the server tree to show the Databases, Login/Group Roles and Tablespaces nodes. The Dashboard tab on the right updates to show live server activity charts for sessions, transactions per second, tuples and block I/O.

The server is now connected. You can browse databases, run queries, manage roles and inspect server statistics entirely through the browser.

Using pgAdmin 4: In the Object Explorer, expand Servers > PostgreSQL 18 (localhost) > Databases. Right-click Databases and select Create > Database. Enter a name in the Database field, optionally set an owner, and click Save. The new database appears in the tree immediately.

Using psql: Connect over SSH and run:

sudo -u postgres psql

Then, in the psql session, run:

CREATE DATABASE myapp; \l \q

The \l command lists all databases including the one you just created. The \q command exits psql.

Do not connect applications directly as the postgres superuser. Create a dedicated role for each application:

CREATE ROLE appuser WITH LOGIN PASSWORD 'choose-a-strong-password'; GRANT ALL PRIVILEGES ON DATABASE myapp TO appuser; \q

To verify that the role was created, list the roles:

\du

The command lists all roles with their attributes. The new appuser role appears with the Login attribute set.

To open a query window in pgAdmin, expand the Servers tree and click the database you want to query. Then click the Query Tool button in the toolbar at the top, or select Tools > Query Tool.

Type a query in the editor pane and press F5 to run it, or use the Execute/Refresh button (the play triangle). Results appear in the data output panel below the editor.

For example, to confirm the current PostgreSQL version from inside pgAdmin, run:

SELECT version();

To list all tables in the public schema of your database, run:

SELECT tablename FROM pg_tables WHERE schemaname = 'public';

You can use the PostgreSQL role password from the credentials file in any connection string. From the virtual machine itself, run:

PGPASSWORD='<postgres.password>' psql \
  "postgresql://postgres@127.0.0.1:5432/postgres" \
  -c 'SELECT current_database(), current_user;'

From an application on the same virtual network, using the private IP:

postgresql://postgres:<password>@<private-ip>:5432/myapp

PostgreSQL 18 accepts remote connections from the same virtual network by default. Connections from outside the virtual network need NSG rule changes and pg_hba.conf updates, as described in Step 11.

The virtual machine has the full PostgreSQL 18 server package installed, which includes psql and all server utilities. You do not need to install a client on the VM.

For a separate application server that needs only the client libraries, run:

sudo apt-get install -y postgresql-client-18

For language-specific drivers, use psycopg2 or psycopg3 for Python, pg for Node.js, the JDBC driver published at https://jdbc.postgresql.org for Java, and pgx for Go.

By default, PostgreSQL 18 listens on 127.0.0.1 only. To accept connections from other hosts on the virtual network, make the following configuration changes.

1. Edit postgresql.conf to listen on all interfaces:

sudo nano /etc/postgresql/18/main/postgresql.conf

Find the listen_addresses line, uncomment it if necessary, and set it to:

listen_addresses = '*'

2. Edit pg_hba.conf to allow the virtual network CIDR:

sudo nano /etc/postgresql/18/main/pg_hba.conf

Add a line for your virtual network. For example, if your subnet is 10.0.1.0/24, add:

host all all 10.0.1.0/24 scram-sha-256

3. Reload PostgreSQL to apply both changes:

sudo systemctl reload postgresql

Keep this CIDR as narrow as possible. Do not use 0.0.0.0/0 unless you have TLS and a strong firewall policy in place.

Back up a single database with pg_dump:

sudo -u postgres pg_dump myapp > /tmp/myapp-backup.sql

To compress the backup:

sudo -u postgres pg_dump -Fc myapp > /tmp/myapp-backup.dump

Restore from a plain SQL dump:

sudo -u postgres psql myapp < /tmp/myapp-backup.sql

Restore from a custom-format dump:

sudo -u postgres pg_restore -d myapp /tmp/myapp-backup.dump

Back up all databases with pg_dumpall:

sudo -u postgres pg_dumpall > /tmp/all-databases.sql

For production workloads, schedule pg_dump as a cron job and copy the output to Azure Blob Storage with azcopy or the Azure CLI.

PostgreSQL writes its logs to /var/log/postgresql/. The main log file for the running cluster is:

/var/log/postgresql/postgresql-18-main.log

To follow the log in real time:

sudo tail -f /var/log/postgresql/postgresql-18-main.log

To check the current log level and logging configuration, run:

sudo -u postgres psql -c "SHOW log_min_messages;"
sudo -u postgres psql -c "SHOW logging_collector;"

The pgAdmin 4 Logs tab under the server node also shows recent log entries in the browser.

Take the following steps before you expose PostgreSQL 18 or pgAdmin 4 to public traffic.

Change the postgres role password. The firstboot service generates a unique random password, but rotate it immediately to one you generate yourself:

sudo -u postgres psql -c "ALTER ROLE postgres WITH PASSWORD 'your-new-password';"

Update the postgres.password value in any applications that use it, and then delete the credentials file:

sudo rm /root/postgresql-credentials.txt

Create application-specific roles. Follow the principle of least privilege. Create a role per application, grant it only the permissions it needs, and never hard-code the postgres superuser password in application code.

Restrict pg_hba.conf entries. Use the most specific CIDR possible for each host entry. Prefer scram-sha-256 over md5 for all new entries. Review and remove any trust entries.

Enable TLS for pgAdmin 4. Apache 2 is already installed. To add TLS, obtain a certificate (Let's Encrypt Certbot works on Ubuntu 24.04), install mod_ssl, and redirect port 80 to 443:

sudo apt-get install -y certbot python3-certbot-apache
sudo certbot --apache -d <your-domain>

The Certbot Apache plugin writes the SSL virtual host configuration and enables automatic renewal.

Enable TLS for PostgreSQL. PostgreSQL 18 supports native TLS on the server socket. Place a certificate and key at the paths set in ssl_cert_file and ssl_key_file in postgresql.conf, and then set ssl = on. The PGDG package ships with a self-signed certificate that activates TLS immediately. Replace it with a CA-signed certificate before you accept external connections.

Restrict NSG rules. Review your network security group after deployment. Allow port 22 only from your known management IP range. Allow port 80 (or 443 after TLS) only from the browsers and application servers that need access. Do not open PostgreSQL port 5432 in the NSG unless you specifically need direct TCP access from outside the virtual network.

To apply Ubuntu 24.04 security updates, run:

sudo apt-get update
sudo apt-get upgrade -y

To upgrade PostgreSQL to a newer minor version (for example from 18.3 to a later 18.x release), run:

sudo apt-get update
sudo apt-get install -y postgresql-18
sudo systemctl restart postgresql

PostgreSQL minor version upgrades (18.x to 18.y) do not require a cluster dump and restore. Major version upgrades (18 to 19) require pg_upgrade or a dump-restore cycle.