PostgreSQL 18 pgAdmin on Rocky 9

Open Marketplace in the Azure Portal, search for PostgreSQL 18 pgAdmin Rocky, 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, set the user name to a name of your choice, and paste your SSH public key. Standard_D2s_v3 is the recommended minimum size for PostgreSQL 18 with pgAdmin. For 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 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. 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-rocky-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-rocky-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. This tier suits the OS disk of a database server whose data is on an attached data disk, and it costs less than the default Premium SSD.

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 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 only by root (0600) and never leaves the virtual machine.

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 with the local PostgreSQL 18 instance pre-registered 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 connected. You can browse databases, run queries, manage roles and inspect server statistics entirely in the browser.

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

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;"

To 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, select the database you want to query, then select the Query Tool button in the toolbar at the top. You can also select Tools, then 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 PostgreSQL version from inside pgAdmin:

SELECT version();

To 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:

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, using 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 Step 11. Connections from outside the virtual network also require NSG rule changes and pg_hba.conf updates, as described there.

The virtual machine 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 Rocky Linux 9 or RHEL 9 that needs only the client libraries:

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, Python uses psycopg2 or psycopg3, Node.js uses pg, Java uses the JDBC driver published at https://jdbc.postgresql.org, and Go uses pgx.

By default, PostgreSQL 18 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 vi /var/lib/pgsql/18/data/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 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:

host all all 10.0.1.0/24 scram-sha-256

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

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

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 /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 regular backups, schedule pg_dump as a cron job and copy the output to Azure Blob Storage with azcopy or the Azure CLI.

With the PGDG packaging on Rocky Linux 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 log level and logging configuration:

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 firstboot service generates a unique random password. Replace it with 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 any applications that use it, 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 narrowest CIDR possible for each host entry. Prefer scram-sha-256 over md5 for all new entries, and review 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 Rocky Linux 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 and sets up automatic renewal.

Enable TLS for PostgreSQL. PostgreSQL 18 supports native TLS on the server socket. Place a certificate and key at the paths given 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 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.

To apply Rocky Linux 9 security updates:

sudo dnf upgrade -y --refresh

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

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 and restore.