PostgreSQL 18 pgAdmin on Alma 9

In the Azure Portal, open Marketplace and search for PostgreSQL 18 pgAdmin AlmaLinux. Select the SecureAnyCloud publisher entry, then select Create.

On the Basics tab, choose your subscription, target resource group and region, and set the VM name. Choose SSH public key as the authentication type, enter a username of your choice, 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/pgsql/18/data. If you expect to store more than 20 GB of data, attach a separate Premium SSD data disk now. After the server is running, configure the tablespace to use it.

On the Networking tab, select your existing virtual network and subnet. Attach an NSG 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 the Security Hardening step.

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

To deploy from the command line, 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-18-pgadmin-alma-9:default: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-alma-9:default: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 necessary for the OS disk when the database itself is on an attached data disk.

When deployment finishes, note the public IP address from the Azure Portal or from az vm show. Connect to the VM 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 VM 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.11/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. The credentials file is readable by root only (0600) and never leaves the VM.

Verify that PostgreSQL 18 is running, and confirm the installed version with the psql client and systemd:

sudo -u postgres /usr/pgsql-18/bin/psql -c 'SELECT version();' systemctl is-active postgresql-18 httpd

The output confirms that PostgreSQL 18.3 is running on port 5432 and that both the postgresql-18 and httpd 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.

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 there as PostgreSQL 18 (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 18 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.

The server is now fully 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 select Save. The new database appears in the tree immediately.

Using psql: Connect over SSH and run:

sudo -u postgres /usr/pgsql-18/bin/psql -c "CREATE DATABASE myapp;"
sudo -u postgres /usr/pgsql-18/bin/psql -c "\l"

The \l command lists all databases, including the one you just created.

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

sudo -u postgres /usr/pgsql-18/bin/psql -c "CREATE ROLE appuser WITH LOGIN PASSWORD 'choose-a-strong-password';"
sudo -u postgres /usr/pgsql-18/bin/psql -c "GRANT ALL PRIVILEGES ON DATABASE myapp TO appuser;"

Verify that the role was created:

sudo -u postgres /usr/pgsql-18/bin/psql -c "\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 select the database you want to query. Then select 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 VM itself:

PGPASSWORD='<postgres.password>' /usr/pgsql-18/bin/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:

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

PostgreSQL 18 accepts remote connections from the same virtual network only after you make the configuration changes in the Allow Remote Connections step. Connections from outside the virtual network also require NSG rule changes and pg_hba.conf updates, as described there.

The VM has the full PostgreSQL 18 server package installed, which includes psql at /usr/pgsql-18/bin/psql and all server utilities. You do not need to install a client on the VM.

For a separate application server on AlmaLinux 9 or RHEL 9 that needs only the client libraries, run:

sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm
sudo dnf install -y postgresql18

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 three changes.

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

sudo vi /var/lib/pgsql/18/data/postgresql.conf

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

listen_addresses = '*'

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

sudo vi /var/lib/pgsql/18/data/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

Part 3. Open the PostgreSQL port on the OS firewall, and reload PostgreSQL to apply both configuration changes:

sudo firewall-cmd --permanent --add-port=5432/tcp sudo firewall-cmd --reload sudo systemctl reload postgresql-18

Restrict the 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 /usr/pgsql-18/bin/pg_dump myapp > /tmp/myapp-backup.sql

To compress the backup:

sudo -u postgres /usr/pgsql-18/bin/pg_dump -Fc myapp > /tmp/myapp-backup.dump

Restore from a plain SQL dump:

sudo -u postgres /usr/pgsql-18/bin/psql myapp < /tmp/myapp-backup.sql

Restore from a custom-format dump:

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

Back up all databases with pg_dumpall:

sudo -u postgres /usr/pgsql-18/bin/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.

With PGDG packaging on AlmaLinux 9, PostgreSQL writes logs to per-day rotated files under the data directory:

/var/lib/pgsql/18/data/log/

To list and follow the most recent log file:

sudo ls -lt /var/lib/pgsql/18/data/log/ | head -5

sudo tail -f /var/lib/pgsql/18/data/log/postgresql-$(date +%a).log

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

sudo -u postgres /usr/pgsql-18/bin/psql -c "SHOW log_min_messages;"
sudo -u postgres /usr/pgsql-18/bin/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 18 or pgAdmin 4 to public traffic.

Change the postgres role password. The first-boot service generates a unique random password, but rotate it right away to one you generate yourself:

sudo -u postgres /usr/pgsql-18/bin/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 httpd is already installed. To add TLS, obtain a certificate (Let's Encrypt Certbot works on AlmaLinux 9), install mod_ssl, and redirect port 80 to 443:

sudo dnf install -y certbot python3-certbot-apache mod_ssl
sudo certbot --apache -d <your-domain>
sudo firewall-cmd --permanent --add-service=https sudo firewall-cmd --reload

The Certbot Apache plugin writes the SSL virtual host configuration automatically 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 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 NSG after deployment. Port 22 should allow only your known management IP range. Port 80 (or 443 after TLS) should allow only 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 AlmaLinux 9 security updates:

sudo dnf upgrade -y --refresh

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

sudo dnf upgrade -y --refresh postgresql18-server postgresql18 postgresql18-contrib
sudo systemctl restart postgresql-18

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.