PostgreSQL 17 pgAdmin

In the Azure Portal, open the Marketplace, search for PostgreSQL 17 pgAdmin, and select the SecureAnyCloud publisher entry. Select 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 17 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/17/main. If you expect to store more than 20 GB of data, attach a separate Premium SSD data disk now. 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.

The defaults on the Management, Monitoring and Advanced tabs are appropriate. Select Review + create, wait for validation to pass, then select Create. Deployment takes around two to three minutes.

Accept the Marketplace terms once, then deploy from the Marketplace image URN:

RG="postgresql-prod" LOCATION="eastus" VM_NAME="postgresql-01" ADMIN_USER="azureuser" IMAGE_URN="sac1647283583153:postgresql-17-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-17-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, which is the right tier for a database server. The default Premium SSD costs more than the OS disk needs when the database itself is on an attached data disk.

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, then 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 17 + 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 17 (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 17 is running, and confirm the installed version with the pg_lsclusters command:

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

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

Open a browser and go to http://<your-vm-public-ip>/pgadmin4/. 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), then select Login.

The pgAdmin 4 dashboard opens. The Object Explorer panel on the left shows a Servers group, with the local PostgreSQL 17 instance pre-registered as PostgreSQL 17 (localhost).

In the Object Explorer, select 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 select OK.

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

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 17 (localhost) > Databases. Right-click Databases and select Create > Database. Enter a name in the Database field, optionally set an owner, and select 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, run:

\du

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

To open a query window, expand the Servers tree and select the database you want to query. Then select the Query Tool button in the toolbar at the top, or choose 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, confirm the current PostgreSQL version from inside pgAdmin:

SELECT version();

List all tables in the public schema of your database:

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, use the private IP address:

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

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

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

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

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

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

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

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

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

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

listen_addresses = '*'

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

sudo nano /etc/postgresql/17/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

Restrict this CIDR as tightly 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, use the custom format:

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-17-main.log

Follow the log in real time:

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

Check the current log level and logging configuration:

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 these steps before you expose PostgreSQL 17 or pgAdmin 4 to public traffic.

Change the postgres role password. The first-boot service generates a unique random password. 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 your applications, 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 the minimum 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 the file 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 automatically and enables automatic renewal.

Enable TLS for PostgreSQL. PostgreSQL 17 supports native TLS on the server socket. Place a certificate and key at the paths specified by ssl_cert_file and ssl_key_file in postgresql.conf, 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. Port 22 should allow only your known management IP range. Port 80 (or 443 after you add TLS) should allow only the browsers and application servers that need access. Keep PostgreSQL port 5432 closed in the NSG unless you specifically need direct TCP access from outside the virtual network.

Apply Ubuntu 24.04 security updates:

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

Upgrade PostgreSQL minor versions (for example, from 17.x to a future 17.y release):

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

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