Percona PostgreSQL

Prerequisites

• An active Azure subscription, an SSH public key, and a VNet with a subnet in the target region
• A subscription to this listing on Azure Marketplace
• A network security group (NSG) that allows TCP 22 for administration and TCP 5432 for database clients. In production, restrict 5432 to your application subnet rather than leaving it open to the internet.

Recommended virtual machine size: Standard_B2s (2 vCPU, 4 GB RAM) for development and light workloads. The shipped configuration is tuned for that size.

For production throughput, choose a larger size such as Standard_E2s_v5 or above. Raise shared_buffers and effective_cache_size in /etc/postgresql/18/main/conf.d/10-sac.conf to match.

Deploy the virtual machine

Create the VM from the image, and open SSH and the database port to your own network:

# Accept the Marketplace terms once per subscription (required before the first CLI deploy)
az vm image terms accept --urn sac1647283583153:percona-postgresql-ubuntu-24-04:default:latest
az vm create \
  --resource-group my-rg \
  --name percona-pg-1 \
  --image sac1647283583153:percona-postgresql-ubuntu-24-04:default:latest \
  --size Standard_B2s \
  --admin-username azureuser \
  --generate-ssh-keys \
  --public-ip-sku Standard
az vm open-port --resource-group my-rg --name percona-pg-1 --port 5432 --priority 900

First boot takes roughly thirty seconds longer than a plain VM. The cluster is created from scratch at that point rather than shipped inside the image.

Retrieve your per-VM credentials

On first boot, the VM generates its own superuser password and TLS certificate. SSH in and read the root-only credentials file:

sudo cat /root/percona-postgresql-credentials.txt

The file is mode 0600 and owned by root. It lists the host, port, role, per-VM password, default database and required SSL mode, along with ready-to-paste connection strings. Nothing is baked into the image, so every deployed VM has its own password and its own certificate.

Confirm the service is healthy

Check that PostgreSQL is active and listening, that the cluster runs on the dedicated data volume, and that TLS is enabled:

sudo systemctl is-active postgresql@18-main.service
sudo ss -tln | grep ':5432' pg_lsclusters df -h /var/lib/percona-postgresql
sudo -u postgres psql -tAc 'SHOW ssl;'

You should see:

• the service reporting active
• PostgreSQL listening on 5432
• the cluster online, with its data directory at /var/lib/percona-postgresql/18/main
• that path mounted on its own 40 GB volume
• ssl reported as on

Connect to the database

On the VM, administration needs no password. It runs through the local Unix socket, where pg_hba.conf uses peer authentication:

sudo -u postgres psql -d appdb -c "SELECT current_database(), current_user;"

To exercise the same path your applications will use, read the per-VM password from the credentials file and connect over TCP with TLS required:

PGPASSWORD="$(sudo sed -n 's/^postgres.password=//p' /root/percona-postgresql-credentials.txt)" \
  psql "host=127.0.0.1 port=5432 dbname=appdb user=postgres sslmode=require" \
  -c "SELECT version();" \
  -c "SELECT ssl, version AS tls, cipher FROM pg_stat_ssl WHERE pid = pg_backend_pid();"

The version banner confirms that you are running Percona Server for PostgreSQL. The pg_stat_ssl row confirms that the connection is encrypted (ssl is t) and reports the negotiated TLS version and cipher. A wrong password on the same path is refused outright.

From a remote client, connect over TLS with the per-VM password from the credentials file. Replace <vm-ip> with your VM's address and <POSTGRES_PASSWORD> with the value from the file:

PGPASSWORD=<POSTGRES_PASSWORD> psql "host=<vm-ip> port=5432 dbname=appdb user=postgres sslmode=require" -c "SELECT now();"

Use the Percona extension set

The distribution's extensions are already installed and preloaded. List them, then read live query telemetry from pg_stat_monitor:

sudo -u postgres psql -d appdb -c '\dx'
sudo -u postgres psql -d appdb -c "SELECT left(query,60) AS query, calls, round(mean_exec_time::numeric,2) AS mean_ms FROM pg_stat_monitor WHERE query IS NOT NULL ORDER BY calls DESC LIMIT 10;"
sudo -u postgres psql -d appdb -c 'SHOW shared_preload_libraries;'
sudo -u postgres psql -d appdb -c 'SHOW pgaudit.log;'

pg_stat_monitor is Percona's replacement for pg_stat_statements. It aggregates statistics into time buckets and records client, plan and histogram detail alongside the usual call counts and timings. You can see not just which statements are slow, but when they were slow and from where.

pgaudit.log ships set to ddl, role. This records schema changes and role or grant changes in the PostgreSQL log without the volume of full statement auditing. Raise it to write or all if your compliance regime requires it.

The demo table seeded on first boot proves that the cluster is live end to end. Read it, and drop it when you no longer need it:

sudo -u postgres psql -d appdb -c "SELECT * FROM sac_demo;"

pg_repack and wal2json are installed but not enabled, because both change runtime behaviour and should be a deliberate choice. Enable either one when you need it:

sudo -u postgres psql -d appdb -c "CREATE EXTENSION IF NOT EXISTS pg_repack;"

Create your application role and database

Do not point your application at the postgres superuser. Create a dedicated role and database for it, and choose your own password:

sudo -u postgres psql -c "CREATE ROLE appuser LOGIN PASSWORD 'choose-a-strong-password';"
sudo -u postgres psql -c "CREATE DATABASE myapp OWNER appuser;"

The new role authenticates exactly like the superuser: scram-sha-256 over TLS from remote clients. It is covered by the same pg_hba.conf rules with no further configuration.

Security posture

No baked credential, and no baked database. The captured image ships an empty data directory. The cluster, the postgres password and the TLS certificate are all created on your first boot and written to /root/percona-postgresql-credentials.txt (mode 0600, root only).

The database cannot start before it is initialised. postgresql@18-main.service is enabled so that it survives every reboot, but it carries ConditionPathExists=/var/lib/sac/data/percona-postgresql-ready. First boot writes that marker only after the credentials file exists, so systemd refuses to serve an uninitialised cluster. Ordering alone would not give that guarantee.

No trust authentication anywhere. pg_hba.conf uses peer on the local Unix socket, scram-sha-256 on loopback TCP, and hostssl ... scram-sha-256 for every remote address. A plaintext remote connection is refused before authentication is attempted.

Per-VM TLS. Each VM mints its own self-signed certificate on first boot, so no certificate material is shared between deployments. To use a certificate from your own authority, point ssl_cert_file and ssl_key_file in /etc/postgresql/18/main/conf.d/10-sac.conf at your files and reload.

Network. Expose only TCP 22 and TCP 5432 in your NSG, and restrict 5432 to your application subnet. The NSG is the first layer of defence, TLS the second and the per-VM password the third.

To rotate the superuser password later, set a new one and update the credentials file to match:

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

Operations

PostgreSQL runs under systemd. Check its state with:

sudo systemctl is-active postgresql@18-main.service systemctl show postgresql@18-main.service -p Id,ActiveState,SubState,MainPID

Inspect logs with sudo journalctl -u postgresql@18-main.service --no-pager | tail -20, or read the cluster log directly at /var/log/postgresql/postgresql-18-main.log. The first-boot log is at /var/log/sac/sac-firstboot.log.

Configuration lives in /etc/postgresql/18/main/. SecureAnyCloud's tuning is isolated in conf.d/10-sac.conf so that it survives package upgrades.

Edit that file, then reload with sudo systemctl reload postgresql@18-main.service for most settings. Restart the service instead for shared_buffers and shared_preload_libraries.

Take a logical backup of a database with pg_dump:

sudo -u postgres pg_dump appdb > /var/tmp/appdb-backup.sql ls -lh /var/tmp/appdb-backup.sql

pg_dump writes schema and data only. Avoid pg_dumpall for routine backups unless you intend to capture role passwords as well. Treat any file it produces as a secret.

To grow the database volume, resize the data disk in the Azure portal, then extend the filesystem with sudo resize2fs /dev/disk/azure/scsi1/lun0.

Support

Every SecureAnyCloud image includes 24/7 support. If you have any questions about this Percona Server for PostgreSQL image, contact us at sales@secureanycloud.com.

Deploy on Azure

Launch Percona Server for PostgreSQL 18 on Ubuntu 24.04 on Azure with 24/7 support from SecureAnyCloud.

View this image

Need Help?

Our support team is available 24/7.

sales@secureanycloud.com